There are many ways you can Filter data in Excel, and in this post I'll cover the Advanced Filter.
Using the Advanced Filter will allow you to set a criteria in place for your filter and then put the results in a separate section or tab. The benefit from a normal filter where you select your criteria using drop downs is that it won't affect the rest of the data.
If you want to follow along with my example, I have the sample database file that I'm going to use available here.
Related Articles
Filtering the Data Using One Criteria
First up, I'm going to look at Sales Rep A's sales for Product A.For the Advanced Filter to work, there are two sections that need to be ready: the criteria, and the extract.
For the criteria, you need to have the fields that you want for your filter entered. In my example I only am looking for Rep A's Product A sales, so I only enter that criteria and don't enter any other fields.
In the extract section, which you'll see just below the criteria section above, I enter the fields I want to see. I don't have to list all the fields, just the ones that I want to see. Please note the field names need to be an exact match with those in your data set. If they are not, the filter will not work as expected.
Now, to execute the Advanced Filter I'll click on the Advanced button that is under the Data tab next to the Filter button.
After doing so, you'll be prompted to enter two sections:
You can either Filter the list, in-place which will effectively do the same job as if you filtered your data using the drop down options. If you select Copy to another location then the third field, Copy to, will be available for input and this is where you will select your extract fields.
These are what my inputs look like:
After I click OK, my results are as follows:
This looks the same as if I were to filter using the drop down, but the benefit is I am effectively copying the data to another location.
Using Multiple Criteria
In the previous example, I used an Advanced Filter just using one set of criteria. In this example, I'll add another to create a more complex filter.
When using the criteria in an Advanced Filter, if criteria are on the same line it is the same as using the AND operator, while if the criteria are on different lines it is the equivalent of using OR.
In the first example, Rep A and Product A were on the same line, meaning that my criteria was that it had to be Rep A and Product A. In the following example, I'm going to add another criteria:
The above criteria says that it will look for Rep A's Product A sales OR Product B when Total Sales are more than $500.
When I re-run the Advanced Filter, I'll have to expand my range to include the new criteria:
The results:
The results are either Product A or B, and B only shows up when the Total Sales are more than $500. You can add even more criteria, you'll just need to expand the criteria range when running the Advanced Filter.
Copying to Another Sheet
In both of these examples I've copied data over within the same tab. However, if I wanted to copy it to another sheet, that is possible as well.
To do this, you'll need to copy the headers you want for the extract into another tab. You need to press the Advanced Filter button on the tab where you want the data extracted to. So if you create another sheet, it's on that sheet that you'll need to press the button for the filter to work.
In addition to the headers, you'll also need to put something in the second row, as the Advanced Filter button will result in an error if there is only one row of data when you click on the button (when you first use it). If there was data there before it should be fine, but the very first time you move data over you might get an error message if there isn't data on the second row.
Once your headers are properly setup, you run the Advanced Filter button and fill in the same data as before. The one difference you'll see is that since I'm on a different tab, the sheet name will show up in the Criteria and List range fields:
Generating a Unique List of Values
There are a lot of different ways you can pull a list of the unique values in a list. Excel has even added a button to remove duplicates, but it will remove the other items in your list, so that may not be ideal.
The Advanced Filter will allow you to do this as well, simply by just leaving your criteria blank and checking off the box for Unique records only
If I want to generate a unique list of Salespersons, I'll run the Advanced Filter with these options:
The result:
Because I only wanted to see the unique values for the Salesperson, I only have that field in my extract list.
Benefit of Using the Advanced Filter
At first glance, you may be wondering why even bother with an Advanced Filter. After all, you can setup the filters yourself using drop downs and then copying and pasting the filtered data somewhere else.
The most likely reason for the Advanced Filter is that in earlier versions of Excel, filtering just wasn't as strong as it is today.
The main benefit using it today is that it can allow you to easily audit and trace your filters. If you just copy and paste after applying filters you won't be reminded of which filters you put in place.
If you use older versions of Excel you'll likely find a lot more usefulness out of the Advanced Filter than if you are using the newer versions. Admittedly, I haven't had a need to use the Advanced Filter in several years and can't imagine that changing anytime soon.
