Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Getting to know Excel 2010: Slicers

by
12th Nov 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst explores the potential of the new Slicers tool that will be included in the next release of Excel.

I must admit to having been slightly underwhelmed when I first started experimenting with Slicers. However, having looked at a series of posts on the Excel Team Blog, the idea of Slicers makes a bit more sense.
Essentially a Slicer is a floating object that contains buttons for filtering a pivot table by all the values for a particular field. For example, the data that we used for our Northwind Invoice pivot table in the previous article on Excel 2010 Sparklines includes a ‘Country’ field.

Here we have selected a cell in our pivot table and chosen Slicer from the Filter group of the Insert tab. We have then chosen the Country field from the list of available fields in our pivot table data. This creates a floating Slicer with a set of country buttons. Clicking on a button filters our pivot table to show values for just that country.

So far, this doesn’t seem a massive step forward – you could add the country field as a report filter to do much the same thing without worrying about the new Slicer tool. However Slicers are all about making it easier to interact with pivot tables and making it easier to see what the pivot table is showing you. If you only select one country then the report filter will show the name of the country but if you select multiple countries, then it will just show ‘Multiple Items’ making it impossible to see at a glance what the pivot table is showing.

Here we have moved our country Slicer to sit beneath our pivot table and used the Slicer Tools contextual tab to change the number of columns to fit the space better. We have then used Control+Click to select several countries. In contrast to the report filter, the slicer clearly shows which countries are selected:
 

Excel 2010 Slicer - data selection

Multiple items can be selected in the Slicer using standard windows techniques such as Control+Click to toggle the selection of individual items, Shift-Click to select a block and Control+drag with the mouse.
The appearance of the Slicer can be changed using the different styles to highlight selected fields and fields with no data.

The increased visibility and ease of use of filtering a pivot table with a slicer are useful, but perhaps not earth-shattering. However, it becomes more impressive when we look at using more than one slicer or attaching slicers to more than one pivot table. Here we have set up three slicers for country, order date and product name. As items are selected in one slicer, the other slicers change to show items for which there is now no data at the end of the list:

Excel 2010 Slicer - multiple selection
 
Finally, we’ll look at attaching a slicer to more than one pivot table. Here we’ve created three separate pivot tables, all based on our Northwind invoices data. We have a top 10 ‘league table’ of sales by country, a chart showing sales by salesperson and a second chart showing sales by order date period. We have then added a slicer based on Product name.

In order to connect this slicer to all of our pivot tables, we click on the ‘PivotTable Connections’ button on the Slicer Tools contextual tab. This lets us select which of the pivot tables in this workbook we want to connect to. Having connected to the three required pivot tables, changing the items selected in the slicer will apply the filter to all of the connected pivot tables. This means we can create an interactive presentation of our data without using a single formula or line of code:
 
Excel 2010 Slicer - PivotTable Connections

Replies (1)

Please login or register to join the discussion.

avatar
By Trevor Scott
12th Nov 2009 23:57

Office 2010

Have only used Word and Excel 2010 in Beta versions. Initially thought them no different to 2007 but they are more slicker/polished products than 2007, user friendly, clearer, not so confused and I especially prefer Word 2010 to 2007. Using Word menu items like the navigation plane are so much more helpful and professional.  

Thanks (0)