Show Values as Percentage in PivotTable: Excel Guide

Updated 7 November 2026 · Standard formula, shown below

To show values as a percentage in an Excel PivotTable, right-click any value in the Values area, choose Show Values As, and then select the percentage option you need (e.g., % of Grand Total, % of Column Total, % of Row Total). This instantly converts raw numbers into percentages without changing your source data. Below we explain every option, when to use each, and how to fix common issues.

Why Use Show Values As Percentages?

PivotTables summarize data, but raw sums and counts are often hard to interpret. A percentage puts each number into context—what share of the total does this product, region, or month represent? This is essential for:

Comparing category contributions (e.g., which product drives the most revenue).
Spotting trends over time (e.g., monthly share of annual sales).
Simplifying reports for audiences who prefer percentages over absolute figures.

Excel’s “Show Values As” feature is built into PivotTables and does not alter your original data—it only changes how the values are displayed.

How to Apply a Percentage in a PivotTable

Here are the exact steps for Excel 2016, 2019, 2021, and Microsoft 365:

Click anywhere inside your PivotTable to activate the PivotTable Analyze (or Options) tab.
Right-click any numeric cell in the Values area (the body of the table).
Select Show Values As from the context menu.
Choose one of the percentage options (see next section).
If you want the numbers to display with a percent symbol, select the cells and press Ctrl+Shift+% or format them as Percentage via the Home tab.

If you prefer menus, go to PivotTable Analyze → Field Settings → Show Values As tab, then pick the calculation.

Understanding the Percentage Options

Excel offers several percentage calculations, each answering a different question. Here are the most common:

% of Grand Total: Each value is divided by the grand total of all values. Example: if total sales are $100,000 and Product A is $25,000, it shows 25%.
% of Column Total: Each value is divided by the total of its column. Useful when rows are categories and columns are time periods.
% of Row Total: Each value is divided by the total of its row. Useful when columns are categories and rows are time periods.
% of Parent Total: Shows the percentage relative to a parent item in a hierarchy (e.g., a region’s share of its country).
% of Parent Row/Column Total: Similar but relative to the row or column parent.
% of Running Total: Shows cumulative percentage as you go down or across. This is great for Pareto analysis.
% Difference From: Shows the percentage difference from a base item (e.g., previous year).

For most users, “% of Grand Total” is the default choice, but the others are powerful once you understand your data structure.

How to Choose the Right Percentage Option

Selecting the wrong option can mislead your analysis. Here’s a quick decision guide:

If you want to see each item’s share of the entire dataset → % of Grand Total.
If your PivotTable has multiple row fields (e.g., Region then Product) and you want each product’s share within its region → % of Parent Row Total.
If you have a time series in columns and want to see each month’s share of the year → % of Column Total (when years are columns) or % of Row Total (when years are rows).
If you want to see how a value changes from one period to another → % Difference From with “Previous” as the base.

Always ask: “What is the denominator?” The denominator is the total you are comparing against. That determines the option.

Common Problems and How to Fix Them

Even experienced users hit snags. Here are typical issues and solutions:

Percentages show as decimals (0.25 instead of 25%): This happens when the cell format is General. Select the values and apply the Percentage format (Home → Number → %).
Show Values As is grayed out: This occurs when you right-click a row or column label, not a value cell. Make sure you right-click a number in the Values area.
The percentage seems wrong: Check if your PivotTable has multiple row or column fields. If so, the “parent” options are more appropriate than “Grand Total”.
You want a percentage of a subtotal, not the grand total: Use “% of Parent Total” and specify the parent field in the dialog.
After changing to percentage, the original numbers are gone: You can add the same field twice—one as raw value and one as percentage. Drag the field into the Values area again and apply a different Show Values As.

Comparing Show Values As to Calculated Fields

Some users try to create percentages using a calculated field (e.g., =Sales/GrandTotal). This is usually a mistake because calculated fields operate on the underlying data rows, not on the aggregated PivotTable totals. As a result, the calculation may be incorrect or produce unexpected results.

The “Show Values As” feature is the correct tool because it works on the aggregated values after the PivotTable has summarized the data. It is also much faster and requires no formulas. Use calculated fields only when you need a custom calculation that is not available in the Show Values As list, such as a weighted average.

Frequently Asked Questions

Why is my percentage showing as 0%?

This usually means the cell format is set to a number with zero decimal places, so small percentages round to 0%. Increase the decimal places or apply the Percentage format with two decimals.

Can I show both the raw number and the percentage in the same PivotTable?

Yes. Drag the same field into the Values area twice. Right-click the second copy, choose Show Values As, and pick your percentage option. Then rename the headings to “Sales” and “% of Total”.

How do I show percentage of a specific base item (e.g., compare to January)?

Use “% Difference From”. In the Show Values As dialog, set the Base Field to the field containing the months, and Base Item to “January”. This will show each month’s percentage difference from January.

Does changing to percentage affect my source data?

No. Show Values As only changes the display in the PivotTable. Your original data remains untouched.

Why does “% of Parent Total” not appear in my Excel version?

This option is available in Excel 2010 and later. If you are using an older version, you may need to use “% of Column Total” or “% of Row Total” as a workaround.

How do I remove the percentage and go back to raw values?

Right-click a value cell, choose Show Values As, and select “No Calculation”. The numbers will return to their original sums or counts.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.