There was a question about Excel Advanced Filter criteria on the Tech Republic blog recently, and I posted an answer. A couple of weeks later, a Tech Republic mug and flag were delivered to my door, as a reward for answering.
The real joy is in solving a problem, but it's fun to get a major award, even if it's not a fancy leg lamp that I can put in the front window.
Keep reading, to see what problem the blogger was having with Excel Advanced Filters, and download a workbook with my suggested solution.
Set Up an Advanced Filter
To use an Excel Advanced Filter, you create a criteria range, with headings that match the ones used in the original table. Then, under one or more of the headings, you enter the filter criteria.
For example, in the screenshot below, the criteria would extract all the records where the quantity ordered is 20 and the product is juice. With an Advanced Filter, you can even extract the data to a different location, all in one step.
In most cases, when you set up an Advanced Filter criteria range, each heading must be identical to a heading in the source data table.
An easy way to make them identical is to link from the criteria headings to the table headings. In the screenshot below, cell F1 is linked to cell B1.
However, there's one situation in which the criteria range headings must NOT match the table headings -- if you use a formula in the criteria row.
In the example below, we'd like to extract the records where the number ordered is different than the number shipped.
In the criteria range, there's a formula in cell G2, to compare the quantity ordered and quantity shipped.
For this filter to work, the heading in cell G1 has to be removed, or changed to something different than any of the table headings.
Another option would be to leave the link to the table heading, and add a space character or underscore.
=C1 & " "
Create Adjustable Criteria Headings
This was the problem that the Tech Republic blogger encountered -- remembering to manually change the heading, or remove it, when using a formula in the criteria range. The question posed included this restriction:
Remember, you don't want to force users to remember that in this particular case... they have to do something special like delete header text! Working with the list and criteria ranges, already in place, how would you get the desired results?
To make the heading adjust automatically, you can use an IF formula to test what's in the cell below.
=C1 & IF(ISLOGICAL(G2), "_" , "" )
If cell G2 contains TRUE or FALSE, then it has a criteria formula, and an underscore is added to the heading.
Download the Advanced Filter Workbook
To see the data and the criteria range heading formulas, you can download the Advanced Filter Criteria Headings sample file. It's in Excel 2003 format, and zipped. The file contains a macro, that lets you run the advanced filter by clicking the Filter button on the worksheet. Enable macros if you want to use that feature.
Watch the Advanced Filter Criteria Video
To see the steps for applying an Advanced Filter, with regular criteria or a formula in the criteria range, please watch this short Excel video tutorial.