Part III: Creating Charts and Other Visualizations
FIGURE 22.15
Using a dynamic range name to display only the last seven data points in a Sparkline
We started by creating a dynamic range name. Here’s how:
- 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). The name always refers to the last seven nonempty cells in column B. To
display a different number of data points, change both instances of 7 to a different
value.
- Chose Insert ➪ Sparklines ➪ Line. The Create Sparklines dialog box appears.
- 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.
- Add new data to column B. The Sparkline adjusts to display only the last seven
data points.
