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

What is Advanced Filter in Excel?

Most of us are familiar with the regular Filter option in Excel. However, the regular Filter has some limitations. For example, it doesn’t allow filtering data to a different location or filtering based on complex criteria. Fortunately, the Advanced Filter in Excel addresses these issues.

Let’s assume we have a dataset (B5:D12) containing several electronic products along with their corresponding state-wise sales. Below, I’ve filtered sales data (>$7,000) for the state of California (CA) using the Advanced Filter. Additionally, I’ve filtered the data to a new location.

What is Advanced Filter in Excel


Method 1 – Remove Empty Cells from One Column Using Excel Advanced Filter:

  • Suppose we have some blank cells in column D (highlighted below). We want to exclude 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

  • Press Enter to get the result.

Remove Empty Cells from One Column Using  Excel Advanced Filter

  • From the Excel Ribbon, go to Data > Advanced Filter.

Remove Empty Cells from One Column Using  Excel Advanced Filter

  • In the Advanced Filter dialog box, choose Copy to another location from the Action section.
  • Specify the following:
    • List range: B5:D12
    • Criteria range: F5:F6
    • Copy to location: B14
  • Press OK.

Remove Empty Cells from One Column Using  Excel Advanced Filter

  • You’ll see that all the blank cells are excluded from the dataset, resulting in our filtered data.

Note:

When selecting the Criteria range, ensure you choose a cell range (here, F5:F6). If you select only one cell, the filter won’t work as Excel requires a range.


Method 2 – Using the AND Function to Exclude Blank Cells from Multiple Columns:

  • Suppose we have blank cells spread over columns D and E (as shown below).

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

Steps:

  • First, type the following formula in Cell G6:
=AND(D5<>"",E5<>"")

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

  • Press Enter to get the result.

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

  • Go to Data > Advanced Filter.
  • Specify the following:
    • List range: B5:D12
    • Criteria range: G5:G6
    • Copy to location: B14
  • Press OK.

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

  • This will exclude the empty cells in columns D and E.

Note: You can add as many columns as needed to the formula using the AND function.


Method 3 – Deleting Empty Cells Using the ‘<>’ Symbol Along with Advanced Filter:

  • Suppose we want to delete blank cells based on two criteria (Date and Sales) from our previous dataset (B5:E12).

Steps:

  • Type ‘<>’ in Cell G5 and H5.

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

  • Go to Data > Advanced Filter.
  • Specify the following:
    • List range: B5:E12
    • Criteria range: G5:H5
    • Copy to location: B14
  • Press OK.

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

  • This will exclude all the blank cells from the dataset.

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

Note:

  • You can use as a criterion to extract data cells that have blank cells.

  • Make sure the headers in the Criteria range (Date, Sales) match those in the parent dataset (here B5:E12).

Download Practice Workbook

You can download the practice workbook from here:


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

Get FREE Advanced Excel Exercises with Solutions!
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