Vertical line on column chart with monthly data

[Guest]

I am attempting to add a vertical line to a dynamic column chart. I've
followed the steps described by Jon Peltier at
http://peltiertech.com/Excel/Charts/AddLineVertSeries.html
This procedure works fine until I change the base unit on the x-axis to
"Days." This causes the columns to turn into vertical lines (since each
monthly observation is really for, e.g., 6/1/2007).

If I leave the x-axis as monthly and then add the secondary x-axis for the
vertical (xy scatter) line, the scale on the secondary x-axis goes from
1/1/1900 - 12/1/2036, which shoves my vertical line far to the right of where
I need it to appear on the primary x-axis.

I realize I can manually alter the min and max of the secondary x-axis to be
the (numerical) equivalents of my min and max for the primary x-axis. But
since this is a dynamic chart, I'd prefer to have the scaling set
automatically instead of manually adjusting it every few months.

Any suggestions will be greatly appreciated.

 

[Jon Peltier]

If the base unit is days, you plot the XY series using the date in days.

If the base unit is months, you must count the number of months since the
beginning of time (January 1900 according to Excel), This is the whole
number part of the X value, and the fractional part depends on where during
the month you want the vertical line.

- Jon

 

[Guest]

I discovered that changing the type of x-axis under Chart Options to Category
instead of Time-Scale gets around the problem of inserting a vertical line on
a column chart with monthly time-series data. And instead of using dates for
the vertical line x-values, indicate the number of columns to the left of the
vertical line + 0.5.

For example, I have monthly data from Jan 2003 - Dec 2006 and I want to
insert a vertical line between the June 2006 and July 2006 columns. After
changing the type of x-axis to Category (and making any necessary format
changes), I paste the following series onto the chart:

Data
30.5 0
30.5 1

I then plot this series on the secondary y-axis with range 0-1 and change
the type of chart for the series to XY (Scatter) with data points connected
by lines. I use 30.5 for the x-value because I want the line to appear
between the 30th and 31st months/columns.

This procedure should work as long as you're using data with regular time
intervals and no missing data points have to be imputed.

 

[Guest]

Tyler

I guess the same Excel workbook as I refered to here
(http://www.microsoft.com/office/com...&tid=2734e7d8-29d9-4900-8e23-4d6741c8ef8f&p=1)
can be of use. Follow the instructions I put there.

Wigi