One of the new features introduced with Excel 2010 is the slicer. This is a really useful feature which is used with pivot tables. This short article describes how to create and use slicers.

How about raising your Excel game by attending one of our Excel intermediate training courses?

Pivot tables have been around in Excel for many versions. However Excel 2010 has the added pivot table feature, the Slicer. Before describing how these are used, you might like to create a pivot table. This pivot table is based on a data list containing several headings or fields which are usually a mix of text, dates, and numerical data. The pivot table allows you to sort and then create subtotals for more than one data table heading. In the pivot table you need to choose at least one row header, one column header and then a numerical field to add in the pivot table cells where the rows and columns intersect.

Pivot tables have row and column field headings with auto filters shows as grey pop downs just above the row and column pivot fields. You can choose an auto filter selection and the pivot table obliges by filtering the data. You can then change the filter criteria, and/or apply another auto filter selection in the same way. One drawback in doing this, however, is that you don't know the auto filter selection you made, unless you just remember it. There is no visual indication on the pivot table filter of your selection.

There is where the SLICER comes into its own. In Excel 2010 you can insert a Slicer from the Pivot Table's Options tab. You can base a slicer on one of the fields used in the pivot table. So if you insert a slicer and choose the required field, the Slicer appears as a separate panel alongside the pivot table. The panel can be moved around and positioned anywhere you choose. The Slicer shows all the values in the pivot table field and the panel is tied to that field's auto filter. So you can select one or more fields in the Slicer and the pivot table will use this selection as the filter criteria, just as if you used the pop down auto filter criteria. The difference is that with the Slicer, you can see the selection(s) you made, as selections show as orange.

You can add additional Slicers around the pivot table to show auto filter criteria for as many pivot table fields as you like. This is useful for pivot tables containing multiple row and column based fields. You can also duplicate a Slicer by copying and pasting, or by adding the same Slicer again, and place each copy in separate parts of the worksheet. Each Slicer duplicate will always show the same selected filter criteria. Slicers can be deleted just as easily. To do this you select the Slicer and choose Delete on the keyboard.

Slicers can also be copied or moved to a different worksheet within the same Excel file and still be used to control the pivot table filters. So you could for example have a data summary in sheet1 of your worksheet along with several Slicers. These data summary cells and Slicers could be linked to pivot table cells in a different worksheet. You could then make choices for data filter analysis using the Slicers in sheet1 and see the results as a summary, still in Sheet1, with all the actual pivoting and filtering occurring in a different sheet.

So Slicers are a visual way of choosing filter criteria for pivot tables. Once you make filter selections, you can see these in the Slicers. And when using multiple fields in pivot table, you can also use multiple Slicers as an easy and visual way to choose filter criteria.

Interested in learning more about pivot tables and Slicers in Excel 2010? A really effective way is to attend a training course. That way you can really boost your Excel skills and knowledge.