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.

Introduction

Advantages of Advanced Filters

Workbook Setup

Set Up a Criteria Range

Create a Field List

Name the Field Column

Build the Extract Range

Using Criteria Formulas

Add Criteria Settings

Get the "All" Criteria

Add the Criteria Formulas

Add the Macro Code

Add the Macro Button

Test the Button

Download the Sample File

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.

advanced filter setup

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.

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.

pivot table with report filters

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.

pivot table with report filters

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.

insert 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

insert slicer

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

insert 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.

criteria range

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.

criter range 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

field list

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.

field list name

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.

select field headings

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.

criteria range formula link

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.

criteria range formula all

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.

criteria range all settings

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.

criteria formula all

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

criteria range formulas

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.

criteria range formula results

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.

assign macro to button

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.

test the macro button

Get the Sample File

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.

Use Slicers to Set Advanced Filter Criteria

Use Slicers to Set Filter Criteria in Excel

Advantages of Advanced Filters

Workbook Setup

Add Pivot Table Slicers

How to Add Slicers

Test the Slicers

Set Up a Criteria Range

Create a Field List

Name the Field Column

Build the Extract Range

Using Criteria Formulas

Add Criteria Settings

Get the "All" Criteria

Add the Criteria Formulas

Add the Macro Code

Add the Macro Button

Test the Button

Get the Sample File

More Filter Tutorials