Learn how to navigate and optimize your use of slicers in Excel PivotTables to effectively filter data. These flexible tools can sort through fields with hundreds or even thousands of values, improving your ability to analyze data sets.
Key Insights
- Slicers are filtering tools in Excel that can sort through all values for a given field in a PivotTable.
- You can utilize slicers for fields with a limited number of values such as states, countries, or categories, or for those with many values such as transaction dates or credit card numbers.
- A slicer can be added by clicking the 'Insert Slicer' button on the PivotTable Analyze tab and selecting the field to slice. This makes the filter field easier to use.
- Multiple values can be selected on a slicer by clicking the 'Multi-Select' button or by pressing the CTRL key while selecting the values.
- Slicers can be customized for easier usage by dragging, resizing, coloring, and precisely aligning them using tools provided on the Slicer tab.
- The 'Report Connection' button allows multiple tables in a workbook to be connected to a slicer, enabling simultaneous filtering of these tables.
Why Use Slicers
-
Visual Filters
Click buttons instead of dropdown menus — faster filter changes.
-
Multi-Pivot Connect
One slicer can filter several pivot tables at once.
-
Dashboard-Friendly
Slicers look professional on dashboards — no jargon visible.
-
Easy Multi-Select
Hold Ctrl or click the multi-select toggle to choose multiple values.
Working with Slicers
Slicers are tools Excel provides for filtering your Pivot Table data.
Slicers are very simple tools, displaying all the values for a given field – no matter how many there. For that reason, most people only use them for fields with a limited number of values such as fields that store states, countries, categories, or statuses. But you could use them for a field with hundreds or thousands of values such as a transaction date field or a credit card number field – something totally unique to each record. Excel won’t stop you!
As shown here in this Pivot Table that organizes my food sales data by category, product, and city, I can add a slicer to make that Filter field, Category, easier to use.
Once added – by clicking the Insert Slicer button on the Pivot Table Analyze tab and selecting the field to slice, I can click on any value listed in the slicer itself – which displays one box per value in the sliced field – and then only the records for that Category are displayed.
If I want to see more than one value, I can either click the Multi-Select button or simply press the CTRL key as I click on the values. So I can say I want to see both Cookies and Crackers, but nothing else.
You can add as many Slicers as there are fields in your data, and you can drag, resize, and even color them to make them easier to use. I’ll add slicers or Product and City, so that each field I’d want to filter on in my Pivot Table is represented. Once inserted, I can place them side by side and using the Slicer tab, change their color, using the Slicer Styles options, and even align them precisely using the Align button.
You can also set precise sizing for the slicers and the buttons within them using the Buttons and Size tools on the Slicer tab, or simply drag the edges of the boxes to resize them both at the same time.
The Slicer Settings button allows you to choose defaults for how your slicers will appear – the name of the selected slicer, the order button values appear, etc.…
Finally, you can use the Report Connection button to choose which of the tables in your current workbook to connect to the selected slicer – making it possible to filter more than one table at a time, simply by checking off the PivotTables you want to connect to.
