MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Build Dashboards with Sparklines and Slicers


May 30, 2019 - by Bill Jelen

Build Dashboards with Sparklines and Slicers. Photo Credit: Carlos Muza at Unsplash.com

New tools debuted in Excel 2010 that let you create interactive dashboards that do not look like Excel. This figure shows an Excel workbook with two slicers, Region and Line, used to filter the data. Also in this figure, pivot charts plus a collection of sparkline charts illustrate sales trends.

A dashboard with two charts, sparklines and two slicers.

You can use a setup like this and give your manager’s manager a touch screen. All you have to do is teach people how to use the slicers, and they will be able to use this interactive tool for running reports. Touch the East region and the Books line. All of the charts update to reflect sales of books in the East region.


Choose from the slicers and all three visualizations update.

Switch to eBooks, and the data updates.

Choose different slicers and the charts update.

Pivot Tables Galore

Arrange your charts so they fit the size of your display monitor. Each pivot chart has an associated pivot table that does not need to be seen. Those pivot tables can be moved to another sheet or to columns outside of the area seen on the display.

Note

This technique requires all pivot tables share a pivot table cache. I have a video showing how to use VBA to synchronize slicers from two data sets at http://mrx.cl/syncslicer.

The dashboards in the previous screenshots were in rows 1-20.  This image shows the pivot tables starting in row 41 that are used to drive all of the charts on the dashboard.

Filter Multiple Pivot Tables with Slicers

Slicers provide a visual way to filter. Choose the first pivot table on your dashboard and select Analyze, Slicers. Add slicers for region and line. Use the Slicer Tools tab in the Ribbon to change the color and the number of columns in each slicer. Resize the slicers to fit and then arrange them on your dashboard.

Initially, the slicers are tied to only the first pivot table. Select a cell in the second pivot table and choose Filter Connections (aka Slicer Connections in Excel 2010). Indicate which slicers should be tied to this pivot table. In many cases, you will tie each pivot table to all slicers. But not always. For example, in the chart showing how Books and eBooks add up to 100%, you need to keep all lines. The Filter Connections dialog box choices for that pivot table connect to the Region slicer but not the Line slicer.

Select one pivot table. Go to Filter Connections on the Analyze tab. Connect the pivot table to both the Line and Region slicer by checking both checkboxes next to the list of Slicers.

Sparklines: Word-Sized Charts

Professor Edward Tufte introduced sparklines in his 2007 book Beautiful Evidence. Excel 2010 implemented sparklines as either line, column, or win/loss charts, where each series fills a single cell.

Personally, I like my sparklines to be larger. In this example, I changed the row height to 30 and <gasp> merged B14:D14 into a single cell to make the charts wider. The labels in A14:A18 are formulas that point to the first column of the pivot table.

A sparkline is a chart that fits in one cell. This report has five sales reps in A14:A18. Five charts in B14:B18 show the 9-year sales trend for each sales rep. This screenshot shows the Create Sparklines dialog, where you specify the original data location and where you want the sparklines to appear.

To change the color of the low and high points, choose these boxes in the Sparkline Tools tab:

In the Sparkline Tools tab of the Ribbon, specify that you want the high point and the low point in a different color.

Then change the color for the high and low points:

Also in the Sparkline Tools dialog, open the Marker Color drop-down. Choose a color for High Point and a color for Low Point.

By default, sparklines are scaled independently of each other. I almost always go to the Axis settings and choose Same for All Sparklines for Minimum and Maximum. Below, I set Minimum to 0 for all sparklines.

In the Sparkline Tools ribbon, got to the Axis drop-down menu. Make two changes, selecting "Same for All Sparklines" for both the vertical axis minimum and maximum.

Make Excel Not Look Like Excel

With several easy settings, you can make a dashboard look less like Excel:

  • Select all cells and apply a light fill color to get rid of the gridlines.
  • On the View tab, uncheck Formula Bar, Headings, and Gridlines.

  • In the View tab of the Ribbon, unselect Formula Bar, Gridlines, and Headings
  • Collapse the Ribbon: at the right edge of the Ribbon, use the ^ to collapse. (You can use Ctrl+F1 or double-click the active tab in the Ribbon to toggle from collapsed to pinned.)
  • Use the arrow keys to move the active cell so it is hidden behind a chart or slicer.
  • Hide all sheets except for the dashboard sheet.
  • In File, Options, Advanced, hide the scroll bars and sheet tabs.

    Excel Options, Advanced, Display Options For This Workbook. Turn off the checkboxes for Show Horizontal Scrollbar, Show Versical Scrollbar, and Show Sheet Tabs.

Jon Wittwer of Vertex42 suggested the sparklines and slicers trick. Thanks to Ghaleb Bakri for suggesting a similar technique using dropdown boxes.

Title Photo: Carlos Muza at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.