Waterfall Chart
Box and Whiskers
Overlapped Bar Chart - Longer Bars in Back
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
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.
Possibly Related Posts:
- Overlapped Bar Chart - Thinner Bars in Front
- Clustered-Stacked Bar Charts
- Clustered-Stacked Column Charts
- Clustered-Stacked-Column Combo Chart With Lines
- Column Chart to Replace Multiple Pie Charts
- Redrawn Electoral Maps
- Clustered-Stacked Column Charts with Vertical Separators
- Excel 2007 Chart Performance - Revisited
Posted: Wednesday, October 8th, 2008 under Formatting.
Comments: 15
Comments
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.
Read the PTS Blog Comment Policy.
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.
Write a comment