Interactive Excel Dashboard Tips Using Slicers and Pivot Charts

We, as data analysts and Excel experts, are always trying to hunt down new and better techniques to create dashboards. Be it in terms of either design or interactivity, there is always something out there which will appeal to us. In the same vein, Excel MVP Mynda Treacy is here to show us some cool tips and tricks to make our dashboards appear more interactive.

So, what are we waiting for?

1 – The Dashboard

As depicted in the pictures below, this is something that can serve as part of a dashboard or a standalone, mini-visualization of some data.


There are a few things to note here:

  • We all know that the “Category” menu, which allows us to select a food item, is actually a Slicer.
  • We can also guess that the graph is a pivot chart, linked to some pivot table the slicer is controlling.
  • But it is a bit difficult to guess at the technique used behind making the table in the middle.

Well, let us not make anyone scratch their head on this one. Check out how it was made below.

2 – The Table

As we can see, that table is actually the last two columns of another pivot table. The Slicer was being used to cover up the names of food items in the first column. Also, the monthly data for each item has been hidden. This can be seen from the presence of the grouped columns sign above column O.

The observant amongst would have spotted these hidden columns even without the sign for grouped columns: by looking at the column names. There is no column showing between B and O!2

3 – Sparklines!

We know how to add Sparklines. We will just unhide the monthly data, add Sparklines on them and then hide the data again. Well, there is one problem with this method: Sparklines will not display the hidden data!

Don’t sweat! There is an inbuilt option in Excel that can remedy the situation. When we have a cell with Sparklines selected, go to the Design tab, click on Edit Data and then select ‘Hidden & Empty Cells…’. This is shown in the image on the right. Now tick the checkbox on ‘Show data in hidden rows and columns’, and click ‘OK’. Alas, it’s working!3

4 – Conditional Formatting

We know now what’s the last step. If anyone didn’t, the title of this section might have given it away. What is the trick behind making the rows of the table highlighted as corresponding food items are chosen from the Slicer. Yes, it’s conditional formatting.
The pivot (bar) chart has a pivot table working behind it. The Slicer is applying filters on it. Within that pivot table if we put ‘Category’ (or whatever the column containing the names is called) as a filter, it will appear at the top.

We would now be able to see that the filter (call it cell X) corresponds to the item selected from the Slicer. So, we can apply Conditional Formatting on the table, to highlight the rows which have the same food item as cell X. Simple, right?4

Get the download


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.