How to Show Percentages in an Excel PivotTable

Key Takeaways

• Excel PivotTables can display values as percentages using the Show Values As feature, with options including % of Column Total, % of Grand Total and % of Parent Row.
• You can show both raw numbers and percentages side by side by adding the same value field to your PivotTable twice.
• PivotTable percentages are a powerful tool for analyzing sales performance, budget allocation, customer profitability and other business metrics.

Let's suppose you have sales by state, and each state belongs to a sales region. What percentage of your sales come from each region? Learning how to show percentages in an Excel PivotTable is one of the most practical skills for business reporting and data analysis. In this article, you'll walk through how to set up a PivotTable, display values as a PivotTable percentage of column total and explore additional percentage options like % of Grand Total and % of Parent Row.

How to Set Up a PivotTable and Show Percentages

Before you begin, make sure you have the following in place:

• Your data is organized in a table format with clearly labeled column headers
• Each column contains a single data type (text, numbers, dates)
• You're using Excel 2016 or later for full access to all percentage options

Create Your PivotTable

1. Align your data in a table format, as shown here. Type "State," "Region" and "Sales" in row A.
2. Drag your mouse across the entire table to select it.
3. On the Ribbon, click on Insert, then click on PivotTable.
4. In the Create PivotTable dialog box, either accept the suggested range for the new table or select a new location. Then click OK.
5. Your column names from step 1 now appear as fields in the PivotTable Field List on the right-hand side of the screen. Drag "Region" to the box marked Row Labels. Then drag "State" to the box marked Row Labels, placing it just below "Region."
6. Drag "Sales" to the box marked Values.

Your new PivotTable should look like this:

Change the Format to Percentages

By default, the column "Sum of Sales" shows the sales dollars for each state, subtotaled in regions. Display the results as PivotTable percentages by following these steps:

1) Click on the triangle to the right of "Sum of Sales" in the Values box. A menu appears. Select Value Field Settings to launch the dialog box.

2) Click on the tab labeled Show Values As.

3) In the dropdown listbox, select % of Column Total then click OK.

Your modified table should look like this:

Understanding Your PivotTable Percentage Options

The Show Values As menu offers several percentage calculations beyond % of Column Total. Each option answers a different analytical question, so choosing the right one depends on what you're trying to measure.

Here's a quick reference for when to use each option:

• Use % of Grand Total when you want to see how each cell contributes to the entire PivotTable
• Use % of Column Total when comparing items within a single column (e.g., which states contribute most to overall sales)
• Use % of Row Total when comparing values across columns for the same row
• Use % of Parent Row when you have grouped data and want to see each item's share within its parent group
• Use % of Parent Column when analyzing hierarchical data from top to bottom
• Use Running % Total when tracking cumulative contribution across a sorted list

% of Grand Total vs. % of Column Total

These two options are the most commonly used, and the difference between them matters. % of Grand Total calculates each value as a percentage of the entire PivotTable's total. If your PivotTable shows sales for three regions across 12 states, every cell is measured against the combined sales of all 12 states.

% of Column Total, on the other hand, calculates each value as a percentage of its column's sum only. Using the same sales-by-region example, each state's percentage reflects its share within the single "Sum of Sales" column.

To switch between them, right-click any value cell in your PivotTable, select Show Values As and choose the option you need from the dropdown. The PivotTable updates instantly.

% of Parent Row and % of Parent Column

When your PivotTable has hierarchical fields - such as Region and State grouped together - % of Parent Row reveals how much each state contributes within its own region. This is especially useful for spotting which states drive the most revenue inside a specific territory.

To apply this option, right-click a value cell, select Show Values As and choose "% of Parent Row Total." Select the parent field (Region, in this example) when prompted. Each state's percentage will now reflect its share of its region's total rather than the overall total.

% of Parent Column works the same way but applies to nested column fields. Use it when your columns contain grouped categories and you want to see each sub-category's contribution within its parent.

How to Show Both Values and Percentages Side by Side

You can display raw numbers and percentages in the same PivotTable by adding the same field to the Values area twice. Here's how:

1. In the PivotTable Field List, drag the "Sales" field into the Values box a second time. Your PivotTable now shows two columns: "Sum of Sales" and "Sum of Sales2."
2. Right-click any cell in the second "Sum of Sales2" column and select Value Field Settings.
3. Click the Show Values As tab, then choose your preferred percentage option (such as % of Column Total or % of Grand Total).
4. Click OK. Rename the column header to something descriptive like "% of Total" by typing directly into the cell.

Your PivotTable now shows the actual dollar amounts alongside the corresponding percentages, making it easy to present both context and proportion in a single view. You can further enhance readability by applying conditional formatting to highlight cells that exceed or fall below target thresholds.

Practical Business Uses for PivotTable Percentages

Now that you've mastered the technique, you can use PivotTable percentages as a powerful tool to analyze which combination of states, products, employees or customers have the most impact on your revenue, expenses and profitability. Here are specific scenarios where percentage analysis delivers immediate insight:

• Sales performance by region: Identify which territories contribute the highest share of total revenue and which are underperforming relative to their size
• Budget variance analysis: Compare each department's actual spending as a percentage of the total budget to spot overruns quickly
• Customer profitability: Determine which customer segments account for the largest share of profit - and which ones cost more to serve than they return
• Expense ratio tracking: Show each expense category as a percentage of revenue to monitor operational efficiency over time
• Employee productivity: Analyze output or sales per team member as a percentage of the group total to identify top performers and coaching opportunities

With a little extra effort, you can apply these percentage views when you have several different fields that overlap, such as product by state by sales rep, and more complex metrics, such as gross profit. Because the PivotTable sorts and groups the data automatically, you can browse to find the combination with the highest profit percentage, the lowest expense ratio or the most favorable increase over last year.

Building confidence with PivotTable percentages is one of many Excel skills that pay dividends across every business function. The more fluent you become with these tools, the faster you can turn raw data into decisions.