Microsoft Office Tutorials and References
In Depth Information
The Sparkline Tools ➜ Design ➜ Group ➜ Axis command lets you override this automatic behavior and
control the minimum and maximum value for each sparkline, or for a group of sparklines. For even
more control, you can use the Custom Value option and specify the minimum and maximum for the
Axis scaling can make a huge difference in the sparklines. Figure 3-11 shows two groups of
sparklines. The group at the top uses the default axis settings (Automatic for Each Sparkline). Each
sparkline in this group shows the six-month trend for the product, but there is no indication of the
magnitude of the values.
The sparkline group at the bottom (which uses the same data), uses the Same for All Sparklines
setting for the minimum and maximum axis values. With these settings in effect, the magnitude of the
values across the products is apparent — but the trend across the months within a product is not
The axis scaling option you choose depends on what aspect of the data you want to emphasize.
Figure 3-11: The bottom group of sparklines shows the effect of using the same axis minimum and maximum
values for all sparklines in a group.
Faking a reference line
One useful feature that’s missing in the Excel 2013 implementation of sparklines is a reference line.
For example, it might be useful to show performance relative to a goal. If the goal is displayed as a
reference line in a sparkline, the viewer can quickly see whether the performance for a period
exceeded the goal.
One approach is to write formulas that transform the data and then use a sparkline axis as a fake
reference line. Figure 3-12 shows an example. Students have a monthly reading goal of 500 pages. The
range of data shows the actual pages read, with sparklines in column H. The sparklines show the
sixmonth page data, but it’s impossible to tell who exceeded the goal and when they did it.