Locked lesson.

## About this lesson

Learn to create and modify basic calculated fields for PivotTables.

## Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Calculated Fields in Pivot Tables.xlsx20.1 KB Calculated Fields in Pivot Tables - Completed.xlsx

35 KB Calculated Fields in PivotTables - Extra Practice.xlsx

38.8 KB

## Quick reference

### Topic

Creating and modifying basic calculated fields for PivotTables in Excel.

### When to use

Calculated fields are used to build mathematical and logical formulas in a PivotTable, rather than relying on adding those calculations to the data source. This is useful, as there are occasions where we cannot modify our source data but need to add extra calculations to our PivotTables.

### Instructions

#### Setting the stage

- Create a PivotTable with the following setup:
- Rows: DRG Definition
- Values: Total Medical Payment, Total Discharges
- Format the Total Medical payments to include 0 decimals
- Adjust the table headers so that:
- Sum of Total Medical Payment become Total Payments
- Sum of Total Discharges becomes Discharges

#### Creating Calculated Fields

- Select a cell in the PivotTable
- Go to PivotTable Tools > Analyze > Fields, Items & Sets > Calculated Field
- In the Name field, replace Field1 with “Payment per Patient”
- Highlight the 0 in the formula field, scroll down the Fields list
- Double click Total Payments, type /, double click Discharges
- Click Add, then OK, and notice that the new field is on the PivotTable

#### Modifying Calculated Fields

- Go back to Fields, Items & Sets > Calculated Field
- Choose “Payment per Patient” from the Name drop down
- Change the formula to =ROUND(‘Total Payments’/’Discharges’,0)
- Click Modify, then OK
- The PivotTable has updated to show your revised formula

Lesson notes are only available for subscribers.