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.