Dynamic chart: add new data and the chart expands.



Click here for sample file

Your data is as shown in the left hand figure. Each month you will add new data and you want the chart to automatically include the new data.

1) Using Insert/Name/Define define two names (see centre figure)
Date    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
Sales   =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

2) Draw a chart using A1:B5

3) Right click on the data series in the chart. And open the Source dialog (see figure at right.) Change the Values setting to =Sheet1!Sales and the Category setting to Sheet1!Date. Note that Excel will change these to the form Dynamic.xls!Sales and Dynamic.xls!Date where Dynamic is the file name.


Return to Tips and Tricks


November 2005