Excel Filter for Blanks: Shark Week 2011
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.
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:
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.
Or watch on YouTube: Excel Advanced Filter Finds Blank Cells