Excel Pivot Table Hack: Display Percentages of Parent Row Totals

Piv­ot tables offer a pow­er­ful way to ana­lyze data, par­tic­u­lar­ly when you need to under­stand pro­por­tions with­in hier­ar­chi­cal cat­e­gories. In this tuto­r­i­al, I’ll show you how to quick­ly add sums in a piv­ot table and dis­play val­ues as a per­cent­age of their par­ent row, allow­ing for imme­di­ate visu­al­iza­tion of how indi­vid­ual items con­tribute to their cat­e­go­ry totals.

This tech­nique is espe­cial­ly valu­able when ana­lyz­ing sales data across prod­uct cat­e­gories and indi­vid­ual items.

Watch my step-by-step video tuto­r­i­al below to see this process in action:

Setting Up Your Pivot Table

To begin cre­at­ing an infor­ma­tive piv­ot table with per­cent­age cal­cu­la­tions, we need to start with a sim­ple dataset that con­tains hier­ar­chi­cal infor­ma­tion. In my exam­ple, I’m using a dataset that includes cat­e­gories (such as fruits, veg­eta­bles, and sweets) along with the spe­cif­ic prod­ucts with­in each cat­e­go­ry and their cor­re­spond­ing rev­enue figures.

The process of cre­at­ing the piv­ot table is straightforward:

1. Select your data range
2. Nav­i­gate to the Insert tab in the Excel ribbon
3. Click on “Piv­ot Table”
4. Choose to place the piv­ot table on an exist­ing work­sheet (I select­ed cell F1 in my demonstration)
5. Click “OK” to cre­ate the basic piv­ot table structure

Once your piv­ot table frame­work is estab­lished, you’ll need to struc­ture it prop­er­ly to show both cat­e­gories and their con­stituent prod­ucts. In the Piv­ot­Table Fields pan­el, drag the appro­pri­ate fields to build your hier­ar­chi­cal view.

Structuring Your Pivot Table

For prop­er hier­ar­chi­cal analy­sis, you’ll want to arrange your fields in a log­i­cal order. In the Rows sec­tion of the Piv­ot­Table Fields pan­el, add your Cat­e­go­ry field first, fol­lowed by the Prod­uct field. This cre­ates a nest­ed struc­ture where prod­ucts appear under their respec­tive categories.

For the val­ues sec­tion, we need to add the Rev­enue field twice — once to show the raw sum and once to show the per­cent­age of par­ent. Sim­ply drag the Rev­enue field to the Val­ues area twice. By default, Excel will sum these val­ues, which is exact­ly what we want for this analysis.

Changing the Layout

By default, Excel dis­plays piv­ot tables in com­pact form, but for bet­ter read­abil­i­ty, I pre­fer the tab­u­lar lay­out. To change this:

1. Go to the Design tab under Piv­ot­Table Tools
2. Click on “Report Layout”
3. Select “Show in Tab­u­lar Form”

This adjust­ment sep­a­rates the Cat­e­go­ry and Prod­uct into dis­tinct columns, mak­ing your data more read­able and eas­i­er to ana­lyze at a glance.

Adding Percentage of Parent Row

Now comes the key part — trans­form­ing one of our rev­enue columns to show per­cent­age of par­ent row. This cal­cu­la­tion will show how each prod­uct con­tributes pro­por­tion­al­ly to its cat­e­go­ry total, and how each cat­e­go­ry con­tributes to the grand total. Fol­low these steps:

1. Right-click on any cell with­in the sec­ond Sum of Rev­enue column
2. Select “Show Val­ues As” from the con­text menu
3. Choose “% of Par­ent Row Total”

This sim­ple change trans­forms the raw num­bers into per­cent­ages, giv­ing you imme­di­ate insight into the pro­por­tion­al con­tri­bu­tion of each item. For instance, in my exam­ple, you can now see that apples rep­re­sent approx­i­mate­ly 35% of all fruit sales, while the fruits cat­e­go­ry as a whole rep­re­sents about 41% of total sales across all categories.

Understanding the Results

After apply­ing the per­cent­age of par­ent row cal­cu­la­tion, your piv­ot table auto­mat­i­cal­ly adjusts to show mean­ing­ful pro­por­tions at every level:

• Indi­vid­ual prod­ucts show their per­cent­age con­tri­bu­tion to their imme­di­ate category
• Cat­e­go­ry subto­tals show their per­cent­age con­tri­bu­tion to the grand total
• The grand total always equals 100%

In my demon­stra­tion, this clear­ly showed that sweets account­ed for approx­i­mate­ly 40% of total sales, veg­eta­bles for about 18%, and fruits for approx­i­mate­ly 41%. With­in each cat­e­go­ry, you can sim­i­lar­ly see the pro­por­tion­al con­tri­bu­tion of each product.

Finalizing Your Pivot Table

To make your piv­ot table more under­stand­able, it’s impor­tant to rename the col­umn head­ers to accu­rate­ly reflect what each col­umn rep­re­sents. In our case:

1. Change the head­ing of the first sum col­umn to sim­ply read “Sum”
2. Rename the sec­ond col­umn to “Per­cent­age of Parent”

These descrip­tive head­ers ensure that any­one view­ing your piv­ot table will imme­di­ate­ly under­stand what the num­bers rep­re­sent with­out need­ing addi­tion­al explanation.

With these adjust­ments com­plete, you now have a com­pre­hen­sive piv­ot table that not only shows the raw rev­enue fig­ures but also clear­ly illus­trates the pro­por­tion­al rela­tion­ships between cat­e­gories and their con­stituent prod­ucts. This dual-view approach pro­vides both absolute and rel­a­tive per­spec­tives on your data, enabling more nuanced analy­sis and decision-making.

This tech­nique is par­tic­u­lar­ly valu­able when ana­lyz­ing sales per­for­mance, bud­get allo­ca­tions, or any hier­ar­chi­cal data where under­stand­ing pro­por­tion­al rela­tion­ships is impor­tant. By visu­al­iz­ing both raw num­bers and per­cent­ages simul­ta­ne­ous­ly, you gain deep­er insights into your data struc­ture and can more effec­tive­ly com­mu­ni­cate those insights to others.