Excel Pivot Table: Add Percentage Column

Charts & pivot tables · Updated November 22, 2026

Yes, you can add a percentage column to an Excel pivot table by using the “Show Values As” feature. Right-click any value in the Values area, choose Show Values As, then select % of Grand Total, % of Column Total, or another percentage option. This converts the existing value field into a percentage without adding a new field. For a separate calculated percentage column (e.g., profit margin), you add a calculated field via PivotTable Analyze > Fields, Items & Sets > Calculated Field.

Why Percentages in Pivot Tables Are Tricky

Many users try to insert a formula in a pivot table cell, like =C2/B2, and get a #REF! error or a value that disappears when the pivot refreshes. That happens because pivot tables are not normal ranges—they are a separate data engine. The correct way is to use the built-in percentage display options or a calculated field. The built-in options are instant and require no formulas; calculated fields give you control over the math but have limitations (see below). Understanding which one fits your goal is the first step.

Method 1: Show Values As (Quickest for One Column)

This method changes how an existing value is displayed—it does not add a new column. It is ideal when you want to see, say, each product’s share of total sales.

  1. In the pivot table, right-click any number in the Values area (e.g., Sum of Sales).
  2. Choose Show Values As.
  3. Select one of the percentage options: % of Grand Total, % of Column Total, % of Row Total, or % of Parent Total (for hierarchies).

For example, with Regions in Rows and Sum of Sales in Values, choosing % of Grand Total shows each region’s sales as a percentage of all regions. The cell values become decimals like 0.25 (25%). To format as percentages, select the cells, press Ctrl+Shift+% (Windows) or Cmd+Shift+% (Mac).

This method does not create a separate column—it replaces the existing numbers. If you need both the raw number and the percentage side by side, drag the same field into the Values area twice, then set one to % of Grand Total. That gives you two columns: one with sums, one with percentages.

Method 2: Calculated Field (For a New Percentage Column)

Use a calculated field when the percentage is based on two different fields in your source data—for example, profit divided by sales. This adds a new column to the pivot table.

Steps:

  1. Click anywhere inside the pivot table.
  2. Go to PivotTable Analyze (or Analyze on Mac) > Fields, Items & Sets > Calculated Field.
  3. In the Name box, type a name like Profit %.
  4. In the Formula box, enter the formula. For example, if your source data has a field named Profit and a field named Sales, type:
=Profit/Sales

Click OK. The new field appears in the PivotTable Field List under Values. Drag it into the Values area if it doesn’t appear automatically.

Important: Calculated fields operate on the sum of the underlying fields, not row-by-row. If your source data has multiple rows per category, the calculated field uses the sum of Profit divided by the sum of Sales. That is usually what you want for a total percentage, but it is not the same as averaging individual row percentages. For example, if you have two rows for Product A—one with Profit 10, Sales 100 (10%) and another with Profit 5, Sales 50 (10%)—the calculated field returns (10+5)/(100+50)=10%, which is correct. But if the rows are Profit 10, Sales 100 and Profit 1, Sales 10, the calculated field gives (11/110)=10%, while the average of the two row percentages would be (10%+10%)/2=10%—same here, but not always. If you need row-level percentages, you must add a helper column in your source data (e.g., =Profit/Sales) and then sum that column in the pivot, but summing percentages can be misleading if the denominators vary.

How to Choose the Right Percentage Type

Excel offers several percentage options, each answering a different question:

  • % of Grand Total: Shows each value as a share of the entire pivot table’s total. Use when comparing parts to the whole (e.g., each product’s share of all sales).
  • % of Column Total: Shows each value as a share of its column’s total. Useful when you have multiple columns representing different groups (e.g., months) and you want to see each row’s share within each month.
  • % of Row Total: Shows each value as a share of its row’s total. Useful for cross-tabulations like survey responses where each row sums to 100%.
  • % of Parent Total: For hierarchical fields (e.g., Region > Country), this shows the percentage of the parent item. For example, each country’s sales as a percentage of its region’s total.
  • % Difference From: Shows percentage change from a base item (e.g., previous month). Choose this if you need a variance column.

For a simple “percentage of total” column, % of Grand Total is the most common choice. For a “percentage of a specific base” (like last year), use % Difference From or % Of with a base field.

Common Problems and Fixes

  • Percentages show as decimals (0.25) instead of 25%: Select the cells and apply percentage number format (Ctrl+Shift+%). Or right-click > Number Format > Percentage.
  • Calculated field returns #DIV/0!: This happens when the denominator field is zero or blank. You can use =IFERROR(Profit/Sales,0) in the calculated field formula, but note that calculated fields do not support all Excel functions—IFERROR is supported in calculated fields in Excel 2013 and later.
  • Percentage column disappears when refreshing: If you used a calculated field, it should remain. If you used Show Values As, the format may reset if you change the field layout. Reapply the setting after major changes.
  • The percentage column shows the same value for every row: This usually means you selected % of Column Total but the column is the same for all rows (e.g., a single column). Choose % of Grand Total instead.
  • Cannot add a calculated field to a pivot table based on a data model (Power Pivot): Calculated fields are not available in pivot tables created from the Data Model. Instead, you must create a calculated column in the Power Pivot table or use DAX measures. To check, go to PivotTable Analyze > Data > Data Model. If it’s grayed out, you are using the Data Model.

Comparing Show Values As vs. Calculated Field

Aspect Show Values As Calculated Field
Adds a new column? No—modifies existing value Yes—adds a new field
Use for Share of total, difference, rank Custom formulas between fields
Row-level precision Uses aggregated totals Uses aggregated totals (not row-by-row)
Available in Data Model pivots? Yes No (use DAX measures)
Flexibility Limited to preset options Any formula you can write

If you only need a percentage of a total, Show Values As is simpler and faster. If you need a ratio like profit/sales, use a calculated field. For complex calculations on data model pivots, learn DAX.

Frequently Asked Questions

How do I add a percentage column to a pivot table in Excel for Mac?

The steps are identical: right-click a value, choose Show Values As, and select the percentage option. For calculated fields, go to the PivotTable Analyze tab (called Analyze on Mac) > Fields, Items & Sets > Calculated Field. Keyboard shortcut for percentage format is Cmd+Shift+% on Mac.

Can I add a percentage column that shows the percentage of a specific row or column?

Yes. Use % of Row Total or % of Column Total. For a custom base (e.g., percentage of a specific month), use % Of and choose the base field and item. For example, to show each month’s sales as a percentage of January, set the base field to Month and the base item to January.

Why is my calculated field percentage wrong when I have multiple rows per item?

Calculated fields use the sum of the underlying fields, not row-level percentages. If you need row-level precision, add a helper column in your source data (e.g., =Profit/Sales) and then sum that column in the pivot. But be aware that summing percentages across rows can overstate the total if denominators vary. For accurate overall percentages, stick with the calculated field approach.

How do I show percentage change from previous year in a pivot table?

Use Show Values As > % Difference From. Set the Base Field to the year field and the Base Item to (previous). This adds a column showing the percentage change from the prior year. For example, if 2023 sales are 120 and 2022 are 100, the result is 20%.

Can I use a percentage column in a pivot chart?

Yes. After you add a percentage column (either via Show Values As or a calculated field), it appears as a series in the pivot chart. Just ensure the chart is based on the pivot table, and the percentage series will be plotted automatically.

Is there a way to add a percentage column without changing the original data?

Yes, both methods work without altering your source data. Show Values As changes only the display in the pivot. Calculated fields store the formula in the pivot table, not in the source data. Your original data remains untouched.

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.