It is helpful and less tedious to make sure formulas and charts in Excel update automatically as new time series data is added to Excel. For instance, risk free rates are updated daily in the files and we would like the charts to get updated automatically. Let’s find out how.
What is a dynamic range?
A dynamic data range is one which expands and contracts as you add data to you range or data array or delete data from it.
How to create a dynamic range?
Click on the Formula tab from the top menu bar in Excel.
Then click on Name Manager.
In the Name Manager window, click New to create a new named range.
In the New Name window, select a Name of your choice for your array of data. This name is what Excel refers to, whenever you use the name in the Excel formulas or charts. Do not use the exact same name as the headers of your data or Excel will get confused. You can add a variation of it. For instance, you can add Array or Series to the header name. Choose simple and consistent names so you can update your charts and formulas easily from memory when you type in the range name.
Choose the Scope, and in this case it is the current Workbook.
Add a comment to remind yourself what this Named Range refer to and where it can be used.
In the Refers to formula bar, point to the data array and/or add a formula which might use Index, Match and Offset functions.
How to write the Refers to formula in the New Name window as it pertains to charts automatic updating.
In order to point to and select the correct, dynamic range in your data you can use the following formula format (please see the video for further guidelines).
=OFFSET(Sheet1!$D$4,0,0,COUNTA(Sheet1!$D$4:$D$1048576))
The Offset formula format is: offset(reference, rows, cols, [height], [width]) which returns a reference to a range that is a given number of rows and columns from a given reference point.
In the above formula, reference Sheet1!$D$4 is where we want the data to start, we selected 0 rows and 0 columns as we want the data to start exactly where the reference is.
[height] would be a dynamic part of the formula where we select all cells in columns D, starting from the reference point, and [width] will be zero as we are only using column D data for this range.
Do the same process for all columns of your data and use the same [height] that was used in the first named range. This is because we are using the first name range as the x-axis data of the chart and it has no blanks. As you play with the formula, you will notice counta() formula does not count the empty cells and this might shift your ranges up and down; hence, to stay consistent, use the [height] that was used for the data which you will point to as the x-axis of your chart.
How to connect the chart to dynamic data range?
On the chart that you have created, right click and choose Select Data.
In the Select Data Source window, in the Legend Entries (Series), click Add (or Edit if you already have a static data in your chart).
Enter the Series Name by pointing to the header of the data to make the headers dynamic as well.
For the Series values, type =OFFSET(Sheet1!$E$4,0,0,COUNTA(Sheet1!$D$4:$D$1048576))
After you added all the y – axis arrays (secondary or primary), add your x – axis data.
Under Horizontal Category axis labels, click Edit. In the Axis label range bar choose the named range by typing =OFFSET(Sheet1!$D$4,0,0,COUNTA(Sheet1!$D$4:$D$1048576))
Now as you add and delete data from the bottom of your array, the chart automatically updates.
The second method is to turn the data into a table and connect the chart to the table data. The OFFSET() method is more flexible while the second method is just a very quick way to turn the data into a table.