How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)

Sometimes, in Excel, large datasets contain blank cells randomly located, which may be required to be deleted later. However, it might seem time-consuming to detect each of the empty cells one by one and delete them. This tutorial will guide you on how to exclude these blank cells using Advanced Filter in Excel.

What is Advanced Filter in Excel?

We all are more or less familiar with the regular Filter option in Excel. But the regular Filter option has some limitations. To illustrate, data cannot be filtered to a different location or you cannot filter data based on complex criteria. Luckily, the Advanced Filter in excel solves all the abovementioned problems. Let’s assume we have a dataset (B5:D12) having several electronic products along with their corresponding state-wise sales. Below here, I have filtered sales data (>$7,000) for the state CA using Advanced Filter. Moreover, I have filtered the data to a new location.

How to Use Excel Advanced Filter to Exclude Blank Cells: 3 Easy Tricks

1. Remove Empty Cells from One Column Using  Excel Advanced Filter

Firstly, I will show you how to exclude blank cells from a particular column. Suppose I have some blank cells in column D (highlighted below). Now I will remove these empty cells.

Steps:

• Type the below formula in Cell F6.

• Once you hit Enter, the formula will give the below result.

• Then, from Excel Ribbon, go to Data > Advanced Filter.

• As a result, the Advanced Filter dialog box will appear. Choose Copy to another location from the Action section. Now, specify List range (B5:D12), Criteria range (F5:F6), and Copy to location (B14) as below. After that press OK.

• Finally, you will see that all the blank cells are excluded from the dataset, and here is our filtered data.

⏩ Note:

While selecting the Criteria range make sure you have selected a cell range (here, F5:F6). If you select only one cell, the filter won’t work as Excel demands a range here.

2. AND Function with Advanced Filter to Exclude Blank Cells from Multiple Columns

This time I will show you how to delete empty cells from multiple columns in Excel. To perform my task, I will take the help of the AND function. Let’s consider the below dataset for the current method. Here we have some blank cells spread over columns D and E.

Steps:

• First, type the below formula in Cell G6 and hit Enter.

• Consequently, we will get the below result.

• Now go to Data > Advanced Filter.
• Then specify the List range, Criteria range, Copy to location as below and press OK (see screenshot).

• Upon pressing OK, we will get the below output; excluding the empty cells in columns D and E.

⏩ Note: Using the AND function argument, you can add as many columns as you want to the formula and thus delete the blank cells.

3. Delete Empty Cells Using ‘<>’ Symbol Along with Advanced Filter in Excel

You can delete empty cells from multiple cells using a not equal to (<>) symbol and Advanced Filter. For instance, from my previous dataset (B5:E12), I will delete blank cells (highlighted in blue) based on two criteria (Date and Sales).

Steps:

• Firstly, type ‘<>’ in Cell G5 and H5.

• Next, go to Data > Advanced Filter.
• As a consequence, the Advanced Filter dialog will show up. Enter List range, Criteria range, and Copy to location as below. Then press OK.

• Finally, we will get all the blank cells excluded from the dataset.

⏩ Note:

• You can extract data cells that have blank cells using =”=” as criteria.

• While applying this method, make sure the headers of the Criteria range (Date, Sales) is exactly similar to the parent dataset (here B5:E12).

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 examples to exclude blank cells using Advanced Filter in Excel. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

<< Go Back to Advanced Filter | Filter in Excel | Learn Excel