What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Why does my Pivot Table show wrong totals?

Refresh the pivot, confirm the source includes new rows (use an Excel Table), and check that numbers are not stored as text.

Tutorials - 2026-01-21

Pivot Table Calculated Fields: Create Custom Calculations

Learn to create calculated fields in Pivot Tables. Add profit margins, growth rates, and custom metrics without changing your source data.

What Are Calculated Fields?

Calculated fields let you create new metrics in your Pivot Table using formulas—without modifying your source data. They're perfect for profit margins, growth rates, commissions, and other derived values. For the complete guide, see Excel Pivot Tables: Complete Guide.

When to Use Calculated Fields

Use Calculated Fields For Don't Use For Profit Margin (Profit/Sales) Row-by-row calculations Commission (Sales × Rate) Complex IF logic Average Price (Sales/Quantity) Referencing external data Growth Rate Calculations needing cell references Key limitation: Calculated fields work on the SUM of values, not individual rows.

Creating a Calculated Field

Step-by-Step \\\ Click anywhere in your Pivot Table Go to PivotTable Analyze tab Click Fields, Items & Sets → Calculated Field Enter a Name for your field Build your Formula using field names Click OK \\\ Example: Profit Margin If your data has "Revenue" and "Cost" fields: \\\ Name: Profit Margin Formula: = (Revenue - Cost) / Revenue \\\ The new "Profit Margin" field appears in your field list!

Common Calculated Field Formulas

Profit Margin \\\ Name: Profit Margin Formula: = Profit / Revenue ' Or if you don't have a Profit field: Formula: = (Revenue - Cost) / Revenue \\\ Average Price per Unit \\\ Name: Avg Price Formula: = Sales / Quantity \\\ Commission (10%) \\\ Name: Commission Formula: = Sales * 0.10 \\\ Markup Percentage \\\ Name: Markup Formula: = (Price - Cost) / Cost \\\ Contribution Margin \\\ Name: Contribution Margin Formula: = Revenue - Variable_Costs \\\ Discount Amount \\\ Name: Discount Amount Formula: = List_Price - Sale_Price \\\

Using Fields in Formulas

Referencing Fields In the formula box, you can: Type field names directly Double-click fields from the list to insert them Use operators: + - * / ^ ( ) Field Names with Spaces If your field name has spaces, just type it as-is: \\\ Formula: = Total Sales / Number of Orders \\\ Excel handles the spaces automatically.

Editing Calculated Fields

Modify an Existing Field \\\ Click in Pivot Table PivotTable Analyze → Fields, Items & Sets → Calculated Field Select the field from the Name dropdown Edit the formula Click Modify \\\ Delete a Calculated Field \\\ Open Calculated Field dialog Select the field from Name dropdown Click Delete \\\

Formatting Calculated Fields

Calculated fields often need formatting (percentages, currency, etc.): \\\ Click any cell showing the calculated field Right-click → Number Format Choose: Percentage, Currency, Number, etc. Set decimal places Click OK \\\

Real-World Examples

Example 1: Sales Analysis Source data fields: Revenue, Cost, Quantity Calculated fields to add: \\\ ' Gross Profit = Revenue - Cost ' Profit Margin % = (Revenue - Cost) / Revenue ' Average Order Value = Revenue / Quantity \\\ Example 2: HR Metrics Source data fields: Salary, Bonus, Hours_Worked Calculated fields: \\\ ' Total Compensation = Salary + Bonus ' Hourly Rate = Salary / Hours_Worked ' Bonus Percentage = Bonus / Salary \\\ Example 3: Inventory Source data fields: Units, Unit_Cost, Unit_Price Calculated fields: \\\ ' Total Value = Units * Unit_Cost ' Potential Revenue = Units * Unit_Price ' Potential Profit = Units * (Unit_Price - Unit_Cost) \\\

Important Limitations

Calculations Use SUMs Calculated fields operate on the SUM of each field, not row-by-row. Example problem: \\\ ' You want average price per transaction ' Data: 3 sales of $100, $200, $300 (quantities: 10, 10, 10) ' You might expect: (100/10 + 200/10 + 300/10) / 3 = $20 ' But calculated field gives: (100+200+300) / (10+10+10) = $20 ' In this case it works, but not always! \\\ No IF Statements You cannot use IF, AND, OR, or other logical functions in calculated fields. Workaround: Add a helper column in your source data. No Cell References You cannot reference specific cells (like A1 or $B$2). Workaround: Add the value as a field in your source data. Limited Functions Only basic math operators work: + - * / ^ ( ) Not supported: SUMIF, VLOOKUP, IF, etc.

Calculated Fields vs Calculated Items

Calculated Fields Calculated Items Create new measures Create new categories Work across all data Work within a field Example: Profit Margin Example: "East + West = Total" More commonly used Less commonly used

Troubleshooting

"Formula is Invalid" Cause: Typo in field name or invalid operator Fix: Double-click fields from the list instead of typing Results Look Wrong Cause: Calculated fields sum first, then calculate Fix: Verify the math makes sense for aggregated data Field Not Showing Cause: Field was created but not added to Values Fix: Drag the calculated field to the Values area #DIV/0! Errors Cause: Dividing by a field that sums to zero Fix: Filter out zero values or handle in source data

Best Practices

Name fields clearly — "Profit_Margin" not "Field1" Document your formulas — Keep a reference sheet Test with known data — Verify calculations are correct Format appropriately — Percentages as %, currency as $ Consider alternatives — Sometimes a source data column is better

Excel Pivot Tables: Complete Guide How to Create Your First Pivot Table Pivot Table vs SUMIFS Common Pivot Table Mistakes

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons \nPivot help: Pivot tables guide · Pivot not updating · Pivot mistakes

Frequently asked questions

What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.
Why does my Pivot Table show wrong totals? Refresh the pivot, confirm the source includes new rows (use an Excel Table), and check that numbers are not stored as text.