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

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.

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.

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

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

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

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

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

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

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

• Next, go to Data > Advanced.

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

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

Steps:

• Type the below formula in Cell D5 and hit Enter.

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

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

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

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

Steps:

• Type the below formula in Cell B15.

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

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

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

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

• 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).

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

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

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

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

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

• 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

• How to Remove Blank Lines in Excel
• How to Ignore Blank Cells in Range in Excel
• How to Leave Cell Blank If There Is No Data in Excel
• How to Delete Blank Cells and Shift Data Left in Excel

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