Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So I have the following set of values in my excel sheet:

excel values

The values on the first column (very low, low, medium, high) are simply socio-economic layer indicators.

As you can see, everything is pretty simple here. I want to make a stacked column chart out of these, displaying the totals not as another layer but as mere totals. To do this, I apply the 'hack' found here

Which leaves me with this:

initial stacked column chart with totals label hack-fix

So far so good. What I don't know how to do though is break down the yellow area, the 'did not wish pregnancy' category, in other words, I want to add, within it's yellow area, which represents the 100%, the entirety of the value itself, relatively speaking, a subvalue, which in this case is called 'interrupted pregnancy'. To make matters clear, this is how our excel tables would look with the added column of values, which I highlighted:

updated excel values

So, with this in mind... I'm lost as to how to show a portion of that yellow area, without modifying the value of yellow, blue or even the totals, with the tiny percentage that represents 'interrupted pregnancy'.

This is HOW the chart SHOULD look if I knew how to do it:

ideal look of updated chart

All help is greatly appreciated. For the record, I'm using excel 2007

share|improve this question
    
Your chart example as shown is inaccurate. As @Jon Peltier suggests, you should show either 1) the correct values in each column segment (i.e. 20.91 and 2.09) or 2) a clustered column that shows the individual breakdown of each section. Currently, your chart is misleading (even if to a minor degree), and may undermine your credibility if someone starts digging into the numbers. –  dav Jun 5 '13 at 12:08
add comment

migrated from stackoverflow.com Jun 3 '13 at 0:56

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 1 down vote accepted

This may call for a stacked clustered column chart, where the value for "interrupted pregnancy" can be shown as a separate bar besides the stacked bars. The technique and its application without code is explained in detail on Jon Peltier's page.

http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/

The technique involves laying out your data in a specific way and "fudging" (technical term!) the chart settings to display the final result.

He also offers a paid tool to automate the creation of such charts.

share|improve this answer
    
Thanks teylyn for the link and most importantly, thanks again Jon for your great excel insight. Everything worked wonderfully. –  Sotkra Jun 6 '13 at 0:55
add comment

Do you want to show the subvalues as 2.09% and 20.91%? Then you need a column that calculates the difference between "didn't wish pregnancy" and "interrupted pregnancy", and just stack them. Use two shades of the same color to indicate they are related.

If you want to show "didn't wish pregnancy" as 23.00% as well as the 2.09% for "interrupted pregnancy", then the clustered-stacked approach mentioned by Teylyn might help show it. The left stack would show the categories, while the right stack would show subcategories.

share|improve this answer
    
One way to get the 23.00% displayed would be to use the same method used to display the 59.90%, etc. totals, i.e., add the original "did not wish pregnancy" data series back to the chart, change its type to line, hide the line and markers, and set the label position to "Below". (I also had to hand-nudge one of the labels to get it positioned more correctly.) –  chuff Jun 3 '13 at 2:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.