How to Use Calculated Fields
In my pivot table basics tutorial we created a calculated field in the pivot cache by adding a calculated column to the pivot table’s underlying data source. Another method for creating a calculated field (based on existing pivot table fields) is to use the pivot table’s ‘Add Calculated Field’ functionality.
Watch the video below to see a demonstration for adding a calculated field. Get the file used in the video here.
Summary Steps for Creating a Calculated Field in a Pivot Table
- Click somewhere on the pivot table so Excel shows the Analyze menu option above the ribbon
- Click on ‘Fields/Items & Sets’ then ‘Calculated Field’ to bring up the ‘Insert Calculated Field’ dialogue box
- Name your new field in the Name box
- Type your calculated expression in the Formula box using existing Fields and mathematical expressions, e.g. =Cashflow2 – CR*2 NOTE: if your fields have spaces in their names use ” brackets, e.g. (=’Cash Flow’ – CR*2)
- Click ‘OK’ and BAM! Excel will add the new field to your table for you
You can even create calculated fields using previously created calculated fields.
The Uses for Calculated Fields
Use a pivot table calculated field when you want your underlying pivot table dataset to remain intact but you would like the flexibility of building calculations into your fields—they can be especially useful for variance analysis in finance, or—if you’re a bit more advanced—for building pivot tables using VBA.
NOTE: If you are trying to add an item to an existing pivot table field see my article on adding calculated pivot items.