Calculate Percentages on Excel Pivot Table Totals

Question

I have Excel source data with Phase, Team, and Early or Late. I've created a pivot table to summarize the data like this:

            Team 1               |  Team 2              |
Phase       Early | Late | Total | Early | Late | Total | Grand Total
---------------------------------------------------------------------
Phase 1       2       3      5       0       1      1          6
Phase 2       4       5      9       2       2      4         13
Phase 3       5       6     11       1       1      2         13
Grand Total  11      14     25       3       4      7         32

I'd like to calculate percentages of the totals (so the total early for team 1 divided by the total for team 1--or 11/25 for 44% early, and then the total late divided by the total, or 14/25 for 56% late).

I tried using formulas below the pivot table, which worked great, but they get out of whack if the source data doesn't have records that fall in a column (so if there are no late items for Team 2, that column disappears). Is there an easy fix or a way to keep the pivot table at a set number of rows or columns?

daniellopez46 daniellopez46 · Accepted Answer · 2012-06-08T17:02:12

Instead of constructing formulas separately from the pivot table you can just set the pivot table to calculate the percentages directly. That way you don't have to worry about how many columns and rows are in the pivot table. In Excel 2010 (2007 s/b the same too) go to the PivotTable Tools ribbon then options and under calculations click the dropdown Show Values As and select % of Parent Column Total.

Calculate Percentages on Excel Pivot Table Totals

1 Answers