You probably know that you can use Advanced Filter in Excel to select records based on a specified criteria. This is different from using the Filter capability on the Home tab. With the Advanced Filter, you can use criteria that does not exist in the data you have, such as filtering on a salary range. Here’s a sample set of data (don’t worry, there’s no one real in this data):
If I want to filter to obtain only the people who earn between $80,000 and $100,000 who live in Illinois, I can certainly use the Home > Filter to do so, but it will require multiple steps. If I use the Advanced Filter, I can build a very tailored filter based on multiple criteria.
To prepare for an Advanced Filter. you need to establish your criteria. You can put this criteria on any worksheet. In this example, I will place the criteria on the same worksheet as the data itself, but again, the criteria can be anywhere in this (or even another) workbook.
There are a couple of important points I want to emphasize:
- You need to tell Excel what column(s) contains the data that you are filtering. In this case I want to filter on the Salary column and the State column. The name of the column has to be included in the criteria or Excel doesn’t know where to look for the data that you filtering on.
- You can use ‘>’ , ‘<‘, and ‘=’ in your criteria specification. In the example above, I am asking for records where the salary is greater than 80,000 and less than 100,000. If I wanted records that matched an exact amount, I could use the ‘=’ operator.
- To indicate that the relationship between your criteria is ‘AND’, put the criteria next to each other horizontally. The example above reads as follows: Give me only the records where the salary is greater than 80,000 AND less than 100,000 AND where the person lives in Illinois (IL).
- To indicate an ‘OR’ relationship, put the criteria directly below each other as shown below. Notice that the OR statement has to make sense–in this case I am asking for records that are greater than 80,000 AND live in Illinois OR less than 60,000 AND live in Illinois.
Finally, if you want to select criteria based on a text value (for example, a name) use the following syntax: =”=TextContent”. For example, if I wanted to see the records of everyone whose last name is “Brown”, I would set up the following criteria:
Now we are ready to proceed to the actual Advanced Filter, which is found on the Data tab.
Let’s take this menu apart step by step.
1) Filter in Place or Copy to Another Location. If you want to Filter the list right where it is, use Filter in Place. Usually, though, we want to put the results somewhere else, so I will focus on Copy to Another Location in this post.
2) List Range. Here, you are being asked where the data is that you want to filter. You can enter the data range manually by entering the start and end cells as follows: $A$1:$M$500 (of course this is my dataset–your range will no doubt be different). (A note about the dollar signs–you definitely want to include these before both the column letter and the row number. They anchor your data selection to ONLY those cells thus specified.)
3) Criteria Range. This is where you specify where your criteria is. In the example above (with the name criteria), the range is $U$1:$U$2 (you can’t see the row number, but take my word for it.)
4) Copy To. This is where you specify where you want the filtered data placed. You only have to specify the top left destination cell. Often you may not even know how large the data set is that you will be copying. Make sure you have an area at least as large as your best estimate of the size of the resulting data set.
5) If you have duplicate records, check the Unique records only box (if you don’t want duplicate records in your filtered data set).
6) Press OK. Your copied records should appear in the location you specified. If it doesn’t work, recheck all the fields listed above. The reason is usually to be found in errors in the data that you entered in the Advanced Filter menu.
Finally, if you start the Advanced Filter on the same sheet where the original data set is located, you won’t be able to copy your filtered data to a different worksheet. To fix this, initiate the Advanced Filter process from the worksheet where you want the filtered data to appear.
Advanced filtering is very convenient when you have data that you want to isolate based on multiple criteria. While you can use the Home>Filter option multiple times to do this (in most but not all cases), it is worth the time to learn how to use the Data>Advanced Filter option to perform the filtering quickly and efficiently.