Excel Pivot Table: Percentage of Two Columns

Charts & pivot tables · Updated November 18, 2026

To show one PivotTable column as a percentage of another in Excel for Microsoft 365 on Windows, add the value field to the Values area twice, then set the second copy to Show Values As > % Of. Choose the field that creates the columns as the base field and the comparison column as the base item—for example, divide each 2025 result by its 2024 result.

What “percentage of two columns” can mean

The phrase can describe a few different calculations, and the right setup depends on what you mean by “of.” A common request is to compare two PivotTable columns, such as this year’s sales with last year’s sales. Another is to show each column as a share of its own total, the row total, or the entire PivotTable total. Those are different percentages, even though they may use the same source data.

For example, imagine a PivotTable with product names in rows, years in columns, and sales in Values. If a product sold $120 in 2025 and $100 in 2024, the 2025 amount as a percentage of 2024 is 120%. The increase is 20%, which is a different calculation: it is the difference divided by the earlier amount. By contrast, if the $120 is 30% of all 2025 sales, that is a share of the 2025 column total, not a comparison with 2024.

Before changing the PivotTable, decide which denominator answers your question: another column’s value, a row or column total, or the grand total. This prevents the common mistake of choosing a percentage display that looks plausible but answers a different question.

Compare one column with another using % Of

Use this method when your two comparison columns are items in the same source field—for example, years in a Year field or actual and budget in a Scenario field. Your source data should have a column for the category, a column for the comparison item, and a numeric column to summarize. A PivotTable can then group the categories into rows and the comparison items into columns.

  1. Click inside the PivotTable. In the PivotTable Fields pane, drag the category field to Rows, the comparison field to Columns, and the numeric field, such as Sales, to Values.
  2. Drag the same numeric field into Values a second time. The first copy will show the original amounts; the second will display the percentages.
  3. Click a value in the second copy of the field. Right-click and select Show Values As > % Of. You can also open Value Field Settings for that copy and choose the calculation on its Show Values As tab.
  4. Set Base field to the field placed in Columns, such as Year, then set Base item to the comparison column, such as 2024. Select OK.
  5. Format the second value field as a percentage. Right-click one of its values, choose Number Format, select Percentage, and choose the number of decimal places.

Suppose the PivotTable summarizes 2024 sales of $100 and 2025 sales of $120 for one product. With Year as the base field and 2024 as the base item, the 2024 result is 100% and the 2025 result is 120%. The result is a ratio to the selected base item, not the percentage increase. If you need the increase, subtract 100% from the 2025 ratio: 120% − 100% = 20%.

Depending on the PivotTable layout, Excel may place the two copies of Sales under a Values heading in the columns. You can rename the copies to labels such as Sales and % of 2024 by editing their custom names in Value Field Settings. Use a name that makes the denominator clear, especially if the report will be shared.

Show a percentage of a row, column, or grand total

If you are not comparing one specific column against another, select the matching total-based calculation instead. Right-click a value in the relevant value field and use Show Values As. Depending on the question, choose % of Row Total, % of Column Total, or % of Grand Total.

  • % of Row Total divides each value by the total across that row. Use it to see how a category’s results are distributed across years, regions, or other column items.
  • % of Column Total divides each value by the total down its column. Use it to see each product’s share of a year’s or region’s total.
  • % of Grand Total divides each value by the overall total represented in the PivotTable.

For example, if a product has $120 in 2025 sales and all products total $400 that year, its share of the 2025 column is 30%. That is not the same as comparing the product’s 2025 sales with its 2024 sales. A quick check is to add the displayed percentages: percentages of a row or column total should generally add to 100% across the complete set of included items. Filters can change which items are included in those totals.

When the two measures are separate fields

Sometimes the columns you want to compare are separate measures rather than items in one field. For example, the source may contain Sales and Budget as two numeric columns. The % Of setting is designed around a base field and base item; it does not simply mean “divide the Sales value field by the Budget value field.” A PivotTable’s ordinary display calculations may therefore not offer the direct comparison you want.

One straightforward option is to calculate the ratio beside the PivotTable. Assume the PivotTable puts Sales in B5 and Budget in C5 for the same category. In D5, enter =B5/C5 and format D5 as a percentage. For example, if B5 is 120 and C5 is 150, the formula returns 0.8, displayed as 80%. This compares the two summarized amounts. Keep the formula outside the PivotTable’s changing area, since refreshing or rearranging the PivotTable can move its cells.

For a ratio that must remain part of the PivotTable, consider a Data Model measure when the workbook uses the Excel Data Model. A measure can calculate the ratio of aggregated totals—for example, total Sales divided by total Budget—rather than summing row-by-row ratios. The measure requires an appropriate model and a valid DAX expression, so it is a more advanced choice. Another option is to add a suitable calculated column to the source data and refresh the PivotTable, but confirm that aggregating that column produces the intended result.

Why calculated fields can give surprising ratios

A frequent misconception is that dividing one source field by another in a PivotTable calculated field will always produce the ratio of their totals. A calculated field can evaluate the expression for source records and then aggregate the results. In general, the sum or average of individual record-level ratios is not equal to the ratio of the summed amounts.

For example, consider two records with Sales and Budget of 10 and 20, then 90 and 180. Each record has a Sales-to-Budget ratio of 50%, and the ratio of the totals is also 50%. But if the records were 10 and 20, then 90 and 100, the record ratios would be 50% and 90%, while the ratio of totals would be 100/120, or about 83.3%. The correct method depends on whether you want to average or sum record-level percentages, or divide the aggregated measures. State that choice before building the calculation.

Format and label the result clearly

Percentage calculations are often stored as decimal ratios: 1 represents 100%, 0.25 represents 25%, and 1.2 represents 120%. Applying percentage number formatting changes how the value is displayed; it does not change the underlying calculation. Avoid multiplying by 100 and then applying percentage formatting, because that would display a value 100 times too large.

Set the number format through the value field rather than formatting only individual cells. Right-click a value, choose Number Format, and select Percentage. This is more reliable when the PivotTable is refreshed because the setting belongs to the value field. Use a descriptive custom name such as % of 2024 or Share of Column Total, and consider showing the original amounts beside the percentage so readers can understand the scale.

Common problems and how to fix them

  • The comparison item is missing: Check that the base item exists in the source data and is visible in the PivotTable. A filter or slicer may have excluded it. Clear or adjust the filter, then revisit the base item setting.
  • Every percentage is 100%: The base field may be set to the wrong field, or the base item may match each displayed item. Select the field that actually creates the comparison columns and choose one fixed item as the denominator.
  • The percentages look 100 times too large: Check whether the values were multiplied by 100 before percentage formatting. A ratio of 1.2 should display as 120%, not 12,000%.
  • The results change after filtering: Total-based percentages usually use the items currently represented in the PivotTable. Check filters, slicers, and hidden items when the denominator appears to change.
  • The PivotTable will not update: Refresh it after changing or adding source records. Select a PivotTable cell and choose PivotTable Analyze > Refresh. If new rows are not included, verify the PivotTable’s source range or use an Excel table as the source so added records can be included when refreshed.

Excel for Mac and Excel for the web

The core approach—adding a second copy of a value field and choosing a Show Values As calculation—is available in current Excel versions, but ribbon placement and dialog presentation can differ between platforms. On Mac, open the value field’s settings from the PivotTable controls and look for Show Values As; the exact surrounding controls can vary by release. Excel for the web also supports PivotTables, but some advanced options may be presented differently or be unavailable compared with desktop Excel. If you cannot find the base field and base item controls in the web interface, open the workbook in the Excel desktop app.

Frequently Asked Questions

How do I show one PivotTable column as a percentage of another?
Duplicate the value field, set the second copy to Show Values As > % Of, then choose the column field and the specific comparison item as the base field and base item.

How do I calculate percentage increase between two PivotTable columns?
First calculate the later column as a percentage of the earlier one. Subtract 100% from that ratio to get the increase. For example, a ratio of 125% represents a 25% increase, not a 125% increase.

Can I divide two value fields directly in a PivotTable?
Not with the same base-item setup used to compare items of one field. For a simple report, calculate the ratio beside the PivotTable. For a model-based report, create a measure that divides the aggregated values.

Why do my PivotTable percentages not add up to 100%?
You may be comparing items with % Of rather than showing shares of a row or column total. Ratios to a fixed base can add to more or less than 100%. Also check for filters, hidden items, and the chosen denominator.

How do I keep the percentage format after refreshing?
Set the number format through the value field’s Number Format option rather than formatting only selected worksheet cells. If it still changes, review the PivotTable options related to preserving cell formatting on update.

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.