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

Get FREE Advanced Excel Exercises with Solutions!

While working with a large range of datasets, we might see some unnecessary blank cells which are pretty annoying. Luckily, in Excel, there are several options available to delete these unwanted blank cells. So, this article will guide you on how you can remove blank cells from a data range in Excel.


How to Remove Blank Cells from a Range in Excel: 9 Methods

Let’s assume, I have a data range (B4:E12) containing sales data for several electronic products (Date-wise). Now, you can delete blank cells manually by selecting them one by one (see screenshot); which seems time-consuming when the data range is large. In this article, I will show you 9 methods to remove blank cells in Excel.

9 Methods to Remove Blank Cells from a Range in Excel


1. Excel ‘Go To Special’ Option to Delete Empty Cells from a Range

We can exclude empty cells from a range with the help of the Go To Special option.

Steps:

  • First, select the range (B4:E12) of data and press F5 or Ctrl + G to bring the Go To dialog box. Next press Special from the dialog box.

Excel ‘Go To Special’ Option to Delete Empty Cells from a Range

  • As a result, the Go To Special dialog box appears. Choose Blanks from the available options and press OK.

Excel ‘Go To Special’ Option to Delete Empty Cells from a Range

  • After you press OK, all the blank cells in the range are highlighted. Now, press Ctrl + – from the keyboard to bring up the Delete dialog. Then depending on your data and requirement, any of the delete options. I have chosen Shift cells up. Press OK again. This option will delete the blank cells and move non-empty cells up.

Excel ‘Go To Special’ Option to Delete Empty Cells from a Range

  • Consequently, here is our ultimate result.

Note:

  • Be careful while choosing the delete options from the Delete dialog. Choosing the wrong delete option will mess up your data range.
  • You can bring the Delete dialog by right-clicking on the selection or following the path: Home > Cells > Delete > Delete Cells.

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


2. Remove Blank Cells from a Range Using Filter Option

Now I will Filter the range for blank cells and later remove those cells.

Steps:

  • Select the range first and press Ctrl + Shift + L to apply Filter on it. As the Filter option is applied, the drop-down arrow will show up.

Remove Blank Cells from a Range Using Filter Option

  • Now, suppose, I will filter the 3rd column of the range (B5:E12) based on Date. To do that, click on the drop-down icon from the Date column, put a checkmark only on the Blanks option, and press OK.

Remove Blank Cells from a Range Using Filter Option

  • As a result, all the rows that contain the blank cells will be filtered. Now, select all the rows and right-click on the selection, click Delete Row.

Remove Blank Cells from a Range Using Filter Option

  • After that, the Microsoft Excel message box will ask for the confirmation of row deletion. Click OK.

Remove Blank Cells from a Range Using Filter Option

  • Then withdraw the filter by pressing Ctrl + Shift + L again. Finally, you will see all the blank cells is gone from the range


3. Apply Advanced Filter Feature to Remove Blank Cells from a Range

The Advanced Filter feature of Excel is an amazing way to exclude empty cells from a data range. Suppose, you want to delete the blank cells from two columns (Date and Sales) from our existing dataset. Let’s see how to do that by applying the Advanced Filter option.

Steps:

  • First,  type the not equal to (<>) symbol in Cell G5 and H5.

Apply Advanced Filter Feature to Remove Blank Cells from a Range

  • Next, go to Data > Advanced.

Apply Advanced Filter Feature to Remove Blank Cells from a Range

  • Subsequently, the Advanced Filter dialog shows up. Now from the box, choose Copy to another location, specify List range (B4:E12), Criteria range (G4:H5), Copy to (B4). Then press OK.

Apply Advanced Filter Feature to Remove Blank Cells from a Range

  • Upon entering OK, the range is filtered to another location as below (blank cells deleted).

Note:

  • Remember the header of the Criteria range (G4:H5) has to be similar to the parent dataset (B4:E12).

4. Remove Blank Cells from a Vertical Range

This time, I will use a combination of IFERROR, INDEX, SMALL, IF, MIN, ISBLANK, and ROW functions to delete blank cells present in a vertical range containing fruit names.

Remove Blank Cells from a Vertical Range

Steps:

  • Type the below formula in Cell D5 and hit Enter.
=IFERROR(INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),"",ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))),"")

Remove Blank Cells from a Vertical Range

  • Upon entering the formula, you will get the below result. Now drag down the Fill Handle (+) tool to get the ultimate result.

Remove Blank Cells from a Vertical Range

  • In the end, you will see that blank cells are excluded from the resulting range.

🔎 How Does the Formula Work?

  • ISBLANK($B$5:$B$12)

Here the ISBLANK function checks whether a cell is blank or not in the range B5:E12 and returns True or False.

  • ROW($B$5:$B$12)

Now, the ROW function returns the row numbers in range  B5:E12 and replies:

{5;6;7;8;9;10;11;12}

  • MIN(ROW($B$5:$B$12))

Then the MIN function finds the lowest row number in the range which is:

{5}

Later,

  • IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1)

The above formula returns:

{1;2;””;4;5;6;””;8}

After that,

  • SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))

Here, the SMALL function returns the k -th smallest value from the range and the formula replies:

{1}

Now comes the INDEX function,

  • INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1)))

The INDEX formula returns

{“Apple”}

Finally,

  • IFERROR(INDEX($B$5:$B$12,SMALL(IF(ISBLANK($B$5:$B$12),””,ROW($B$5:$B$12)-MIN(ROW($B$5:$B$12))+1), ROW(A1))),””)

The IFERROR function returns a blank if the INDEX formula returns an error.


5. Blank Cells Removing from a Horizontal Range List

Unlike the previous method, I will remove empty cells from a horizontal range of data. This time too, I will use a combination of Excel functions (IF, COLUMN, SUM, INDEX, and SMALL).

Steps:

  • Type the following formula in Cell B8.
=IF(COLUMN(B:B)<=SUM(--($B$5:$I$5<>""))+1,INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>"",COLUMN($B$5:$I$5)-1,""),COLUMN(B:B)-1)),"")

Blank Cells Removing from a Horizontal Range List

  • Once you hit Enter, the formula will give the below result. Drag the Fill Handle tool to the right to get the final output.

Blank Cells Removing from a Horizontal Range List

  • Finally, here is the ultimate result. All the blank cells are deleted from the above range.

🔎 How Does the Formula Work?

Let’s explain the first part of the formula which is:

  • COLUMN(B:B)<=SUM(–($B$5:$I$5<>””))+1

The above formula returns

{TRUE}

Where,

  • COLUMN(B:B)

The COLUMN function replies to column number of B:B which is:

{2}

Then.

  • $B$5:$I$5<>””

This will return:

{TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE}

Later,

  • SUM(–($B$5:$I$5<>””)

The SUM function sums up the count of TRUE values and replies:

{6}

Then come to the other part of the formula:

  • INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1))

The above formula returns:

{“Apple”}

Where,

  • IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””)

Here, the IF function checks whether $B$5:$I$5<>””, and replies accordingly:

{1,2,””,4,5,6,””,8}

Then,

  • SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1)

Later, the SMALL function returns the k-th smallest value from our data range which is:

{1}

Finally, here is the whole formula:

  • IF(COLUMN(B:B)<=SUM(–($B$5:$I$5<>””))+1,INDEX($B$5:$I$5,0,SMALL(IF($B$5:$I$5<>””,COLUMN($B$5:$I$5)-1,””),COLUMN(B:B)-1)),””)

The above formula returns:

{Apple}


6. Excel FILTER Function to Delete Empty Cells

If you are working in Excel 365, you can use the FILTER function to remove blank cells from an Excel range. To apply the function we will convert the data range (B4:E12) to an Excel table by pressing Ctrl + T.

Excel FILTER Function to Delete Empty Cells

Steps:

  • Type the below formula in Cell B15.
=FILTER(Table1,Table1[Products]<>"","")

Excel FILTER Function to Delete Empty Cells

  • Press Enter.
  • The above formula will result in an array (outlined in blue color) deleting blank cells from the first column (Products) of the above table.

Read More: How to Remove Blank Cells in Excel


7. Use Find Option to Remove Empty Cells from a Range in Excel

We can delete blank cells from a range very easily by using the Find option in Excel.

Steps:

  • First, select the range (B5:E12) of data. Then press Ctrl + F to bring the Find and Replace dialog. After the dialog appears, leave the Find what field blank, choose Values from the Look in the drop-down, put a checkmark on Match entire cell contents, and finally press Find All.

Use Find Option to Remove Empty Cells from a Range in Excel

  • Consequently, you will get a list containing the blank cells. Now select the whole output by holding the Ctrl key. Then go to Home > Cells >Delete > Delete Cells to bring the Delete dialog.

Use Find Option to Remove Empty Cells from a Range in Excel

  • After that, choose the delete option and press OK (see screenshot).

Use Find Option to Remove Empty Cells from a Range in Excel

  • As a result, here is the output I have received as I have chosen the Shift cells up delete option. Click on OK.

Use Find Option to Remove Empty Cells from a Range in Excel

  • In conclusion, press Close to end the process.


8. Remove Blank Cells from a Range Using Excel Sort Option

In this method, I will show you how to remove empty cells from a range using the Sort option in Excel.

Steps:

  • Select the range at first. Then go to Data > Sort & Filter > Sort A to Z icon (see screenshot).

Remove Blank Cells from a Range Using Excel Sort Option

  • As a result, the data range will be sorted as below. All the blank rows are listed at the end of the range.

Remove Blank Cells from a Range Using Excel Sort Option

  • Now press Ctrl + – from the keyboard to bring the Delete dialog. Choose the Delete Row option and press OK.

Remove Blank Cells from a Range Using Excel Sort Option

  • Lastly, here is the ultimate result. All the blank rows are deleted from our data range.


9. Excel Power Query to Delete Empty Cells

In this method, I will show you how to remove blank cells using Excel Power Query. Let’s explore the process of doing that. For the ease of my operation, I have converted my data range to a table by pressing Ctrl +T.

Steps:

  • Click anywhere in the table, go to Data > From Table/Range.

Excel Power Query to Delete Empty Cells

  • As a consequence, the table below will show up in the Power Query Editor window. Here, by default, null is put in all blank cells. Now from the new window, follow the path: Home > Remove Rows > Remove Blank Rows.

Excel Power Query to Delete Empty Cells

  • As a result, all the rows that were containing null are removed. Now to close the operation, go to Home > Close & Load > Close & Load.

Excel Power Query to Delete Empty Cells

  • In conclusion, the ultimate result will appear in a new sheet in Excel as below.

Read More: How to Remove Unused Cells in Excel


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to remove blank cells from a range in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles


<< Go Back to Remove Blank CellsBlank 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.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo