What is a Slicer? One can think of a slicer as an extension of a pivot table which makes the job of filtering the pivot table data easier. One shortcoming that pivot tables earlier had was that you could not control them remotely. Short of VBA, you could do things with pivot tables only when you had one in front of you. A slicer on the other hand acts as a ‘remote control’ for the pivot table. You can place a slicer anywhere you want and without needing to move the pivot table, use the slicer to filter out data.
How do I create a slicer?
To create a slicer, we begin by creating a pivot table first. Once that is done, we select a cell within the pivot table. Click on the ‘Insert’ option in the ribbon and then click the ‘slicer‘ button.
An insert slicer box appears which shows you the list of available pivot fields that you can choose from. Pick the ones you would like to use.
A floating box based on the pivot field you choose appears. It will contain all the unique values in the raw data. You can turn on more than one slicer at a time. To filter the pivot table, simply click the desired value in a particular slicer and the pivot table would adjust to show you the pertaining to the selection you make. So in essence, slicers are more or less enhanced pivot table filters.
How does a slicer work in Excel 2010
Here are a few things that you can do with a slicer in Excel 2010:
Click on a slicer and filter the pivot table
Intuitive and simple (and obvious). Clicking on any value in the slicer, as explained above, will help you filter the pivot table. You can choose one or more slicers at a time. If you want to select and pick more than one sliver, press the Ctrl key and while keeping it pressed, click on the fields in the slicer one by one. The moment you release the Ctrl key, the pivot table will update itself. You can also pick up a field from the slicer and drag-drop it over the pivot table
Tip- You can create multiple copies of the same slicer by simply copy pasting it to the same or another sheet !
Place them in another sheet and use them
This is the where the fun starts. Place the slicer anywhere in the workbook and it will still work. Let’s take an example. Imagine your pivot table is palced in Sheet1 and the slicer is placed in sheet3. Now you have a good looking dashboard in Sheet3 and a number of cells in that sheet refer to the pivot table in sheet 1. Using the slicer, you can easily filter out the pivot table in Sheet1 and the cells in Sheet3 would get updated the new values – all without having to access the pivot table. Sweet no !
Slicers work on Fields not part of Pivot Table display
In the earlier versions of the pivot table, you could only slice the data based on all the fields that were ‘visible” – meaning had been explicitly made part of the pivot table display. Let’s take an example. You have a pivot table based on an underlying data set with five data points – Product Name, Region, Manager, Sales Volume and Date of Sale. We create a pivot table in which the the managers feature in the row fields, the products in the column fields along with the sales volume summarized in the data field. In a normal pivot table, you would be able to use only the fields that you see on the screen to filter the pivot table. If you wanted to filter on say ‘Region’, a field that we did not make a part of the pivot table display, we’d have to go to the pivot table wizard (Excel 2003) or the pivot table field list (Excel 2007) and then put a filter on this field. A slicer makes short work of this job. All you have to do is to turn on the slicer for that particular field (remember – a slicer can be turned on even for fields that are not part of the pivot table) and start using it.
In our case, this is how a full pivot table would look like:
To filter out on a particular field that’s not part of the pivot table display (in this case “Region”), we simply use the slicer for that field to do the job for us. As you can see, the other slicers adjust accordingly to disable selection of their own fields that are no longe
Each pivot table has its own slicers. So if you have two pivot tables based on the same underlying source data and having the same field list and structure, a slicers for both the pivot tables – even if they are based on the same field, would work independently.
You can change how to slicer is displayed including the name and the caption. The caption is particularly useful – if you are using more than two slicers based on the same field, one can change the caption to distinguish between them.
Still hungry for some more fun – Try using VBA with slicers!!!
So that was a quick round up on the slicer feature in Microsoft Excel. You can download a workbook with a pivot table and a slicer here or click on the button below: