top of page

Slice & Dice - Slicers Part 1/2


What are slicers? In this two-parter, I'm going to show you how to create slicers and how to use one slicer to control more than one pivot tables.

To answer my question, slicers are:

  • Used to filter data visually

  • Make it faster and easier to filter Tables, PivotTables, PivotCharts and Cube functions

  • A great addition to dashboards

Note: Slicers are only available in Excel 2010 or above and the screenshots below were taken using Excel 2013 so there may be slight variances as to where things are located in previous versions of Excel.

Example

I'm going to create a slicer using my "Crime Statistics" data, based on ‘Council Area’ from the PivotTable below:

To insert slicer:

STEP 1

Click anywhere inside the PivotTable > ANALYZE tab > under “Filter” select “Insert Slicer” > select which field(s) you would like the slicer to reflect.

I want to add a slicer on "Council Area". To do that, I simply have to tick the box next to "Council Area" and click OK.

STEP 2

When I say “filter data visually”, what that means is if I click on any Council Area(s), my PivotTable will show the Council Area(s) I’ve selected. To select more than one Council Area (in a row), click and drag.

To select non-contiguous (not in a row) items (in this case Council Area), after clicking one item, hold down CTRL key before selecting the next.

STEP 3

I can format my slicer by clicking on the slicer > under “Slicer Tools” > OPTIONS tab. I can make changes such as renaming the slicer, changing the slicer colour, adding more columns so that my slicer won’t be so long, adjusting the height & width, etc.

I can make more changes to the slicer by clicking on "Slicer Settings"

There, I can rename by slicer, choose to show or hide items visually / with no data, etc.

Note that slicers work best when your data is not cumulative (i.e. at a particular point in time). If it is and you do use it, you'll notice that if you don't select an item (i.e. filter is cleared) the total value (for example, a bank balance) will be overstated.

There are other ways you can present such data visually by using 'combo boxes' / 'data validation' which I will cover in future posts.

In the meantime, have a go at creating slicers here: Crime Statistics

Notes: Categorical data and images of this tutorial are based on the following:

Comments

bottom of page