EXCEL TABLES
How to Add Advanced Filter Capabilities to Excel Tables
Excel Tables offer the ability to filter on two criteria in one
column. Here one way to search for many criteria in a Table.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

In Excel 2007, Microsoft added the powerful Table
functionality.
One useful feature of Tables is the ability to filter any number
of columns. The filter control for each column allows us to
search for two criteria. However, when I use these filters I
often have at least two problems with them.
First, when I explore a lot of data by using many filters in
several different columns, I find myself doing a LOT more
clicking than I really want to do.
Second, I occasionally need to filter on more than two criteria
in a column, a feature that Tables don’t offer.
So recently, I finally got tired of all that clicking; and I got
tired of the twofilter limit. So I decided to search for a
better way.
And I found it.
Add a Filter Column to Your Excel Tables
The idea is simple: To reduce all the clicking when we explore
Table data, it would be much easier if we could rely on one
column of formulas that returns TRUE if the row has the data
we’re looking for, and FALSE otherwise.
But what should that column of formulas look like?
Before I show you one approach, let’s take a quick look at the
column of data that I rely on the most in the data I’ve been
using. (More precisely, the column contains “meta data”, which
typically is defined as “data about data.”)
The Tags column contains category labels that describe the
contents of each row of economic data. I separated each tag with
a “” so I can search for a complete tag when I want. (I didn’t
use commas as a separator because some of the tags have embedded
commas, as you can see in the second row here:
usansamonthlyblsrateunemploymentmsamtmissoula
usansamonthlyblsrateunemploymentcountymtgallatin
county, mt
usamonthlysablsrateunemploymentstatemt
usasaquarterlybeaunemploymentstatetransfersfrb
districtbenefitsfrb stl district
Although these tags describe economic data from the Federal
Reserve Bank of St. Louis, I strongly suggest that you use a
similar approach when you create Excel Tables of your own
business data. You could add a column of tags to General Ledger
data, product data, inventory data, and so on. If you do, you’ll
probably find it a lot easier to find just the items you need
when you report and analyze the data in your Tables.
Using the SingleCriteria Search Formula
Here’s the first formula I created. It’s a long formula, which
I’ll wrap to six lines so you can read it more easily:
=AND(
ISERR(SEARCH(“state”,[@Tags]))1,
ISERR(SEARCH(“msa”,[@Tags]))0,
ISERR(SEARCH(“county”,[@Tags]))0,
ISERR(SEARCH(“frb district”,[@Tags]))0
)
As you can see, this formula consists of four individual
searches, surrounded by an AND function. Here’s what it does…
The AND function returns TRUE if each of its arguments has a
nonzero value. (In your spreadsheet, FALSE has a zero value and
TRUE has a value of 1.) But if any argument returns a zero
value, AND returns FALSE. Specifically, in the formula above,
AND returns TRUE if a tag DOES contain “state”, and does NOT
contain “msa”, and does NOT contain “county”, and does NOT
contain “frb district”.
If you’ve not used Table formulas before, the “[@Tags]”
reference will look strange to you. Microsoft calls this a
“structured reference.” In formulas, you reference data in
Table columns by using the name of the column within brackets.
The “@” character tells Excel to use the value in the Tags
column that’s on the same row as the row that the formula is on.
How the Single Criteria Search Formula Works
To see how the four
SEARCH arguments work, take a look at the
first SEARCH above.
If the SEARCH function finds “state” in its row within the
Tags column, the ISERR() function returns FALSE, which has a
numeric value of zero. So we turn this FALSE value to TRUE by
subtracting the value of 1. (Remember, Excel treats zeros as
FALSE, and all other values as TRUE. So the 1 value in this
case still evaluates as TRUE.)
On the other hand, if the SEARCH function does NOT find
“state”, ISERR returns TRUE, which has a numeric value of 1.
But when we subtract 1, we turn this value to zero, which Excel
treats as FALSE.
Now look at the second SEARCH line. If the SEARCH function finds
“msa” in its row within the Tags column, the
ISERR function
returns FALSE. This FALSE value stays FALSE because we
subtracted zero, which doesn’t change the value at all. I could
have skipped that “0″ piece entirely; but I added it for two
reasons.
First, by adding “1″ or “0″ to each SEARCH function in the
formula, I make it very easy to see where I need to modify the
logic when I want to. So, for example, if I want to change the
formula to select for “county” tags, I merely change “0″ to
“1″.
Second, honestly, I get a little confused by the many changes
between TRUE and FALSE, particularly after I’ve been away from
the Table for a while. So the “1″ piece tells me YES, the
formula is looking for “state”. And the “0″ pieces tell me
that NO, the formula does not want the other three items.
Final Notes about the SingleCriteria Search Formula
Each test in the filter formula above looks at data in the same
column. However, each of the four SEARCH functions could just as
well be looking for different information in different columns
within the Table. In fact, if you use the MultiCriteria Search
Formula that I’ll describe next, you’ll use this singlecriteria
formula primarily for testing multiple columns of data.
Also, remember that this formula is like any other Excel
formula, even though it uses a new method to reference data
within the Table. Therefore, rather that hardcoding “state”,
“msa”, and so on, I could have put those text items in cells
above the Table. That way, I could change the filter criteria
merely by changing values in cells.
Also, of course, I could replace “1″ and “0″ with something
like “$B$1″ and “$B$2″, where cells B1 and B2 could contain 1
or 0, or TRUE or FALSE. That would have let me change the logic
of what to include or exclude merely by switching between TRUE
and FALSE, and then refreshing the Table by pressing Alt+F5.
Finally, I’ll conclude this post by telling you what I don’t
like about this formula…
All four sections of the formula above are looking at the same
cell. So you would think we could find a way to use one SEARCH
function to search one cell using each item in a list, and then
return summary information about the results it finds.
Take Your Next Steps
First,
I recently figured out a way to use one SEARCH function to
search each item in a list,
which I explain in How to
Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel.
Second, if you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
