This tutorial combines dynamic range names with Sparklines so your dashboard users can scroll Sparklines through time. This gives your users the power to scan historical data trends across large amounts of data. (Sparklines are only available in Excel 2010 and newer.)
Normally, Sparklines use a cell reference, such as $E$14:$J$14, to reference the data they chart. In this cool example Sparklines use a range name that calculates the cell reference. The range name is a special type of range name. It is a range name formula containing the OFFSET function. Your users will be able to scroll left or right through years and expand or contract the range of the Sparkline.
OFFSET calculates the cell references for the Sparklines based on the start column in D5 and the width of the range in D6. The OFFSET function is stored in a range name that is used by Sparkline.
Dynamic range names created with OFFSET are one of the most powerful Excel features Excel power users can learn. Every Excel mid-level and power user will benefit from learning how to create dynamic range names with OFFSET. When you finish this tutorial you will know to combine dynamic range names, OFFSET, and Sparklines to create a great dashboard feature that makes it easy to scroll through large lists of data while seeing a Sparkline that shows trends or variations.
Dynamic range names are cell reference ranges that are calculated. One common use for this is to recalculate a cell range reference when a list changes size. Another frequent use, explained in other tutorials in Critical to Success, is to create charts that automatically adjust as the size of their data reference changes.
See other uses of OFFSET by searching for “dynamic charts” or “OFFSET” in Critical to Success. The search box is at the top right corner. There are some pretty cool examples. In fact, take a look at these,
The OFFSET formula calculates a cell reference that depends upon the data you feed it. OFFSET syntax is,
The attributes of the OFFSET function are,
|Reference||Reference is the upper-left corner of the original cell reference|
|Rows||Rows is the number of rows up or down the new calculated reference is from the original reference. In this tutorial Rows is 0 because the top-left corner for the calculated cell reference is the same as the original top-left corner.|
|Cols||Cols is the number of columns left or right the new calculated reference is from the original reference. In this tutorial Cols is 0 because the top-left corner for the calculated cell reference is the same as the original top-left corner.|
|Height||How many rows high the new calculated cell reference is.|
|Width||How many columns wide the new calculated cell reference is.|
Setting Up the Dynamic Sparkline Tutorial
This tutorial uses a long list of real data. It also has a scroll bar that controls the contents of D5. You may want to download this tutorial. To practice the steps below,
2. Save the tutorial to a new name. Use the copy to follow this tutorial.
3. Remove the Sparklines by selecting the cells containing the Sparklines and choose Sparkline Tools Design, in the Group group, select Clear and clear all Sparklines.
4. Remove the rngScrollSparkline range name by selecting the Formula tab, in the Defined Name group, click Name Manager. When the Name Manager dialog appears delete the name rngScrollSparkline.
If you do not have a Developer tab on your ribbon you will need it to create the scroll bar. Click her to learn how to add the Developer tab that contains the scrolling bar and other items like drop-down lists.
Click here to learn how to create a scrolling bar like that one that scrolls through dates in this tutorial. This example shows you how to use a scroll bar to scroll a normal chart, but drawing and formatting the scroll bar works the same.
Create the Dynamic Range Name that drives Dynamic Sparklines
Each Sparkline in column D will need a range name for its data. However, you don’t need to create a new dynamic range name for each Sparkline. By using a relative reference (no $ in the cell reference) you can create one OFFSET function and one range name that works for all Sparklines. The dynamic range name will adjust depending upon the cell it is used in.
Create the dynamic range name in cell D14 (this one name will be used by other Sparklines),
1. Select cell D14. MAKE SURE YOU SELECT D14!
2. In the ribbon, click the Formulas tab, in the Defined Name group, click on Define Name to display the Define Name dialog box.
3. In the Name text box enter, rngScrollSparkline
4. In the Refers to text box enter the OFFSET formula,
=OFFSET(‘shtScrolling Sparklines’!E14,0,’shtScrolling Sparklines’!$D$5,1,’shtScrolling Sparklines’!$D$6)
You can click the reference tool to the right of the Refers to box and then click on cells rather than typing the references.
ALERT! Be sure you enter E14 as a relative reference, without the $ signs. The fact that it is a relative reference makes its calculated reference depend upon the location where the range name is used. In this tutorial the range name will be in Sparklines going down column D.
5. Click Ok.
The OFFSET formula is stored in the range name rngScrollSparkline, not as a cell reference in the worksheet. You can use this range name formula as a cell reference inside any worksheet formula or dialog box just as you would a normal cell reference, however, the range name will calculate a cell reference that starts from its current location and uses the starting column and width specified by D5 and D6.
OFFSET works like this,
OFFSET(‘shtScrolling Sparklines’!E14,0,’shtScrolling Sparklines’!$D$5,1,’shtScrolling Sparklines’!$D$6)
|Reference||E14 is a relative reference one to the right of the cell D14 where you created the range name. As you use this range name in other Sparklines they will also look for their reference one cell to the right.|
|Rows||0 means the calculated reference is not offset. It is in the same row.|
|Cols||$D$5 means the number of columns to the right of Reference for the starting location will be the number in $D$5.|
|Height||1 means the calculated range is one row high.|
|Width||$D$6 means the width of the range for the Sparkline data is the width specified in $D$6.|
Testing the Cell Reference Calculated by OFFSET and a Dynamic Range Name
Test your dynamic range name, rngScrollSparkline, before creating Sparklines with it. You can see the dynamic range the OFFSET formula calculates.
1. Click in cell D14.
2. Press the Go To key, F5, to display the Go To dialog box. You won’t see the range name in the box because the range name is a formula, it is not in the worksheet.
3. Type rngScrollSparkline in the Reference text box.
4. Click Ok.
The cells to the right of D14 that will be used by the Sparkline should be selected.
Creating Sparklines that Scroll through Time
Now comes the easy part, creating the Sparklines that scroll through time.
To create the Sparklines,
1. Click in cell D14.
2. On the ribbon on the Insert tab, in the Sparklines group, click the Line tool.
3. In the Data Range text box enter rngScrollSparkline.
4. Press Ok.
5. A dynamic Sparkline will appear in D14. Change the values in D5 and D6 and you will see the Sparkline change.
6. Copy the Sparkline down to the last row of data of states by dragging the handle at the lower right corner of the Sparkline in D14.
If you want to see some other cool ways to create dynamic full-sized charts, check out these dynamic Excel chart tutorials,