How to Delete Empty Cells in Excel (6 Methods)

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:

dataset for 6 Methods to Delete the Empty Cells in Excel

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:

🔗 Steps:

❶ Select the empty cells that you want to delete.

❷ Then press CTRL + – to delete them.

Delete Empty Cells Manually in Excel


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:

🔗 Steps:

❶ Select the entire data table first.

usage of go to special

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

❸ Click on the Special option.

go to dialog box in excel

❹ Now select Blanks from the list and hit Ok.

Go To Special to Delete Blank Cells in Excel

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:

🔗 Steps:

❶ Select the whole dataset.

find command usage in excel

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

Use Find Command to Remove Empty Cells in Excel

Now you’ve got all the empty cells identified.

❼ Finally, press CTRL + – to delete all of them.


Similar Readings:


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.

🔗 Steps:

❶ Select the whole data table.

❷ Go to Data ▶ Filter.

Delete Blank Cells in Excel Using AutoFilter

❸ Click on any one of the dropdown icons.

❹ Select Blanks and hit Ok.

 in Excel Usage of AutoFilter to Delete Blank Cells

Now you’ve got all the filtered empty cells.

auto filter in excel

❺ 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

=""
within another cell to refer to the empty cells.

advanced filter criteria

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

🔗 Steps:

❶ Select the whole data table.

❷ Go to Data ▶ Sort & Filter ▶ Advanced.

Remove Empty Cells in Excel Using Advanced Filter

After all these steps, the Advanced Filter dialog box will appear. From the box,

❸ Set List range as:

$B$4:$E$13

Criteria range as:

$G$4:$G$5

❺ Then hit Ok.

Remove Empty Cells in Excel Using Advanced Filter criteria dialog box

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:

🔗 Steps:

❶ Select cell G5 to insert the formula.

❷ Type the formula

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

❸ Hit the ENTER button.

Use the Filter Function to Delete Blank Cells in Excel

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:

filter dynamic array function


Things to Remember

📌 You can press the CTRL + – keys together to delete cells.

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


Further Readings

Mrinmoy

Mrinmoy

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

ExcelDemy
Logo