Use slicers to create interactive, dynamic Excel charts and dashboards

posted May 8, 2014, 1:31 AM by Krisztina Szabó   [ updated May 8, 2014, 6:13 AM by r ]
Freddy: Well, we'll see. Good night.
Fraü Blucher: Would the doctor care for a brandy before retiring?
Freddy: No, thank you.
Fraü Blucher: Some warm milk, perhaps?
Freddy: No, thank you very much. No thanks.
Fraü Blucher: Ovaltine?
Freddy: Nothing, thank you. I'm a little tired.
Fraü Blucher: Then I will say good night.
Freddy: Good night.
Fraü Blucher: Good night, darling. Good night, Herr Doktor.
Freddy: Good night, Fraü Blucher.

by The FrankensTeam

We would like to share an easy and simple method how you can improve user interaction - for example in case of complex, interactive charts or dashboards - with the help of slicers! Slicers were introduced by Microsoft in Excel 2010 to make easier and more visible filtering data in pivot tables and pivot charts - but now we will show you a slightly different usage...

The method is similar to the way of working of Excel’s built-in List box form control, but slicers look way much more better! The user will see all the possible choice values on the slicer and can click on one. Then we can use the chosen value for whatever we want. The trick is that we use the pivot table behind the slicer only for capturing the chosen value.

Let’s see step by step:

1) Create a Pivot table using your original database or alternatively you can set up a separated list as base of the pivot table.
2) Add the field you would like to use on the slicer to the row labels of the Pivot table.
3) Insert slicer and link it to the field.
4) Use the pivot table row label record cell as if it was the linked “output” cell of the slicer. You can refer this cell in the formulas feeding your chart.
5) You can move the slicer to other worksheet, also you can size it to be in-line with a data table on your sheet.

Using this way, slicers could be great (and no-VBA) alternative to active cell based or mouse-rollover based dynamic solutions, and also can replace validation lists or form controls. Using the pivot table cell as "output cell" makes easy to apply the technique in existing models.
You can build dashboards or other complex charts, and also you can use the trick with chart types which are not allowed to be used as Pivot Chart (xy scatter, bubble and stock charts).

Here is an example (detail) of a recent dashboard-work created by The FrankensTeam:

Or another example of our Chord diagram - you can also see and compare the two other versions of this chart: months ago we created it with mouse rollover and validation (no VBA) too. We have changed it to work with slicers - it took only a minute. If you are interested, you can read the original post.

Leave a comment