Automatically Change Excel Filter Heading

techrepublic 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.

AdvFilterHead02

Identical Headings

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.

=B1

AdvFilterHead01

Different Headings

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.

=C2<>D2

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.

AdvFilterHead04

Another option would be to leave the link to the table heading, and add a space character or underscore.

=C1 & ” “

AdvFilterHead05

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.

AdvFilterHead06

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.

AdvFilterHead07

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.

___________

You may also like...

2 Responses

  1. Jim Cone says:

    Debra,
    Since you obviously are pining for a leglamp, I will let you have mine.
    Where should I send it?
    ‘–

    Note to others…
    “pining”: suffer with longing

  2. Thanks Jim, but you’d kick yourself if you gave away your leg lamp!

    Anyway, I have a Christmas tree ornament leg lamp (packed in a box marked “FRAGILE”). I don’t think the city bylaws allow anything larger than that to be in the front window. ;-)

Leave a Reply

Your email address will not be published. Required fields are marked *