How to compare current year to prior year in bar chart?

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
....


"substring" wrote:

> 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.
>

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.pdbook.com


"substring" wrote:

> 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.
>

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.



"John Mansfield" wrote:

> 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.pdbook.com
>
>
> "substring" wrote:
>
> > 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.
> >

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.


"simon" wrote:

> 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.
>
>
>
> "John Mansfield" wrote:
>
> > 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.pdbook.com
> >
> >
> > "substring" wrote:
> >
> > > 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.
> > >