Dynamic Charts

Dynamic Charts

If you have charts that you regularly update with new data,  dynamic range names can help. Let's look at a simple data set where we have flow data for each week.

We want to setup our chart so that it automatically updates as we add additional weeks of flow data. We do this by creating dynamic range names that expand as we add data.

Step 1 - Define X Value Range Name: To create a range name, go to Insert  > Name > Define.

The Define  Name window appears. We add "Week" in Name box and enter an Offset formula in the Refers to box to define our dynamic range name. The general offset formula is:

   = Offset(start cell, rows offset, cols offset, number of rows, number of cols)

 Step 2 - Define Y Value Range Name: Now that we have defined our Week range name, we can define of the Y value (flow)  range with an Offset formula in the Refers to box.

We have a choice for our Y value. We can use a formula similar to the Week range formula or we can make the Flow range a direct offset to the Week range because the Flow range is one column to the right of the week range.

The Offset formula for a new range offset from an existing range is:

    = Offset(book_name!range_name,0,C)

where C is column Offset from existing range.

To define the existing range, notice that we have to give both the workbook name followed by an ! and the range name.

Excel now has two dynamic range names.

Step 3 - Edit Chart Series formula to show dynamic range names. We do this by selecting our chart and the data series. We than want to replace the standard X and Y value cell references with our dynamic range names.

In this example the dynamic range names are

PDA_4_Charting.XLS!week and

PDA_4_charting.XLS!Flow.

Press enter and we are done. Our chart will now automatically update when we add weekly data.

Dynamic Chart That Plots Last X Values

Would you like your chart to show the last 12 months, last 24 values, or last 30 days of data? You can use dynamic range names to have your chart plot the last number of data points that you specify.

We will need to set up 5 dynamic ranges to accomplish this.

1. Last_row    =   Offset(Data!$A$1,CountA(Data!$A:$A)-1,0)
2. No_points   =  Offset(Data!$D$4)
3. First_point  =   Offset(Last_row-(No_points-1),0)
4. Dyn_X       =   Offset(First_row,0,0,No_points)
5. Dyn_Y      =    Offset(Dyn_X,0,1)

The example download file shows this in action.      dynamic_chart_plot_last_X_values.xls