by Svetlana Cheusheva, updated on
This tutorial shows how to add slicer to tables, pivot tables and pivot charts in Excel 2010, 2013, 2016 and 2019. We will also explore more complex uses such as creating a custom slicer style, connecting one slicer to multiple pivot tables, and more.
Excel PivotTable is a powerful way to summarize large amounts of data and create summary reports. To make your reports more user-friendly and interactive, add visual filters, aka slicers, to them. Hand off your pivot table with slicers to your colleagues and they won't bother you each time they want the data filtered differently.
Slicers in Excel are graphic filters for tables, pivot tables and pivot charts. Due to their visual qualities, slicers fit especially well with dashboards and summary reports, but you can use them anywhere to make filtering data faster and easier.
Slicers were introduced in Excel 2010 and are available in Excel 2013, Excel 2016, Excel 2019 and later versions.
Here's how you can filter the pivot table data by selecting one or more buttons in the slicer box:
Basically, slicers and pivot table filters do the same thing - show some data and hide other. And each method has its strengths and weaknesses:
To get started with slicers, please follow the below guidelines that show how to add a slicer for your Excel table, PivotTable, or PivotChart.
Creating a pivot table slicer in Excel is a matter of seconds. Here's what you do:
As an example, let's add two slicers to filter our pivot table by Product and Reseller:
Two pivot table slicers are created immediately:
In addition to pivot tables, the modern versions of Excel also let you insert a slicer for a regular Excel table. Here's how:
That's it! A slicer is created and you can now filter your table data visually:
To be able to filter a pivot chart with a slicer, you can actually make a slicer for your pivot table like explained above, and it will control both the pivot table and the pivot chart.
To integrate a slicer with your pivot chart more closely like shown in the screenshot above, carry out these steps:
This will insert the already familiar slicer box in your worksheet:
Once you have a slicer, you can use it to filter the pivot chart data straight away. Or, you may want to make a few improvements, for example, hide the filter buttons on the chart, which have become redundant since you are going to use the slicer for filtering.
Optionally, you can place the slicer box within the chart area. For this, make the chart area bigger and the plot arear smaller (simply by dragging the borders), and then drag the slicer box to the empty space:
Tip. If the slicer box gets hidden behind the chart, right-click the slicer, and select Bring to Front from the context menu.
Excel slicers were designed as user-friendly filter buttons, so their use is simple and intuitive. The below sections will give you some hints on how to get started.
Once a pivot table slicer is created, simply click on one of the buttons inside the slicer box to filter your data. The pivot table will update immediately to show only the data that matches your filter settings.
To remove a specific item from the filter, click the corresponding button in the slicer to unselect the item.
You can also use a slicer to filter data that are not shown in the pivot table. For example, we can insert the Product slicer, then hide the Product field, and the slicer will still filter our pivot table by product:
If multiple slicers are connected to the same pivot table and clicking a certain item inside one slicer makes some items in the other slicer greyed out, that means there is no data to display.
For example, after we select "John" in the Reseller slicer, "Cherries" in the Product slicer gets greyed out, indicating that John did not make a single "Cherries" sale:
There are 3 ways to select multiple items in an Excel slicer:
To move a slicer to another position in a worksheet, put the mouse pointer over the slicer until the cursor changes to a four-headed arrow, and drag it to a new position.
As with most Excel objects, the easiest way to change the slicer's size is by dragging the edges of the box.
Or, select the slicer, go to the Slicer Tools Options tab, and set the desired height and width for your slicer:
To fix the position of a slicer in a sheet, just do the following:
This will keep your slicer from moving as you add or delete rows and columns, add or remove fields from the pivot table, or make other changes to the sheet.
You can clear the current slicer settings in one of these ways:
This will remove the filter and select all items in the slicer:
To disconnect a slicer from a given pivot table, here's what you do:
Please keep in mind that it won't delete the slicer box from your spreadsheet but only disconnect it from the pivot table. If you'd like to restore the connection later, open the Filter Connections dialog box again, and select the slicer. This technique might come in handy when the same slicer is connected to multiple pivot tables.
To permanently delete a slicer from your worksheet, do one of the following:
Excel slicers are easily customizable - you can change their look and feel, colors, and settings. In this section, we will focus on how you can refine a slicer that Microsoft Excel creates by default.
To change the default blue color of an Excel slicer, do the following:
Tip. To see all available slicer styles, click the More button:
If you're not quite happy with any of the built-in Excel slicer styles, make your own one :) Here's how:
At first sight, certain slicer elements may sound confusing, but the below visual will hopefully give you some clues:
Tips:
When you have too many items in a slicer that do not fit within the box, arrange the items in multiple columns:
Now, you can select the slicer items without having to scroll up and down.
By using this approach, you can even make your slicer look like tabs behind your pivot table:
To achieve the "tabs" effect, the following customizations have been made:
One of the best things about Excel slicers is that they are fully customizable. You simply right-click the slicer, and click Slicer Settings… The Slicer Settings dialog box will show up (the screenshot below shows the default options):
Among other things, the following customizations might prove useful:
To build powerful cross-filtered reports in Excel, you may want to connect the same slicer to two or more pivot tables. Luckily, Microsoft Excel provides this feature too, and it does not require any rocket science :)
To link a slicer to multiple pivot tables, please follow these steps:
Alternatively, select the slicer, go to the Slicer Tools Options tab > Slicer group, and click the Report Connections button.
From now on, you can filter all the connected pivot tables with a single click on a slicer button:
In the same manner, you can connect one slicer to multiple pivot charts:
Note. One slicer can be connected only to those pivot tables and pivot charts that are based on the same data source.
When sharing your worksheets with other users, you may want to lock your pivot tables from editing, but keep the slicers selectable. Here are the steps for this set up:
Please see How to protect and unprotect Excel worksheet for more information.
Now, you can share your worksheets even with Excel novices without worrying about the safety of your data - other users won't mangle the format and layout of your pivot tables, but will still be able to use your interactive reports with slicers.
I hope this tutorial has shed some light on how to insert and use slicers in Excel. To gain more understanding, you are welcome to download our sample workbook with examples below. I thank you for reading and hope to see you on our blog next week!
Excel Slicer examples (.xlsx file)
Table of contents