Excel 2010 Bible

Part III: Creating Charts and Graphics

510

Figure 21.8 shows two groups of Sparklines. The group at the top uses the default axis settings

(Automatic for Each Sparkline). Each Sparkline shows the six-month trend for the product, but

there is no indication of the magnitude of the values.

FIGURE 21.8

The bottom group of Sparklines shows the effect of using the same axis minimum and maximum values for
all Sparklines in a group.

For the Sparkline group at the bottom (which uses the same data), I changed the vertical axis mini-

mum and maximum to use the Same for All Sparklines setting. 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 apparent.

The axis scaling option you choose depends upon what aspect of the data you want to emphasize.

Faking a reference line

One useful feature that’s missing in the Excel 2010 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.

You can, however, to transform the data and then use a Sparkline axis as a fake reference line.

Figure 21.9 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 six-

month page data, but it’s impossible to tell who exceeded the goal, and when they did it.

Figure 21.10 shows another approach: Transforming the data such that meeting the goal is

expressed as a 1 , and failing to meet the goal is expressed as a –1. I used the following formula (in

cell B18) to transform the original data:

=IF(B6>$C$2,1,-1)