Chapter 21: Creating Sparkline Graphics
515
I 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).
This name always refers to the last seven non-empty cells in column B. To display a dif-
ferent number of data points, change both instances of 7 to a different value.
- Chose Insert ➪ Sparklines ➪ Line.
- 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.