Excel - Percent of Parent with Pivot Table - Excel - Episode 1404

2,865 views · Published 13 May 2011 · 8:08 · Indexed 6 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Calculating Percentage of Parent in Excel or Power Pivot.

Welcome back to another exciting episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel.com and I'm joined by the real Excel Is Fun, not that imposter out there trying to steal Mike's identity. In today's episode, we'll be tackling the topic of "Percentage of Parent" and showing you two different ways to achieve this in Excel.

First, we'll start with a pivot table. We want to create a table that shows the Dallas market as a percentage of the central region. To do this, we'll insert a pivot table and select "Existing Worksheet". Then, we'll add the fields "Region" and "Market" to the rows section and "Total Revenue" to the values section. Now, here comes the new feature in Excel 2010 - we can easily calculate the percentage of Chicago divided by the Central region by dragging the "Total Revenue" field a second time and selecting "Show Values As" > "Percentage of Parent Row Total". This gives us the desired result, although it may not look quite right to some users.

For those using Excel 2007 or 2003, we have a workaround. We'll add an extra column to our data set and calculate the percentage of parent manually. Then, we'll create a pivot table and add the fields "Region" and "Market" to the rows section and the new "Percentage of Parent" field to the values section. This gives us the same result as the Excel 2010 method, but it may not be as efficient for larger data sets.

But wait, there's another solution! We can use the power of Power Pivot to achieve the same result in a much faster and more efficient way. We'll create a linked table in the Power Pivot tab and then create a pivot table with the fields "Region", "Market", and "Revenue". Here's where the magic happens - we can use the "Calculate" function to apply filters to our data and get the desired result. This method is especially useful for larger data sets, as it only needs to do the calculation 15 times instead of potentially thousands.

So there you have it - three different ways to calculate the percentage of parent in Excel. Whether you're using the new feature in Excel 2010, the workaround in Excel 2007 or 2003, or the power of Power Pivot, you now have the tools to easily calculate this important metric. Thanks for tuning in to another episode of the Dueling Excel podcast, and we'll see you next week for more Excel tips and tricks from MrExcel and Excel Is Fun.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to the episode and hosts
(00:12) Explanation of the topic: Percentage of Parent
(00:30) First method using a pivot table in Excel 2010
(01:32) Second method using a formula in Excel 2007 and 2003
(04:32) Power pivot solution using measures
(04:42) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Calculate function
Excel 2007 and 2003 pivot table
Excel 2010 pivot table
Large data set calculation
Linked table
Percent of region
Percentage of parent
Pivot table calculation
Power pivot solution
Revenue calculation
SUMIF function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153082/


Another Dueling Excel Podcast with Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen. Today, in Episode #1404, Mike and Bill look at finding the Percent of the Parent Row Total in Excel 2003 to 2010 using Formulas and/or PowerPivot.

More from this channel