Excel slicers for pivot tables and charts

How to add a slicer for Excel pivot table

Creating a pivot table slicer in Excel is a matter of seconds. Here's what you do:

1. Click anywhere in the pivot table.
2. In Excel 2013 and later, go to the Analyze tab > Filter group, and click the Insert Slicer button. In Excel 2010, switch to the Options tab, and click Insert Slicer.
3. The Insert Slicers dialog box will pop up and show the checkboxes for each of your pivot table fields. Select one or more fields for which you want to create a slicer.
4. Click OK.

As an example, let's add two slicers to filter our pivot table by Product and Reseller:

Two pivot table slicers are created immediately:

How to create a slicer for Excel table

In addition to pivot tables, the modern versions of Excel also let you insert a slicer for a regular Excel table. Here's how:

1. Click anywhere in your table.
2. On the Insert tab, in the Filters group, click Slicer.
3. In the Insert Slicers dialog box, tick off the check boxes for one or more columns that you want to filter.
4. Click OK.

That's it! A slicer is created and you can now filter your table data visually:

How to insert a slicer for pivot chart

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:

1. Click anywhere in your pivot chart.
2. On the Analyze tab, in the Filter group, click Insert Slicer.
3. Select the checkboxes for the slicer(s) you want to create, and click OK.

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:

Slicer as a visual pivot table filter

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:

How to select multiple items in a slicer

There are 3 ways to select multiple items in an Excel slicer:

• Click the slicer buttons while holding the Ctrl key.
• Click the Multi-Select button (please see the screenshot below), and then click on the items one by one.
• Click anywhere inside the slicer box, and press Alt + S to toggle on the Multi-Select button. Select the items, and then press Alt + S again to toggle multi-selection off.

Move a slicer in Excel

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.

Resize a slicer

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:

Lock the slicer position in a worksheet

To fix the position of a slicer in a sheet, just do the following:

1. Right click the slicer, and then click Size and Properties.
2. On the Format Slicer pane, under Properties, select the Don't move or size with cells box.

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.

Clear slicer filter

You can clear the current slicer settings in one of these ways:

• Click anywhere in the slicer box, and press the Alt + C shortcut.
• Click the Clear Filter button in the upper right corner.

This will remove the filter and select all items in the slicer:

Disconnect slicer from a pivot table

To disconnect a slicer from a given pivot table, here's what you do:

1. Click anywhere in the pivot table from which you wish to disconnect a slicer.
2. In Excel 2013 - 365, go to the Analyze tab > Filter group, and click Filter Connections. In Excel 2010, go to the Options tab, and click Insert Slicer > Slicer Connections.
3. In the Filter Connections dialog box, clear the check box of the slicer you want to disconnect:

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.

Change slicer style

To change the default blue color of an Excel slicer, do the following:

1. Click on the slicer for the Slicer Tools tab to appear on the ribbon.
2. On the Slicer Tools Options tab, in the Slicer Styles group, click on the thumbnail you want to use. Done!

Create a custom slicer style in Excel

If you're not quite happy with any of the built-in Excel slicer styles, make your own one :) Here's how:

1. On the Slicer Tools Options tab, in the Slicer Styles group, click the More button (please see the screenshot above).
2. Click the New Slicer Style button at the bottom of the Slicer Styles gallery.
3. Give a name to your new style.
4. Select a slicer element, click the Format button, and choose the formatting options for that element. When finished, move on to the next element.
5. Click OK, and your newly created style will appear in the Slicer Styles Gallery.

At first sight, certain slicer elements may sound confusing, but the below visual will hopefully give you some clues:

• "With Data" elements are slicer items associated with some data in the pivot table.
• "With no Data" elements are slicer items for which there is no data in the pivot table (e.g. the data was removed from the source table after a slicer was created).

Multiple columns in Excel slicer

When you have too many items in a slicer that do not fit within the box, arrange the items in multiple columns:

1. With the slicer selected, go to the Slicer Tools Options tab > Buttons group.
2. In the Columns box, set the number of columns to show inside the slicer box.
3. Optionally, adjust the height and width of the slicer box and buttons as you see fit.

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:

• The slicer was set up in 4 columns.
• The slicer header was hidden (please see the instructions below).
• A custom style was created: the slicer border was set to none, the border of all items and the fill color of the "Selected item with data" was set to match the color of pivot table's header row. Please see How to create a custom slicer style for more details.

Change the slicer settings

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:

• Hide the slicer header by clearing the Display header box.
• Sort slicer items ascending or descending.
• Hide items with no data by unselecting the corresponding box.
• Hide items deleted from the data source by clearing the relevant check box. With this option unchecked, your slicer will stop showing old items that were removed from the data source.