Excel - (Column) chart - Z-index of data series according to actual value
I have a column chart with multiple data series. For example:
I don't want to have columns next to each other so I set reduced Series overlap
and Gap width
in Format Data Series...
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 :) ).
(//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.
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.
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).