To add a percentage to an Excel PivotTable, put the value field in the Values area a second time, then right-click a number in that copy and choose Show Values As followed by the percentage calculation you need. Choose % of Grand Total, % of Row Total, or % of Column Total to show each value as a share of the appropriate total.
What a PivotTable percentage shows
A PivotTable percentage is usually a way to display an existing summarized value relative to a total. Excel calculates the percentage from the data summarized in the PivotTable; it does not change the underlying source records. For example, if a PivotTable summarizes sales by region and product, you can show each product’s sales as a share of its region’s sales, or as a share of all sales.
This is different from adding a percentage sign to a number. Number formatting can make 0.25 appear as 25%, but it does not calculate a share. The Show Values As setting performs the calculation, while number formatting controls how its result looks. Understanding that distinction helps prevent a common mistake: formatting an ordinary sales amount as a percentage does not turn it into a meaningful sales share.
Excel applies the calculation to the values produced by the PivotTable’s summary function, such as Sum or Count. A percentage therefore depends on both the source data and the way the PivotTable groups and summarizes it.
Add a percentage as a second value field
For Microsoft Excel for Microsoft 365 on Windows, use these steps to keep the original totals visible alongside the percentages:
- Click anywhere inside the PivotTable. If one does not exist yet, select the source data and choose Insert > PivotTable, then arrange the fields in the PivotTable Fields pane.
- In the field list, drag the numeric field you want to analyze, such as Sales, into the Values area a second time. You should now have two entries for that field. If the field list is hidden, click inside the PivotTable and choose PivotTable Analyze > Field List.
- Click a value in the second copy of the field. Right-click and choose Show Values As, then select the appropriate option, such as % of Grand Total.
- For a clearer label, open the field’s menu in the Values area and choose Value Field Settings. In the Custom Name box, enter a useful label such as Sales % of Total. Avoid using a name that exactly duplicates an existing field caption.
- In the same dialog, select Number Format, choose Percentage, set the desired decimal places, and select OK. This formats the field consistently even when the PivotTable updates.
You can also use Value Field Settings > Show Values As to choose the calculation. The right-click menu is a quick route, while the settings dialog makes it easier to confirm which field copy you are changing and set its number format.
Choose the right percentage calculation
The denominator is the key decision. A correct percentage for one question may be misleading for another, so decide what you want each number to be a share of before selecting an option.
- % of Grand Total: divides each displayed value by the PivotTable’s overall total. Use it to see how much each category contributes to all the summarized data.
- % of Row Total: divides each value by the total for its row. Use it to compare the mix across columns within each row, such as each product’s share of a region’s sales.
- % of Column Total: divides each value by the total for its column. Use it to compare the mix down a column, such as each region’s share of sales for one product.
- % of Parent Row Total or % of Parent Column Total: calculates a share relative to a parent item in a hierarchy. These choices are useful when fields such as year, quarter, and month are arranged in nested row or column groups.
- % of Parent Total: shows a value as a share of its parent item. The exact result depends on the base field and item you select in the settings, so check the hierarchy and available options before relying on it.
Some other choices in Show Values As are not percentages. For example, Difference From displays a difference from a selected base item, rather than a percent share. The options available can depend on the fields in the PivotTable and how they are arranged.
Example: percentage of a grand total
Imagine a sales summary with North sales of $120 for Product A and $80 for Product B, and South sales of $90 for Product A and $110 for Product B. The overall total is $400. If the PivotTable has Region in Rows, Product in Columns, and Sum of Sales in Values, selecting % of Grand Total makes each cell a share of $400.
- North, Product A: $120 divided by $400 equals 30%.
- North, Product B: $80 divided by $400 equals 20%.
- South, Product A: $90 divided by $400 equals 22.5%.
- South, Product B: $110 divided by $400 equals 27.5%.
The four percentages add to 100%. If you choose % of Row Total instead, North’s two products are each calculated against North’s $200 total: Product A is 60% and Product B is 40%. South’s row also totals $200, so its shares are 45% and 55%. The individual percentages can therefore look very different even though the source amounts have not changed.
Format the result without changing the calculation
For a PivotTable value field, set the format through Value Field Settings > Number Format. This is generally more reliable than selecting the visible cells and using the Home tab’s percentage button. Direct formatting can be lost or applied inconsistently when the PivotTable is refreshed, expanded, or rearranged. A field-level number format is associated with the value field.
Choose the number of decimal places based on the data and how the report will be read. Whole percentages are easy to scan, while one or two decimal places can help when values are small or close together. For example, 22.5% may communicate a small category’s contribution more accurately than a rounded 23%. Avoid displaying excessive precision if it makes the report harder to interpret.
Excel stores a percentage as a decimal fraction for display purposes: 25% represents 0.25. A percentage format adds the percent sign and scales the displayed number accordingly. If a result unexpectedly appears as 2,500%, check whether the number was already multiplied by 100 before it was formatted. With Show Values As, Excel calculates the share; you generally should not multiply that result by 100 yourself.
Percentage of total versus a calculated field
Show Values As is usually the simplest choice when you want a summarized value expressed as a share of a row, column, grand total, or parent. It is built for comparing the PivotTable’s grouped results, and the percentage changes when you filter or rearrange the PivotTable because its relevant totals can change.
A calculated field answers a different question: it creates a value from source fields, such as calculating profit from sales and cost. In a standard PivotTable, use PivotTable Analyze > Fields, Items, & Sets > Calculated Field to create one. A calculated field is not automatically the right method for a percent-of-total report. Its formula operates on the source fields as defined for the PivotTable, and the resulting calculation may not equal each grouped value divided by the displayed total.
If the source data already contains a percentage for each record, decide whether you need a sum, average, or another meaningful summary of those record-level percentages. Summing percentages is often not meaningful. For example, averaging store-level conversion rates may need to be weighted by the number of visitors at each store; an unweighted average can tell a different story. The appropriate method depends on what the percentage represents and what question the summary should answer.
Common problems and how to fix them
- The percentage option is missing or the value is not summarized as expected: Confirm that the field is in the Values area. Open Value Field Settings and check the summary function, such as Sum or Count. A text field may default to Count rather than Sum.
- There is only one value column: Drag the field into Values again. Then apply Show Values As to the second copy so the original figures remain available for comparison.
- The numbers do not add to 100%: Check which total you selected. Percentages of a row or column add to 100% within that row or column, not necessarily across the entire report. Filters, hidden items, and displayed subtotals can also affect what readers see.
- The percentage looks much too large: Check that the field is set to a percentage calculation and has not been multiplied by 100 in a source formula. Also check whether an existing percentage field is being summarized as a sum.
- The value field shows Count instead of Sum: Excel may treat the source column as text, or the data may contain blanks or nonnumeric entries. Inspect the source values, correct the data type or contents, and refresh the PivotTable.
- The total row is confusing: A percentage displayed on a grand-total row may not be useful in the same way as the detail percentages. Review the total and subtotal behavior in the finished report, and consider whether showing those rows helps explain the calculation.
Refreshing and sharing the PivotTable
When source records change, refresh the PivotTable so it includes the updated data. Right-click inside it and select Refresh, or choose PivotTable Analyze > Refresh. The percentage field’s calculation should remain in place, but a refresh cannot include new records that fall outside the PivotTable’s source range. Turning a well-structured source range into an Excel table before creating the PivotTable can make it easier for the source to expand as rows are added.
After filtering, remember that the displayed percentage may be relative to the items included in the PivotTable’s current calculation. This is useful for analyzing a selected period or region, but it means a percentage can change when a filter changes even if the underlying sales for a particular item do not. When sharing a report, label the percentage field clearly and make filters visible so readers understand its scope.
On Excel for Mac, the same basic method applies: add the value field again and use its value-field settings or the contextual menu to choose a percentage calculation. Ribbon and dialog placement can differ slightly from Windows. Excel for the web also provides PivotTable tools, but available commands and their locations can vary by the web interface; if a specific setting is unavailable, open the workbook in the desktop application.
Frequently Asked Questions
How do I show a PivotTable value as a percentage of the total?
Place the value field in Values, right-click a value, choose Show Values As > % of Grand Total, and format the field as Percentage if needed. Add the field a second time if you also want to keep the original amount visible.
How do I show percentages instead of numbers in an Excel PivotTable?
Apply a percentage calculation such as % of Row Total to the value field. If you want only percentages displayed, you can remove or hide the original value-field copy, but keeping both is often clearer for comparison.
Why do my PivotTable percentages not add up to 100%?
They may be percentages of different row or column totals, or the visible values may be filtered. Verify the calculation type and the denominator. Grand-total percentages across all included detail items should add to 100%, subject to rounding.
Can I show the sales amount and its percentage together?
Yes. Drag the same numeric field into the Values area twice. Leave one copy as Sum and set the other to the desired percentage calculation, then give each copy a distinct custom name.
Does a PivotTable percentage update when I add data?
The calculation updates when the PivotTable is refreshed and the new records are included in its source range. If added rows are outside that range, adjust the source or use an expanding Excel table as the source before refreshing.
Related
- ChatGPT Excel Data Analysis: How to Get Useful Results
- Excel Analysis ToolPak: What It Does and How to Use It
- Analysis ToolPak in Excel: How to Enable and Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Generate a Heat Map in Excel: Color Scales and TipsTo generate a heat map in Microsoft Excel for Microsoft 365 on Windows, select the numeric cells, then…
- LEGO Inventory Spreadsheet: Track Sets, Parts, and GapsA LEGO inventory spreadsheet is a workbook for recording the sets and individual pieces you own, or for…
- Find and Replace Fonts in PowerPoint: A Complete GuideTo find and replace a font throughout a PowerPoint presentation in Microsoft 365 for Windows, go to Home…
- Data Validation Named Range in Excel: Setup and FixesA data validation named range is a named cell range used as the source for an Excel drop-down…
- How to Mail Merge in Word: A Complete Step-by-Step GuideTo mail merge in Word, prepare a recipient list—usually an Excel workbook—then use the Mailings tab to connect…
- Excel Table Colors: How to Change and Customize ThemExcel table colors are controlled mainly by table styles: select a cell in the table, open the Table…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.