How do I make x-axis dates in area chart not start from/drop to ze

[David Moye]

When I create an area chart from with a date range as the x-axis, there are
diagonals that take the chart to the data value for the first day, and from
the last data value down to the axis. What I would prefer are verticals as
the diagonals sure make it look like the chart has ended with a 0 y-axis
value, or starts with a 0 y-axis value. Any ideas?

 

[Luke M]

Select the x-axis, and format. Adjust the scale to bring the chart into the
boundaries you want. Note that you'll need to enter a serial number
representing the date you want.

 

[Tushar Mehta]

I cannot reproduce this problem unless I add dates both before and after the
dates with actual data and include this extended range in the graph. You may
want to check and adjust the range plotted...or, as already suggested, adjust
the min and max values of the corresponding axis so that the plotted range is
restricted to that with data in it. When entering the new min. and max.
values you can specify the value as a date (1/1/2009, for example).

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Jon Peltier]

To clarify Luke's and Tushar's posts about setting axis scale parameters.

In Excel 2003 and earlier, you can enter parameters in any valid format for
any axis. You can enter dates or serial numbers if you are plotting against
date.

In Excel 2007 SP1 you can enter parameters as dates, but only for the X
axis. (Prior to SP1 an X axis of an XY chart rejected date entries.)

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[David Moye]

Tushar,

That is exactly what I'm trying to do (dates not in the data for the graph).
E.g. I want a chart that shows a whole month's worth of daily values for the
current month. It's only the 13th of the month. It would be nice if, on the
13th, the value dropped vertically instead of sloping down to the axis on the
following day. Is there a way to have this more descriptive behavior? I say
more descriptive because the default makes it appear that on the 19th, the
value will be zero (or the y-axis "zero" anyhow).

 

[Tushar Mehta]

It turns out that if the y value cells are empty (no value and no formula
that evaluates to "") or if they evaluate to NA() -- shown as #N/A -- the
area chart ends in a vertical drop.

So, if you have formulas in your y column, consider replacing "" with NA().
If this is a problem for aesthetic or down-stream calculations, simply create
a duplicate of the data and modify the formula in there.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu