Pivot Tables are used for summarizing large datasets in Excel. While displaying the sum of values is standard, calculating the percentage of the grand total is crucial to show the contribution of each item in the list. In this article, we will guide you through two primary methods to calculate the percentage of grand total in Excel Pivot Table. We will use the Field Settings tool and the Show Values As shortcut for quick calculation.
To calculate the percentage of grand total in Excel Pivot Table, here is one simple solution by using the Show Value As tool.
➤ Right-click on any cell within the Sum of Revenue Amount column.
➤ Hover the mouse over Show Values As.
➤ From the extended submenu, select % of Grand Total to get the percentage of grand total in the Pivot Table.
Using Field Setting Tool to Calculate Percentage of Grand Total
Using Field Settings, you will gain complete control over how values are summarized and displayed. This is one of the simplest ways to convert the total values into a percentage of the Grand Total.
Suppose we have a sample dataset containing Date, Airline, Route Region, and Revenue Amount. Here, we will create a Pivot Table with this data and then use the Field Settings feature to calculate the percentage of grand total.
➤ Select your dataset.
➤ Go to the Insert tab and click PivotTable.
➤ In the PivotTable from table or range dialog box, ensure the correct range is selected.
➤ Select New Worksheet to place the Pivot Table on a clean sheet.
➤ Click OK.
➤ In the PivotTable Fields pane, drag the Years (Date) field and the Days (Date) field into the Rows area.
➤ Drag the Revenue Amount field into the Values area.
The Values field will now be labeled Sum of Revenue Amount. As a result, the Pivot Table shows the total revenue for each day, grouped by year, with the final grand total value at the bottom.
Now, we will use the Field Settings tool to calculate the percentage for the revenue amount.
➤ Select any cell within the Sum of Revenue Amount column in your Pivot Table.
➤ Go to the PivotTable Analyze tab.
➤ In the Active Field group, click Field Settings.
The Value Field Settings dialog box will open.
➤ Click on the Show Values As tab.
➤ From the Show values as dropdown menu, select % of Grand Total.
➤ Click OK.
Finally, the figures in the Pivot Table will immediately change to percentages. You can now see the revenue contribution of each day to the overall grand total of 100%.
Using Show Value As Feature to Calculate Percentage of Grand Total
If you prefer a quicker method, you can use the Show Value As feature to calculate the percentage of grand total. First, we will use this method to calculate the percentage of grand total and then show the values in percentage for the parent row and the row total.
➤ Right-click on any value cell within the Sum of Revenue Amount column.
➤ Hover the mouse over Show Values As and select % of Grand Total.
The Pivot Table values will be updated to show their respective percentages of the grand total.
In some cases, you might have multiple fields in the Rows area, and you need to calculate the percentage contribution of a sub-item relative to its parent group rather than the entire grand total. This is often called % of Parent Row Total.
To explain this, we will modify the Pivot Table structure.
➤ In the PivotTable Fields pane, drag the Route Region field below the Years (Date) field in the Rows area.
The Pivot Table now shows the revenue broken down by year and then by route region (Domestic/International).
➤ Select a cell from the Sum of Revenue Amount column and right-click to open the context menu.
➤ Click Show Values As and select % of Parent Row Total.
Finally, we will get the revenue amount of each row item (e.g., Domestic) as a percentage of its immediate parent row total (e.g., 2023).
Here, we will add another row label to calculate the percentage from the parent row only.
➤ Drag the Airline field below the Route Region in the Rows area.
Thus, we will get a Pivot Table just like below.
➤ Right-click on any value cell in the Sum of Revenue Amount column.
➤ Click Show Values As > % of Row Total.
Since we have multiple fields, the Show Values As (Sum of Revenue Amount) dialog box will appear, asking for the Base Field.
➤ From the Base_Field dropdown, select Route Region and click OK.
This means the percentage of each airline’s revenue will be calculated relative to its Route Region (the parent of Airline).
The Pivot Table will show the percentage of each airline’s revenue contribution within its specific Route Region. For example, for “2023 > Domestic“, the sum of Delta and JetBlue revenue percentages is 100%.
Frequently Asked Questions
Why is my % of Grand Total not showing correctly?
This usually happens if you have filters applied or if your Pivot Table is using Count instead of Sum.
Can I use % of Grand Total in a Calculated Field?
No. Calculated Fields don’t directly support % of Grand Total, but you can duplicate the value field and apply the setting.
Why are my percentage calculation options greyed out?
This usually happens if the field in the Values area, or if the field is not a numerical field. Ensure your data column only contains numbers before creating the Pivot Table.
Concluding Words
Above, we have explored several ways to calculate the percentage of grand total in Excel Pivot Table. Whether you choose the Show Values As shortcut or the Value Field Settings tool, both methods allow you to calculate data to convert it into percentages. Utilizing the % of Parent Row Total feature provides the percentage within nested groups. If you have any questions, please don’t hesitate to share them in the comments section below.
