Excel: Slicers – Pivot table

Excel: Slicers – YOUR OBJECTIVE
You created a pivot table and now want to filter it using a variety of criteria. Until now you’ve used report filters to do this. A disadvantage of report filters is that it is not always easy to recognize which items have been filtered, especially when multiple filters have been applied. Excel now offers Slicers that give you the capability of filtering pivot tables quickly and dynamically, but above all in such a fashion that you can immmediately recognize the current filtering state.

Excel slicers contain buttons that correspond to each entry type for a particular field. For example, if there were three designations for customer groups in your original spreadsheed, the corresponding slicer will contain three appropriately labeled buttons.
The following illustration shows a pivot table that was filtered for Customer Groups A and B, using a report filter. It is not clear which filtering criteria were applied:

But just see the difference when same filtering is carried out using a slicer. In this case, the applied filtering criteria are obvious:

This learning module “Excel: Slicers – Pivot table” shows you how to use excel slicers to filter information in pivot tables.

Excel slicers: HOW DOES IT WORK?
Inserting excel slicers
To insert one or more excel slicers, go first to the Options tab, under PivotTable Tools. There, in the Sort & Filter group, click the  button.
The Insert Slicers dialog box will open. It lists all column labels in your original data table:

In this dialog box, you can select the criteria with which you want to filter. After activating the fields that interest you, click OK.

Excel slicers: HINT
When you select multiple fields, a slicer will be inserted for each field you select.

Excel slicers: HINT
You can select any column labels from the original source file, not just fields currently included in the pivot table.
For each field you choose, Excel will insert a corresponding slicer into the pivot-table worksheet. All displayed criteria are enabled by default. You can recognize active fields by the background color on their corresponding buttons. Naturally, when all criteria are selected in a slicer, no filtering for that field will take place.

Excel slicers: HINT
Using your mouse, you can conveniently position each slicer anywhere on the worksheet. In this way you can, for example, keep excel slicers from covering important information on your pivot table.
Filtering with excel slicers
Now, if you click any of the buttons in a slicer, the pivot table will be filtered by that criterion.

In the above example, the letter B was clicked. Consequently, the pivot table now considers data only for customers in Customer Group B.

Excel slicers: HINT
To filter by more than one criterion, hold the [Ctrl] key depressed while you click one criterion after another.

You can remove the filter or filters by clicking the icon in the slicer box:

When you do that, the pivot table will be displayed without filtering in that field.
If you’ve inserted several excel slicers, the filter criteria selected in them combine with one another, i.e., they are additive, each one reducing the data further. In the following illustration, for example, only data for customers in Customer Groups B & C in the Midwest sales region are displayed:

Because excel slicers remain visible even after applying the desired filtering, you can always see at a glance which filters are currently active.
Customizing the excel slicer box’s appearance
You can customize the slicer box’s appearance by varying its size and style. To do so, select the slicer box of interest by clicking anywhere on it. For this purpose, you’ll want to avoid the filter buttons. Then, on the Options tab, under Slicer Tools, choose the style you want to apply from the selection catalog in the Slicer Styles group. (For more styles, click the More button at the lower-right corner of the catalog.)

To adjust the slicer box’s size, click on a sizing handle at the sides or the corners of the slicer box, and then, while holding the mouse button depressed, push or pull the handle until you are satisfied with the box’s size. Alternatively, you can go to the Size group, on the Options tab, under Slicer Tools, to specify the exact size of the selected slicer box:

You can also adjust the size of the buttons in a slicer box. To do so, go to the Buttons group, on the Options tab, under Slicer Tools. There you can set the number of columns and the height and width of the buttons:

Removing excel slicer boxes
If you want to remove a slicer box, select the box first by clicking on any free spot in it. Then press the [Delete] or [Del] key on your keyboard.

Excel slicers: HINT
You can also remove the slicer box by right-clicking in it and then selecting the relevant command from the selection menu. In the current example, you would remove the Customer Group box by selectiong: Remove “Customer Group”.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook


Kein Banner zum Anzeigen