Add a bar to a stacked column chart

[niravrph]

Hi,

I have a series of sales data (X-axis is Jan11, Feb-11, Mar-11, Apr-11) for which I generated a Stacked Column in 3D chart. Can you please tell me how I would add a NEW bar to each month? I tried but was unsuccessful. Essentially I would like this bar to serve as a "reference bar" to compare against, for each month. That is, I have sales figures from each district stacked together in one bar for January. I would now like to compare this bar to a "reference bar" that shows how district sales compared against the National sales goal.

Can you advise me please?

Thank you,
NIRAV

 

[Steve=True]

First off, you may want to reconsider the 3D chart. most 3D charts make it hard to compare bars from most data sets. 3d charts are not recommended.

Second, i Dont think you can do a combined chart in 3D. Therefore, you can't add a second bar or even a line. Yet another problem with 3D. In fact, my test doesn't even allow me to create a second YAxis.

I would suggest 2d column chart with a line on the second axis that shows the national average

 

[niravrph]

Steve,

Thank you but you will have to go slower. I switched it to a 2D chart. Now how to I add the reference bar? Not sure what you mean by "second axis". Sorry to be dull.

NIRAV

 

[Steve=True]

Welcome to 2D Better chart type but doesn't look as fancy.

What version of Excel are you in?

 

[niravrph]

Steve,

You've made me to a 2D convert! 3D is overplayed anyway.
I'm in Excel 2007.

Got it nice and pretty actually... now how to do I add the reference bar to each month?

NIRAv

 

[Steve=True]

Coolio.

In 2007 add your data next to your last month. So if your average total sales across all districts was 20, and you have 4 districts, i would put

..........Jan.....Feb......Mar....Average
Dist1
Dist2
Dist3
Dist4

Then right click anywhere in your chart
and choose "Select Data"

Next click "Add" in the Legend Entries (series) area.

For the name, select the Average Cell

For the series values, select your Average Range for all districts.

Then click okay till you get back to your chart.

It will add a series to your chart on top of the stacked columns, but don't fear.

next right click on the newly created Average series.

Choose "Change series chart type"

Select a Line. (my preference)

If you want a bar, you will first have to right click on the new Average Series and then click on Select "Format Data Series"

Here you would choose "Secondary Axis"

then you would click okay and then following that right click new Average series that is now on the secondary axis and Choose "Change series chart type" Select a Column Chart if you prefer.

 

[Steve=True]

I forgot to say that the reason you can't just change the chart type to Column is becuase if they are on the same primary axis, it will change your stacked chart to a column chart as well.

 

[niravrph]

Can't get it, Steve. I right clicked Secondary Axis.. and nothing happens at all. Continuing on with your directions from that point on also doesn't result in any change.

NIRAV

 

[Steve=True]

What is your email? I can send you a 1.6 mb file with a demo MP4 format.

 

[niravrph]

niravrph at gmail.com

Thanks!