Filters > Advanced Filter > Slicers Use Slicers to Set Advanced Filter Criteria Use Excel Slicers to select criteria for an Advanced Filter, and show the matching records from the database. This makes it easy to view specific data, and keep the original records safe on a hidden worksheet.

Use Slicers to Set Filter Criteria in Excel

To make it easy to see specific data, while keeping the data safe, set up Slicers that you can use to filter the database in an Excel workbook. Then, just click a button, to run a macro that pulls the matching records from the database. The screen shot below shows how the Slicers and buttons work.

You can download the sample file, and follow the instructions below, to see how it's built.

Advantages of Advanced Filters

Advanced Filters have several advantages over other ways of getting data from a table. For example, they:

• keep people away from source data, so it stays safe
• run vey quickly
• let you select specific fields for the output
• return values, instead of formulas (easier to copy and paste elsewhere)

Workbook Setup

In this example, there are 3 sheets used for the setup to use Slicers to set filter criteria in Excel:

• SalesData
• Pivot_Filters
• Output

On the SalesData sheet, there is a formatted Excel table, named Sales_Data.

On the Pivot_Filters sheet, there is a pivot table based on that source data. That pivot table has 3 fields in the filter area, and no other fields.

Add Pivot Table Slicers

Next, Slicers were added, for each of those fields.

After that, the Slicers were moved to the Output sheet, which will be used to show the filter results.

How to Add Slicers

In this video you can see the steps for adding a slicer to a pivot table, and then using slicers to filter the data. The written steps are below the video

To add a pivot table slicer, follow these steps:

• Select a cell in the pivot table
• On the Ribbon's Insert tab, in the Filter group, click Slicer.

The Insert Slicers dialog boxopens, with a list of fields from the pivot table source data.

• In the list of pivot table fields, add check marks for the slicer(s) you want to create
• For example, in the video, slicers were added for Genre and ActorName

• Move and resize the slicers, if necessary, so they fit on the empty areas of the worksheet

Test the Slicers

To test the slicers, click the Slicer buttons in any of the Slicers that you added to the worksheet.

• For example, click on a Slicer button in the Genre slicer, to see only those items in the pivot table.
• Note: Other Slicers will show related items at the top, in dark font
  • Unrelated items will be listed below that, in light-coloured font.

To remove the Slicer filters, click the Clear Filter button, at the top right of the Slicer

Set Up a Criteria Range

The macro will run an Advanced Filter, so a criteria range is required. This was set up on the Pivot_Filters sheet, with heading cells (D3:F3) and criteria cells (D4:F4).

In cells D3:F3, formulas link the heading cells to the field names in the pivot table filters.

Next, follow these steps to name the criteria range:

• Select the entire criteria range, D3:F4
• Click in the Name box, to the left of the Formula bar, and type a name for this range -- CritSlicers
• Press the Enter key, to complete the name.

Formulas will be added to the criteria cells later.

Create a Field List

On the Output sheet, people will be able to select the fields that they want to see in the filter extract. The next step is to make a list of fields, and use that to create a data validation drop down list in each heading cell.

To create a list of headings:

• Copy the heading cells from the database
• On the Pivot_Filters sheet, select cell H4
• On the Excel Ribbon, click the Home tab, and click Paste Special
• Select Values, and Transpose, and click OK.
• In cells H3:I3 add the headings "Field" and "All"
• Format the list as an Excel table, named tblHead

Name the Field Column

The field names will be used to create data validation drop down lists, so name that column in the table --  HeadingsList.

Build the Extract Range

Next, you will build an Extract range for the Advanced Filter, with room for 5 fields from the source data.

• On the Output sheet, select cell B8:F8, and name that range as ExtractSlicers
• Format the cells with a border

To create the drop down lists:

• Select cell B8:F8, and on the Excel Ribbon, click the Data tab
• Click Data Validation, and for Allow, choose List
• Click in the Source box, and type:  =HeadingsList
• Click OK, to close the Data Validation window.

Next, use the drop down lists to select a heading for each cell in the Extract range.

Using Criteria Formulas

If one item will always be selected in each Slicer, the Criteria Range could use a simple link to each filter cell in the pivot table.

But, if a Slicer is cleared, the pivot table filter shows "(All)", and that won't work as a criterion for our Advanced Filter. It would look for "(All)" in the Category column, and not find it, so the filter wouldn't return any records.

We have to use a criterion that  means "show everything".

• For text fields, we can use the asterisk wildcard � "*"
• For number fields or date fields, you could use ">0", or ">= �9999999"

Add Criteria Settings

In the All column, for each field that has a Slicer, type the criteria setting that you want to use if the Slicer is cleared, and the pivot table filter shows "(All)".

For the text fields, I entered an asterisk, and for the number fields I used ">0". You could use different criteria, based on your data.

Get the "All" Criteria

Below the Criteria range, another set of formulas will get the criteria setting from our table, for cases when "All" is selected. The formula uses the INDEX and MATCH functions to pull the values from the Field List table.

Enter the following formula in cell D7, and copy it across to F7

=INDEX(tblHead[[All]:[All]],MATCH(D3,HeadingsList,0))

The formula looks for the field name in cell D3, and finds its match in the HeadingsList range. Then, it returns the value from the All column in that row.

Add the Criteria Formulas

Next, we'll add formulas in the criteria row, to use with the Advanced Filter. Each formula is slightly different, and refers to its field name and value in the pivot table filters. The formula checks the field in the source data, to see if the selected item appears.

• If the item is found in the source data, that item is used as the criterion.
• If the item isn't found, e.g. (All) or (Multiple Items), the alternate criterion is used.

Enter the following formula in cell D4:

=IF(COUNTIF(INDIRECT("Sales_Data[" & A3 & "]"),B3),B3,D7)

Enter the following formula in cell E4:

=IF(COUNTIF(INDIRECT("Sales_Data[" & A4 & "]"),B4),B4,E7)

Enter the following formula in cell F4:

=IF(COUNTIF(INDIRECT("Sales_Data[" & A5 & "]"),B5),B5,F7)

Here's the result, if none of the Slicers are cleared. I've used grey fill on all the cells with formulas. The Advanced Filter would return all the records in the Sales_Data table

Here's the result, after selections are made in the Category and Orderyr Slicers. The Advanced Filter would return all the records for Snacks sales in 2016, for any Region.

Add the Macro Code

Next, you'll add the macro -- GetDataForSlicersSel -- that runs an Advanced Filter. Add the following code to a regular module in the workbook:

Sub GetDataForSlicersSel()
Dim wsPF As Worksheet
Dim wsSD As Worksheet
Dim wsOP As Worksheet

Set wsPF = Sheets("Pivot_Filters")
Set wsSD = Sheets("SalesData")
Set wsOP = Sheets("Output")

wsSD.Range("Sales_Data[#All]").AdvancedFilter _
  Action:=xlFilterCopy, _
  CriteriaRange:=wsPF.Range("CritSlicers"), _
  CopyToRange:=wsOP.Range("ExtractSlicers"), _
  Unique:=False
End Sub

Add the Macro Button

The final step is to add a button to run the GetDataForSlicersSel macro.

• In the Output sheet, insert a rounded rectangle shape
• With the shape selected, type Get Data
• Format the shape and text any way you'd like.
• Right-click on the shape, and click Assign Macro
• In the list of macros, select GetDataForSlicersSel
• Click OK

Note: After you have assigned a macro to a shape, you can select the shape by right-clicking on it.

Test the Button

To test the button, select an item from each Slicer, then click the button. You should see the matching records below the extract range headings.

Then, change one of the extract range headings, and click the Get Data button again. The data in the changed column should be the only data that changes.

Get the Excel Files ✅

• Filter Slicers: For a zipped workbook with the "Use Slicers to Set Filter Criteria in Excel" example, click here. The zipped file is in xlsm format, so enable macros when you open the file.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Filter Tutorials

Advanced Filter Criteria Examples

Advanced Filter Macros

AutoFilter Basics

AutoFilter Tips

AutoFilter Macros