Calculated Fields

A Calculated Field in a Pivot Table allows you to create a new field using a formula based on existing fields. It performs calculations inside the Pivot Table without changing the original data source.

Why Use Calculated Fields?

• Perform custom calculations
• Create new metrics (e.g., Profit, Percentage)
• Avoid modifying source data
• Enhance Pivot Table analysis
• Build advanced reports

Example Scenario

Suppose your data contains:

• Sales
• Cost

You want to calculate:

Profit = Sales – Cost

Instead of adding a column in the source data, you can create a Calculated Field.

Steps to Create a Calculated Field

1. Click inside the Pivot Table
2. Go to PivotTable Analyze tab
3. Click Fields, Items & Sets
4. Select Calculated Field
5. Enter:
   • Name: Profit
   • Formula: = Sales – Cost
6. Click Add
7. Click OK

Excel will add the new field to the Pivot Table.

Formula Rules

• Use field names (not cell references)
• Do not use individual cell numbers
• Basic operators allowed:
  • • (Addition)
  • – (Subtraction)
    • (Multiplication)
  • / (Division)

Another Example

Calculate Commission (10%)

Formula:
= Sales * 10%

This creates a new calculated field for commission.

Edit or Delete a Calculated Field

1. Go to Fields, Items & Sets
2. Click Calculated Field
3. Select field name
4. Modify formula or click Delete

Important Notes

• Calculated Fields work on summarized data
• They apply to the entire Pivot Table
• Cannot use complex Excel functions like VLOOKUP directly
• Always ensure field names are spelled correctly

Calculated Field vs Calculated Column

Calculated Field	Calculated Column
Created inside Pivot Table	Created in source data
Uses field names	Uses cell formulas
Applies to summary level	Applies to row level

Best Practices

• Keep formulas simple
• Use clear field names
• Double-check results
• Refresh Pivot Table after changes

Conclusion

Calculated Fields allow you to extend the power of Pivot Tables by creating custom calculations without modifying your original data. This feature is essential for advanced reporting and professional data analysis in Excel.