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.


Download Practice Workbook

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


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.

What is Advanced Filter in Excel


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

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.

Remove Empty Cells from One Column Using  Excel Advanced Filter

Steps:

  • Type the below formula in Cell F6.
=D5<>""

Remove Empty Cells from One Column Using  Excel Advanced Filter

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

Remove Empty Cells from One Column Using  Excel Advanced Filter

Remove Empty Cells from One Column Using  Excel 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.

Remove Empty Cells from One Column Using  Excel Advanced Filter

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

Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel


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 AND function. Let’s consider the below dataset for the current method. Here we have some blank cells spread over columns D and E.

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

Steps:

  • First, type the below formula in Cell G6 and hit Enter.
=AND(D5<>"",E5<>"")

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

  • Consequently, we will get the below result.

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

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

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

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

Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)


Similar Readings:


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.

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

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

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

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

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

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

Read More: Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)


Conclusion

In the above article, I have tried to discuss several examples to exclude blank cells using Advanced Filter 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