Excel - (Column) chart - Z-index of data series according to actual value

I have a column chart with multiple data series. For example:

Example data

Default column chart

I don't want to have columns next to each other so I set reduced Series overlap and Gap width in Format Data Series...

What I get

Now the Series are one over the other. But their Z-index (Z-position) is defined by the Series order in the chart, so when the last Series has highest value, its column outgroves the others which are not visible.

Is somehow possible to order the columns according their actual value? I'd like to bring the smallest value most front. Like in this image (ancient mspaint-fu used here :) ).

What I want to get
(//edited - the first version was wrongly mspainted)

PS: I need it for really big data series (it looks like histograms), so I definitely don't want to put the columns next to each other. But it could be "filtered" to low series view where using of other chart type could cause distortion of displaying these discreet values.

Thanks for your suggestions!


One way is to create a processing table that works out the ascending order of the series.

enter image description here

Columns E:G work out the 1st smallest, 2nd smallest and so on using the SMALL() formula, where the first parameter is your row of values, and the second the rank - so cell E3 is =SMALL($A3:$C3,1), F3 =SMALL($A3:$C3,2) and G3 =SMALL($A3:$C3,3). Copy down as many rows as you have, and add as many columns as you need if it's more than 3 series.

Then we need a processing table, shown in columns J:R.

A column for each series is present, for every possible position in ascending order. In the "1st" column group, in column J we can check whether series A is the 1st smallest series using the formula

`=IF(E3=A3,A3,0)`

If there is a match, the value for the series is shown. If not, zero is shown.

Now if you create your clustered column chart using range J2:R6 and apply 100% series overlap, you'll see you need to reorder the columns. Using the Select Data dialog box, reorder the columns so 3C is at the top and 1A at the bottom.

enter image description here

Finally, change the colours of each series. All A series must be blue, all B series must be orange and C all grey (or whatever your actual colours are).