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

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

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

- 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

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

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

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

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

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

**Excel VBA: Check If Multiple Cells Are Empty (9 Examples)****Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)****How to Fill Blank Cells with 0 in Excel (3 Methods)****Apply Conditional Formatting in Excel If Another Cell Is Blank****Null vs Blank in Excel**

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

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

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

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

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

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