Stack Columns In Order Of Size
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
When you create a stacked column chart, the columns are stacked in the order that the series are added to the chart. When the simple data shown below is plotted, Series A is on the bottom, Series B is stacked on A, and C is stacked on B. This order disregards the individual values of the points at each category.
- – - 
It is often desirable to sort the individual stacks so that smaller values are plotted on larger values. Last week I was asked how to do this, and I thought I’d try doing it with worksheet formulas, and if that became too complex then I’d work something up in VBA.
Worksheet Formula Approach
The worksheet formula approach requires more than three series, each series having some values and some zeros, so that stacking thee values and zeros together shows the visible data points in order of size. The data range is duplicated below, with a second range constructed with formulas to plot the visible data in the proper order.
For three visible series, seven series are required. The formulas, derived with careful algorithms and especially with trial and error, are listed below. These formulas go into row 2, and are copied down to fill the range.
$F$2 =IF(AND(B2>=C2,B2>=D2),B2,"") $G$2 =IF(AND(D2>B2,D2>C2),D2,"") $H$2 =IF(OR(C2>B2,C2>=D2),C2,"") $I$2 =IF(OR(AND(B2>=C2,B2<D2),AND(B2>=D2,B2<C2)),B2,"") $J$2 =IF(OR(D2<=B2,D2<=C2),D2,"") $K$2 =IF(AND(C2<=B2,C2<D2),C2,"") $L$2 =IF(AND(B2<C2,B2<D2),B2,"")
Create a chart with the calculated values, and use the same formatting for all series with the same name.
Remove redundant legend entries: click once to select the legend, then once more to select the legend entry (the text, not the colored box), and press Delete.
Three is about the most distinct series you could easily do with this technique. If three series required seven columns of calculations, four must require eleven or more (I got too tangled up to sort this out).
An upcoming post will show a VBA technique that is not limited to three series.
Possibly Related Posts:
- Stack Columns In Order Of Size With VBA
- Clustered-Stacked Column Charts with Vertical Separators
- Order of Legend Entries in Excel Charts
- Clustered-Stacked Bar Charts
- Clustered-Stacked-Column Combo Chart With Lines
- Interactive Charts with Checkboxes and Formulas
- Legends in Excel Charts – Formats, Size, Shape, and Position
- Overlapped Bar Chart – Longer Bars in Back
- Adding Excel Chart Data
- Interactive Charts with Checkboxes and VBA
Posted: Monday, May 18th, 2009 under Charting Principles.
Comments: 11
Comments
Comment from Scott
Time: Monday, May 18, 2009, 7:29 am
Interesting, but I think I would see more value in dynamically sorting left to right by the size of each series. There are occasions where it is nice to quickly evaluate the order of series in a column.
Also, your 7 columns didn’t consistently deal with ties (e.g. columns H & L ). When A&B are tied and they are the largest values they stay in A/B order, but if they are tied and C is the largest value, they swap to be B/A based on the order of your 7 comparison columns.
I would have probably attacked it with three columns (one per series) with functions for the RANK in the row. Followed by nine columns of (A,B,C),(A,B,C),(A,B,C) with logic of if A is rank 1 it’s value goes in the first A column, if it is rank 2 it goes in the second A column etc. After that the same plotting can be done that you show with deleting the legend entries etc.
Comment from Jon Peltier
Time: Monday, May 18, 2009, 8:54 am
Scott -
Your approach seems somewhat simpler to construct and follow than mine. See, like I tell people, sometimes it’s smarter to use a couple extra columns than try to write some massively clever formulas which only save a column or two.
When I get a chance I’ll rewrite my blog entry using your setup.
Comment from DMurphy
Time: Monday, May 18, 2009, 10:07 am
You like setting a challenge, Jon, so here is my solution to the 4-series problem. I’m not a great fan of embedded IFs/ANDs/ORs, so I went down the line of using MAX, MIN and LARGE functions (SMALL could also have worked):
http://www.box.net/shared/mm3i06ql3r
Please feel free to share.
Comment from Jon Peltier
Time: Monday, May 18, 2009, 1:12 pm
I started with LARGE, but got tripped up by duplicate values. I’ll have to check out your workbook to see how you handled that.
Comment from DMurphy
Time: Tuesday, May 19, 2009, 2:40 am
Sorry folks – just noticed that it fails if there are triplicates! Needs further thought!
Comment from DMurphy
Time: Tuesday, May 19, 2009, 3:01 am
…but here’s a version which appears to work if there are 3 or 4 identical values.
Comment from Jon Peltier
Time: Tuesday, May 19, 2009, 5:19 am
One trick. if you need to preserve order when sorting duplicates, is to add a small amount to each value according to position. If columns are A to Z, left to right, add something like (value+column())/10000 to each. It means maintaining an extra data region, but you need an extra anyway to contain the sorted values.
Comment from Bob
Time: Wednesday, May 20, 2009, 2:53 pm
Hi,
Wouldn’t the same information be better presented in a standard cluster chart?
Or perhaps a panel chart?
Just a thought.
Cheers,
Bob
Comment from Jon Peltier
Time: Wednesday, May 20, 2009, 6:21 pm
Bob -
The more I look at this chart, the less effective I think it is. I think I’d prefer a clustered column chart.
Pingback from Analytics Team » Blog Archive » Reorder stacked columns in Excel charts
Time: Thursday, May 21, 2009, 12:18 am
[...] in Excel, the columns are normally stacked in whatever order you put the series in. Check out this how-to guide for reorganizing the columns so the largest values are on the bottom of each [...]
Comment from Bob
Time: Thursday, May 21, 2009, 4:08 pm
Hi,
Just because it is possible to build this chart in Excel. Doesn’t mean you should. :)
Although, well crafted VBA is always fun to look at for inspiration.
Cheers,
Bob
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.