To delete empty cells is quite a common and basic task that we might need to do frequently in Excel. There are multiple ways available in Excel that enable us to remove empty cells in Excel. In this regard, we will learn 6 distinct methods that you can use to delete empty cells in Excel from this article with ease.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
6 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:
So, without having any further discussion let’s get into all the methods one by one.
1. Delete Empty Cells Manually in Excel
We can delete empty cells by selecting them all and then apply the delete command manually. You can follow the steps below to get the whole procedure in detail:
❶ Select the empty cells that you want to delete.
❷ Then press CTRL + – to delete them.
2. Use Go To Special to Delete Blank Cells in Excel
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:
❶ Select the entire data table first.
❷ Press CTRL + G to open up the Go To dialog box.
❸ 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.
3. Use Find Command to Remove Empty Cells in Excel
We can use the Find 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:
❶ Select the whole dataset.
❷ Press CTRL + F to open up the Find and Replace dialog box.
❸ Leave the Find what box empty.
❹ Mark ticks on the Match entire cell contents.
❺ Select the Find All option.
❻ Press CTRL + A to select all the empty cells.
Now you’ve got all the empty cells identified.
❼ Finally, press CTRL + – to delete all of them.
- How to Delete Blank Cells in Excel and Shift Data Up
- Remove Blank Cells in Excel (10 Easy Ways)
- How to Remove Blank Lines in Excel (8 Easy Ways)
4. Delete Blank Cells in Excel Using AutoFilter
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.
❶ Select the whole data table.
❷ Go to Data ▶ Filter.
❸ Click on any one of the dropdown icons.
❹ Select Blanks and hit Ok.
Now you’ve got all the filtered empty cells.
❺ Press CTRL + – to delete the filtered empty cells.
5. Remove Empty Cells in Excel Using Advanced Filter
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
As we are done with setting up criteria, we can move to the next steps:
❶ Select the whole data table.
❷ Go to Data ▶ Sort & Filter ▶ Advanced.
After all these steps, the Advanced Filter dialog box will appear. From the box,
❸ Set List range as:
❹ Criteria range as:
❺ Then hit Ok.
As soon as you are done with all the previous steps, you will get your data table without the empty cells as follows:
6. Use the 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.
💡 Caution: 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:
❶ Select cell G5 to insert the formula.
❷ Type the formula
❸ Hit the ENTER button.
As 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:
Things to Remember
📌 You can press the CTRL + – keys together to delete cells.
📌 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. You are recommended to 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. We will try to respond to all the relevant queries asap. And please visit our website exceldemy to explore more.