One useful feature of Excel 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 two-filter 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 “metadata”, 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:
|usa|nsa|monthly|bls|rate|unemployment|msa|mt|missoula|
|usa|nsa|monthly|bls|rate|unemployment|county|mt|gallatin county, mt|
|usa|monthly|sa|bls|rate|unemployment|state|mt|
|usa|sa|quarterly|bea|unemployment|state|transfers|frb district|benefits|frb stl district|
Although these tags describe economic data from the Federal Reserve Bank of St. Louis, I suggest that you keep a similar approach in mind 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 Single-Criteria 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 non-zero 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 Single-Criteria 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 Multi-Criteria Search Formula that I’ll describe next, you’ll use this single-criteria 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 hard-coding “|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.
