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.


Download Practice Workbook

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


9 Methods to Remove Blank Cells from a Range in Excel

Let’s assume, I have a data range (B4:E12) containing sales data of 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 Fill Blank Cells in Excel with Go To Special (With 3 Examples)


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 at 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

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


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

Read More: How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas


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 reply:

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

Related Content: How to Remove Blanks from List Using Formula in Excel (4 Methods)


5. Blank Cells Removing from a Horizontal Range List

Unlike the previous method, now I will remove empty cells from a horizontal range of data. This time too, I will use the 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 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}

Read More: VBA to Count Blank Cells in Range in Excel (3 Methods)


Similar Readings


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 Ignore Blank Cells in Range in Excel (8 Ways)


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

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)


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.

Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)


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 Autofill Blank Cells in Excel with Value Above (5 Easy Ways)


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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo