In most cases, it’s best if you keep people away from your data in Excel. It’s too easy to mess things up! Instead, set up a worksheet where they can use Slicers to set filter criteria in Excel.
Then click a button, and a macro dumps the data onto the worksheet. In the screen shot below, you can see how it works – easy, quick, and safe!
Use Slicers to Set Filter Criteria in Excel
Here’s a quick demo of the setup. Click the Slicers, then click the Get Data button. You can even change the fields if you want to.
You can download the sample file (link at the bottom of this page), to see how it’s built. There are full instructions on my website, and a quick overview below.
How It Works
There are 4 key pieces in the setup to use Slicers to set filter criteria in Excel:
- Sales data table
- Pivot table with filters
- Worksheet with Slicers and an output area
- Macro that runs an Advanced Filter
Sales Data Table
On the SalesData sheet, there is a formatted Excel table, named Sales_Data. This sheet can be hidden, and kept away from accidental damage.
Pivot Table with Filters
On another sheet that could be hidden away, there is a pivot table based on the Sales data table. There are only 3 fields in the pivot table, and they are in the filter area. Three Slicers were connected to this pivot table – one for each of the pivot table filters.
The main sheet has the Slicers (cut and pasted from the pivot table sheet) . There is also an output area (the Extract range for the Advanced Filter).
There are drop down lists in the output area, where you can select the fields that you want to see in the results.
Advanced Filter Criteria Range
The macro runs an Advanced Filter, so there is a criteria range, on the Pivot Table sheet.
The criteria cells have formulas, and they show the item selected in each Slicer. If a Slicer is cleared, its criteria cells shows a wildcards character.
For example, here’s the result, after selections are made in the Category and Orderyr Slicers, and the Region Slicer is cleared. The Advanced Filter would return all the records for Snacks sales in 2016, for any Region.
Run the Advanced Filter Macro
There is a Get Data button on the Output sheet, and it runs the Advanced Filter macro.
To run that macro, select an item from each Slicer (or clear the Slicers), then click the Get Data button. You should see the matching records in the Output area.
Then, change the Slicer selection, or the extract range headings, and click the Get Data button again. You’ll see different results in the output area.
Why Use an Advanced Filter?
An Advanced Filter is a great way to pull data from an Excel table, for lots of reasons. For example, they:
- keep people away from source data
- are quick to run
- allow you to limit the number of fields shown (and the drop down lists make it easy to change the fields)
- return values, instead of formulas
- make it easy to copy and paste to another location
Why Use Slicers to Select the Criteria?
Even though Advanced Filters are awesome, their criteria range setup can be confusing. Slicers take away that confusion, because:
- you can set up the criteria range in the background, so other people can ignore that
- after you select from one Slicer, other Slicers show related items at the top – you won’t pick criteria combinations that don’t exits
Download the Sample File
To get the full instructions, and to download sample file, go to the How to Use Slicers With Excel Advanced Filter page on my website.
The zipped file is in xlsm format, so enable macros when you open the file.