Contextures

Use Slicers to Set Advanced Filter Criteria

Use Slicers to select criteria, 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. Then, just click a button, to run a macro that pulls the matching recrods 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

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

Next, Slicers were added, for each of those fields. The Slicers were moved to the Output sheet, which will be used to show the filter results.

pivot table with report filters

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

Advantages of using 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)

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

go to top

More Filter Tutorials

Excel Advanced Filter Introduction

Advanced Filter Criteria Examples

Advanced Filter Macros

AutoFilter Basics

AutoFilter Tips

AutoFilter Programming

Search Contextures Sites

 

pivot power premium

 

 

Excel Data Entry Popup List

 

 

30 Excel Functions in 30 Days

 

 

 

 

 

Last updated: September 1, 2016 6:39 PM