The Right Way to Create an Excel Rolling Chart

Creating reports on a regular schedule is a common task for the business Excel user. When you need to create a Rolling chart that reflects data in a specific timeframe – such as the previous 12 months – you can quickly find yourself in a maintenance nightmare, updating your charts manually to include the new month’s data and exclude the now “out of date” data. The good news is that with the OFFSET function, you can create a dynamic rolling chart that automatically refreshes your charts far more easily than adjusting cell references or deleting the old data.

The following steps demonstrate how to use the OFFSET function to create an annual rolling chart. We have a table that shows amounts of contaminate present in an imaginary water sample taken monthly. We want a chart that shows the current month’s data along with the previous 11 months. To follow using our example below, download Excel Rolling Chart.xlsx

Our initial chart shows two years of data.

Specify a Dynamic Named Range

First create a named range for the data that uses the OFFSET function to make it reflect a relative timeframe:

1. Make sure your curser is in an empty cell and no data in your table is selected to begin, then select Define Name on the Formulas tab to open the New Name dialog box.
2. Give the range a recognizable name in the Name: A name must use the following syntax:
   • First character can only be a letter, an underscore “_” or a backslash “\”
   • You can use letters, numbers, periods, and underscores in the rest of the name, but NOT spaces. Names are NOT case sensitive – “DATA” will be seen as the same as “data”
   • Names cannot be the same as a cell reference

In this example our named range is AnnualData. (Note: if you want to follow along and create a new chart of your own, type your own range name in the textbox and substitute that name in the formulas below. See also Changing Names in the bonus paragraph at the end of this article.)

3. In the Refers to: text field, replace the default cell reference with your OFFSET function. The syntax for OFFSET is:

OFFSET(reference, rows, cols, [height], [width])so the formula for our range’s data will be

=OFFSET(‘Research Data’!$B$1,COUNT(‘Research Data’!$B:$B),0,-12,1)Notice that the COUNT function gives us a total number of rows in our table, and we then specify how many rows we want to include in the [height] argument. This is where you can change the number of months (for example) you want to include in your chart. If, instead, you added columns to your data each month, you would adjust your formula accordingly.

HINT! Excel will try to be “helpful” and add cell references or ranges to the Refers to: text field if you click on something outside of the dialog box. You may have to type or paste very carefully into this text box to avoid “extra” data in your formulas.

4. Create a named range for the labels following the same steps above. The formula for our labels will be: =OFFSET(AnnualData,0,-1)   We can use the range we created for the data as our reference and subtract 1 from our width to capture the correct range.

Assign Chart Data Source to Dynamic Named Range

Create your line chart as you normally would if you have not already. Now, you need to tell the chart to use our dynamic range instead of the entire table. Click on the chart to activate the Chart Tools contextual tabs.

1. On the Design tab, click Select Data.
2. In the Select Data Source dialog box, select the first data series and click
   • In the Series values: text box in the Edit Series dialog box, replace the default table range with the dynamic data named range. Do not change the sheet name and exclamation (!) that precedes it.
   • Click OK
3. Click on any label in the Horizontal (Category) Axis Labels selection box, then click
   • In the Axis Labels dialog box, replace the default range with the dynamic label named range.
   • Click OK.

Now your chart will only show the last 12 rows of data.

When you update your data by adding more rows, the chart will automatically show the new last 12 rows of data, creating a rolling chart effect.

Note that if you want to show more than one series of data on your chart, you will need to create a named range and repeat the above process for each series! This might take you a little more time to set your charts up, but will save you much more in the long run as you simply add to your data and the chart does the rest.

Bonus! Changing Names

Once you have specified named items in your workbook, you may want to go back and edit them from time to time. Perhaps you want to change your OFFSET to show 6 months instead of 12, for example. To edit an existing name, click the Name Manager button on the Formulas tab. Select the name you wish to edit, then click the Edit button. Make your changes in the dialog box as you did when first creating it. Just be careful not to change the name itself if other formulas depend on it.

OPTIONAL: To check your work and compare named ranges, you can download Excel Rolling Chart_Complete.xlsx.