Customize Sparklines in Your Excel Dashboards and Reports

By Michael Alexander

Sparklines give you the ability to show data trends in your Excel dashboards and reports. When you activate a cell that contains a sparkline, Excel displays an outline around all the sparklines in its group. You can then use the commands on the Sparkline Tools→Design tab to customize the group of sparklines.

Handle hidden or missing data in Excel dashboards and reports

In some cases, you just want to present a sparkline visualization without the numbers. One way to do this is to hide the rows or columns that contain the data. This figure shows a table with the values displayed, and the same table with the values hidden (by hiding the columns).

image0.jpg

By default, if you hide rows or columns that contain data used in a sparkline graphic, the hidden data does not appear in the sparkline. In addition, blank cells are displayed as a gap in the graphic.

To change these default settings, go to the Sparkline Tools tab on the Ribbon, and select Design→Sparkline→Edit Data→Hidden & Empty Cells. In the Hidden and Empty Cell Settings dialog box, specify how to handle hidden data and empty cells.

Change the sparkline type in Excel dashboards and reports

Excel supports three sparkline types: Line, Column, and Win/Loss. After you create a sparkline or group of sparklines, you can easily change the type by clicking the sparkline and selecting one of the three icons located under Sparkline Tools→Design→Type. If the selected sparkline is part of a group, all sparklines in the group are changed to the new type.

If you’ve customized the appearance, Excel remembers your customization settings for each sparkline type if you switch among different sparkline types.

Change sparkline colors and line width in Excel dashboards and reports

After you create a sparkline, changing the color is easy. Simply click the sparkline, click to open the Sparkline Tools tab on the Ribbon, and select Design→Style. There you find various options to change the color and style of your sparkline.

For Line sparklines, you can also specify the line width. Choose Sparkline Tools→Design→Style→Sparkline Color→Weight.

Colors used in sparkline graphics are tied to the document theme. If you change the theme (by choosing Page Layout→Themes→Themes), the sparkline colors then change to the new theme colors.

Use color to emphasize key data points in Excel dashboards and reports

Use the commands under Sparkline Tools→Design→Show to customize the sparklines to emphasize key aspects of the data. The options in the Show group are:

  • High Point: Apply a different color to the highest data point in the sparkline.

  • Low Point: Apply a different color to the lowest data point in the sparkline.

  • Negative Points: Apply a different color to negative values in the sparkline.

  • First Point: Apply a different color to the first data point in the sparkline.

  • Last Point: Apply a different color to the last data point in the sparkline.

  • Markers: Show data markers in the sparkline. This option is available only for Line sparklines.

You can control the color of the sparkline by using the Marker Color control in the Sparkline Tools→Design→Style group. Unfortunately, you cannot change the size of the markers in Line sparklines.