Excel Pivot Table Percentages: How to Show Them

Charts & pivot tables · Updated November 22, 2026

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):

  1. Click anywhere inside your pivot table to activate the PivotTable Analyze tab.
  2. Drag the numeric field (e.g., Sales) into the Values area. It appears as “Sum of Sales.”
  3. Right-click the value field in the pivot table (or in the Values area of the Field List).
  4. Choose Show Values As from the context menu.
  5. Select the percentage type you need (see the next section).
  6. 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:

  1. Click in the pivot table and go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
  2. Name the field (e.g., “Margin”).
  3. Enter a formula like =Profit/Sales (assuming those are field names).
  4. 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

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.