To show a pivot table value as a percent of two columns, add both fields to the Values area, then use the “Show Values As” > “% of” option and select the base field you want as the denominator. For example, if you have Sales and Returns columns, you can display Returns as a percentage of Sales directly in the pivot table.
Understanding the Default Calculation
By default, a pivot table sums numeric fields and shows the raw total. When you add two columns (say, “Sales” and “Returns”) to the Values area, you get two separate columns of totals. To express one as a percentage of the other, you need to change the calculation for one of those value fields. The “Show Values As” feature in Excel lets you convert a value into a percentage of another value, a grand total, or a specific field.
For instance, if your source data has columns: Region, Sales, Returns. After creating a pivot table with Region in Rows, and both Sales and Returns in Values, you’ll see each region’s total sales and total returns. To see returns as a percent of sales, you’d change the Returns field to “% of” and choose Sales as the base field.
Step-by-Step: Percent of a Specific Column
Here’s how to set it up in Excel for Microsoft 365 on Windows (steps are similar on Mac, but the menu is called “Show Values As” under the field settings).
- Create a pivot table from your data (Insert > PivotTable).
- Drag the field you want as the denominator (e.g., Sales) into the Values area.
- Drag the field you want as the numerator (e.g., Returns) into the Values area as well.
- In the Values area, click the dropdown arrow next to the Returns field, then select “Value Field Settings”.
- Go to the “Show Values As” tab.
- From the dropdown, choose “% of”.
- In the “Base Field” list, select the field that contains the denominator (in this case, Sales). The Base Item should be left as “(previous)” or change to a specific item if needed.
- Click OK. The Returns column now displays as a percentage of Sales.
For Excel on Mac, the path is the same: right-click the field in the Values area, choose “Show Values As”, then “% of”. In Excel for the web, right-click the value field and select “Show Values As” > “% of”.
Using Calculated Fields for More Control
If you need a percentage that isn’t directly a simple ratio of two existing columns (for example, profit margin = (Sales – Cost) / Sales), you can add a calculated field. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. In the dialog, name the field (e.g., “Margin”), and enter a formula like =(Sales-Cost)/Sales. This creates a new field in the pivot table that calculates the percentage for each row. Note that calculated fields use the sum of the underlying fields, so the formula operates on the totals, not row-by-row.
Percent of Row or Column Total vs. Percent of Two Columns
Many users confuse “percent of column total” with “percent of two columns”. The former shows each value as a share of the total for that column (e.g., each region’s sales as a % of all regions’ sales). The latter, as described above, shows one column’s value relative to another column’s value in the same row. To get the column total percentage, choose “% of Column Total” in the Show Values As menu. For two columns, you must use “% of” and specify the base field.
Common Problems and Solutions
- Percentages show as decimals (0.25 instead of 25%): Right-click the value field, choose Number Format, and select Percentage.
- Getting #DIV/0! errors: This happens when the denominator is zero. You can use IFERROR in a calculated field, but for a standard pivot table, you may need to filter out zero rows or use a helper column in the source data.
- Base field not appearing: The base field list only shows fields that are in the pivot table’s source data, not necessarily those in the Values area. Ensure the denominator field is present in the source data, even if not displayed.
- Percentages change when you filter: By default, “% of” calculations are based on the visible data. If you filter out rows, the percentages recalculate. To keep them stable, you might need to use GETPIVOTDATA or a calculated field outside the pivot.
Example: Sales and Returns by Region
Suppose your data has Region, Sales, and Returns. After following the steps above, your pivot table might look like this:
- Region: North, Sales: 1000, Returns: 50 (shows as 5.00%)
- Region: South, Sales: 2000, Returns: 100 (shows as 5.00%)
- Region: East, Sales: 1500, Returns: 90 (shows as 6.00%)
The Returns column now displays the percentage of Sales. This is useful for comparing return rates across regions.
Alternatives: Using Formulas Outside the Pivot
If you prefer not to use the pivot table’s built-in percentage feature, you can create a regular formula next to the pivot table. For example, if the pivot table has Sales in column B and Returns in column C, you could put in D2: =C2/B2 and format as percentage. However, this formula will not automatically adjust if you refresh or change the pivot layout. A more robust approach is to use GETPIVOTDATA to reference specific pivot values, but that can be complex. For most users, the “Show Values As” method is simpler and updates automatically.
Frequently Asked Questions
Q: How do I show a percentage of two columns in a pivot table in Excel?
Add both fields to the Values area, then change one field’s “Show Values As” to “% of” and select the other field as the base field. That displays the first as a percentage of the second.
Q: Why is my percentage showing as a decimal like 0.05?
Excel stores percentages as decimal fractions. Right-click the value field, choose “Number Format”, and select “Percentage” to display it as 5%.
Q: Can I calculate a percentage of two columns without adding both to the pivot?
Yes, you can add a calculated field with a formula like =Field1/Field2, but be aware it uses the sum of each field. Alternatively, use a formula outside the pivot referencing the pivot cells.
Q: What does “% of Parent Row Total” mean?
That option shows the value as a percentage of the total of its parent row in a hierarchical pivot table (e.g., a subcategory as a % of its category). It’s different from “% of” which uses a specific base field.
Q: Does this work in Google Sheets?
Google Sheets has a similar feature: in the pivot table editor, under “Values”, click the dropdown next to the field, choose “Show as”, then “% of” and select the base field. The steps are analogous.
Q: How do I get the percentage to stay fixed when I filter the pivot?
By default, percentages recalculate based on visible data. To keep them fixed, you could copy the pivot table as values, or use a calculated field that references the original data range (but that may not respect filters).
Related
- Excel Insert Yes No Checkbox: 2 Ways
- Excel Instrument Repair Vernon CT: Full Guide
- Excel Interpolate Between Numbers: How To
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Data Source Not Valid? Fix ItThe Excel message “The data source reference is not valid” usually means Excel cannot resolve the range, table,…
- Excel Add Horizontal Line to Chart: Steps and TipsTo add a horizontal target or reference line to an Excel chart, add a data series whose value…
- Excel Graph Not Showing All Data? How to Fix ItIf an Excel graph is missing selected data, check its source range first: select the chart, choose Chart…
- How to Change Bubble Size in an Excel Bubble ChartTo change bubble sizes in an Excel bubble chart, right-click a bubble, choose Format Data Series, open Series…
- Excel Chart Design: Choose and Improve the Right ChartExcel chart design means choosing a chart that fits the question, arranging the data so Excel can interpret…
- Excel Trendline Values: How to Display and Calculate ThemTo get trendline values in Excel, select the chart’s data series, add a trendline, and tick Display Equation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.