Shade the Normal Range in a Sparkline

August 08, 2023 - by Bill Jelen

Problem: The examples in Tufte’s book would often draw a rectangular box to show the expected normal range for a sparkline. This allowed you to see when the sparkline deviated from normal.

Strategy: This functionality is not built in to Excel sparklines, but you can use drawing tools to create a fairly good normal range.

These guidelines provide a rough set of steps for adding the shading:

Use the Axis Scaling settings and use a custom value for both the minimum and maximum value. This allows you to actually know that the sparklines range from exactly 0% to 1.5%.

Copy the real values for one or two sparklines and paste them outside of the data range. You are going to be temporarily changing those values and you want to paste the real values back into the dataset when you are done.

Suppose you want to draw a box for the caution range of 0.5% to 1%. Fill the values for one sparkline with 0.005. Fill the values for the next sparkline with 0.01.

Using Insert, Shapes, Rectangle and draw a rectangle over the sparklines. Make the bottom of the rectangle line up with the 0.005 sparklines and make the top of the rectangle line up with the 0.01 sparklines.

On the Drawing Tools Format tab, choose Shape Outline, None.

Choose Shape Fill, and choose a color for the highlighted area.

Press Ctrl+1 or use the dialog launcher in the Shape Styles group to get to the Format Shape dialog box. There is a Transparency slider in the Fill category of the dialog box. Change the slider up to 75% transparent. Click Close to close the dialog box.

Adjust the top and bottom of the shape one last time so that they line up with the 0.005 and 0.01 guides in the two temporary sparklines.

Click away from the rectangle shape.

Copy the real values from the temporary area back to the sparkline data.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nathan Anderson on Unsplash