Newbie Questions - X Axis and Data Range

[Eli]

This must be incredibly easy, but I've poked around in most of the
chart options I can find and haven't been able to see it. I have a
sheet that looks something like:

6/11/05 6/12/05 6/13/05 ...
Inventory Totals
A 419 431 432 ...
B 145 145 144 ...


I'm charting only the values in the A row, 419, 434, etc. An
appropriate range is shown on the Y axis. I have the X axis formatted
as dates, but I get the dates 1/1, 1/2, etc. How do I tie the X axis
labels to the dates in my data?

Another question: This data will grow indefinitely. How can I specify
a cell range for the chart so that I don't have to respecify it every
day? That is, when I add columns P and Q and R to my spreadsheet, the
chart would pick them up automatically, but also not display any blank
dates at the righ-hand end of the chart.

 

[Eli]

This must be incredibly easy, but I've poked around in most of the
chart options I can find and haven't been able to see it. I have a
sheet that looks something like:

6/11/05 6/12/05 6/13/05 ...
Inventory Totals
A 419 431 432 ...
B 145 145 144 ...


I'm charting only the values in the A row, 419, 434, etc. An
appropriate range is shown on the Y axis. I have the X axis formatted
as dates, but I get the dates 1/1, 1/2, etc. How do I tie the X axis
labels to the dates in my data?

Another question: This data will grow indefinitely. How can I specify
a cell range for the chart so that I don't have to respecify it every
day? That is, when I add columns P and Q and R to my spreadsheet, the
chart would pick them up automatically, but also not display any blank
dates at the righ-hand end of the chart.

All right... I figured out the first problem. Found the X Axis stuff
on the Series tab of the Source Data settings.

But could still use an answer to the second question.


And I have another. I'd like to add a second series to the chart, but
the numeric range differs enormously from the first series.

6/11/05 6/12/05 6/13/05 ...
Inventory Totals
A 419 431 432 ...
...
J 12 11 13 ...

If I plot row J on the chart then my Y axis values go from something
like 410-440 to 0-440 and the line plotting the A values is flattened
to the point of being useless. I'd like to plot the J values to see
if there's a correlation to the trends in A, but I don't want the Y
axis scaling to reflect the J range at all. Is this doable? It would
kinda be like overlaying two different charts, independantly scaled on
the Y axis and without displaying the labels for the second chart.

 

[Jon Peltier]

Eli -

Glad you found the X Values on the Series tab. For your next chart, put
the X values in the row right above the first set of Y values:

6/11/05 6/12/05 6/13/05 ...
A 419 431 432 ...
B 145 145 144 ...

Keep the top left cell blank. Select all the data, including the dates,
the labels in the first column, the blank corner cell; then start the
chart wizard. Excel will sort out the details for you.

Here's another hint: Most times it's better to put the data in columns
instead of rows. Since there's only 256 columns, you'll run out of dates
in just over 8 months (or almost a year if you're only doing weekdays).
But you have 65k rows, so plenty of room to expand.

Okay, enough helpful hints. For your data with disparate values, double
click on one series, and on the Axis tab, select Secondary. Now you have
two Y axes, which can be scaled independently. Put the large values on
one axis, and format all large valued series to use that axis; put the
small values and series on the other axis.

For the incredible expanding data, you need a dynamic chart, built using
dynamic ranges. I have a few examples and a lot of links on my web site:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______