Excel Pivot Tables have a lot of usefulÂ calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT COLUMNÂ TOTALÂ calculation.

This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, expenses, attendance, or anything that can be quantified.

In the example below I show you how to get theÂ Percent of Parent ColumnÂ Total:

STEP 1:Â Insert a new Pivot table by clickingÂ on your data and goingÂ toÂ Insert > Pivot Table > New Worksheet or Existing Worksheet

STEP 2:Â In theÂ ROWSÂ section put in theÂ SalesÂ Person field, in theÂ COLUMNSÂ put in the Sales Qtr andÂ FinancialÂ YearÂ fields and in theÂ VALUESÂ area you need to put in theÂ SalesÂ field twice, I explain why below:

STEP 3:Â Click the second Sales fieldâ€™sÂ (Sum of SALES2) drop down and chooseÂ Value Field Settings

STEP 4:Â Select theÂ Show Values AsÂ tab and from the drop down chooseÂ % of Parent ColumnÂ Total.Â

Also change theÂ Custom Name into %Â of Parent ColumnÂ Total to make it more presentable.Â ClickÂ OK.

STEP 5:Â Notice that theÂ %Â of Parent ColumnÂ TotalÂ data is in a decimalÂ format that is hard to read:

To format the % of Parent ColumnÂ TotalÂ column, click the secondÂ Sales fieldâ€™sÂ (%Â of Parent ColumnÂ Total) drop down and chooseÂ Value Field Settings.Â

The goal here is for us to transformÂ numbers fromÂ a decimalÂ format (i.e. 0.23), into a percentageÂ format that is more readable (i.e. 23%).

STEP 6: Click the Number Format button:

STEP 7:Â Inside theÂ Format Cells dialogue box, make your formatting changes within here and pressÂ OKÂ twice. Â

In this example, we used theÂ PercentageÂ category to make our %Â of Parent ColumnÂ TotalÂ numbers become more readable.

You now have your Pivot Table, showing the % of Parent ColumnÂ TotalÂ for the Sales data of years 2012, 2013, and 2014 grouped by Quarters.

All of the Sales numbersÂ are now represented as a Percentage of its Parent’s Subtotal i.e. Percentage of each Quarter’s Subtotal.

You can see that theÂ red boxes are represented as 100% in totality when grouped together for each Quarter (encapsulatingÂ blue box):

Click on the image to enlarge…