Stacked bar(column) charts are a popular way to depict 2 more series of related data, like sales of 2 products.
But there are several ways to stack the bars in a bar chart. Here is a list of 6 ways to stack them
1. One on top of another
Advantages: Easy to create, takes less space
Drawbacks: Hard to compare, only first value starts at zero
2. Separated
Advantages: Easy to read and compare
Drawbacks: takes more space, needs extra calculation for the gap series
3. Mirrored:
Advantages: looks fancy and takes less space, good for large data sets
Drawbacks: needs extra calculation
4. Partially Overlapped
Advantages: Easy to compare, Easy to make
Drawbacks: One series dominates another, good where domination is needed (like this vs. last year)
5. Completely Overlapped
Advantages: easy to compare
Drawbacks: Needs extra formatting, not always produces good results
6. Hanged from Top and Bottom
Advantages: none
Drawbacks: difficult to compare, needs extra formula to calculate gap series
I like 2 and 5 and use them whenever I can.
What about you? How do you like your bars?
PS: for the purpose of discussion neglect other important chart elemets like labels, colors etc.
32 Responses to “[Reader Poll] Stacked, Seperated or Mirrored ?”
#4 and #5
ps how do you make #4 in Excel?
4-Partially Overlapped followed closely by 3-Mirrored
Hi,
I like 2 but would prefer a third bar showing the total value - this would facilitate easy comparison of the total value while the other two provide a comparison of the components.
Paresh
Nice taxonomy. Don't you consider "not overlapped" a separate case from #4?
I also liked the monochrome color scheme, by a curious coincidence almost the same color and shades I used for my most recent bar chart in a professional capacity (it was a 1, but I flirted with a 2 for a bit before settling).
I don't agree that #3 saves space compared to #2, but I think #6 can actually save space. (because the minimum total height is no longer the sum of the maxima of the two series, but is the maximum sum, which is usually less)
Most of the time I use #4 and #5. I use #5 when I want to see, what percentage from a plan is already achieved, while #4 is better (maybe even the best) for comparison purposes.
Also I find combined charts very useful. I often use combination of bar chart and line chart.
I prefer 5 - great for Budget v Actual - but I always make the overlapping bar narrower than the overlapped bar.
Saves space on the side-by-side version.
The extra space used by one variation over another is not very substantial, especially if you count in axis labels. The extra calculations amount to one extra column in the worksheet, big deal. The selection should be made based on what shows the data more effectively.
The 'Separated' version (#2) is my favorite of these bar charts. It provides a distinct baseline for each series in the stack. It's easy to see patterns, but not as easy to compare values between stacks.
I prefer a completely non-overlapped variety of the side-by-side bars over the partially overlapped one (#4). Partially overlapping the bars gives the ones in front greater emphasis, and that is not always (usually) what is desired. DMurphy's approach of thinning the bars in front may help. This clustered chart is probably my second favorite.
Likewise the darker color of the border in the front (transparent) bars in #5 gives them greater emphasis. I like this variation, though, so I try to pick my colors carefully. I often use the outline for goal and the fill for actual, so it is like a set of thermometer charts being filled.
I like #1 least of all. The mirrored and the opposing (hanging) variations are attractive, and if comparisons are not too critical I may use either.
Chandoo, Jon:
No ofense:
These are not bar charts. They are column charts (for God' sake!).
My favorite is Number 2, Separated.
Regards.
Leonel -
Until Microsoft Excel became widespread, everyone called these bar charts, whether the bars were horizontal or vertical. Purists and Excel neophytes still call them bar charts. I went along with Chandoo's terminology so he doesn't think I'm always picking on him 😉
What about fourteen 2-slice pie charts? Or two 14-slice pie charts?
I'm in favor of terminology that de-emphasises the supposedly important difference between horizontal and vertical orientation, so I mostly say "bar" for any type. Likewise, I'll often call a vertical line chart a line chart and not call it a "dot plot" just because it's sideways.
@Govert: You can change the overlap of a chart columns (or bars) by,
- select any data series by right clicking it
- go to format data series
- in the options area adjust overlap to some value between 0 and 100
- adjust gap between series as well since bars may look smaller
@Derek: yeah, I should have added another chart without any overlap.
@Leonel: Thanks for pointing it out. I generally use the term bar chart to refer this class of charts no matter what is their orientation. But I have added the word column in the main post to make it understandable for everyone.
@John: If you say so.. 😉
Chandoo - I'm sure J-Walk was simply baiting me....
Chandoo,
Everyone else seemed to pass on #1 (stacked), but I don't see any other one that shows the total of the 2 series. If the audience is interested primarily in the trend of the sum of the series, and less on how they compare against each other, only #1 fills that need.
What other options are there for a chart that shows both the sum of the series, and the comparison?
Rene
Just add the value of the sum of each bar. Just try to dont mess with the other labels (if you are labeling each values anyway)
@Rene: A valid point. Often when you are comparing sales of 2 or more products, you are still interested in the total sales trend and Option 1 can work well in such cases despite its poor comparison performance. May be the reason why only that option is included in the Excel.
But, again with other charts, you can still get the total using data labels, as Fulvioo suggested.
@Chandoo: That wasn't too difficult.............
Thanks
[...] inspiration and ideas visit: Stacked Bar chart techniques, 14 ways to visualize last year vs. this year [...]
[...] Stacked Bar Chart Techniques [...]
I like 1 and 2 for most cases.
How do you make number 3? I've seen these in reports but haven't figured it out. Any tips? Does this also work when I have two sets of data for the upside and two for the downside?
Any help would be great!
Thanks
@Montafoner: You can create the chart in 3 by adjusting the series 2 to be negative (just multiply the values by -1). You can correct the label display using custom cell formats (for eg. code like 0;0; can help) If you have two sets of data, then you need to use some more data massaging before you get the chart. One way is to create 4 new series of data using formulas.
3rd series remains the same
4th series is 3rd + actual 4th
2nd series is negative actual 2nd
1st series is negative of 1st + 2nd
In effect, you are creating the mirror series by adjusting the data.
Let me know if you have difficulty visualizing this. I can plan for a tutorial on this.
chandoo,
It will help me enourmously if you could have a tutorial for mirrored charts. I've tried on my own but I don't get it (as you can tell I'm not a math wiz) 🙂
Thanks,
Jackie
I am using #3 to display things like Product Backlog progress, with the negative portion showing the "drive by" additions. Let's say this is a stacked bar in blue with the negative part in pale blue (as here).
On the same chart I would like to display the work accomplished within a particular sprint, as a clustered bar (i.e. next to the product backlog, in green let's say), with its own "drive by" component in pale green.
In other words, I want to display four series, grouped as two sets of stacked bars displayed side-by-side as clustered bars.
Thanks,
- Sivani
How would I make #2 in excel?
Thanks
Crawford
@Crawford... Assuming the two series are in column A and B, in column C make a helper series that would introduce gap. Fill it with a formula like =1000-A1
Now plot these three series in a stacked column chart and adjust the series such that A is at bottom and B is at top.
Chandoo,
Even though this is an older poll I would still like to vote for #2. I just had to create an assessment result overview and that one works perfect with extending it to 11 columns (6 with data and 5 little helpers 🙂 )
How was Copenhagen? And how are the twins doing?
how do u create no 1
@Thabie.. it is a default chart in Excel. Select your data (suitably in a table format), go to insert > column charts > and select "Stacked column chart" to get one.
Years after the fact, but I like 6. I use it when comparing results achieved on a national video mystry shopper, but use the column from above to represent "Lost Opportunity to Improve", gradient blue from bottom, getting lighter to top of achieved result,a dn the gradient red from getting lighter as it comes down to meet achieved performance. The smaller the top Red, the better they achieved, greater red area means there is a huge "Opportunity to Improve"
Chandoo,
Thanks, as always, for the helpful info.
I am currently puzzling over a stacked bar chart. I am graphing three datapoints which are independent and related, but not additive. The Excel default on the ribbon (what they call a stacked column) is adding my numbers into a cumulative total which is misleading and wrong. How to I plot the three datapoints in a way that shows the columns overlapping, but not accumulating?
Thanks!
John
By the way, I've tried to do a simple three column chart, then adjust the overlap and gap (as you suggested above). The chart I'm working will not respond at all to the option of overlap (of any percentage).
I guess I'll start over and see if a fresh chart behaves any better.
By the way, I'm on a mac, excel 2008.
Thanks!
John
Hello Forum
First of all I thank all of you for your valuable inputs regarding excel usage.
I would like to get help on below issue.
I am requeting yout help on creating a bar graph of image shown with the data shown.
Image i got it while browsing in Internet.Data is my data which need to be formated as in this image.
Thanks in Advance.
Vignesh.V