How to Make Empty Cells Blank in Excel (3 Methods)

The sample dataset below will be used for illustration purpose. It contains some products in column B and the total sales of each product in column C. We can see that cell C6 and cell C9 have no data. Those cells seem blank but they are not. Those cells are empty with some spaces. We need to make those empty cells blank.

Method 1 – Make Empty Cells Blank Using Excel VBA

STEPS:

• Go to the Developer tab from the ribbon.
• Click on Visual Basic and this will open the Visual Basic Editor or press Alt + F11.

• Another way to open the Visual Basic Editor is right-clicking on the sheet and selecting View Code.

• Enter the following VBA code below.

VBA Code:

Sub EmptyCells_Blank()
Dim myRange As Range
Set myRange = Selection
For Each cell In myRange
cell.Value = Trim(cell)
Next
End Sub

• Press F5 key to run the code or click on the Run Sub button.

• This will make all the empty cells blank.

Method 2 – Excel Filter Option to Make Empty Cells Blank

STEPS:

• Select the whole data range.
• Go to the Home tab on the ribbon.
• Click on the Sort & Filter drop-down menu.
• Select Filter.

• The data has drop-down lists. It means that the data is filtered.
• Click on the Sold drop-down list.
• Select the Blanks from the selection and click OK.

• The empty cells can be seen as the data is sorted and filtered.
• To make the empty cells blank, go to the Home tab.
• Click on the Clear drop-down menu bar under the Editing group.
• Select Clear Content.

• The cells are now blank.

• Right-click on the filtered data range, select Filter and select Clear Filter From “Sold”.

• Go to the Home tab, click on Find & Select and select Go To Special.

• The Go To Special dialog box will open.
• Select the Blanks and click OK.

• It will show the blank cells.

Method 3 – Manually Make Empty Cells Blank in Excel

STEPS:

• Click on the cell that looks blank but there is some space. We chose cell C6.

• Press Backspace.
• This will remove all the space and make the cell blank.

Download Practice Workbook

Related Articles

• How to Deal with Blank Cells That Are Not Really Blank in Excel
• Return Non Blank Cells from a Range in Excel
• Null vs Blank in Excel
• How to Highlight Blank Cells in Excel
• How to Set Cell to Blank in Formula in Excel
• Formula to Return Blank Cell instead of Zero in Excel

<< Go Back to Excel Cells | Learn Excel