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?

  • 1
    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

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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