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

Get FREE Advanced Excel Exercises with Solutions!

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.

What is Advanced Filter in Excel


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.

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

  • Then, from Excel Ribbon, go to Data > 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.


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.

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.


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo