Thread: Automatically update Graphs in Excel 2003

How do I get a graph to automatically update when I enter new data (I am using Excel 2003)? I have seen how some people suggest naming ranges using the OFFSET function. However, this only appears to work when the data is listed vertically.

My data is listed horizontally - i.e. row B1 contains "November 2000," and each column contains another month up to CZ1 which contains "May 2009." This data is updating each month.

Morover, I have a line graph that tracks the sales of multiple items - ie rows 2 through 5 are apples, oranges, grapes, bananas. Each month, the sales of each of the four items are updated, and I would like my line graph to update automatically. Please help. Thank you.

The named range with OFFSET is the way to go and depending on if you want to include all columns in your graph or the last x columns on your graph.

To include all use:

Code:

=OFFSET(Sheet1!$A$1,0,COUNT(Sheet1!$1:$1))

To include x (in this example last 3) use:

Code:

=OFFSET(Sheet1!$A$1,0,COUNT(Sheet1!$1:$1),1,-3)

I would use this as the named range for the xVal and you will need to create a named range for each series. the easiest way would be to offset from your xVal:

Code:

=OFFSET(xVal, 1, 0) 'ser1 
=OFFSET(xVal, 2, 0) 'ser2
=OFFSET(xVal, 3, 0) 'ser3