What a calculated field does in a pivot table
A calculated field is a new column you create inside a pivot table that performs math on the data already there. Instead of manually adding numbers in a separate column, you tell the pivot table to multiply, divide, add, or subtract existing fields for every row at once. The calculation updates automatically if your source data changes.
The most common use is turning raw numbers into percentages, ratios, or adjusted totals. For example, if your pivot table shows sales by region and you want to see what percentage each region represents of the total, a calculated field does that when ready. You do not have to create formulas in a separate sheet or manually update them when new data arrives.
Calculated fields work differently in Excel and Google Sheets, and the steps are not the same. Both are straightforward once you know where to find the option, but the menu locations differ.
Key Takeaways
- A calculated field performs math on data already in your pivot table and updates automatically when source data changes.
- In Excel, you access calculated fields through the PivotTable Analyze tab, then Insert Calculated Field.
- In Google Sheets, you create a calculated field by opening the pivot table editor and adding a new field with a custom formula.
- The formula you write references other fields in the pivot table by their names, not cell addresses.
- Calculated fields appear as a new column in your pivot table and can be formatted like any other field.
How to add a calculated field in Excel
Click anywhere inside your pivot table to set up it. The ribbon at the top will show a PivotTable Analyze tab (or PivotTable Tools depending on your Excel version). Click that tab.
In the ribbon, find the Fields, Items & Sets button, then click the dropdown arrow next to it. Select Calculated Field. A dialog box will open asking you to name your new field and write a formula.
Type a name for your calculated field in the Name box — something like "Percentage of Total" or "Adjusted Price". In the Formula box, write the calculation using field names in single quotes. For example, if you want to divide Sales by Total Revenue, you would type 'Sales'/'Total Revenue'. Use the plus sign for addition, minus for subtraction, asterisk for multiplication, and forward slash for division.
Click Add, then OK. The new field appears in your pivot table when ready. You can drag it to a different position in the table layout if you want it in a specific column.
How to add a calculated field in Google Sheets
Right-click on your pivot table and select Edit pivot table. The pivot table editor panel opens on the right side of the screen.
Scroll down in the editor to find the Values section. Click the Add button (the plus sign) next to Values. A new row appears with a dropdown that says Summarize by. Click that dropdown and select Custom formula at the bottom of the list.
A formula box appears. Type your calculation using the field names from your pivot table. For example, if your pivot table has columns for "Revenue" and "Cost", you would type =Revenue-Cost to create a profit field. Google Sheets does not require quotes around field names like Excel does.
Press Enter. A name field appears below the formula. Type a name for your calculated field, then click Insert. The new field is added to your pivot table as a new column.
Common mistakes when writing calculated field formulas
The most frequent error is using cell addresses instead of field names. In a calculated field, you cannot write =A2+B2 the way you would in a regular spreadsheet formula. You must use the actual names of the fields in your pivot table. If your field is called "Q1 Sales", you write 'Q1 Sales' in Excel or Q1 Sales in Google Sheets.
Another common problem is forgetting that calculated fields work on the summarized data in the pivot table, not the raw source data. If your pivot table already groups sales by region and month, a calculated field operates on those grouped totals, not on individual transactions. This is usually what you want, but it means you cannot use a calculated field to filter or reorganize your data — only to do math on what is already displayed.
In Excel, if your formula references a field name with spaces or special characters, you must put the entire name in single quotes. 'Total Revenue' works; Total Revenue without quotes will cause an error. Google Sheets is more forgiving and usually handles spaces without quotes, but using them does not hurt.
Formatting and positioning your calculated field
Once your calculated field appears in the pivot table, you can format it like any other column. Right-click the calculated field column and choose Format Cells (Excel) or use the format menu (Google Sheets) to change the number format to percentage, currency, or decimal places.
In Excel, you can drag the calculated field to a different position in the pivot table layout by dragging it in the Values area of the PivotTable Fields panel. In Google Sheets, the order of fields in the Values section of the editor determines their position in the table — drag them up or down to reorder.
If you want to remove a calculated field, right-click it in the pivot table and select Delete, or find it in the pivot table editor and click the X next to it.
When a calculated field is not the right choice
Calculated fields work best for straightforward math on summarized data. If you need to perform complex logic — like "show this value only if another field is greater than 100" — a calculated field may not be flexible enough. In those cases, you might need to add a helper column to your source data before creating the pivot table, or use a separate formula in another part of your sheet.
Calculated fields also cannot reference cells outside the pivot table or pull in data from other sheets. If you need to multiply your pivot table values by a number stored elsewhere, you have to either add that number to your source data first or use a regular spreadsheet formula in a column next to the pivot table.
If your calculation depends on the order of rows or needs to compare one row to another (like calculating the difference between consecutive months), a calculated field will not work. Those situations require formulas in a separate column.
Frequently Asked Questions
Can I use a calculated field to filter my pivot table?
No. Calculated fields perform math on data that is already in the pivot table, but they cannot be used to hide or show rows based on their values. If you want to show only rows where your calculated field is above a certain number, you need to add a filter to the source data before creating the pivot table, or filter the pivot table manually after the calculated field is created.
What happens to my calculated field if I refresh the pivot table with new data?
The calculated field stays in place and recalculates automatically using the new data. You do not have to recreate it. The formula you wrote remains the same, but the results update to reflect the refreshed source data.
Can I use a calculated field to divide one pivot table value by another?
Yes. If your pivot table shows both "Units Sold" and "Revenue", you can create a calculated field with the formula 'Revenue'/'Units Sold' (Excel) or =Revenue/Units Sold (Google Sheets) to show revenue per unit. The calculated field performs the division on every row of the pivot table.
Why does my calculated field show an error in some cells?
This usually happens when the formula tries to divide by zero or when a field name is misspelled. Check that your field names match exactly what appears in the pivot table, including spaces and capitalization. In Excel, make sure field names with spaces are wrapped in single quotes.
Can I create a calculated field that references another calculated field?
In Excel, yes — you can write a formula that uses a field you created as a calculated field. In Google Sheets, this is not supported. If you need to build calculations on top of each other, create the first calculated field, then use a regular spreadsheet formula in a separate column for the second calculation.