{"id":4511,"date":"2023-10-10T11:45:00","date_gmt":"2023-10-10T15:45:00","guid":{"rendered":"https:\/\/exceltheatre.com\/blog\/?p=4511"},"modified":"2025-08-06T19:12:49","modified_gmt":"2025-08-06T23:12:49","slug":"video-show-percent-subtotal-excel-pivot-table","status":"publish","type":"post","link":"https:\/\/exceltheatre.com\/blog\/archives\/2023\/10\/10\/video-show-percent-subtotal-excel-pivot-table\/","title":{"rendered":"Video &#8211; Show Percent of Subtotal &#8211; Excel Pivot Table"},"content":{"rendered":"<p>In an Excel pivot table, you can use built-in custom calculations, for a different view of the data.<\/p>\n<p>For example, in this video I set up the pivot table to show what % of monthly sales were Binders, and what % for each colour \u2013 red, blue, and black.<\/p>\n<p><!--more--><\/p>\n<p><iframe loading=\"lazy\" title=\"Show Percent of Subtotal in Excel Pivot Table\" width=\"840\" height=\"630\" src=\"https:\/\/www.youtube.com\/embed\/NZaMVirDXrw?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>You can get the sample file that I used for this video on <a href=\"https:\/\/contextures.com\/xlpivot10.html\" target=\"_blank\" rel=\"noopener\">the Pivot Table Show Values As page<\/a>, on my Contextures site.<\/p>\n<h3>Example: % of Parent Row Total<\/h3>\n<p>In this example, the pivot table has:<\/p>\n<ul>\n<li>Item and Colour in the Row area<\/li>\n<li>Month in the Column area<\/li>\n<li>Sum of Units sold, in the Values area<\/li>\n<\/ul>\n<p>Follow the steps below, to show the additional sales details:<\/p>\n<ul>\n<li>the % for each colour&#8217;s sales \u2013 Black, Blue and Red<\/li>\n<li>compared to the item&#8217;s total sales<\/li>\n<li>in each month \u2013 Jan and Feb<\/li>\n<\/ul>\n<figure style=\"width: 377px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2023\/10\/customcalculationparentrow0203.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"pivot table with monthly sales per product colour\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2023\/10\/customcalculationparentrow0203_thumb.png\" alt=\"pivot table with monthly sales per product colour\" width=\"377\" height=\"342\" border=\"0\" \/><\/a><figcaption class=\"wp-caption-text\"><em>pivot table with monthly sales per product colour<\/em><\/figcaption><\/figure>\n<h3>Show % of Item Sales<\/h3>\n<p>In the pivot table screen shot below, I&#8217;ve added another copy of the Units field to the values area.<\/p>\n<p>For the new field, I followed the steps below, to change the calculation settings<\/p>\n<ul>\n<li>Right-click one of the Units value cells<\/li>\n<li>In the pop-up menu, click <i>Show Values As <\/i><\/li>\n<li>In the next pop-up menu, click <i>% of Parent Row Total<\/i><\/li>\n<\/ul>\n<p>In the pivot table, the second Units field changed, to show:<\/p>\n<ul>\n<li>the % for each colour&#8217;s sales<\/li>\n<li>compared to the item&#8217;s total<\/li>\n<li>in each month.<b> <\/b><\/li>\n<\/ul>\n<p>For example,<\/p>\n<ul>\n<li>195 Binders were sold in January<\/li>\n<li>51% (100 units) of those January Binders were Black colour<\/li>\n<\/ul>\n<figure style=\"width: 337px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2023\/10\/customcalculationparentrow02.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"Show Percent of Subtotal in Excel Pivot Table\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2023\/10\/customcalculationparentrow02_thumb.png\" alt=\"Show Percent of Subtotal in Excel Pivot Table\" width=\"337\" height=\"230\" border=\"0\" \/><\/a><figcaption class=\"wp-caption-text\"><em>Show Percent of Subtotal in Excel Pivot Table<\/em><\/figcaption><\/figure>\n<h3>More Pivot Table Info<\/h3>\n<p>For more Pivot Table tips, videos and examples, visit the following links:<\/p>\n<p><a href=\"https:\/\/www.contextures.com\/exceldategrouping.html\" target=\"_blank\" rel=\"noopener\">Ungroup dates in pivot table<\/a><\/p>\n<p><a href=\"https:\/\/www.contextures.com\/xlPivot10.html\" target=\"_blank\" rel=\"noopener\">Show Percent of Subtotal in Pivot Table<\/a><\/p>\n<p><a href=\"https:\/\/www.pivot-table.com\/2017\/08\/01\/pivot-table-show-values-as-of-parent-total\/\" target=\"_blank\" rel=\"noopener\">Pivot Table Show Values As % of Parent Total<\/a><\/p>\n<p><a href=\"https:\/\/www.pivot-table.com\/2013\/06\/26\/show-percent-of-subtotal-in-pivot-table\/\" target=\"_blank\" rel=\"noopener\">Show Percent Of Subtotal In Pivot Table<\/a><\/p>\n<p>______________________<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In an Excel pivot table, you can use built-in custom calculations, for a different view of the data. For example, in this video I set up the pivot table to show what % of monthly sales were Binders, and what % for each colour \u2013 red, blue, and black.<\/p>\n","protected":false},"author":2,"featured_media":4510,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_kadence_starter_templates_imported_post":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-4511","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-videos"],"_links":{"self":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4511","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/comments?post=4511"}],"version-history":[{"count":4,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4511\/revisions"}],"predecessor-version":[{"id":4675,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4511\/revisions\/4675"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media\/4510"}],"wp:attachment":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media?parent=4511"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/categories?post=4511"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/tags?post=4511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}