Free Practice WorkbookExcel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the Excel Formula to Calculate Percentage of Grand Total. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, expenses, attendance, or anything that can be quantified.
Key Takeaways
- Simplifies Data Analysis: Displaying values as a percentage of the grand total helps you understand the contribution of each data point relative to the overall total, making data analysis more insightful.
- Quick and Easy Setup: You can quickly calculate and display percentages by modifying the value field settings in a Pivot Table, eliminating the need for manual calculations.
- Dynamic Updates: Percentages automatically update when the data source changes or filters are applied, ensuring the analysis remains accurate and up-to-date.
- Supports Comparative Insights: This feature allows you to easily compare different categories or items to see which ones have the highest or lowest contribution to the grand total.
- Enhances Visual Reporting: Adding percentages to a Pivot Table improves clarity in reports and presentations, making it easier for stakeholders to grasp the significance of the data.
Table of Contents
How to Show The Percent of Grand Total With Excel Pivot Tables
STEP 1: Select any cell in the data table.
STEP 2: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table.
STEP 3: Insert a new Pivot In the Create PivotTable dialog box, select the table range and New Worksheet, and then click OK.
STEP 4: In the ROWS section put in the Sales Month field, in the COLUMNS put in the Financial Year field and in the VALUES area you need to put in the Sales field twice, I explain why below:
The Pivot Table will look like this:
STEP 5: Click the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings
STEP 6: Select the Show Values As tab and from the drop-down choose % of Grand Total.
Also, change the Custom Name into Percent of Grand Total to make it more presentable. Click OK.
An Excel formula to calculate percentage of grand total will be added!
STEP 7: Notice that the Percent of Grand Total data is in a decimal format and it is hard to read it:
To format the Percent of Grand Total column, click the second Sales field’s (Percent of Grand Total) drop down and choose Value Field Settings.
The goal here is for us to transform numbers from a decimal format (i.e. 0.23), into a percentage format that is more readable (i.e. 23%).
STEP 8: Click the Number Format button.
STEP 9: Inside the Format Cells dialog box, make your formatting changes within here and press OK twice.
In this example, we used the Percentage category to make our Percent of Grand Total numbers become more readable.
You now have your Pivot Table, showing the Excel Pivot Table percentage of totalfor the sales data of years 2012, 2013, and 2014.
All of the sales numbers are now represented as a Percentage of the Grand Total of $32,064,332.00, which you can see on the lower right corner is represented as 100% in totality:
This completes our tutorial on the Excel formula to calculate percentage of grand total!
You can even use this option to show the sales by category as a percentage of the grand total.
Troubleshooting Common Issues
Solutions for Frequent Pivot Table Problems
When working with PivotTables, there are certain issues that crop up time and again. You might face problems like data not updating, incorrect totals, or finding that new data isn’t being included in the PivotTable after refreshing. But not to worry! These can be quickly resolved.
To ensure data is current, always click “Refresh” after adding new data—just go to the “Data” tab and select “Refresh All” from the “Connections” group. If totals seem off-kilter, double-check that all the necessary cells are included in the data range. It’s also smart to verify that there are no blank rows within the data as this might interrupt the data sequence, leading to unexpected results.
For when new rows or columns of data don’t seem to appear, it might be time to adjust the PivotTable’s data source. Head to the “Change Data Source” button under the “Options” tab in the PivotTable Tools. There, modify the data range to encompass the new data areas.
For those confusing moments when values show up as a ‘Count’ rather than a ‘Sum’, it’s likely because of the non-numeric data sneaking in. Just ensure all the data in that field are numbers or change the field setting from “Count” to “Sum”.
Remember to check if date values haven’t been grouped if they seem disorderly; right-click the date field and opt for “Group” to tidy things up. And if ever you encounter error values or blank cells, it’s a clue to revisit the source data to correct or fill in those gaps.
Overcoming Limitations When Displaying Percentages
Displaying percentages in PivotTables can come with its own set of challenges, but fortunately, you can easily overcome these with a few tweaks. If you aim to show values as a percentage of the grand total, you may notice that the default settings don’t always meet your needs.
One common stumbling block is getting the number format to play nice with percentage calculations. To keep your percentages free of unnecessary decimal clutter, head to the “Value Field Settings” and click the “Number Format” button. Then, choose the ‘Percentage’ category and set the decimal places to zero. This will give you a clean, easy-to-read percentage.
Another tricky aspect is when you want to show percentages for subtotal levels within the data. Here’s where the “Show Values As” option becomes your best friend. If you’ve got a parent field in the columns or rows area, use the “% of Parent Total” option to display the percentage relative to subtotals rather than the grand total.
Occasionally, you might want to display a percentage breakdown side by side with the count. To get this side-by-side view, add your value field to the values area twice – once set to “count” and the second time set to “show value as a percentage of the grand total.” This way, you’ll get a comprehensive view demonstrating both the count and the proportion it represents out of the whole.
Remember, being adept at customizing number formatting and using the many “Show Values As” options is the key to getting your PivotTable to accurately reflect the data story you’re trying to tell.
Frequently Asked Questions
How do I display values as a percentage of the grand total in a Pivot Table?
To show values as a percentage of the grand total, right-click on the value field in your Pivot Table, then select Show Values As > % of Grand Total. This will automatically calculate and display each value as a percentage of the overall total.
Can I apply the “Percent of Grand Total” calculation to multiple fields in the Pivot Table?
Yes, you can apply this calculation to multiple fields. Add the fields you want to analyze to the Values area, then repeat the process of selecting Show Values As > % of Grand Total for each field.
Does filtering the data affect the percentages shown?
Yes, applying filters will update the percentages dynamically, recalculating them based on the visible data only. This ensures the percentages reflect the filtered dataset rather than the entire data source.
Can I display both the actual values and the percentages in the same Pivot Table?
Yes, you can display both. Add the same field to the Values area twice—one will display the actual values, and the other can be formatted to show as a percentage of the grand total.
Is it possible to calculate percentages of a specific row or column total instead of the grand total?
Yes, you can. Instead of selecting % of Grand Total, you can choose % of Row Total or % of Column Total under Show Values As to calculate percentages based on specific rows or columns.
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.
30 Days - Full Access 
