A calculated field lets you create a new column of math based on data already in your pivot table

A calculated field is a column you add to a pivot table that performs a calculation using the numbers already there. Instead of building the math in a separate sheet, you define the formula once, and the pivot table applies it to every row automatically. This is useful when you need a percentage, a difference, a ratio, or any other derived number that depends on your existing data.

The process differs slightly between Excel and Google Sheets, but the concept is the same: you tell the pivot table which fields to use in the calculation, write the formula, and the table does the work. The calculated field updates whenever you refresh the pivot table or change the underlying data.

Key Takeaways

  • A calculated field performs math on pivot table data and appears as a new column without requiring a separate formula in each cell.
  • In Excel, you access calculated fields through the PivotTable Analyze tab; in Google Sheets, you use the Data menu and select "Create a field".
  • You reference other fields in your formula by their exact names, enclosed in square brackets in Excel or as field references in Google Sheets.
  • Calculated fields work best for percentages, ratios, and straightforward arithmetic; complex logic or text operations often require a helper column instead.

How to add a calculated field in Excel

Open your pivot table in Excel. Click anywhere inside the pivot table to set up it. Then go to the PivotTable Analyze tab at the top of the ribbon (this tab only appears when a pivot table is selected). In the Calculations group, click Fields, Items, & Sets, then select Calculated Field from the dropdown.

A dialog box titled "Insert Calculated Field" will open. In the Name field, type a name for your new column—something like "Profit Margin" or "Percentage of Total". In the Formula field, type your calculation. Reference other fields by putting their exact names in square brackets. For example, if you want to divide Revenue by Units Sold, type =Revenue/Units Sold (with the field names in brackets: =[Revenue]/[Units Sold]). Click OK, and Excel adds the column to your pivot table.

If you need to edit the formula later, go back to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, select the field name from the list, and click Modify.

How to add a calculated field in Google Sheets

Open your pivot table in Google Sheets. Click the Data menu at the top, then select Pivot table > Edit pivot table. This opens the pivot table editor panel on the right side of the screen. At the bottom of the editor, click Create a field.

A dialog box will appear. Give your field a name in the Field name box. In the Formula box, type your calculation. In Google Sheets, you reference fields by typing their name directly or by clicking the field name from the list that appears as you type. For example, to calculate a percentage, you might type Revenue / Total Revenue or use the formula builder to select fields from the dropdown. Click Create, and Google Sheets adds the column to your pivot table.

To edit a calculated field in Google Sheets, open the pivot table editor again, find the field in the list, click the three-dot menu next to it, and select Edit.

Common formulas and what they do

A percentage of total divides one field by the sum of all values in that field. In Excel, this looks like =[Sales]/SUM([Sales]). In Google Sheets, type Sales / SUM(Sales). This shows what fraction of the total each row represents.

A profit margin subtracts cost from revenue and divides by revenue. In Excel: =([Revenue]-[Cost])/[Revenue]. In Google Sheets: (Revenue - Cost) / Revenue. This tells you how much profit you keep from each dollar of sales.

A year-over-year change compares this year's number to last year's. In Excel: =([2024]-[2023])/[2023]. In Google Sheets: (2024 - 2023) / 2023. This shows growth or decline as a percentage.

A straightforward difference subtracts one field from another. In Excel: =[Actual]-[Budget]. In Google Sheets: Actual - Budget. This shows whether you came in over or under.

When a calculated field won't work and what to use instead

Calculated fields work well for arithmetic and basic comparisons. They struggle with text operations, conditional logic (if-then statements), or calculations that depend on the row number or position in the table. If your formula needs to say "if this value is negative, show it in red" or "combine these two text fields", a calculated field is not the right tool.

Instead, create a helper column in your source data before you build the pivot table. Add a column to your original spreadsheet with the formula you need, then include that column when you create the pivot table. This gives you more flexibility and lets you use any formula your spreadsheet supports. The pivot table will then treat the helper column as regular data and let you use it like any other field.

Another option is to add the calculation after the pivot table. Copy the pivot table to a new location, then add formulas in adjacent columns that reference the pivot table's numbers. This keeps your pivot table clean and lets you change the calculation without rebuilding the table.

Troubleshooting calculated fields that don't work

If your calculated field shows an error or a blank column, check that you spelled the field names exactly as they appear in the pivot table. Field names are case-sensitive in some spreadsheets and must match the original column header. If a field name contains spaces or special characters, make sure you included them in the formula.

If the formula works but the numbers look wrong, verify that you are using the correct operation. Division by zero (dividing by a field that contains 0) will show an error in those cells. If you need to avoid this, use a helper column with an IF statement instead: =IF(Denominator=0, 0, Numerator/Denominator).

If the calculated field does not appear in your pivot table after you create it, check that you added it to the Values area of the pivot table editor. In Excel, the field should appear automatically, but in Google Sheets, you may need to drag it into the Values section of the editor panel.

When to use a calculated field versus a regular formula

Use a calculated field when you want the same calculation applied to every row in the pivot table and you do not want to maintain separate formulas. The pivot table updates the calculated field automatically when you refresh the data or change the pivot table structure.

Use a regular formula in a helper column when your calculation is complex, involves text, or depends on conditions. A helper column also works better if you plan to filter or sort by the calculated value, because pivot tables sometimes behave unpredictably when you try to sort by a calculated field.

Use a formula in an adjacent column (outside the pivot table) when you want to keep the pivot table itself straightforward and do not want the calculation to be part of the table structure. This approach is slower to set up but gives you the most control and makes it easiest to change the formula later without rebuilding the table.

Frequently Asked Questions

Can I use a calculated field to divide one pivot table column by another?

Yes. In Excel, type =[Field1]/[Field2]. In Google Sheets, type Field1 / Field2. The pivot table calculates the division for every row. If Field2 contains zeros, those rows will show an error unless you use a helper column with an IF statement to avoid division by zero.

What happens to a calculated field if I refresh the pivot table?

The calculated field stays in place and recalculates based on the new data. If the underlying fields change or new data is added, the calculated field updates automatically. If you delete a field that the calculated field depends on, the calculated field will show an error.

Can I use a calculated field to count or sum values based on a condition?

No. Calculated fields do not support conditional logic like SUMIF or COUNTIF. Use a helper column in your source data instead, add the formula there, and then include that column in the pivot table.

Why does my calculated field show the same value for every row?

This usually means the formula is referencing a total or sum instead of individual field values. Check that you are dividing or subtracting the correct fields. If you want each row to show its own percentage of the total, make sure the denominator is the sum of the entire field, not a single cell.

Can I delete a calculated field without deleting the pivot table?

Yes. In Excel, go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, select the field, and click Delete. In Google Sheets, open the pivot table editor, find the field in the list, click the three-dot menu, and select Delete.