Developer Forum

I like to create a bar chart to compare, e.g. the sales figures of product
abc, for the current year (from Jan to Dec) against the numbers for prior
year (from Jan to Dec). Basically with 2 bars (current and prior) side by
side for each month. The data can be on the same worksheet or in separate
worksheet for each year.

I am having a hard time to make it work. How can I define the data range
such that Excel will know it should be a comparison?

Any help will be very much appreciated.

Put your data in this form:

Month 2003 2004 2005
Jan
Feb
Mar
Apr
May
...

You can set your data up like this . . assuming the range A1:C13.

Curr Year Prior Year
Jan 6 6
Feb 4 5
Mar 5 7
Apr 6 8
May 7 7
Jun 5 4
Jul 3 5
Aug 4 6
Sep 5 5
Oct 4 3
Nov 3 4
Dec 2 5

Put your curser in cell A1. Go to the Standard Toolbar and Select the Chart
Wizard. In Step 2 of the Chart Wizard (the Data Range tab), make sure that
you have selected "Series in: Columns". To show the actual years in your
legend, change "Curr Year" to 2005 and "Prior Year" to 2004.

--
Regards,
John Mansfield
http://www.yqcomputer.com/

Thank you Barb and John for your help. I have figured out how to do that. I
think it might be a bug in Excel or something. I cannot change the data range
of an existing chart from single year to multiple years (current and prior)
without messing up the chart. But if I delete the chart and create a new one.
It works beautifully.

Now, I run into another problem. I have another chart that uses the Stacked
Bars. Bascially, I have 3 items for each month of the current year and they
are stacked on top of each other. Now, if I add the prior year data, they
simply stack on top of the current year instead of showing side-by-side.

These are the sample of my data:
2005 Jan Feb Mar...
ItemA 10 20 15
ItemB 20 10 5
ItemC 15 10 20

2004 Jan Feb Mar...
ItemA 20 15 20
ItemB 10 5 10
ItemC 20 15 10

Now, how can I stack up Item A, B, and C of 2005 and Item A, B, and C of
2004 side-by-side for each month?

Thanks.

Well, problem solved. It wasn't easy, but I found a workaround that requires
some hacks.

If anyone from the Microsoft development team is watching this thread,
please, please, please add a chart type to handle stacked bars that compare
current year with prior year.

Thanks.