Stacked and separated dynamic Excel charts

posted Aug 13, 2013, 6:50 AM by roberto mensa   [ updated Aug 14, 2013, 8:31 AM by Krisztina Szabó ]
Igor: Where are you going?
Dr. Frederick Frankenstein: To wash up. I've got to look normal.
[his bowtie pops open]
Dr. Frederick Frankenstein: We've all of us got to behave normally.

by The FrankensTeam

Stacked charts are good tools for visualizing the total value and the contribution of each value to the total across categories. The problem is that you can not compare the categories, because (except the bottom-one) the starting point of the columns is different.
The solution is to separate the columns - as you can see here:
We really like this dynamic solution, it serves both visualization needs: compare the total and compare the categories too.
Technically only need to use one stacked column chart, add new data series between the categories, and set it invisible. This new data series will be 0 if you want to have a real stacked chart. If you want to separate the columns, this series will represent the distance between the columns. In Excel it could easily be done using a support table and some formulas.
On top of the stacked and "separated" charts, 100% stacked version could also be useful, so we included this version to our model. We use a simple list to choose from the three chart types.

Some technical details:
On sheet Tbl_support you can find the support table with the extra data series. This is the base of the chart. Only one formula is used to calculate all the data depending on your chart type choice. If you have more data in your original data table, you have to copy these formula down and/or right, then you have to extend all the data series on the chart (or add new data series for the extra categories).
On sheet Setting you can see a Step value - this is the base gap between the columns. If you increase the value, the distance will be larger.
In Excel 2013 all movment is smoothed, so the Excel version is really looks like the original web-based solution. On this youtube video you can see how it is:

Downloads Excel file:

You could easily change the data series to stacked area chart to have the same construction in area version.

And of course the horizontal bar version - in this case it is not enough to change the type, need to change the labels too.