[Use your browser's BACK button to return to the PRIME FAQ Page or click here if you came directly to this page.]
This was taken from "The Underground Guide to Excel for Windows", Chapter 6, and can be found under the heading Dynamic Charts -- An Old Trick. This material has been updated for Excel 97.
Click here to order The Underground Guide to Excel for Windows from Amazon.
Say you have a chart showing sales vs. sale inquires by month. Each month you have to update the chart to include the previous month's data. You'd like to just add the new figures to the spreadsheet and have it automatically added to the chart.
Looks pretty ordinary, no? But when Apr was typed in it was added to the X-axis of the chart. Enter a value in E2 and it appears in the chart as if by magic. All the tricky stuff is out of sight, but what it does is pretty neat. When you enter the data for April in cells E1:E3 this chart automatically includes the data. One second it's just Jan through Mar, and the next it's Jan through Apr. Here's how it works:
First, save your workbook. In order for this technique to work properly you need to save your workbook and we recommend you give the worksheet that contains your data a unique name. In this example the workbook is named DynamicChart1.xls and the sheet is named SalesInfo.
Next, create some named formulas in the sheet; one for the X-axis data and one for each data series you want charted. Select any cell in SalesInfo, select Insert Name Define, enter XAxis in the Names in Workbook text box, enter the formula shown below in the Refers to text box, click Add, and continue until the remaining named formulas are defined.
| Range Name | Named Formula |
| XAxis | =OFFSET(SalesInfo!$A$1,0,1,1,COUNTA(SalesInfo!$B$1:$M$1)) |
| YInquiries | =OFFSET(SalesInfo!$A$1,1,1,1,COUNT(SalesInfo!$B$2:$M$2)) |
| YSales | =OFFSET(SalesInfo!$A$1,2,1,1,COUNT(SalesInfo!$B$3:$M$3)) |
The OFFSET() function is very useful in that it lets you designate a range of cells starting some number of rows and columns from a starting point. In this case you use "SalesInfo!$A$1" as the starting point. The next argument tells OFFSET() how many rows down (if positive) or up (if negative). Next is the number of columns down or up. The next two arguments define the size of the range you want: the height and the width.
Take the XAxis named formula as an example. It says start at A1, go zero rows down (no change), go one column right, the defined range is 1 row high, and the width is determined by the COUNTA() function. COUNTA() returns the value of the number of non-blank cells in the range "SalesInfo!$B$1:$M$1" which covers the 12 months that'll ultimately be charted.
This use of COUNTA() for non-blank cells, and COUNT() for non-zero cells, is the trick that makes this technique work. As you add data to the chart each month, x-axis data on row 1, and y-axis data on rows 2 and 3, the range of cells to chart is not fixed but dynamic. It changes each month. The OFFSET() formulas automatically determine the cells that contain data and provide a handy reference to that range.
This takes care of the data in the worksheet but how to utilize the named formulas in the chart?
The easiest way to create the chart for this example is by using the existing data in SalesInfo sheet. Create the chart and then edit the =SERIES() formulas for each data point in the chart itself as shown here.
Note: To edit each series formula: in the chart click on (in our example) each column in turn that represents a series and you can edit the formula for each in the formula bar.
| Data Point | Named Formula |
| 1 | =SERIES(SalesInfo!$A$2,DynamicChart1.xls!XAxis,DynamicChart1.xls!YInquiries,1) |
| 2 | =SERIES(SalesInfo!$A$3,DynamicChart1.xls!XAxis,DynamicChart1.xls!YSales,2) |
The syntax of the =SERIES() formula is series_name, X-axis, Y-axis, order number. For the x-axis and y-axis you substitute the appropriate defined formula names. This smoke and mirrors is required because Excel won't let you use other functions within the =SERIES() function.
|
Return to Top