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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920