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

Certainly, to delete empty cells is quite a common and basic task that we might need to do frequently in Excel. Luckily, there are multiple ways available in Excel that enable us to remove empty cells in Excel. In this regard, we will learn 8 distinct methods that you can use to delete empty cells in Excel from this article with ease.

Before going to the next part, let’s have a quick look at the brief about the methods we are going to use to delete empty cells in Excel.

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


Download Practice Workbook

You are recommended to download the Excel file and practice along with it.


8 Methods to Delete the Empty Cells in Excel

We will be using a sample student mark sheet as our dataset to demonstrate all the methods throughout the article. Now, let’s have a sneak peek of the data table:

Dataset showing student mark sheet of different courses

You can see that there are some empty cells in that dataset that we want to remove. We will try to demonstrate 8 unique methods for this. So, without having any further discussion let’s get into all the methods one by one.


1. Deleting Empty Cells with Keyboard Shortcut

We can delete empty cells by selecting them all and then applying the delete command manually. You can follow the steps below to get the whole procedure in detail:

🔗 Steps:

  • First, select the empty cells you want to delete.
  • Then press CTRL and (Minus) to delete them.

Press (CTRL and -) keys to delete empty cells

  • Then, select an option according to your need from the dialog box.

Selecting shift cells up option while deleting cells

Hence, 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)


2. Using Go To Special to Remove Blank Cells 

You can use the Go To Special feature to find the empty cells first in Excel and then delete them. In this regard, here are the steps to follow:

🔗 Steps:

  • First, select the entire data table first.

Select data range in Excel

  • Then, press CTRL + G to open up the Go To dialog box.
  • Here, click on the Special option.

Select Special in Go To pop up box

  • Now select Blanks from the list and hit OK.

Select Blanks on Go To Special dialog box

 

After all of these steps, you’ve selected all the blank cells within the data table.

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

  • Now press CTRL + – to delete those empty cells and you will get the result.

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


Similar Readings


3. Utilizing Find and Replace Command

We can use the Find and Replace command instead of using the Go To Special feature to find empty cells. After identifying the empty cells, we can easily delete those cells. Now follow the steps below:

🔗 Steps:

  • First, select the whole dataset.

Select the whole dataset

  • Then, press CTRL+F to open up the Find and Replace dialog box.
  • Here, leave the Find what box empty.
  • Next, mark ticks on the Match entire cell contents.
  • After that select the Find All option.
  • Finally, 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 keysNow you’ve got all the empty cells identified.

Choosing Empty Cells and deleting them

  • Finally, press (CTRL and ) to delete all of them like Method 1.

Read More: Find If Cell is Blank in Excel (7 Methods)


4. Erasing Blank Cells Using Filter Option

There’s an amazing feature embedded in Excel that can assist you to find and delete the blank cells. Follow the steps below to see how you can use the feature to remove empty cells in Excel.

🔗 Steps:

  • First, select the whole data table.
  • Then, go to Data Filter.

Select the dataset and apply the Filter option

  • Next, click on any one of the dropdown icons.
  • Here, select Blanks and hit OK.

Find blank cells with Filter option in order to delete them

Now you’ve got all the filtered empty cells.

Selecting and deleting empty cells

  • Press (CTRL and) to delete the filtered empty cells and check the other names to display them.

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


Similar Readings


5. Employing Advanced Filter to Remove Empty Cells 

For using the Advanced Filter option, we need to set up the criteria first. To set up criteria,

Firstly, we need to specify one of the table headers in a different cell.

Secondly, We need to insert the following text within another cell to refer to the empty cells

=""

Setting up Criteria for applying Advanced Filter to delete blank cells

As we are done with setting up criteria, we can move to the next steps:

🔗 Steps:

  • First of all, select the whole data table.
  • Now, go to Data Sort & Filter Advanced.

Apply Advanced Filter feature

  • After that, the Advanced Filter dialog box will appear. From the box,

➤ Set List range as:

$B$4:$E$13

➤ Set Criteria range as:

$G$4:$G$5

  • Then hit OK.

Advanced Filter dialog box entering list and criteria ranges

As soon as you are done with all the previous steps, the corresponding rows will be hidden and you will get your data table without the empty cells as follows:

Results after removal of empty cells with advanced filter

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


6. Applying FILTER Function to Delete Blank Cells in Excel

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

You can use this function only either in Excel Online or Office 365.

Now follow to steps below to see how to use this function:

🔗 Steps:

  • First, select cell G5 to insert the formula.
  • Then type the following formula:

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

Apply FILTER function to delete empty cells in Excel

  • Now, hit the ENTER button.

Now, since the FILTER function is a dynamic array function, just after pressing the ENTER button, it will automatically cover its necessary spaces to store all the data excluding the empty cells as follows:

FILTER function has deleted empty cells in Excel

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


7. Implementing Sort Option to Delete Empty Cells

Besides, let’s see how we can implement the Sort feature of Excel to get rid of empty cells.

🔗 Steps:

  • First of all, select the data range > then go to the Data tab > and select Sort A to Z option under Sort & Filter group.

Apply Sort Filter to arrange data in ascending way

Hence, Excel will arrange the data in an ascension way and you will get the blank cells at the bottom

Identify blank cells to them from Excel

After that, just select them and delete them manually like Method 1.


8. Delete Empty Cells with Power Query

For one thing, Power Query is a very powerful tool in Excel as we can use it for enormous purposes. We will apply this tool to delete empty cells.

🔗 Steps:

  • First, select the data range > then go to the Data tab > select From Table/Range option.

Import data to From Table/Range option

  • Here, the Create Table dialog box will appear on the sheet. Here, keep the My table has headers option marked and click OK.

Create Table dialog box

  • As a result, the Power Query Editor window will appear on the worksheet.
  • Here, go to the Home tab > then click the dropdown of Reduce Rows > click Remove Rows > select Remove Blank Rows.

Remove Blank Rows in the Power Query Editor

  • Now, the empty rows will no longer be available.
  • After that, click Close & Load

Load the Power Query data to Excel worksheet

As a result, you will find that the data without empty rows is now available in the Excel worksheet.

Final output after deleting empty cells applying power query


How to Delete Blank Cells in a Range with VBA Code in Excel

Till now we have learned the ways to delete empty cells in several ways. You might be wondering what to do if you want to delete blank cells in a range with VBA code.

Don’t worry. Let’s hack the steps for doing this task.

🔗 Steps:

  • First, select the range of data,

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

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

Insert Module window to apply VBA code

  • After that, the Module window will appear. Enter the following code in that window.

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

In this section, we’ll explain the VBA code used to delete empty cells in Excel.

  • First, name the sub-routine Deleting_Empty_Rows().
  • Next, use the With statement to specify the range of cells, here it is the B5:E13 cells.
  • Afterward, 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

  • Next, press F5 to run the code. So, now you will get an output like the image below without any blank cells in the selected range.

Deleted blank cells in a range with VBA code


Things to Remember

📌 First of all, you can press the CTRL + – keys together to delete cells.

📌 Secondly, press CTRL + G to open up the Go To dialog box.


Conclusion

To sum up, we have discussed 6 methods to delete empty cells in Excel. Now, we recommend you download the practice workbook attached along with this article and practice all the methods with that. And, don’t hesitate to ask any questions in the comment section below, since we try to respond to all the relevant queries asap. Additionally, make sure to visit our website exceldemy to explore more.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo