Use Slicers to Set Filter Criteria in Excel

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.

Use Slicers to Set Filter Criteria in Excel

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.

advancedfilterslicercriteria15

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.

advancedfilterslicercriteria021

Output Sheet

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.

advancedfilterslicercriteria09

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.

advancedfilterslicercriteria16

Run the Advanced Filter Macro

There is a Get Data button on the Output sheet, and it runs the Advanced Filter macro.

advancedfilterslicercriteria12

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.

advancedfilterslicercriteria01

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.

_______________

You may also like...

4 Responses

  1. Hocine Satour says:

    Nice idea, thank you very much Debra.

  2. XLarium says:

    Brilliant idea, Debra.
    This should be widely promoted.

  3. david says:

    These are great, ive got them working on my own data set, however they only show part of the data when a criteria is selected. (Im working these from a list of suppliers and their skill set. I have 15 Acrylic suppliers, however when I select acrylic on the services splicer it only shows 7 suppliers.) any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *