Visualize underlying parts and total value - alternative column chart

posted Apr 3, 2016, 12:45 PM by Krisztina Szabó   [ updated Apr 3, 2016, 12:47 PM ]

Freddy: Damn your eyes.
Igor: [to camera] Too late.

by The FrankensTeam

Well, when I had sent my solution to Igo-r about the combined column chart I made to visualize total number and underlying data, he sent me back an alternative one which... well, easier to set up, while visually almost the same.

Here the "thin" columns are added as column chart - each row of the original data table is one data series. (You can easily do it: select the underlying data part as rectangular range, add column chart. Then go to the Select data menu and click Switch Row/Column button.)

The trick is that: total columns are created using up-down bars. You have to add two more data series to the chart: one for the category total values, and another with 0 values. Both are changed to line chart type. Now add up-down bars to on of the line. Up-down bars appear as columns between the two line data series.
Unfortunately up-down bar is always in the foreground of the chart, so it will cover the thin columns. To step over this problem, you can set the color of the bar with transparency, or use borders only. In our solution we chose gradient color.

Width of the up-down bar columns can be set under the settings of the line series of which the bars belong to. (Unfortunately this is not available in Excel 2007 - you will need a row of VBA code to do it in this version.)

You can download our file to see how it looks like.