Last year, we celebrated the Discovery Channel’s Shark Week, by using the LARGE and FLOOR functions.
This year, we’ll pay tribute to the three known species of sharks that are filter feeders, by declaring this Excel Advanced Filter Week.
Yes, we’ll have three fun-filled, action-packed days of Excel filtering fabulousness – one day for each filter feeding shark. Please hold your applause until all three articles have been posted.
Advanced Filter Criteria Range
We’ll kick off the week’s celebrations by filtering rows with missing data (blank cells) to a different worksheet.
When you’re using an Advanced Filter, usually you would enter a heading, and one or more criteria, in a criteria range, like the one shown below.
In this example, you would be filtering the customer order list for any orders with Cookies as the product.
Blank Cells in Data
However, if you want to filter orders with a blank cell for Product, you can’t just leave the criteria range blank.
A blank criteria cell is interpreted as “No criteria”, so all the records would pass through the filter. That might be fine for a shark, but not for an Excel report.
Filter for Blanks in Advanced Filter
Instead of leaving the criteria cell blank, you can use a formula, to check for empty cells. In this example, the first product data is in cell C2, so the formula is:
=C2=””
The two double quote marks represent an empty string, so if C2 is not blank, the formula result is FALSE.
Only the records that calculate to TRUE would pass through the filter.
Remove the Criteria Range Heading
If you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings.
You can either clear the heading cell in the criteria range, or type a different heading.
I usually clear the heading cell, because that’s quick and easy!
Run the Advanced Filter
After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.
In this example, the filter is started from the Blank Orders sheet, and the list and criteria range are on the Orders sheet.
Download the Advanced Filter Blanks Workbook
To see the sample data, and test the filter, you can download the Advanced Filter for Blanks sample workbook. The file is in Excel 2007 format, and is zipped.
Watch the Advanced Filter for Blanks Video
To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel video tutorial.
___________________
Thanks, David and Khushnood.
You can leave the criteria heading cell blank, when using a criteria formula, but you need to include those heading cells in the criteria range, in the Advanced Filter dialog box.
For another variation on modified criteria range headings, see my post on Automatically Change Excel Filter Heading
Thanks for both answers David.
I’ll try these options when I need the flexibility you mentioned in your earlier reply.
@Debra
When using formulas in the criteria range the heading can either be blank or Different from what was used in the data table, so instead of Product it could be mProduct
@Sam, thanks, I agree with that. In my previous comment I was replying to David’s comment:
“You can include the header in the filter criteria range or not – it doesn’t affect the results either way. I typically include the header cell for the of consistency across my criteria.”
Yes, you can leave the header cell blank, or make it different from the data table headings.
But you have to include the header cells when selecting the criteria range. It’s not optional.
I didn’t know that you could set up a criteria range and run an advanced filter. I think it is so great that technology is advancing so that we can have better data filters! I am sure that those who use computers a lot would love to use a data filter to help them in their work.