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 ROWÂ TOTALÂ calculation.

This is a new calculation in Excel 2010 and onwards.

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 RowÂ 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 and Sales QtrÂ fields, in theÂ COLUMNSÂ put in the FinancialÂ YearÂ field 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 RowÂ Total.Â

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

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

To format the % of Parent RowÂ TotalÂ column, click the secondÂ Sales fieldâ€™sÂ (%Â of Parent RowÂ 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 dialog box, make your formatting changes within here and pressÂ OKÂ twice. Â

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

You now have your Pivot Table, showing the % of Parent RowÂ TotalÂ for the sales data of years 2012, 2013, and 2014.

All of the sales numbersÂ are now represented as a Percentage of its Parent’s subtotal. Â

You can see that each red box is represented as 100% in totality for each encapsulatingÂ blue box i.e. Quarterly Sales as a percentage of each Sales PersonÂ´s Total Sales

If you like this Excel tip, please share it

email

Pinterest