Make your dashboards interactive
Sometimes it’s too hard to include everything that a reader might want to examine for decision support. One way to present more information concisely is to add some interactive features, allowing the reader to dice, slice and customize what is being reported. Here are three tools that can do just that if you use pivot tables and pivot charts in building dashboards.
Slicers are a way to filter your pivot table report by any field. Using multiple Slicers can also provide information as well as filter. Let’s begin with a single Slicer first. Here are the steps:
1. From inside the pivot table, click into the Pivot Table tools Analyze (Options in Office 2010) tab.
2. In the Filter group, click the Insert Slicer button. You can also find the Insert Slicer button on the Insert tab.
3. Select the field you wish to apply to the Slicer.
HINT: If you don’t see your fields, but a list of data connections, close the dialog box and click into any one cell of data inside the pivot table, not in a blank cell outside it. It will normally default to somewhere in the middle of your worksheet.
One way to make it easier on the eyes is to insert a blank column to the left of column A and drag the Slicer over there. Should your pivot table grow and change as a result of clicking on the Slicer, the Slicer itself won’t move.
Adding a second Slicer gives added functionality. You can see in the diagram that when you filter for something in one Slicer, it only shows you the filtered available fields in the second Slicer.
Use the Slicer Tools contextual tab to apply different colors to multiple Slicers for better visual cues. (Available in PC Office Versions 2010 and Mac Office 2016.)
Report Filters actually do what Slicers do. They’ve just been doing them longer! While slicers are visually appealing and easy to use, Report Filters are more robust. Let’s talk about how to add one, then what it can do.
1. From your Pivot Table Fields panel, drag the field you wish to use to Report Filter to the upper left quadrant of the layout area.
2. Select the field or fields to filter by in the area provided above your pivot table.
One thing that Report Filters can do that Slicers can’t is search for your filtering criteria. So, if you have large lists from which to filter, a Report Filter may be more useful than a Slicer. (Available in all versions of Excel on both PC and Mac.)
Timelines were introduced in Office 2013 and are another type of slicer. You insert it the same way, but it will only offer the fields that contain valid date data.
If you don’t see a field listed that you believe should be, it’s likely that while the data might look like dates, it is not formatted that way. An easy check to make is to clear the formats on the cell. A proper date will go back to its serial number form, while a text string will remain the same.
Slicers and Report Filters will also affect the content of charts and graphs created from the same pivot table.
Tip: Be sure to put instructions on your dashboards, letting readers know how to use them to create the exact views they need.