Pinterest Pixel

Show The Percent of Parent Column Total With Excel Pivot Tables

Bryan
 .
Show The Percent of Parent Column Total With Excel Pivot Tables | MyExcelOnline Show The Percent of Parent Column Total With Excel Pivot Tables | MyExcelOnline

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:

Show The Percent of Parent Column Total With Excel Pivot Tables | MyExcelOnline

download excel workbook Percent-of-Parent-Column-Total.xlsx

 

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

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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:

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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.

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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%).

Show The Percent of Parent Column Total With Excel Pivot Tables

 

STEP 6: Click the Number Format button:

Show The Percent of Parent Column Total With Excel Pivot Tables

 

 

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.

Show The Percent of Parent Column Total With Excel Pivot Tables

 

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…

Show The Percent of Parent Column Total With Excel Pivot Tables

 

 

PIVOT BANNER

If you like this Excel tip, please share it
Show The Percent of Parent Column Total With Excel Pivot Tables | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Group By Custom Dates With Excel Pivot Tables

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...