Visualize underlying parts and total value using column chart

posted Mar 29, 2016, 3:52 AM by Krisztina Szabó   [ updated Apr 4, 2016, 2:10 PM ]

For the experiment to be a success, all of the body parts must be enlarged.
Inga: In other words: his veins, his feet, his hands, his organs vould all have to be increased in size.
Freddy: Exactly.
Inga: He would have an enormous schwanzstucker.
Freddy: That goes without saying.
Inga: Voof.
Igor: He's going to be very popular.

by The FrankensTeam

Few days ago a colleague of mine showed me a chart in Tableau which he put together to visualize total category value and underlying data (eg. sub-categories). It was a nice and useful example to see the details behind summarized values.
I replicated his example in Excel using column chart and error bars (those thin dark-blue lines):

It is a combination of column and xy scatter charts where I hide the scatter points (no marker) and add error bars with sub-category values.

The base data table looks like:

Because this is just a top view, I put the categories into columns with sorted sub-category values below.
Using "dots start" and "dots end" cells you can position the error bars according to the width of the columns.

All the calculations behind the chart is performed in named formulas.
If you need more columns or rows, you can change the name "my_data".

Download our workbook with the example chart.

Update: Here you can read about another method to create similare chart.

This post can help to use the template in your own workbook: How to copy charts based on named formulas to other workbook?

Can also be interesting to read in the topic:
Stacking cubes charts