Excel’s Pivot Table Percentage is a unique feature that lets users analyze data relative to the whole by displaying values as percentages of the total. Understanding the relative contribution of each data point to the entire dataset depends on this functionality. Users can quickly convert absolute values into relevant percentages by selecting “% of Column/Row Total” or “% of Grand Total” when using the “Show Values As” option. This tool helps analysts better grasp the relative importance of each data point by enabling them to spot patterns, trends, and outliers within the dataset. With its clear and graphic depiction of the distribution of values within a dataset, Pivot Table Percentage is an effective tool for data-driven decision-making. With pivot tables, your data may be easily shown as percentages, giving you a better idea of how different categories compare.
SYNTAX & ARGUMENTS
To evaluate and summarize data using percentages in pivot tables, specific syntax and arguments are used based on the need:
- % of Grand Total:=ShowValuesAs (“Field_Name”, “% of Grand Total”)
- % of Column Total:=ShowValuesAs(“Field_Name”, “% of Column Total”)
- % of Row Total:=ShowValuesAs(“Field_Name”, “% of Row Total”)
- Running Total:=ShowValuesAs (“Field_Name”, “Running Total In”, “Column/Row”)
- % of Parent Row Total:=ShowValuesAs(“Field_Name”, “% of Parent Row Total”)
- Custom % Calculation:=ShowValuesAs(“Field_Name”, “Custom Calculation”, “Formula”)
- Field Name: The field whose values you wish to display as percentages.
- Type of Calculation: The type of percentage computation (e.g., “% of Grand Total,” “% of Column Total”).
- Running_total_In: Specifies if the running total is calculated in rows or columns.
- Custom_calculation_Type: Specifies the type of computation for custom calculations.
- Formula: The custom formula used for a custom computation.
To perform any of the calculations above, follow these steps.
Step 1: Click on anywhere in your pivot table and navigate to PivotTable Analyze tab and then click on field settings

Step 2: The previous step should pop out a Value Field settings box. In the Value field setting box, click on Show Value as

Step 3: Proceed to the dropdown and select the relevant calculation

USAGE NOTES
Excel pivot tables with percentages can enhance data display and analysis. Here are some guidelines for using percentages in pivot tables:
- Understanding the Grand Total Percentage: Express each cell value as a percentage of the total using “% of Grand Total.” This is great for understanding the relative importance of each component.
- Comparing Columns with Percentage of Column Total: Analyze how each cell value in a column contributes to the column’s total using “% of Column Total.” This is beneficial for identifying trends within specific categories.
- Using % of Row Total to Analyze Rows: Evaluate the importance of each cell value within its corresponding row total using “% of Row Total.” This helps in understanding the distribution of elements within a row.
- Running Total for Cumulative Insights: Use “Running Total In” to compute cumulative percentages in rows or columns, which is useful for tracking cumulative changes over time.
- Hierarchical Analysis with Percentage of Parent Row Total: Determine how each cell value contributes to the total of its immediate parent row using “% of Parent Row Total.” This is helpful for nested data analysis.
- Flexible Custom Percentage Calculations: Use the “Custom Calculation” option to create custom percentage calculations. This allows for complex computations in unique scenarios.
- Formatting for Clarity: Format percentage numbers correctly for visual clarity. Use PivotTable tools to format cells as percentages, adjusting decimal places as needed.
- Dynamic Interactivity: Experiment with different percentage calculations to see how the Pivot Table changes. Modify calculations based on the insights you want to derive.
- Clearing and Resetting Percentages: When changing the focus of analysis, clear the current percentage settings. To reset, right-click on the cell and select “No Calculation” under “Show Values As.”
- Trying New Things to Gain Expertise: Practical experimentation is the most effective way to understand the significance of percentages.
USES OF PIVOT TABLE PERCENTAGES
Pivot table percentages are useful for structured data analysis and presentation. Here are common applications:
- Comparative Analysis: Compare contributions of items or categories to the total.
- Market Share Analysis: Determine each item’s total sales or revenue percentage share.
- Budget Performance Analysis: Compare actual and budgeted amounts or percentages.
- Distribution Analysis: Understand how data is distributed among groups.
- Contribution Analysis: Determine the proportion each component contributes to the total.
- Time Series Analysis: Represent values over time relative to a baseline period.
- Risk Assessment: Calculate each risk’s percentage to the overall risk.
- Conversion Rates: Determine conversion rates (e.g., sales or sign-ups) by dividing total opportunities by conversions.
- Employee Performance: Calculate the percentage of goals completed by each employee.
- Survey Analysis: Calculate the percentage distribution of responses.
- Portfolio Analysis: Express each asset or investment as a percentage of the total value.
- Quality Control: Determine the percentage of total units produced with errors or defects.
PIVOT TABLE PERCENTAGE EXAMPLES
Sales Contribution Analysis
Suppose you want to examine how each product category contributes to overall sales in a dataset.
- Product Categories: Cake, Soda, Jollof Rice, Meat pie, Chicken pie, Sausage, Ice cream, Water, Suya
- Sales: $500, $900, $1200, $850, $1000, $600, $2000, $700, $4000
Steps to Include Sales Percentages column
Step 1: Create a pivot table with “Product Category” in rows and “Sales” in values.
Step 2: Add a calculated field named “Sales Percentages” with the Formula =Sales / SUM(Sales)
. To add the calculated field, go to PivotTable Analyze tab, click on Fields, Items & Sets, and choose Calculated Field. In the Insert Calculated Field dialog box, type your desired name for the calculated field, which in this case is “Sales Percentages”. In the Formula area, create your calculation using the available fields.
Step 3: Format the “Sales Percentages” field as a percentage.

The % of Grand Total can also be derived directly by using the Value field Settings. You’ll however have only 2 columns: Row label and Sum of Sales Percentages. Let’s see an illustration below:

Budget vs. Actual Expenses Analysis
Analyze the percentage variance between budgeted and actual expenses for each department.
Preliminary Information
There are three departments in our data set with the information below:
- Departments: HR, Finance, Marketing
- Budgeted Expenses: $50,000, $60,000, $40,000
- Actual Expenses: $48,000, $55,000, $38,000
Steps to Include % Variance Analysis Column
Step 1: Create a pivot table with “Departments” in rows.
Step 2: Add “Budgeted Expenses” and “Actual Expenses” in values.
Step 3: Add a calculated field named “Expense Variance %” with the formula= (Actual Expenses – Budgeted Expenses) / Budgeted Expenses.
Step 4: Format the “Expense Variance %” field as a percentage
Please see the visual representations of the steps in our video below:

Conversion Rate Analysis
Analyze the conversion rate (percentage of visits resulting in sign-ups) from website visits and registration data. Let’s assume that there are 10,000 visits and 1200 signups, we would follow the steps below to perform a conversion rate analysis using Pivot table percentages.
Step 1: Make “Visits” and “Sign-ups” values in a pivot table.
Step 2: Create a calculated field called “Conversion Rate %” with the formula =Sign-ups / Visits
.
Step 3: Format the “Conversion Rate%” field as a percentage.
