How to Remove Blank Cells in Excel (10 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Blank cells inside the dataset sometimes become disturbing. These also create difficulties in the calculation. There are numerous methods to remove blank cells in Excel. In this article, we are going to know about them with explanations and examples. In the picture below, you can see the overview of the result that we will get after removing blank cells from a data set.

The overview image of removing blank cells in excel


How to Remove Blank Cells in Excel: 10 Quick Ways

In this section, I will describe 10 simple and quick methods to remove blank cells in Excel. Let’s explore the methods one by one.


1. Removing Blank Cells Manually in Excel

We can manually remove blank cells. Assuming we have a dataset of the Customer’s payment history with a lot of blank cells.

Remove Blank Cells Manually in Excel

STEPS:

  • First, select all the blank cells by pressing the Ctrl key from the keyboard.

Selecting Blank Cells Manually in Excel

  • Next, right-click on the mouse and select Delete.

Selecting Delete option from context menu

  • Or we can simply go to  Home > Cells > Delete.

Opening Delete options from the ribbon

  • Now we can see a small window. Select the required option and click OK.

deleting ENtire row

  • Finally, we can get the result.

Results after removing blank cells

Read More: How to Delete Blank Cells and Shift Data Up in Excel


2. Using ‘Go To Special’ Feature to Delete Excel Blank Cells

Removing blank cells from a huge dataset is quite difficult if we try manually. ‘Go to Special’ can play an important role here. Let’s say we have a Customer’s payment history dataset.

Using ‘Go To Special’ Feature to Delete Excel Blank Cells

STEPS:

  • Firstly, select the whole range containing blank cells at first.
  • Secondly, go to Home > Editing.
  • Thirdly, from the Find & Select drop-down click ‘Go To Special’.

Using ‘Go To Special’ Feature to Delete Excel Blank Cells

  • Consequently, we can see a small window pop up.
  • Then, select the Blanks option and click OK.

Using ‘Go To Special’ Feature to Delete Excel Blank Cells

  • Here we can see all the selected adjacent & non-adjacent blank cells.

selection of blank cell by Go to Special feature

  • Now, go to Home > Delete > Delete Sheet Rows.

Opening Delete Option from the ribbon

  • After clicking that, we can see the final result.

Results after deleting blank cells

Read More: How to Remove Blank Cells from a Range in Excel


3. Use of Keyboard Shortcut to Erase Blank Cells in Excel

The keyboard shortcut is another easy way to remove blank cells. To know more, follow the steps below.

STEPS:

  • Select all the blank cells from the range.
  • Now press the ‘Ctrl + ’ keys for the result.

4. Remove Empty Cells with Find Command

The Find command is an Excel built-in option. Here we are going to use it in a dataset of the Customer’s payment history with blank cells.

Remove Empty Cells with Find Command

STEPS:

  • First, select the whole data range from the worksheet.
  • Now, in the Home tab, select Editing.
  • Then, go to Find & Select > Find. We can also press the Ctrl+F keys to open the Find menu window.

choosing Find Option from ribbon

  • In this window, click Options to see the advanced search criteria.
  • Next, keep the Find what box blank.
  • After that, select Sheet from the Within the drop-down box.
  • We have to make sure that the ‘Match entire cell contents’ box is ticked.
  • Then select Values from the Look in the drop-down box.
  • Click on Find All.

Opening Find and replace Dialoge box

  • Here, we can see all the blank cells. According to our dataset, there are 8 blank cells.
  • Now, press Ctrl + A to select them all and select Close to omit the window.

Selecting all the finds

  • Now, go to Home > Delete > Delete Sheet Rows.

Opening delete Option from rib

  • At last, we can see the output.

Results after deleting blank


5. Use of Filter Option for Removing Blank Cells

An in-built option Filter can help us to find the blank cells from the below dataset and remove them.

Use of Filter Option for Removing Blank Cells

STEPS:

  • First, select the whole dataset.
  • Next, go to the Home tab.
  • Click Sort & Filter > Filter.

Use of Filter Option for Removing Blank Cells

  • We can see the filter toggle in each column.
  • Select one of them.
  • From the drop-down, uncheck Select All & check Blanks.
  • Press OK.

Use of Filter Option for Removing Blank Cells

  • Now we can see the filtered blank cells.

Use of Filter Option for Removing Blank Cells

  • Select the cells without the Header and delete them manually.

  • Again click on the filter toggle.
  • Click on Select All and select OK.

Selecting all from the Folter dialogue box

  • In the end, we can filter data without blank cells.


6. Use of Advanced Filters to Remove Blank Cells in Excel

Sometimes we can use the Advanced filter with a condition for removing blank cells in Excel. From the bellow dataset, we are going to remove all the blank Date cells. For this, we need to take some initial steps. First, select the criterion cell C14. Here we type “<>”. Also, we need to insert the total header where we want to see the result.

Use of Advanced Filters to Remove Blank Cells in Excel

Now, follow the steps below.

STEPS:

  • Firstly, select the entire dataset.
  • Then, go to Data > Advanced.

Use of Advanced Filters to Remove Blank Cells in Excel

  • Consequently, a small advanced filter window pops up.
  • Now, insert the list and criteria ranges, and where to copy. Also, select the option to copy another cell.
  • Press OK.

Inserting the parameters in Advanced Filter Dialogue box

  • Finally, we can see the result in cell range B16:E16.

result after applying advanced filter for removing blank cells


7. Use of the Sort Option to Delete Excel Blank Cells

We can remove Excel blank cells by sorting them. Assuming we have a dataset like the previous methods.

Use the Sort Option to Delete Excel Blank Cells

Now follow the steps below to remove blank cells using the Sort option.

STEPS:

  • Firstly, select the data range.
  • Secondly, go to the Data tab.
  • Now, from the Sort & Filter section, select the ascending or descending Sort command.

Use the Sort Option to Delete Excel Blank Cells

  • Now, we see that all the blank cells are at the end of the dataset.

Sorted data after so sorting data

  • Select the blank cells and delete them manually to see how the dataset looks.

Result after deleting blank image


8. Insertion of FILTER Function to Remove Blank Excel Cells

In an Excel Table, we can use the FILTER function. It’s a dynamic array function. Let’s say we have a data table of the Customer’s payment history in the B4:E11 range. We are going to remove the blank cells and show the result in Cell B14 by filtering the data according to the Amount row.

Insert FILTER Function to Remove Blank Excel Cells

STEPS:

  • Firstly, select Cell B14.
  • Secondly, type the formula:
=FILTER(Table1,Table1[Amount]<>"","")

Using FILTER Function to Filter out data

  • Now, hit Enter to see the result.

Results after using FILTER Function


9. Erase Blank Cells After the Last Used Cell with Data

To remove the formatting of blank cells of the given data set after the last used cell with data, we can follow these steps.

Data set with smae formatted blank cells

STEPS:

  • Firstly, select the first blank cell of the header.
  • Secondly, press Ctrl + Shift + End to select the range of the cells between the last used cells with data and the current data.

selecting the blank cells

  • Thirdly, go to Home > Delete >  Delete Sheet Columns.

Choosing Delete Sheet Columns option

  • As a result, you will see that the blank columns have been deleted.

the blank columns have been deleted.

Read More: How to Remove Unused Cells in Excel


10. Use of Power Query to Remove Empty Cells in Excel

 Power Query is an Excel Business Intelligence tool. We are going to use this powerful tool for removing blank row cells. Here is our data table.

Use of Power Query to Remove Empty Cells in Excel

STEPS:

  • Firstly, select any cell in the table.
  • Then to add data to the Power Query window, go to Data > From Table/Range.

Use of Power Query to Remove Empty Cells in Excel

  • Now, select the Home tab.
  • From the Remove Rows drop-down, click Remove Blank Rows.

Removing Blank Rows from Power Query Editor

  • Then, for creating a new table without blank rows, click the Close & Load option.

Choosing Close & Load to option

  • Lastly, we can see the new table. We also can replace this data with the original one but it’s optional.

Results after loading the Power Query in a sheet


Practice Workbook

Download the following workbook and exercise.


Conclusion

By using these methods, we can easily remove blank cells in Excel. Moreover, there is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Excel Remove Blank Cells: Knowledge Hub


<< Go Back to Excel Blank Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo