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?
23 Answers
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'...
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!
Switch back to the Pivot table View. Then right click the Sum row and change it to percentage.
1
