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.

Dataset Overview

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.

Method 1 – Using Excel Advanced Filter

• Suppose we have some blank cells in column D (highlighted below). We want to exclude these empty cells.

Steps:

• Enter the below formula in Cell F6.

• Press Enter to get the result.

• From the Excel Ribbon, go to Data and select 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.

• 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

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

Steps:

• Enter the following formula in Cell G6:

• Press Enter to get the result.

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

• 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 – Using the ‘<>’ Symbol Along with the Advanced Filter

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

Steps:

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

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

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

⏩ 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