Work with Slicers in Excel

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.