Overlapped Bar Chart – Longer Bars in Back
by Jon Peltier
Wednesday, October 8th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Robert Kosara of EagerEyes.org compares popular vote to electoral college vote in The Electoral College and Second Terms. He came up with a very clever and rather effective bar chart to display his data. I’ve included a mere excerpt of Robert’s chart below, enough to show the visual appearance of the chart. The two series he compares overlap, such that the shorter bar is in front and the longer bar extends further along the axis scale.
Robert liked the way the chart displayed the data, but noted that he had to use a stacked type of chart with numerous additional columns of data to support the required series. I thought about it for a moment, and realized he could use a clustered chart type with full overlap, and only one additional worksheet column and one extra chart series.
I’ll demonstrate the technique on the following dummy data. There are two sets of numbers (“One” and “Two”) for a half dozen categories.
A simple clustered bar chart shows all of the data, but there’s too much back and forth of the two series to allow clear analysis.
When the two series are overlapped, we only see series “One” where it is greater than series “Two”, in category C.
We can address this by adding another column to the data, called “One-A” in the table below. This column has a data value matching that of series “One” if that value is less than the corresponding value of series “Two”; otherwise the value is zero. This results in a second set of series “One” values that will plot in front of series “Two” when overlapped.
The formula in cell D2 (which is copied into D3:D7) is:
=IF(B2<C2,B2,0)
A clustered bar chart now has three series.
The extra series “One-A” is formatted to match series “One”.
When the series are overlapped 100%, we get the effect Robert worked so hard to achieve, but with a minimum of fuss.
Incidentally, I didn’t care for the gray background below 50% in Robert’s chart. I found that the darker background selectively lightened the foreground bar colors, making it difficult to assess values close to 50%. For Robert’s chart, showing election results, I’d limit my vertical gridlines to 50% and 100%.
Update: October 13, 2008
Robert Kosara has followed up his original post with Popular vs. Electoral Votes Using Stacked Bar Charts, in which he describes the mechanics of his stacked bar approach. The data range looks like this:
Cells D2, E2 and F2 have the following formulas, which are copied into the rows below.
D2: =IF(B2<C2,B2,0) E2: =C2-D2 F2: =MAX(0,B2-C2)
Select A1:A7, then hold Ctrl while selecting D1:F7, and create a stacked column chart. Format series One and One-A the same, then remove the One-A legend entry by clicking once to select the legend, then again to select the One-A legend label, then press Delete. The result is visually indistinguishable from the chart just above this update.
Update: October 10, 2008
Reader Stag Lee commented that Robert’s chart type was not intuitive, and suggested a slight modification, as shown here:
The protocol to create this chart is presented in Overlapped Bar Chart – Thinner Bars in Front.
Related Posts:
- Overlapped Bar Chart – Thinner Bars in Front
- Clustered-Stacked Bar Charts
- Conditional Stacked Clustered Column Chart with Targets
- Order of Legend Entries in Excel Charts
- Clustered-Stacked-Column Combo Chart With Lines
- Redrawn Electoral Maps
- Column Chart to Replace Multiple Pie Charts
- Clustered-Stacked Column Charts
- Clustered-Stacked Column Charts with Vertical Separators
- Funnel (Tornado) Chart
Posted: Wednesday, October 8th, 2008 under Formatting.
Comments: 23
Comments
Comment from Robert Kosara
Time: Wednesday, October 8, 2008, 10:31 am
Interesting! I agree about the gray being to dark, I’ll change that. I did think about keeping only the 50% and 100% lines, but I wanted to make it easier to read the numbers.
Comment from StackLee
Time: Wednesday, October 8, 2008, 11:14 am
I like the re-design that includes the One-A bars. If you added a One-A bar for “C” you end up with a bullet type format that makes comparison/comprehension much easier.
Comment from Jon Peltier
Time: Wednesday, October 8, 2008, 12:18 pm
Stack Lee – I don’t think I understand your point. There is no One-A bar for C, because series One is greater than series Two for category C.
Comment from Chandoo
Time: Wednesday, October 8, 2008, 12:22 pm
This is very clever Jon, excellent way to achieve something that looks almost impossible at first take.
Comment from TV
Time: Thursday, October 9, 2008, 1:20 pm
I find the color inversion confusing.
As it’s shown here, the end mark is all that matters in indicating the raw value, and the color scheme gives the One series an impression of being very small in category C (according to a standard reading of stacked bars).
I would prefer the bullet style where series A would have a no background color (or be a different width) and I can compare the series B relative to that benchmark with no confusion of reading the individual values…
Comment from Jon Peltier
Time: Thursday, October 9, 2008, 3:59 pm
TV -
If we know that the bars are overlapping and not stacked, this chart type makes it very easy to see which categories have a “One” value greater than its “Two” value (i.e., “C”).
In your favored alternative, would you have both A and B in the same bullet graph? I have tried numerous ways to compare two column series. In some cases the chart is too cluttered, while in other cases, one series is emphasized at the expense of another.
I discussed this in my Tornado Charts and Dot Plots article. Here is a cluttered chart:
Here are a couple which are less cluttered but emphasize the filled series:
These two show less bias toward the shaded series, but not much less:
This chart is least biased of all:
Comment from Stacklee
Time: Friday, October 10, 2008, 11:47 am
I think the step “One-A is formatted…” is the best version. Visualize one large width blue bar (but really 2 bars) overlayed with one small or medium width green bar. When series “two” is less than “one”, it is simple and clear, IMO, – but you need to add a “one A” for “c”.
Comment from Robert Kosara
Time: Friday, October 10, 2008, 1:38 pm
TV, you’re right, but the point of this chart was also to see the “amplification” from the electoral college system, so the part that both bars have in common is really not all that interesting. What is interesting though, is where the electoral vote was less than the popular, so these things were meant to stand out (and that’s why I spent so much effort on this, to make that point).
If you want to look at it as a stacked chart, it’s simply stacking the gain on top of the popular vote – so that works too.
Comment from TV
Time: Friday, October 10, 2008, 1:44 pm
Jon:
I see your concern about bias. My comment assumed the series are not independent… if the two series are, say, time 1 and time 2 of the same data, then I think the emphasis of time 2 would be acceptable. If they are independent, then yes, the shaded series pulls a bias that should not be there.
I agree the last chart is unbiased, and for a side by side comparison that is effective.
Comment from Jon Peltier
Time: Friday, October 10, 2008, 8:14 pm
Stack Lee -
The chart you describe is discussed in Overlapped Bar Chart – Thinner Bars in Front.
Comment from giedrius
Time: Friday, November 14, 2008, 7:15 am
Hi,
Is there a way to make column One stacked? I.e. I want to show yearly budget figure and the budget for the quarter in the same column. Then column Two would show actual achievement figure for the quarter.
Thanks.
Comment from Jon Peltier
Time: Friday, November 14, 2008, 7:21 am
Giedrius -
Technically anything is feasible. The question is whether it’s desirable.
Which chart would have the quarterly and annual budgets combined? If you mean the one which is the topic of this post, I think it would be more cluttered and confusing than it is worth. If you mean one of the charts from the comments, well, you would run into the same problem of confusion over mixed annual and quarterly numbers.
Comment from giedrius
Time: Friday, November 14, 2008, 7:40 am
Hi again,
I mean chart that is just above my initial question (your post of October 10, 2008, 8:14 pm). I want the dark blue column (called One) to come in two colors – one representing the quarterly figure, the other – the rest of the total figure for the year (so that the total blue+other color column height represents total budget for the year).
Why you say the numbers will be mixed? They will not, to my understanding. In some organisations you might need to have a feeling both how you are proceeding during the quarter and year in total. Especially when budgeting is not very precise and/or achievements during quarters vary severely. Of course, one can make two separate graphs, but I thought it would be excellent to have everything in one.
Comment from Jon Peltier
Time: Friday, November 14, 2008, 8:06 am
Well, it can be done. Just make a stacked chart, with four series on the primary axis and four on the secondary axis. Something like this:
Beware the optical illusion.
Comment from giedrius
Time: Friday, November 14, 2008, 8:41 am
Well, that is not what I imagined, but I got your point. Thanks a lot.
Comment from James
Time: Friday, April 17, 2009, 7:20 am
Hi Jon,
another excellent post!
I’m having a couple of difficulties with having a 3 series chart. Just using overlap tends to favour the largest one. Basically I want the 3rd series as a “background” to the other 2. Does this approach depend on the secondary axis only? Is there a z-index facility to use with overlap?
Also, on an unrelated note, one of columns is very small, but still a positive integer, and appears below the y-axis. Is this an unresolved bug with the system?
Cheers,
James
Comment from Jon Peltier
Time: Friday, April 17, 2009, 7:28 am
James -
There is a particular Z-order to the series. Primary axis series are behind secondary group series of the same chart type. Within an axis group, the earlier plot order series are below the later plot order series.
Think of the series as being laid down in order. Primary series are laid down first, in the order 1, 2, 3, then secondary.
Comment from savithri.v
Time: Sunday, September 6, 2009, 5:09 pm
Hi Sir,
Is it possible to overlap columns in a clustered stacked column chart?
Suppose one of the clustered columns depicts investments categorised in 3 sections -operational assets, non operational assets & sunk costs stacked on top of each other & the 2nd column shows equity injections into the project & retained earnings from the project due to shareholders stacked one on the above.
In a situation where the retained earnings is negative from a couple of projects, is it possible to show this negative figure as overlapping on the equity portion (instead of stacking it below zero) to illustrate how much of the equity injections have been eaten away by losses from those projects?
savithri
Comment from Jon Peltier
Time: Sunday, September 6, 2009, 7:27 pm
Savithri – I’m not sure I visualize your intended graphic. How would overlapping successfully depict negative numbers?
Comment from savithri.v
Time: Monday, September 7, 2009, 4:24 am
Dear Sir,
Sorry – as the internet link was down, saw this just now
I felt that if the overlapping is made with a thinner column in the front, it would portray the portion of the funding that is eaten away by the losses.
I had to get a presentation done this morning as part of a Balance Sheet structure. As I couldn’t figure out how to get the message across, I added another series for ABS value of losses, changed it to scatter type, used a minus Y error bar (100%) & formatted it with the maximum possible weight but still couldn’t get the desired width.
And as always…I can no other answer make but thanks, and thanks, and ever thanks (I’m learning Shakespeare from my son!)
savithri
Comment from Ken Stern
Time: Wednesday, September 16, 2009, 3:47 pm
As usual, very illuminating and great suggestions. I have one further variation I can’t seem to get to work. I have three sets of yearly estimates – low, medium, high. I’d like to show each year as a trio of columns with partial overlap. I want the lowest to be both on the left and in front (so each set of 3 increases to the right and towards the back, in effect).
I can’t seem to get both orders correct at once. In short, the right-most bar (i.e. the one with the highest series number) is always in front. Is there a way to play with front/back bar overlap without changing the order of the series? (I’d post a picture if there were an easy way but hopefully I’m explaining it clearly.)
(If I only had two series, I could get it working by playing with primary/secondary axes, but as it is these need to all be on the same axis anyway.)
Comment from Jon Peltier
Time: Wednesday, September 16, 2009, 11:22 pm
Ken -
You can’t interweave two series in that manner. A series can be either completely in front of or completely behind another series.
Comment from Jon Peltier
Time: Monday, October 26, 2009, 7:49 am
Karan asked how to get the taller-bar-behind-shorter bar effect if there are more than two series. Essentially it’s the same, but you need several extra series. Here is one way to approach it.
Add some columns to the data:
The formulas in E2 through H2 are
=IF(OR(B2<C2,B2<D2),B2,0)
=IF(C2<D2,C2,0)
=IF(AND(D2<B2,D2<C2),D2,0)
=IF(AND(B2<C2,B2<D2),B2,0)
Here is the chart, not fully overlapped:
And here is the chart, with fully overlapped bars:
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.