Complex Criteria - AutoFilter in Excel

Office for Mere MortalsLast updated: 17 July 2018

We address the problems related to certain types of searches that the AutoFilter in Excel won’t perform.

By Helen Bradley

In a recent article of Office for Mere Mortals I showed you how to extract data from a list using the AutoFilter tool. At the time I suggested there were certain types of searches that the filter wouldn’t perform. Now is the time to address this problem. The AutoFilter fails to deal with OR searches where the data to match is in different columns.

DETAILING THE PROBLEM

Using the AutoFilter options and the Custom search you can search for all people who live in CA by searching for CA in the state column. You can also search for all people in CA or NH by choosing the Custom option and selecting the OR option and entering CA and NH in the dialogs.

In addition, you can search using an AND search if the data to match is in one column. For example to find all sales people who sell $500 or more and less than $1,000, create a search using the Custom option for the sales amounts and configure it to the AND option. Enter is greater than or equal to 500 and is less than 1000 in the dialogs to extract the information you’re looking for.

The one search that can’t be done using any of the AutoFilter tools is an OR search of the type: show me everyone who either lives in CA or who has sales of more than $500. There is no provision for using different column headings in the Custom search dialog so you can’t use that to write this search. The other alternative would be to select CA in the state list and write a Custom search on the sales amount field to locate everyone who sells more than $500. This search, however works as an AND search locating everyone who both lives in CA and who sells more than $500. It doesn’t work for us if we want to see everyone who either lives in CA or who sells more than $500. Right now we’re out of luck.

DOING IT THE OLD WAY

If you studied spreadsheets in the “good ol’ days” before the AutoFilter tool, I can probably cause you to break out into a cold sweat by mentioning criteria ranges. This was the way all searches used to be performed and it was a complex and confusing process. These days, the searches are easier to perform and it’s the only way we’ll be able to run an OR search using criteria involving data in multiple columns.

Here’s how to perform the search, first you need to create what is called a Criteria range to tell Excel what columns to use for the search and what to look for in these columns. So, let’s assume we want to perform a search to find:

Everyone who either lives in CA or who sells more than $500

Start by duplicating the column headings from your data table and paste them into a region of the worksheet where they are out of the way of your data. To do this, simply select the cells containing the column headings and copy and paste them into this new location.

Immediately under the State column heading that you have just created, type CA as we’re looking for all entries that match this state abbreviation. Two cells below the Sales amount heading type >500 as this will locate all entries where the sales are more than 500. It is critical that you place these two values on different rows – by doing so, you are phrasing the search as an OR search. If you place them on the same row it’s equivalent to performing an AND search.

Now, to perform the actual extraction and if you have an AutoFilter already in place, then choose Data, Filter, Advanced Filter. If you do not have an AutoFilter in place, start by selecting your list of data (the one containing all the sales people’s details) and including the heading row and then choose Data, Filter, Advanced Filter. Choose the Filter the list in place option and ensure that the List Range points to the range containing your list of data, if not, select it now including the column headings.

Click in the Criteria range area in the dialog to activate it and drag over the full set of headings and the two rows beneath that you just created – i.e. the range which contains the criteria you’re wanting to match and the column headings. When you click Ok your data list shrinks to display only those records which match your criteria.

To return to viewing all your data, choose Data, Filter, Show All.

OTHER USES

Once you understand how to set up and use a Criteria range you can use it to perform very complex queries. If you need to create AND queries you should place the values you’re looking for in cells on the same row – to create OR queries these values should appear in different rows.

Take care that when you repeat the filtering process to ensure that the Criteria range covers the headings and all the criteria but nothing more. If you include an empty row in the Criteria range the entire data list will be considered a match so it will look as if the search has failed.

Sooner or later you will probably encounter the situation where you need to perform an AND type query using multiple entries in the same column. In our previous example this occurred when we needed to find all people with sales more than 500 and less than 1000. In that case we used the Custom type of search because it allowed us to use multiple conditions on the same column. When you’re using Criteria ranges you solve this problem by making two copies of the column heading you’re interested in and place one condition under one heading and the other condition under the second heading both on the same row.