Difficulty Aligning Series Data With Proper Dates

[Mike M.]

I ‘m building a column graph using Excel 2007 and I’m having difficulty
aligning a series of data with the proper dates. I have 2 series of data –
Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09,
etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10,
Jun-10, etc). After creating each series, I select the appropriate dates
(Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m
having is that when I select the second set of dates (for Series 2), it
overrides the first – so the x-axis initially shows 2009 dates, but then
changes to 2010. To get around this, I just reset the dates using the Format
Axis option – resulting in all the 2009 and 2010 dates appearing on the
x-axis.. Unfortunately, when I reset the dates in this manner, the 2009
columns of data are moved over to the 2010 dates – resulting in the Mar-09
column positioned next to the Mar-10 column. And the same applies to the
other dates as well – Jun 09 is positioned next to Jun-10. What I want is
the 4 quarterly columns for 2009 to be aligned with the 2009 dates and the 4
quarterly columns for 2010 to be aligned with the 2010 dates.

Any assistance you can provide would be greatly appreciated.

Mike M.

 

[Jon Peltier]

You can only use one set of dates. But it's possible to fake out Excel:

Plot Two Time Series With Different Dates
http://peltiertech.com/WordPress/plot-two-time-series-with-different-dates/

Line charts and column charts work the same way.

- Jon

 

[Mike M.]

Jon,

Thanks for your response. I read thru the suggestion and I will definitely
try applying it to my graph.

BTW, the way I got around this limitation was similar to your suggestion -
by adding a secondary axis. I created a column chart for both series, then
converted the second series (2010 data) to a line chart. I then added a
secondary axis that aligned with the values of the second series. Its not as
'clean' as your suggestion, but with the legend clearly indicating the line
graph represents 2010 data, the reader should be able to comprehend what
they're seeing in the graph.

Thanks again for your help.

Mike M.

 

[Jon Peltier]

Many ways to skin that cat. You could also convert the chart to an XY
chart. If you want the nice date formatting of the X axis, you can keep
the first series as a line chart series and convert any others to XY. (I
know you talk of column charts, but that doesn't seem appropriate for a
time series.)

- Jon