Categorized | excel 2010, pivot table

Slicer in Excel – The Pivot Table Remote Control




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Slicer

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.

slicers-in-excel-2010

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.

inserting-a-slicer-in-excel-2010

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.

insert-slicer-message-box

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:

slicer-settings

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.

Slicer Settings


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.

To close a slicer, simply right click on the slicer and select “Remove”. Better still, select the slicer and press “Delete” :-)

You can also change the height and width of the slicer button, right click and choose “Size and Properties”. While at it, you can also sort the slicer fields.

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:

slicer-in-excel
.

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Ryan Marsh wrote:

    Whats the most straightforward way to implement something like this that would be compatible with 2003?

  2. Ajay wrote:

    @Ryan – Here are some VBA code snippets to work with pivot tables. You could construct a ‘slicer’ by modifying parts of this code. Once you’ve read the list of pivot fields (and the corresponding pivot items) into an array, you can then probably use a set of visual basic listbox (with multi select option turned on) to present that information to the user. The listbox’s ListBox1_Click() can be used to respond to the click action on any of the slicer items by the user.

    Regards

  3. André Luiz Bernardes wrote:

    Hi I like translation your article “” to portuguese and publish in my blog: http://brzaccessdeveloper.blogspot.com/

    I´ll have your permission???

    André Bernardes

  4. Ajay wrote:

    @André – by all means :-) (a credit would be great though)
    Regards,

  5. Mary wrote:

    Need to know the VALUE of the slicer chosen by the user. My report has 5 slicers. I want to label the report — and the graphs — using the values of the slicers chosen. For example, “Areas=All”, “Industry=Banking, NonProfit”, and “Key Account = No”.

  1. Excel 2010 - Beta Review | Excel & VBA - da Tab Is On wrote:

    [...] 2010 introduces a new feature called the slicer. Think of a slicer as a ‘remote control’ for a pivot table. A silcer serves the same [...]

  2. Slicer VBA Code - Create, Change and Modify Slicer using VBA | Excel & VBA - da Tab Is On wrote:

    [...] Slicer in Excel – The Pivot Table Remote Control [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel