- Adding Sparklines to a Worksheet
- Understanding the Axis Size of Sparklines
- Creating Column Sparklines for High Temperature Data
- Forcing Each Sparkline to Have the Same Scale
- Adding Labels and Shading the Normal Range on a Sparkline
- Adding a Reference Line
- Adding Shading to Show the Normal Range
- Showing Negative Values
- Win/Loss Sparklines
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Adding Labels and Shading the Normal Range on a Sparkline
Professor Tufte's original sparklines often had a shaded band that showed the acceptable normal range for a measurement. The built-in Sparkline tools in Excel 2010 do not offer this functionality, but you can use Excel's drawing tools to add such shading.
Labels showing the max and min values will help to provide a sense of the vertical scale of the sparkline.
Consider the sparklines shown in the following figure.
){kind=small}
The labels and shading are created outside of the Sparkline tool, using regular Excel.
Chart titles of "Line 1" and so on are text typed in the cell that contains the sparkline. Set the vertical alignment to top and make the font size smaller.
The Max and Min label to the left sparkline is created by typing 1.5%, pressing Alt+Enter three times; then typing 0%. If you wanted those values to be calculated in response to the data, you could use this formula: =MAX(D6:H13)&"%"&REPT(CHAR(10),3)&MIN(D6:H13)&"%"
If you are using the formula, be sure to turn on Wrap Text using the icon in the Alignment group of the Home ribbon tab. Use a smaller font size. Adjust the row height so that both values fit in the cell and so that the min is near the bottom of the cell.
The hour labels along the horizontal axis are created by typing this: 7<alt+enter>8<alt+enter>9<alt+enter>10<alt+enter>11<alt+enter>12<alt+enter>1<alt+enter>2<alt+enter>
This will create a cell that is very tall. On the Home tab, select the "ab" drop-down and choose Rotate Text Up.
)
Make the font size smaller and adjust the column width until the labels line up perfectly.
){kind=small}