Part III: Creating Charts and Graphics

FIGURE 21.11

The axis in the Sparklines represents the goal.

Specifying a Date Axis

Normally, data displayed in a Sparkline is assumed to be at equal intervals. For example, a Sparkline might display a daily account balance, sales by month, or profits by year. But what if the data aren’t at equal intervals?

Figure 21.12 shows data, by date, along with a Sparklines graphic created from Column B. Notice that some dates are missing, but the Sparkline shows the columns as if the values were spaced at equal intervals.

FIGURE 21.12

The Sparkline displays the values as if they are at equal time intervals.

To better depict the data, the solution is to specify a date axis. Select the Sparkline and choose Sparkline Tools Design Group Axis Date Axis Type. Excel displays a dialog box, asking for the range that contains the dates. In this example, specify range A2:A11. Click OK, and the Sparkline displays gaps for the missing dates (see Figure 21.13).

512

Chapter 21: Creating Sparkline Graphics

FIGURE 21.13

After specifying a date axis, the Sparkline shows the values accurately.

Auto-Updating Sparklines

If a Sparkline uses data in a normal range of cells, adding new data to the beginning or end of the range does not force the Sparkline to use the new data. You need to use the Edit Sparklines dialog box to update the data range (choose Sparkline Tools Design Sparkline Edit Data). But, if the Sparkline data is in a column within a table (created by using Insert Tables Table), then the Sparkline will use new data that’s added to the end of the table.

Figure 21.14 shows an example. The Sparkline was created using the data in the Rate column of the table. When you add the new rate for September, the Sparkline will automatically update its Data Range.

FIGURE 21.14

Creating a Sparkline from data in a table.

513

Part III: Creating Charts and Graphics

Displaying a Sparkline for a Dynamic Range

The example in this section describes how to create a Sparkline that display only the most recent data points in a range. Figure 21.15 shows a worksheet that tracks daily sales. The Sparkline, in cell F4, displays only the seven most recent data points in column B.

FIGURE 21.15

Using a dynamic range name to display only the last seven data points in a Sparkline.

Need More about Sparklines?

This chapter describes pretty much everything there is to know about Excel 2010 Sparklines. You may be left asking, Is that all there is? Unfortunately, it is.

The Sparklines feature in Excel 2010 certainly leaves much to be desired. For example, you’re limited to three types (Line, Column, and Win/Loss). It would be useful to have access to other Sparkline types, such as a column chart with no gaps, an area chart, and a stacked bar chart. Although Excel provides some basic formatting options, many users would prefer to have more control over the appearance of their Sparklines.

If you like the idea of Sparklines — and you’re disappointed by the implementation in Excel 2010 — check out some add-ins that provide Sparklines in Excel. These products provide many additional Sparkline types, and most provide many additional customization options. Search the Web for sparklines excel, and you’ll find several add-ins to choose from.

514

Chapter 21: Creating Sparkline Graphics

I started by creating a dynamic range name. Here’s how:

1.Choose Formulas Defined Names Define Name, specify Last7 as the Name, and enter the following formula in the Refers To field:

=OFFSET($B$2,COUNTA($B:$B)-7-1,0,7,1)

This formula calculates a range by using the OFFSET function. The first argument is the first cell in the range (B2). The second argument is the number of cells in the column (minus the number to be returned and minus 1 to accommodate the label in B1).

This name always refers to the last seven non-empty cells in column B. To display a different number of data points, change both instances of 7 to a different value.

2.Chose Insert Sparklines Line.

3.In the Data Range field, type Last7 (the dynamic range name). Specify cell E4 as the Location Range. The Sparkline shows the data in range B11:B17.

4.Add new data to column B. The Sparkline adjusts to display only the last seven data points.

515