Skip to main content

Excel 2010 Slicers

Slicers are a new tool in Excel 2010 which make it easier to arrange data from a PivotTable, to extract meaningful relationships between data elements. PivotTables, in themselves, can filter the way information is viewed, but Slicers make this easier and more flexible.

The example worksheet we're using in this tutorial contains the kind of data you might have from test results in a school or college. The raw data shows the scores for two students, Jane and Catherine, in six different subjects, over three months. In real life, of course, the data set could be far more extensive, covering whole classes of students over full academic years.

To make more sense of it, for example to compare the overall scores for the two students in a single subject, would be longwinded working from the table here, but once a pivot table is made of the figures, it becomes far easier to work with.

If you have always been a bit nervous of PivotTables, just think of them as a reporting tool. To make one from the data here, simply highlight the table, click on the Insert tab in the ribbon and then on PivotTable. Accept the range shown in the Create PivotTable dialog and Excel creates a new worksheet and switches to it.

On the PivotTable worksheet, select all the column headings from the original table, which are now labelled as fields in the PivotTable Field List dialog. As you do this, the PivotTable builds on the left-hand side of the worksheet. The information is still not that much easier to understand, though, which is where the Slicers come in.

Click on the Insert tab and then on Slicer in the Filter group. The Insert Slicers dialog opens and you can click in each of the check boxes, which are again the column headings from the original table. For each box, a corresponding Slicer appears. Slicers are graphic objects, floating over the top of the worksheet, and can be moved around by dragging them.

Each contains the selections available under a column heading so, for instance, the Month Slicer contains selections for October, November and December and the Student Slicer contains Catherine and Jane. Drag the Slicers to the right-hand side of the screen, to keep them out of the way of the table.

Now they can be used to filter the information. Say, for example, you want to compare the overall scores of the two students in Art over the three month period we have data for. In the Student Slicer, click Catherine, in the Subject Slicer click Art and leave the Score and Month Slicers with all their options selected.

The table is now reduced to show the combined score for Catherine, which is 140. With the sample data, if you select Jane instead of Catherine in the Student Slicer, you can see Jane's aggregate score is 152.

Slicers make analysing data in Excel spreadsheets a lot easier than using PivotTables alone and, once you get the hang of them, are easier to use than most other analysis tools.