Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Tom’s Tutorials For Excel: Advanced Filter Calculated Criteria

Advanced Filter is among Excel’s most powerful and versatile tools. This example shows how a formula can serve as the criterion for filtering a table, based on a separate list of criteria items.

In Picture #1, you see an unfiltered table in range A4:C16 with names in column A.
The list of three names in range E1:E3 are what you want to filter the table for.
In cell C2 is the criteria formula =ISNUMBER(MATCH(A5,$E$1:$E$3,0)).

When you enter this formula, whether it returns TRUE or FALSE is irrelevant. What is important is that the cells above and below the criteria formula cell (in this example, cells C1 and C3 respectively) are empty. Notice also that the reference to cell A5 relates to the fact that cell A5 is the first cell with actual data (below the header row) in the Name column being filtered.

Picture #1 — Unfiltered Table

In Picture #2, you have called the Advanced Filter dialog, which is done from any version of Excel by pressing Alt+D+F+A. For Excel versions 2003 or before, click Data > Filter > Advanced Filter. For versions 2007 or 2010 click the Data tab and “Advanced” in the Sort and Filter panel of the Ribbon. Notice that the List range criteria includes the table’s headers, and the Criteria range includes cell C1 (which is empty) and cell C2 (which contains the formula).

Picture #2 — Advanced Filter Dialog

When you click OK to the Advanced Filter dialog as shown in Picture #2, the filtered result is shown in Picture #3, with the names in E1:E3 (Bill, Bob, Tom) being the only names visible in column A.

Picture #3 — Filtered List of Names