A calculated field lets you create a new column in your pivot table using math based on existing columns

A calculated field is a column you add to a pivot table that performs a calculation on the data already there. Instead of manually typing formulas into each row, you define the calculation once, and the pivot table applies it to every row automatically. This is useful when you need to show percentages, differences, ratios, or any other math based on your existing columns.

The steps differ slightly between Excel and Google Sheets, but the concept is the same: you tell the pivot table what calculation to perform, give it a name, and it appears as a new column you can use like any other field.

Key Takeaways

  • A calculated field performs math on existing pivot table columns and appears as a new column you can sort and filter like any other field.
  • In Excel, you create a calculated field through the PivotTable Analyze tab, while in Google Sheets you use the pivot table editor panel on the right side.
  • The formula you write references the names of existing columns in your pivot table, not cell references like A1 or B2.
  • Once created, a calculated field updates automatically when your source data changes, but you cannot place it in the Values area of the pivot table layout.

How to add a calculated field in Excel

Open your pivot table in Excel. Click anywhere inside the pivot table to set up it. At the top of the screen, click the PivotTable Analyze tab (or PivotTable Tools depending on your Excel version). In the ribbon, look for a button labeled Fields, Items, & Sets or Calculations, then select Calculated Field.

A dialog box will open. In the Name field, type a name for your new column — something like "Profit Margin" or "Total Cost". In the Formula field, type your calculation. Use the names of your existing columns in the formula, not cell letters. For example, if your pivot table has columns called "Revenue" and "Expenses", you would type =Revenue-Expenses to create a profit column. Click OK and the new field appears in your pivot table.

How to add a calculated field in Google Sheets

Open your pivot table in Google Sheets. On the right side of the screen, you should see the Pivot table editor panel. If it is not visible, click anywhere in the pivot table and it will appear. Scroll down in the editor panel until you see a section labeled Values. Below that section, click Add a value.

A new row will appear. Click the dropdown that says Summarize by and select Custom formula from the bottom of the list. In the formula field that appears, type your calculation using the names of your existing columns. For example, =Revenue/Total Sales to show each item as a percentage of total sales. Google Sheets will ask you to name the field — type something descriptive like "Percentage of Total". The calculated field now appears in your pivot table.

Writing the formula for your calculated field

The most important difference between a calculated field formula and a regular spreadsheet formula is that you use column names, not cell references. If your pivot table has a column called "Units Sold" and another called "Price Per Unit", you write =Units Sold*Price Per Unit, not =A2*B2. The pivot table handles explore this to every row.

You can use basic math operators: + for addition, - for subtraction, * for multiplication, and / for division. You can also use parentheses to control the order of operations, just like in regular math. For example, =(Revenue-Expenses)/Revenue calculates profit margin by subtracting expenses from revenue, then dividing by revenue. If a column name has spaces in it, some versions of Excel require you to wrap the name in single quotes, like ='Total Sales'.

Common calculated fields and their formulas

A profit or margin calculation subtracts costs from revenue: =Revenue-Expenses or =(Revenue-Expenses)/Revenue for profit margin as a percentage. A percentage of total divides one column by the sum of all values in that column: =Sales/Total Sales. A year-over-year change compares two time periods: =(2024 Sales-2023 Sales)/2023 Sales if your pivot table has separate columns for each year.

A ratio or comparison divides one metric by another: =Actual/Budget to show how actual spending compares to what was planned. A weighted average multiplies values by weights and divides by the sum of weights: =(Score*Weight)/Total Weight. These are starting points — your actual formula depends on what columns your pivot table contains and what question you are trying to answer.

What happens when your source data changes

If you update the data in the original spreadsheet that feeds your pivot table, the pivot table itself updates when you refresh it. The calculated field updates at the same time, because it is based on the columns in the pivot table, not on fixed numbers. In Excel, right-click the pivot table and select Refresh. In Google Sheets, the pivot table updates automatically.

If you add a new row to your source data and refresh the pivot table, the calculated field automatically applies to that new row as well. You do not have to recreate the formula or copy it down manually.

Limitations and when to use a regular formula instead

A calculated field cannot be placed in the Values area of your pivot table layout — it can only appear as a regular column. This means you cannot sum or average a calculated field the way you can with other columns. If you need to aggregate a calculation (for example, sum up all the profit margins), you may need to add a helper column to your original data instead, then include that column in your pivot table.

Calculated fields also cannot reference cells outside the pivot table or use functions like VLOOKUP. If your calculation needs to pull data from another table or sheet, add that data to your source spreadsheet first, then create the pivot table. For very complex calculations, sometimes it is faster to create a regular formula in a column next to your pivot table rather than wrestling with calculated field syntax.

Frequently Asked Questions

Can I edit or delete a calculated field after I create it?

Yes. In Excel, go back to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, select the field you want to change from the list, edit the formula, and click OK. In Google Sheets, click the three dots next to the calculated field in the Values section of the pivot table editor and select Edit or Remove.

Why does my calculated field show an error?

The most common cause is a typo in the column name. Make sure the name in your formula matches exactly what the column is called in your pivot table, including capitalization and spaces. If a column name has spaces, try wrapping it in single quotes like ='Column Name'. Also check that you are not trying to divide by zero or reference a column that does not exist in the pivot table.

Can I use a calculated field to show a percentage of the total?

Yes, but only if your pivot table includes a "Total" or "Grand Total" column. If it does, use the formula =Column Name/Total. If your pivot table does not show totals, you may need to adjust your pivot table settings to display them first, or calculate the percentage manually in a helper column in your source data.

What is the difference between a calculated field and a calculated item?

A calculated field creates a new column based on math from existing columns. A calculated item (available in Excel only) creates a new row or category within an existing field. For most tasks, you want a calculated field. Calculated items are more specialized and used less often.