Advanced Filter Tool – A Quick Overview
We begin with a typical data set that includes information about company, location, item codes and descriptions, quantities, and sales.
Our goal is to filter the data based on multiple criteria and place the filtered results on a separate sheet.
We could use the traditional dropdown filter controls…
…but our criteria may change frequently, and as previously mentioned, we want the results on a separate sheet.
We don’t want the user messing with or having to read the full data set.
Setting Up the Filter
In our scenario, we want to list records that have the word “laptop” anywhere in the Article Description field and that occur on or after July 1st of 2019 in the Date field.
Off to the side, we set up a small table with the column headings of the table that we wish to examine for inclusion in our results set. In this example, we are searching for records of a certain Date and Article Description.
Our Date will be any record that is greater than or equal to 7/1/2019, and the Article Description will use the *laptop* argument. By book-ending the word “laptop” with asterisks, we are saying that any amount of any characters may occur before or after the word “laptop”.
Important Step for
Separate Output Sheet
By default, the Advanced Filter tool will not allow placement of the output to a separate sheet. This is because most users start the tool on the sheet that contains the data and the criteria. The output must be placed on the sheet you were on when starting the tool.
A simple workaround is to start the tool from a different sheet. (This is NOT the trick I’m talking about, but it’s a good one. The REAL trick is coming up very soon. Bear with me.)
- Select the destination sheet.
- Launch the Advanced Filter tool be selecting Data (tab) -> Sort & Filter (group) -> Advanced.
- In the Advanced Filter dialog box, select the option labeled “Copy to another location”.
- In the List Range field, browse to the sheet holding the data and select the data range. For this example, the range is cells A2 through K117 on the Data
Data!$A$2:$K$117- In the Criteria Range field, browse to the sheet holding the data and select the filter arguments. For this example, the criteria range is cells M2 through N3 on the Data
Data!$M$2:$N$3- In the Copy To field, select the cell that will serve as the upper-left corner of the output. For this example, the output will be placed in cell A1 of the Results sheet.
Results!$A$1
- Click OK to complete the operation.
The filtered list is placed on the Results sheet.
Time for the Virtually Unknown Trick
If you’ve stuck with this, you probably haven’t seen much new in the way of Advanced Filters.
Your Excel life is about to change.
In our example, we are filtering records (rows); making a large data set smaller from a row point of view.
When we point to a location to place the results, we get every field (column) from the original data.
But what if we don’t want all the fields (columns) from the original data set? And what if we want the output fields to be in a different order from the original data set?
This is where the trick comes in to play.
We can create an output of fields that don’t even contain the filter fields used for defining criteria.
- In the destination location for the results, create a mini header row that mimics the header names from the fields you want to include in the output.
- Start the Advanced Filter tool (Data -> Sort & Filter -> Advanced).
- Select “Copy to another location”.
- Set the List Range to the area holding the data (same as before: Data!$A$2:$K$117)
- Set the Criteria Range to the area holding the filter criteria (same as before: Data!$M$2:$N$3)
Here’s the most important step!
- In the Copy To field, select the headings you created in Step 1.
Results!$A$1:$C$1
Advanced Filter returns only the records (rows) that match the criteria (same as before), but now filters the columns to only retain those fields of interest.
Notice that our results don’t include the Date or Article Description information used in the query.
We could even have Advanced Filter rearrange the column order by creating the output header in a different order.
Conclusion
The ability to filter records has long been a stable of advanced Excel users.
The ability to simultaneously filter columns is a feature rarely demonstrated as it is not widely known.
As a side note: the Advanced Filter tool is one of the fastest processing tools in Excel when filtering data sets.
If you are writing macros that filter records and/or columns, and you are using traditional dropdown filter techniques, give the Advanced Filter a try.
In tests using data sets containing hundreds of thousands of records, Advanced Filter can reduce in milliseconds what traditional filters take many minutes to reduce. And I’m talking MANY minutes.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
