Use 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.
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 Filters have several advantages over other ways of getting data from a table. For example, they:
In this example, there are 3 sheets used for the setup to use Slicers to set filter criteria in Excel:
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.
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.
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:
Formulas will be added to the criteria cells later.
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:
The field names will be used to create data validation drop down lists, so name that column in the table -- HeadingsList.
Next, you will build an Extract range for the Advanced Filter, with room for 5 fields from the source data.
To create the drop down lists:
Next, use the drop down lists to select a heading for each cell in the Extract range.
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".
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.
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
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.
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.
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.
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
The final step is to add a button to run the GetDataForSlicersSel macro.
Note: After you have assigned a macro to a shape, you can select the shape by right-clicking on it.
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.
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.
Last updated: September 29, 2021 3:12 PM