Dynamic Graph Range Updating In Excel

If you are like me and you make extensive use of Microsoft Excel, then perhaps you too have been frustrated at one point when the data range for a graph or a series of graphs change and you have to manually update these.

I was this year old when I discovered that Excel does have a way in which you can make the data ranges for graphs dynamic, meaning when the number of data points in the range change these can be updated dynamically saving you time and removing frustration.

Let's consider the following five data points that are also graphed.

If more data were to be added, say you now have values for ID's 6-10 well, you can manually edit the data range for the graph and it will display the new, full set of values you have. But this process can also be automated.

First, using a simple 'COUNT' function one can determine the number of data points to graph, which is five in the above example. Let's say for this example the 'COUNT' function is entered into cell "C17". If we now add more values to the table, for ID's 6-10, then this count will also increase and tell us that we now in fact have ten values to graph but the graph will need manual updating.

To set up dynamic updating of graph data ranges the first step is to set up dynamic data ranges for the sheet. To do that, click on 'Formula' in the taskbar, then select 'Name Manager'.

This will open the 'Name Manager' tab, click 'New' and enter a name for the dynamic data range i.e. 'MyDynamicValues' and let's assume it will be related to 'Sheet1' only for now. In the 'Refers to' part type: =OFFSET(Sheet1!$C$6,0,0,Sheet1!$C$17,1) as shown below.

In the OFFSET formula, '$C$6' refers to the first cell in the data range which contains the first y-axis value namely '71.8'. '$C$17' refers to the cell with the COUNT function which tells me how many data points we have to graph. In the initial case, we have five data points as demonstrated above. The '1' at the end tells the dynamic data range to be one column wide. The two zero's after '$C$6' tell the dynamic data range that no offsets are to be applied from this starting location. MAKE SURE TO USE THE $ signs as indicated.

Repeat this step to generate a second dynamic data range (i.e. MyDynamicID) to also allow the ID values to change dynamically. When done the following dynamic ranges should be available:

Now the graph data sources need to be updated to use the new dynamic data range sets. Right-click on the graph and select ' Select Data', then select the data series you want to change and click 'Edit'. In the 'Series values:' selection enter the following and click on 'OK'.

Now click 'Edit' under the Horizontal (Category) Axis Labels and enter the following and click 'OK'.

Click 'OK' to finalize the changes.

The data ranges for the graph should now be dynamic. To test it, enter new values for the ID's 6 to 10 and watch the graph update.

HAPPY GRAPHING