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.

**Table of Contents**Expand

## 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**.

`=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))),"")`

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

`=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)),"")`

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

`=FILTER(Table1,Table1[Products]<>"","")`

- 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,is put in all blank cells. Now from the new window, follow the path:*null***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**