Excel Charts - Overlay Bars?

Is it possible to overlay Excel bar charts so that it looks something like this:



I know I can adjust overlap, but for some reason you can't adjust "layers" like you can with photo editors, and it hides the smaller bar which I want layered on top of the longer one. And I don't want a stacked chart because that's cumulative.

 

I've done it with 2 set of data using overlap with the right formatting looks close to what you want to do, should be the same with 3

1. Create a bar or column chart with two series.

2. Decide which series should be on top of the other.

3. Find this topmost series in the chart. Right-click it, and then choose Format Data Series. The Format Data Series dialog box launches.

4. Select the Series Options section, and then set the Series Overlap to 100.
This setting ensures that two series are layered one on top of the other.

5. Select the Fill section, and then choose “No fill”. This tells Excel to make this data series transparent, with only the border visible.

6. Click Close.

Next, you want to make sure the right series is on top.

7. Right-click the chart, and then choose Select Data. Make sure the series that’s supposed to be on top is at the bottom of the list. To move a series, select it, and then click the down arrow button. Excel draws the series from top to bottom in this list, so the one it draws last ends up in front.

8. Now select the other series, right-click it, and then choose Format Data Series. Select the Border Color section, and then choose “No line”.
You can also choose any fill color or fill effect. You may need to try several combinations in order to have enough contrast for the overlay chart to really work.

9. Click Close.

 

Thank you, I'll give that a shot.

 

Thanks +1!

Also, any idea to add two labels on the bar chart? I basically want a value of the benchmark result on the inside of the bar and the % diff baseline (% difference from baseline) shown on the outside. So basically to look like this:



I shopped the % values on that chart just to show what I want.

You can download that sample file here: http://www.mediafire.com/?jl2bo4ad2g3eagz

 

From what I remember is you can edit the Data Labels by editing the Format Data Label Control Panel (I'm using Excel 2010 though).

 

I'll install Office 2010. I have it through TechNet just haven't bothered to install it yet. The only thing 2007 lets me do is set it as Series Name, Category Name, or Value. I can't specify additional labels. Just seems you should be able to say use these, now add more labels using these, etc.

 

Just tried Office 2010, same options. No way to choose two sets of labels for a bar.

edit: Hot damn! I think I figured it out. You have to use Secondary Axis to do it. Not 100% sure how it works, but it's working for me so far.

 

I did't read thoroughly what you were trying to do,
but if you are trying to this it's in chart options

 

Thanks, what do you mean "chart options"?

 

I meant "chart type".
Select your data and then choose one from the bar charts

 

There's only stacked bar and 100% bar, there's no default chart type to overlay bars. Stacked bar will add everything together, and I don't want that. But I got it figured out.

 

there is a default type. It is to do with how you set your data series.
It's good you figured it out anyway.

 

I gave up because of the complexity of the data I was using, just made it simpler anyhow. Oh well at least I learned something. Thanks.

 

I was playing around with one of my charts the other day.
If you make the 3D chart look 2D then you can have an overlayed one.

 

Interesting idea, will consider that next time!

 

I have not messed with this stuff in quite a while. It's easy but you need to know the "tricks" especially when you start trying to do labeling and cosmetic stuff.

I wonder if I ever did a chart like what you want, I want to say I have but cant find any examples.

Lot o' Purdy Charts


Edit: Ok just did it with a normal chart no problems. I just did 100% series overlap in the format options with the normal (not stacked) column chart. If you have a problem with the overlay order (largest covers over the rest) then do what I did and move the series do the reverse order.

Give me some data and I will see if I can make the chart out of it and perhaps do a video tutorial for you.

 

I did find a way but it's not very efficient. I'm realizing Excel charts are really quite basic and require tricks to make them work right. I'd think after all these years they'd have improved greatly.

 

The way I just did it was pretty basic/easy.

Just normal chart and modify one option, if needed change series order. Would be nice if they had option to change overlap order but they do not.

Didn't take the time to pretty this up or anything but I used the data from that charge image above.

 

So what is your unique idea that is so different?????

 

This site explains how to create transparent bars
Spreadsheet Page Excel Tips: Creating A Transparent Chart Series

There you go,

 

I had transparency on the example above and took it off it looked bad with overlayed bars because the colors become inconsistent.