How to Delete Empty Cells in Excel (8 Easy Methods)

In this tutorial, we’ll be covering some easy ways to delete empty cells in Excel. We’ll use the following dataset (a sample student mark sheet) and methods.

Method 1 – Using Keyboard Shortcuts

We can simply select the cells we want to delete and remove them manually.

Steps:

Select the empty cells you want to delete.
Press CTRL and – (Minus) to delete them.

Select an option as required from the dialog box.

Excel will delete the empty cells from the worksheet.

Method 2 – Using Go To Special

You can use the Go To Special feature to first find the empty cells and then delete them.

Steps:

Select the entire data table.

Press CTRL + G to open up the Go To dialog box.
Click on the Special option.

Select Blanks from the list and hit OK.

All the blank cells within the data table are selected.

Press (CTRL and –).

The empty cells are deleted.

Similar Articles

Method 3 – Using Find and Replace Command

We can also use the Find and Replace command to find empty cells. Then we can easily delete them:

Steps:

Select the whole dataset.

Press CTRL+F to open the Find and Replace dialog box.
Here, leave the Find what box empty.
Tick Match entire cell contents.
Select the Find All option.
Press CTRL+A to select all the empty cells.

All the empty cells are identified.

Press (CTRL and –) to delete them all as in Method 1.

Method 4 – Using Filter Option

Steps:

Select the whole data table.
Go to Data ▶ Filter.

Click on any one of the dropdown icons.
Select Blanks and hit OK.

All the filtered empty cells are selected.

Press (CTRL and –) to delete the filtered empty cells.
To restore the whole dataset, repeat the process above, except untick Blanks this time.

Similar Articles

Method 5 – Using Advanced Filter

In order to use the Advanced Filter option, we need to set up some criteria first.

STEPS TO SET UP CRITERIA:

Specify one of the table headers in a different cell.
Insert the following text within another cell to refer to the empty cells:

=""

Our criteria are set up. Let’s delete some empty cells.

Steps:

Select the whole data table.
Go to Data ▶ Sort & Filter ▶ Advanced.

The Advanced Filter dialog box will appear.

Set List range as:

$B$4:$E$13

Set Criteria range as:

$G$4:$G$5

Click OK.

The corresponding rows will be hidden, and our data table without the empty cells is displayed.

Method 6 – Using FILTER Function

We can use a dynamic array function called the FILTER function to delete the empty cells in Excel. As this is a dynamic function, whenever we update our data in our data table, the filtered results get updated automatically too.

This function is only available in Excel Online or Office 365.

Steps:

Select cell G5 to insert the formula.
Enter the following formula:

=FILTER(B5:E13,(B5:B13<>"")*(C5:C13<>"")*(D5:D13<>"")*(E5:E13<>""))

Press ENTER.

Since the FILTER function is a dynamic array function, it will immediately generate a new table excluding the empty cells.

Method 7 – Using Sort Option

Steps:

Select the data range
Go to the Data tab.
Select Sort A to Z option under Sort & Filter group.

Excel will arrange the data in ascending order, leaving the blank cells at the bottom.

Simply select them and delete them manually like in Method 1.

Method 8 – Using Power Query

Steps:

Select the data range.
Go to the Data tab.
Select From Table/Range.

In the Create Table dialog box that opens, keep the My table has headers option marked and click OK.

The Power Query Editor window will appear on the worksheet.

Go to the Home tab.
Click the dropdown of Reduce Rows.
Click Remove Rows.
Select Remove Blank Rows.

Click Close & Load.

The data without empty rows is now displayed in the worksheet.

Using VBA Code to Delete Blank Cells in a Range

Steps:

Select the range of data.

Press ALT+F11 to open the Visual Basic Editor window.
Here, click Insert and select Module.

The Module window will appear.

Enter the following code:

Code:

Sub Deleting_Empty_Rows()

  With Range("B4:E13")

    If WorksheetFunction.CountA(.Cells) > 0 _

    Then .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp

End With

End Sub

💡 Code Explanation

Name the sub-routine Deleting_Empty_Rows().
Use the With statement to specify the range of cells, here it is B5:E13.
Use the If Then statement to check for blank cells with the help of the CountA function, and delete them.

Press F5 to run the code.

Output will look like the image below, without any blank cells in the selected range.

Things to Remember

Press the CTRL and – keys together to delete cells.

Press CTRL + G to open up the Go To dialog box.

Get FREE Advanced Excel Exercises with Solutions!

How to Delete Empty Cells in Excel (8 Easy Methods)

Method 1 – Using Keyboard Shortcuts

Method 2 – Using Go To Special

Method 3 – Using Find and Replace Command

Method 4 – Using Filter Option

Method 5 – Using Advanced Filter

Method 6 – Using FILTER Function

Method 7 – Using Sort Option

Method 8 – Using Power Query

Using VBA Code to Delete Blank Cells in a Range

Things to Remember

Related Articles

Leave a reply Cancel reply