How To Use Slicers In Excel 2010

Tue 23rd August 2011

One of the very useful new features introduced in Excel 2010 is the slightly strange sounding term Slicers. These are used only with Pivot Tables and let you easily see and change settings for autofilter applied to a pivot table. This article describes how to use them and summarises their features.

One of the very useful new features introduced in Excel 2010 is the slightly strange sounding term Slicers. These are used only with Pivot Tables and let you easily see and change settings for autofilter applied to a pivot table. This article describes how to use them and summarises their features.

To use a slicer you first need a pivot table. So you might like to create your own example or use an existing pivot table. Once the pivot table is created you can of course use the pivot frame to change the fields displayed in the pivot table. You can also show or hide Field Headers. In the field headings on the pivot table you'll also see the familiar autofilters with the grey pop down selectors. You can then select an autofilter for a particular field, choose a criteria and apply the apply. Unfortunately once the autofilter is applied you can no longer see the criteria details you selected. This can be a significant drawback when working with relatively complex pivot tables. Slicers have been introduced to overcome this - they show filter criteria.

To start using Slicers we'll start with a completed pivot table. With the pivot table still selected ensure you are viewing the Options tab, then choose Insert Slicer. In the Insert Slicers panel you'll see a list of all the original data fields from the data source. Note that all the original data fields are here, not just the ones you've used in the pivot. In the Insert Slicers panel try selecting one field and clicking OK. The Slicer appears in a new panel with a list of all the values in that field in the table. If you then select more than one field in the Slicer panel, one Slicer will appear for each field selected. Each Slicer can be positioned independently on the worksheet.

Slicers are actually just simple visual filters for the pivot table. Try clicking on one of the values in the Slicer. You'll see that the pivot table is filtered for this value, and the Slicer shows the selected value. If you happen to have chosen a Slicer for a field already in the pivot table, try selecting the pivot table's autofilter for that field. You'll see the value chosen in the Slicer selected in the Autofilter. If you click the value in the slicer again to deselect it, the pivot table returns to the original list. So in essence Slicers are visual filters which let you choose criteria to autofilter the pivot table.

What makes Slicers particularly useful is that you can easily apply an autofilter based on any field from the table. And once applied you can see the selection made, so you can tell from the Slicer what autofilter criteria are being used. An even more useful feature is that if you click off the pivot table to deselect it, the Slicers still show, and still work. So you can arrange a pivot table with one or more Slicers alongside, and apply the various criteria, then unselect the pivot table. All the Slicers still show so you can tell at a glance what filtering is in place.

Because the Slicer panel is actually an image, you can edit it in the usual way. So you can move, resize or delete a Slicer just as you would do for a picture or shape. You can also copy and paste a Slicer, either within the same worksheet, or to another worksheet or workbook. If you do end up with two Slicers for the same field you can select a field value from either Slicer.

A Slicer also has its own context sensitive Options tab which you can see if you select a Slice. This tab lets you change Slicer settings and choose different Slicer styles, change the Slicer caption and change the Slicer sort settings. You can also arrange multiple Slicers in layers and change the layer order and make some Slicers invisible. This can be a really useful feature if you set up several Slicers for a pivot table, but want to send different Slicer views to different people without needing to setup separate pivot tables. You can also use Slicers for more than one pivot table at time, by using the Pivot Table Connections command in this tab.

So Slicers are a very powerful and easy way to apply filtering to one or more pivot tables or pivot charts. The Slicer panels let you view or change filter criteria, and multiple Slicer panels can be arranged around a relatively complex pivot table to allow easy filter management.

Interested in learning more about Excel 2010? You might like to consider attending a training course. This way you can learn lots more about Excel in a short time and take your Excel skills to the next level.

Author is a freelance copywriter. For more information on excel course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1876-how-use-slicers-in-excel-2010.html