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.
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:
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:
- Then, select an option according to your need from the dialog box.
Hence, Excel will delete the empty cells from the worksheet.
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:
- First, select the entire data table first.
- Then, press CTRL + G to open up the Go To dialog box.
- Here, click on the Special option.
- Now select Blanks from the list and hit OK.
After all of these steps, you’ve selected all the blank cells within the data table.
- 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)
- How to Fill Blank Cells with 0 in Excel (3 Methods)
- If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
- VBA to Count Blank Cells in Range in Excel (3 Methods)
- Excel VBA: Find the Next Empty Cell in Range (4 Examples)
- How to Ignore Blank Cells in Range in Excel (8 Ways)
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:
- First, 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.
Now you’ve got all the empty cells identified.
- 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.
- First, select the whole data table.
- Then, go to Data ▶ Filter.
- Next, click on any one of the dropdown icons.
- Here, select Blanks and hit OK.
Now you’ve got all the filtered 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)
- How to Set Cell to Blank in Formula in Excel (6 Ways)
- Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
- How to Return Value if Cell is Blank (12 Ways)
- Find & Count If a Cell Is Not Blank (with Examples)
- How to Fill Blank Cells with Value Above in Excel (4 Easy Methods)
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
As we are done with setting up criteria, we can move to the next steps:
- First of all, select the whole data table.
- Now, go to Data ▶ Sort & Filter ▶ Advanced.
- After that, the Advanced Filter dialog box will appear. From the box,
➤ Set List range as:
➤ Set Criteria range as:
- Then hit OK.
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:
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.
Now follow to steps below to see how to use this function:
- First, select cell G5 to insert the formula.
- Then type the following formula:
- 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:
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.
- First of all, select the data range > then go to the Data tab > and select Sort A to Z option under Sort & Filter group.
Hence, Excel will arrange the data in an ascension way and you will get the blank cells at the bottom
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.
- First, select the data range > then go to the Data tab > select 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.
- 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.
- Now, the empty rows will no longer be available.
- After that, click Close & Load
As a result, you will find that the data without empty rows is now available in the Excel worksheet.
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.
- First, select the range of data,
- Then, press ALT+F11 to open the Visual Basic Editor window.
- Here, click Insert and select Module.
- After that, the Module window will appear. Enter the following code in that window.
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.
- 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.
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.
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.
- How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
- Fill Blank Cells in Excel with Go To Special (With 3 Examples)
- How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
- Fill Blank Cells with N/A in Excel (3 Easy Methods)
- How to Find and Replace Blank Cells in Excel (4 Methods)
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Make Empty Cells Blank in Excel (3 Methods)