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.

Overview of the article on how to delete empty cells in Excel with Excel function and features


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.

Press (CTRL and -) keys to delete empty cells

  • Select an option as required from the dialog box.

Selecting shift cells up option while deleting cells

Excel will delete the empty cells from the worksheet.

Results after deleting empty cells in Excel with keyboard shortcut

Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)


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.

Select data range in Excel

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

Select Special in Go To pop up box

  • Select Blanks from the list and hit OK.

Select Blanks on Go To Special dialog box

 

All the blank cells within the data table are selected.

Selecting empty Cells in Excel to delete them with CTRL and minus keys

  • Press (CTRL and).

The empty cells are deleted.

Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)


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.

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.

Find the Empty Cells with the Find and Replace Command and select all with CTRL + A keysAll the empty cells are identified.

Choosing Empty Cells and deleting them

  • 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.

Select the dataset and apply the Filter option

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

Find blank cells with Filter option in order to delete them

All the filtered empty cells are selected.

Selecting and deleting empty cells

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

Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)


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:

=""

Setting up Criteria for applying Advanced Filter to delete blank cells

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

Steps:

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

Apply Advanced Filter feature

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.

Advanced Filter dialog box entering list and criteria ranges

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

Results after removal of empty cells with advanced filter

Read More: How to Remove Blank Lines in Excel (8 Easy Ways)


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<>""))

Apply FILTER function to delete empty cells in Excel

  • Press ENTER.

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

FILTER function has deleted empty cells in Excel

Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)


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.

Apply Sort Filter to arrange data in ascending way

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

Identify blank cells to them from Excel

  • 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.

Import data to From Table/Range option

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

Create Table dialog box

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.

Remove Blank Rows in the Power Query Editor

  • Click Close & Load.

Load the Power Query data to Excel worksheet

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

Final output after deleting empty cells applying power query


Using VBA Code to Delete Blank Cells in a Range

Steps:

  • Select the range of data.

select data range to delete blank cells in Excel with VBA macro

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

Insert Module window to apply VBA code

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.

Apply VBA code to delete empty cells in a range in Excel

  • Press F5 to run the code.

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

Deleted blank cells in a range with VBA code


Things to Remember

Press the CTRL and keys together to delete cells.

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


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo