Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet.
Here we start with monthly data for Retailer A, showing Petcare sales complete up to July.

pic-a.jpg

A standard Excel line chart of this data would be shown thus:

pic-b.jpg

To reduce the displayed area to the existing data within the actual column, while allowing it to expand automatically as more months figures are added, we use defined names.

Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3.

In the “Names in workbook” box type in a descriptive name for your chart data, in this case I have used ChartActual.
In the “Refers to” box we use the offset function to define the data within column C.

pic-c.jpg

The offset function is written in the following format:

=OFFSET(Ref,rows,column,height,width)

The COUNTA function is the vital part that makes the range responsive to the number of cell entries in the Actual column. This function returns the number of non-blank cells in a range. It is written in the height section of the OFFSET function and therefore determines the number of rows that will make up the name ChartActual.

In this example the reference is $C$1 which corresponds to the “Actuals” title, the resulting area starts 1 row down and 0 columns across. The number of rows corresponds to the number of entries in column C less 1 for the title entry and the total column width is 1.

The same procedure is now used to define names for the ChartLabels and ChartTargets.

pic-d.jpg

The OFFSET function is again used, this time referencing the newly defined ChartActuals. So ChartTarget starts on the same row as ChartActuals but is 1 column to the left of it. When the height and width component of the OFFSET function is not stated the height and width will be the same as the reference.

pic-e.jpg

The final stage involves changing the graphs data source.
Right click within the graph and choose Source Data.

pic-f.jpg

Select Actual in the Series box and in the Values box type in the newly defined name, in this case Sheet1!ChartActuals.
Perform the same action for Target and use Sheet1!ChartLabels to define the Category (X) axis labels.
Click OK and the chart should now look like this but is in addition fully responsive to new entries.

pic-g.jpg