To calculate a percentage in an Excel PivotTable, add a numeric field to Values, open that field’s Show Values As options, and choose a calculation such as % of Grand Total, % of Row Total, or % of Column Total. This displays each summarized value as a share of the selected total, without changing the source data. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A worksheet containing a consistent table of source data, with a header in every column. For example, use columns such as Date, Region, Product, and Sales.
- A numeric field to summarize, such as sales, units, or hours. Values stored as text cannot be summed correctly.
- A clear idea of the comparison you want: each item as a share of the whole, of its row, of its column, or of a parent category.
You do not need to calculate percentages in the original rows first if your goal is to show each summarized PivotTable value as a percentage of another PivotTable total. Excel can perform that calculation in the PivotTable itself.
Step-by-Step
- Prepare the source data. Check that the first row contains unique, descriptive column headings and that there are no completely blank rows or columns in the middle of the data. Keep one record per row: for example, a row might record a product’s sales in one region on one date. Remove subtotal and grand total rows from the source range; the PivotTable will create its own totals. If practical, click inside the data and press Ctrl+T to make it an Excel table, then confirm that My table has headers is selected. On Mac, use Command+T to create a table. A table is useful because it can expand when you add more source rows.
- Insert a PivotTable. Click a cell in the source data, then select Insert > PivotTable. Check that the table or range shown is the data you expect. Choose New Worksheet if you want the PivotTable on its own sheet, and select OK. Excel opens a blank PivotTable and the PivotTable Fields pane. In Excel for Mac, the Insert tab also has the PivotTable command, though dialog layout can differ slightly. In Excel for the web, use Insert > PivotTable when the command is available for your workbook.
- Build a useful summary. In the PivotTable Fields pane, drag a category such as Region into Rows, and a second category such as Product into Columns if you want a grid by region and product. Drag Sales into Values. Excel will usually summarize numeric sales using Sum. If it shows Count instead, check the source field for numbers stored as text or blank cells, then change the summary by opening the field menu in Values and choosing Value Field Settings > Summarize Values By > Sum, if Sum is available. Your PivotTable should now show the totals that will serve as the percentage bases.
- Add the same value field again if you want totals and percentages side by side. Drag Sales into the Values area a second time. The first copy can remain as the original amount, while the second copy becomes a percentage. This makes the result easier to read and lets you compare an amount with its share. If you only need percentages, you can change the existing value field instead. Excel may label the second field “Sum of Sales2” or a similar name; you can give it a clearer custom name later.
- Open the percentage calculation options. Click a number in the value field you want to convert. Right-click and point to Show Values As. Choose the calculation that matches your question. You can also open the value field’s menu in the Values area, select Value Field Settings, and use the Show Values As tab. In Excel for Mac, use a right-click or Control-click to open the contextual menu; the available calculation names are generally the same, although the menu presentation can vary.
- Choose the right percentage base. Select % of Grand Total to show each cell as a share of all summarized values. Choose % of Row Total to make the values across each row add to 100%, or % of Column Total to make the values down each column add to 100%. For nested row or column fields, options such as % of Parent Row Total, % of Parent Column Total, or % of Parent Total show a child item’s share of its parent category. The precise parent option depends on how your fields are arranged. Select an option and check the result against the question you are trying to answer.
- Format the result as a percentage. A Show Values As calculation may initially appear as a decimal. Right-click a result in the percentage field and select Value Field Settings, then select Number Format. Choose Percentage, set the number of decimal places, and select OK twice. For example, a value of 0.25 displays as 25% with percentage formatting. Setting the format through Value Field Settings is preferable to formatting a few cells individually, because the field-level format is more likely to persist when the PivotTable refreshes or changes shape.
- Rename the percentage field and verify it. In Value Field Settings, enter a clear name such as “% of Grand Total” in Custom Name, then select OK. If the name duplicates another field caption, use a distinct label. Check one result manually: divide that item’s displayed amount by the relevant row, column, or grand total and confirm that the share matches. Also check whether the relevant group adds to 100%, allowing for rounding. If your source data changes, right-click inside the PivotTable and choose Refresh; a PivotTable based on an Excel table can include newly added table rows after refresh.
For example, suppose a PivotTable summarizes three regions with sales of $200, $300, and $500. The grand total is $1,000, so their shares of the grand total are 20%, 30%, and 50%. If those same regions appear across a row for one product, % of Row Total instead compares each region’s sales with that product’s row total. The percentage depends on the selected base, not just on the values shown.
Common Mistakes
- Choosing the wrong total. “% of Grand Total” answers a different question from “% of Row Total.” A result can be mathematically correct but misleading if its denominator does not match the comparison you intend. Identify the denominator before selecting the calculation.
- Expecting percentages of individual records. Show Values As works on the summarized values in the PivotTable. It does not calculate a percentage separately for every source row and then average those percentages. If you need a row-level percentage, calculate it in the source data or use an appropriate measure, then summarize that field.
- Confusing percentage display with percentage calculation. Applying Percentage number formatting changes how a value looks; it does not calculate a share. Conversely, Show Values As can calculate a share while the cell still displays a decimal. Use both the correct Show Values As option and percentage number formatting.
- Using a field that Excel treats as text. Sales values containing text, inconsistent symbols, or imported numbers stored as text can make Excel count records instead of summing amounts. Clean or convert the source field, refresh the PivotTable, and confirm that the Values field summarizes by Sum.
- Expecting rounded values to total exactly 100%. If the underlying shares have more decimal places than are displayed, the rounded figures can add to 99.9% or 100.1%. Increase the displayed decimal places to inspect the difference before assuming the calculation is wrong.
- Typing over PivotTable results. PivotTable values are generated from the source data and calculation settings. Editing a displayed percentage directly is not a reliable way to change the result; the edit may be blocked or lost on refresh.
Tips
- Keep the original amount and percentage in separate Values fields when readers need both the scale and the share. Rename them so the distinction is obvious, such as “Sales” and “Sales % of Row.”
- Use a row percentage when the question is “What portion of this row does each column represent?” Use a column percentage for “What portion of this column does each row represent?” Use grand-total percentages to compare every cell with the entire report.
- For a hierarchy such as Region followed by Product in Rows, a parent-total percentage can show each product’s contribution within its region. Expand the hierarchy and inspect the labels so the parent-child relationship is clear.
- Use percentage formatting with a sensible precision. Whole percentages work well for a high-level overview; one or two decimal places may be useful when the values are close. Avoid showing more digits than the data can meaningfully support.
- Turn the source range into an Excel table before building the PivotTable if new records will be added regularly. Refresh the PivotTable after changes, and confirm the PivotTable’s data source includes the records you expect.
- Keep a simple test case or manually check one cell when building a report for others. A quick division using the displayed amount and the intended total can reveal whether the chosen base is correct.
Troubleshooting
The Show Values As option is missing or unavailable: Confirm that you selected a value inside the PivotTable’s Values area, rather than a row label or a normal worksheet cell. Open Value Field Settings for the value field and check the Show Values As tab. If the PivotTable uses a data model or an external source, some options or menus may differ from a standard range-based PivotTable.
The values show as 0.2 instead of 20%: The calculation may be correct but the field is not formatted as a percentage. Open Value Field Settings, choose Number Format, and select Percentage. Do not multiply the displayed values by 100 manually; percentage formatting handles the display.
Every result is 100%: Check whether you selected a row or column percentage while each row or column contains only one value, or whether the PivotTable layout is not grouping the items you intended. Add the relevant category field to Rows or Columns and choose the percentage base again. If you intended to compare all items, choose % of Grand Total.
The PivotTable shows Count instead of Sum: Excel may have detected text or blanks in the numeric source column. Inspect the source values, convert text-formatted numbers to numbers, and make the column consistent. Then refresh the PivotTable and, in Value Field Settings, choose Sum. If Sum remains unavailable, check for nonnumeric entries in that field.
New records do not appear: Right-click the PivotTable and select Refresh. If the source was a fixed cell range, open PivotTable Analyze > Change Data Source and update the range, or rebuild the PivotTable from an Excel table that expands with new records. In Excel for Mac, the contextual tab may be labeled PivotTable Analyze or shown with a slightly different layout.
Parent percentages are missing or confusing: Add the parent and child fields to the same row or column hierarchy, with the parent above the child, and expand the relevant levels. Then reopen Show Values As and select the matching parent-total option. If the fields are in separate areas or the hierarchy is different, a parent-based percentage may not be available or may use a different parent than you expect.
Frequently Asked Questions
How do I show each PivotTable value as a percentage of the total?
Right-click a value in the PivotTable, choose Show Values As > % of Grand Total, then format the field as Percentage through Value Field Settings if necessary.
How do I make each row add up to 100%?
Choose Show Values As > % of Row Total for the relevant value field. Each value is divided by the total of its row, so the row’s percentages should add to 100%, subject to rounding.
Can I show the sales amount and its percentage together?
Yes. Add the same numeric field to Values twice. Leave one copy as a sum and set the second copy to the required Show Values As calculation. Give each field a distinct name and format the percentage copy as Percentage.
Does a PivotTable percentage use the average of row percentages?
No. Show Values As calculates a share from the summarized PivotTable values and the selected total. If you need to average percentages calculated for individual records, create or prepare a row-level percentage field and choose a suitable summary method for that field.
Why do the displayed percentages not add up to exactly 100%?
Rounding is the most common reason. The underlying values may total 100%, while the rounded figures on screen add to slightly more or less. Increase the decimal places to see whether the difference is only display rounding.
Can I calculate a percentage of a parent category?
Yes, when the PivotTable has a suitable nested row or column hierarchy. Select a parent-total option such as % of Parent Row Total or % of Parent Column Total, then verify the result against the parent item shown in the layout.
Related
- Choose an Excel Data Range: Methods, Tips, and Shortcuts
- Excel Letter Count: Count Letters and Characters in Cells
- Excel Very Slow Loading Data? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Row Data: Meaning, Structure, and How to Use ItExcel row data is information arranged horizontally across a worksheet, with each value in a separate cell and…
- New Home Budget Spreadsheet: How to Build OneA new-home budget spreadsheet should separate one-time buying and moving costs from recurring monthly expenses, then compare planned…
- Excel Workbook Statistics: What It Counts and How to Use ItExcel’s Workbook Statistics feature gives you a quick count of items in the active worksheet or the whole…
- Google Sheets Duplicate File: How to Make a CopyTo duplicate a Google Sheets file in a web browser, open the spreadsheet and choose File > Make…
- Excel Data Analysis: Where to Find the OptionsExcel’s statistical Data Analysis option is the Analysis ToolPak. In Excel for Microsoft 365 on Windows, enable it…
- Average Growth Rate in Excel: Calculate It Step by StepTo calculate the arithmetic average of period-by-period growth rates in Excel, calculate each rate in a helper column,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.