MO-211 Expert Prep
Module 4 · Lesson 4.7

Add Calculated Fields

Edit on GitHub

Calculated fields allow you to perform custom mathematical operations on existing PivotTable data without modifying the original source table. This is essential for calculating metrics like Profit Margin, Tax, or Commission directly within your analysis.

Part 1: Creating a Calculated Field

A calculated field creates a new "virtual" column in your PivotTable based on a formula you define using other fields.

  1. Click anywhere inside the PivotTable.
  2. Go to the PivotTable Analyze tab.
  3. In the Calculations group, click Fields, Items, & Sets > Calculated Field....
  4. Name: Type a unique name for your new field (e.g., Bonus).
  5. Formula: Define your calculation. Use the Fields list to insert existing headers into the formula bar.
    • Example: = Sales * 0.05
  6. Click Add, then OK.

Part 2: Essential Rules and Limitations

For the MO-211 exam, you must understand how PivotTables process these formulas to avoid calculation errors.

  • Order of Operations: Excel always Sums the underlying data before applying the formula.
    • Example: A calculated field for Unit Price (= Revenue / Quantity) will divide the Total Revenue by the Total Quantity, which is correct.
  • Formula Restrictions: You cannot use cell references (like $A$1) or defined names in a calculated field. You can only use the fields listed in the dialog box.
  • Simple Logic only: While basic math (+, -, *, /) is standard, complex array formulas or functions that require ranges (like VLOOKUP or SUMIF) are not supported here.

Part 3: Modifying and Deleting Calculated Fields

If you need to change a formula after it has been created:

  1. Open the Calculated Field dialog box again (PivotTable Analyze > Fields, Items, & Sets > Calculated Field).
  2. Click the drop-down arrow in the Name box and select the field you want to change.
  3. Modify the formula and click Modify.
  4. To remove it completely, select the field name and click Delete.

Technical Checklist for the Exam

Goal Action
Open Calculation Menu PivotTable Analyze > Fields, Items, & Sets
Create New Field Type name > Enter formula > Click Add
Insert Field into Formula Double-click the field name in the "Fields" list
Edit Existing Formula Select name from dropdown > Change formula > Click Modify
Remove Calculated Field Select name from dropdown > Click Delete

Official Resources: