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.

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.

Steps:

• Enter the below formula in Cell F6.

• Press Enter to get the result.

• From the Excel Ribbon, go to Data > 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 to Exclude Blank Cells from Multiple Columns

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

Steps:

• First, enter the following formula in Cell G6:

• Press Enter to get the result.

• Go to Data > 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 – 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:

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

• Go to Data > 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