Graph Autorange

[WildWill]

Consider the following data set (A1:F4):

05 Jan 09 12 Jan 09 19 Jan 09 26 Jan 09 02
Feb 09
Apples 14 22
Bread 02 03
Milk 10 09

I have a graph that shows me the values of Apples, Bread and Milk over time,
using the dates indicated as values on the Y-Axis. My problem is that every
week, I have to open up each graph and manually select the new data range,
since each week has an additional column of data ( I dont want the graph to
show emplty fields). E.g. in week 05 Jan, the data range for my graph will be
A1:B4, In week 12 Jan, I then have to change the range to A1:C4, etc, etc.

How can I get my graphs to auto range the data fileds always to include all
columns which have values in them?

 

[PJ]

Hi WildWill

Sounds like you need to use dynamic ranges in your chart. Check out Jon
Peltier's site for details about how to do this:
http://peltiertech.com/Excel/Charts/Dynamics.html

 

[Jon Peltier]

Are you using Excel 2003 or 2007? Can you bear to transpose your data, so
the dates are in the first column and the labels in the first row?

If so, then transpose the range, then convert it into a List (Excel 2003) or
Table (2007) by selecting the range and typing CTRL + L. Now recreate your
charts using the data in this List or Table. As the table expands and
contracts, any formula that refers to a whole column of the List/Table will
automagically update to the new length of the List/Table. This includes
Series formulas in charts, so the charts update to reflect the new data
range.

- Jon