To show percentages in an Excel pivot table, drag the numeric field into the Values area, then right-click it, choose Show Values As, and select the percentage type you need (e.g., % of Grand Total). For example, if you have sales by region, this instantly converts raw sums into the share each region contributes to the overall total.
Why Percentages Matter in Pivot Tables
Pivot tables are Excel’s fastest way to summarize large datasets, but raw counts and sums only tell part of the story. A regional sales figure of $50,000 is hard to interpret without knowing the company total is $500,000. Percentages normalize those numbers, letting you compare categories of very different sizes at a glance. They also reveal patterns that absolute values hide—for instance, a small product category might have a tiny total but a surprisingly high growth percentage.
Excel’s Show Values As menu is the built-in tool for this. It appears when you right-click any value field in the pivot table. The menu offers several percentage calculations, each answering a different question. Understanding which one to use is the key to meaningful analysis.
How to Add Percentages: Step-by-Step
Here’s the core process in Excel for Microsoft 365 on Windows (Mac steps are nearly identical):
- Click anywhere inside your pivot table to activate the PivotTable Analyze tab.
- Drag the numeric field (e.g., Sales) into the Values area. It appears as “Sum of Sales.”
- Right-click the value field in the pivot table (or in the Values area of the Field List).
- Choose Show Values As from the context menu.
- Select the percentage type you need (see the next section).
- If you want to keep the original numbers, drag the same field into Values a second time and apply the percentage to only that copy.
On a Mac, the right-click menu is the same. In Excel for the web, right-click the value and you’ll see Show Values As as well, though some advanced options (like % of Parent Row Total) may be limited.
Types of Percentages in Show Values As
Excel offers several percentage calculations, each with a distinct meaning. Choose based on the question you’re asking:
- % of Grand Total – Shows each value as a percentage of the overall total. Best for a single dimension (e.g., each product’s share of all sales).
- % of Column Total – Shows each value as a percentage of its column’s total. Useful when columns represent categories and you want to compare within each.
- % of Row Total – Shows each value as a percentage of its row’s total. Ideal for comparing across columns within each row.
- % of Parent Row Total – In a two-level row layout, this calculates the percentage of the immediate parent row. For example, if you have Region > Product, this shows each product’s share within its region, not the whole company.
- % of Parent Column Total – Same idea but for columns.
- % of Parent Total – Shows percentage of the selected parent level (you choose the base field).
- % Difference From – Shows percentage change compared to a base item (e.g., this month vs. last month).
For most beginners, % of Grand Total is the default choice. It answers “What share of the whole does this row represent?”
Common Problems and Fixes
Several issues trip up users when working with percentages:
- Percentages appear as decimals (0.25 instead of 25%). This happens when the cell format is General. Select the value cells, press Ctrl+Shift+% (Windows) or Cmd+Shift+% (Mac) to apply percentage format, or right-click > Number Format > Percentage.
- Values show as counts instead of sums. If your data contains text or blanks, Excel may default to Count. Right-click the field > Summarize Values By > Sum.
- Percentages don’t update when you filter. By default, % of Grand Total recalculates based on the visible data. If you want percentages based on the unfiltered dataset, you need a different approach (e.g., a calculated field or a separate pivot table).
- You see “100.00%” for every row. This typically happens when you apply % of Row Total to a layout where each row has only one value. Check your row and column fields.
- Percentages are off when using multiple value fields. If you have both Sum of Sales and Count of Orders, the percentage applies to each field separately. Make sure you’re looking at the right field.
Percentages vs. Calculated Fields
Some users try to create percentages by adding a calculated field with a formula like =Sales/Total. That approach rarely works because the calculated field operates on each row of source data, not on the pivot table’s aggregated totals. The Show Values As method is always the correct choice for percentages of totals.
However, if you need a percentage that isn’t in the menu—like a ratio of two different fields (e.g., profit margin = Profit/Sales)—you should use a calculated field. To do that:
- Click in the pivot table and go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
- Name the field (e.g., “Margin”).
- Enter a formula like
=Profit/Sales(assuming those are field names). - Click Add, then OK.
The result appears as a new field in the Values area. You may need to format it as a percentage manually.
How to Choose the Right Percentage Type
Selecting the wrong percentage type leads to misleading analysis. Here’s a quick decision guide:
- If you have one dimension (e.g., products only) → % of Grand Total.
- If you have two dimensions (e.g., products by region) and want to see each product’s share within each region → % of Parent Row Total (with Region as the parent).
- If you want to compare regions side-by-side, where each column’s total is 100% → % of Column Total.
- If you want to see how each row compares to its own row total (e.g., product mix within a single row) → % of Row Total.
- If you want to see change over time → % Difference From with the previous item as the base.
Test your choice by checking if the percentages in each row/column sum to 100% where expected. If they don’t, you may have the wrong type.
Formatting and Display Tips
Once you have percentages, you can polish the presentation:
- Use Ctrl+Shift+% to format cells as percentages with two decimal places. To change decimals, right-click > Number Format > Percentage and adjust.
- To show percentages alongside raw numbers, add the field twice: once as Sum, once as % of Grand Total. Then rename the headers (e.g., “Sales” and “% of Total”).
- To sort by percentage, right-click any value in the column and choose Sort > Largest to Smallest.
- To add a percentage column to a pivot chart, the chart will automatically use the percentage values if you’ve changed the field.
Remember that the Show Values As setting is per field, not per pivot table. If you have multiple value fields, each can have its own calculation.
Frequently Asked Questions
Why does my pivot table show “Count of Sales” instead of “Sum of Sales”?
This happens when the source data column contains text or blank cells. Excel defaults to Count for non-numeric data. Check your source data for blanks or text entries, fix them, and then right-click the field > Summarize Values By > Sum.
How do I show percentages that add up to 100%?
Use % of Grand Total when you have a single row field. The percentages will sum to 100% across all rows. If you have multiple row fields, they may not sum to 100% because each level calculates its own total.
Can I show percentages without changing the original data?
Yes. The Show Values As feature only changes how the field is displayed in the pivot table. Your source data remains untouched. You can also copy the pivot table and change the copy to percentages while keeping the original as numbers.
What’s the difference between % of Grand Total and % of Parent Row Total?
% of Grand Total shows each cell as a share of the entire pivot table’s total. % of Parent Row Total shows each cell as a share of its immediate parent row (e.g., if you have Region > Product, each product is a percentage of its region, not the whole company). Use the latter when you want to compare within groups.
Why do my percentages show as 0% after I apply the format?
This usually means the underlying value is very small relative to the total, so it rounds to 0% with the default decimal places. Increase the decimal places by right-clicking > Number Format > Percentage and setting more decimals.
Can I use percentages in a pivot table with multiple value fields like Sum and Average?
Yes. Each value field can have its own Show Values As setting. For example, you can have Sum of Sales shown as % of Grand Total and Average of Sales shown as % of Column Total. Right-click each field individually to set its calculation.
Related
- Excel How to Select Certain Cells: 6 Quick Ways
- Excel Transpose: How to Quickly Flip Data
- How to Rename a Cell in Excel: A Complete Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Tree Chart in Excel: Create and Format a TreemapIn Excel, a “tree chart” usually means a Treemap: a chart that displays hierarchical categories as nested rectangles…
- Excel Stacked Column Chart: Show Total LabelsTo show totals on an Excel stacked column chart, add a row or column of totals to the…
- Aggregate Excel Pivot Tables: Methods and ExamplesTo aggregate data in an Excel PivotTable, select a clean source range, choose Insert > PivotTable, and place…
- Excel Doughnut Chart With a Total in the Center ExplainedYes. In Excel for Microsoft 365 on Windows, create a doughnut chart, calculate the total in a worksheet…
- Excel Pivot Table: How to Hide Blank Rows and ValuesTo hide a blank category in an Excel PivotTable, open the filter arrow for its Row Labels or…
- Excel Pivot Table Field Name Not Valid? Fix ItThe Excel “PivotTable field name is not valid” error usually means the selected source range has a missing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.