Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My Excel 2010 data (simplified) looks like this:

Product ID     Product Family     Safe For Kids    Under $10   Free Shipping   
1              Housewares         1                0           1
2              Pots-n-Pans        0                1           0
and so on...

I want to end up with a "clustered column" chart which shows, for each product family, the percentage of products in that family which are "Safe for Kids", "Under $10", or have "Free Shipping". Each product family should have three bars each, one for each percentage.

It's easy to do this for absolute values by using a sum in my PivotChart for each column. But I can't figure out how to turn those sums into percentages of the total number of rows in each category.

Any ideas?

share|improve this question
Would be adding a formular column to the data sheet and using it inside the Pivot table an option or is this not possible? – Tex Hex Aug 3 '11 at 9:58
I could create more columns but was hoping to avoid this. – Justin Grant Aug 3 '11 at 23:20

Switch back to the Pivot table View. Then right click the Sum row and change it to percentage.

share|improve this answer
That won't actually calculate percentages against the other columns, because the original data is not normalised. – AdamV Aug 5 '11 at 13:34

Add calculated fields to the PivotTable directly: Total = Safe + Under + Free Safe_pc = Safe / Total Under_pc = Under / Total Free_pc = Free / total

Don't bother to include the original data in the PT, just include the three new fields for Safe_pc, Under_pc, Free_pc (drag to the values area, it will take care of column headings for you) Right click each one to change number format to percentage. For neatness, click each value column heading and type to rename, eg to "% Safe" rather than "Sum of Safe_pc"

Insert your clustered pivot chart.

Job done. Hope this helps

If you don't actually need the figures but want exactly the same visual result, you could always use a "stacked 100% chart" on the original numbers. Personally I think stacking here makes slightly more sense than clutering, as it makes very clear that these are "parts of a whole" and not to be compared directly to one another in magnitude, it's all about proportions. Just sayin'...

share|improve this answer

In this post, scroll down to Debra Dagliesh's answer. It involves a simple additional column, and like @AdamV's answer, uses calculated fields. I think it's exactly what you need to do!

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .