## About this lesson

When you need to display values that are a difference from another value in a PivotTable. This is very useful for showing growth/decline from previous periods.

## Lesson versions

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

2016, **2019**.

## Exercise files

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

Difference From Calculations.xlsx197.3 KB Difference From Calculations - Completed.xlsx

198.8 KB

## Quick reference

### Difference From Calculations

Calculating differences from other fields in PivotTables.

### Where/when to use the technique

When you need to display values that are a difference from another value in a PivotTable. This is very useful for showing growth/decline from previous periods.

### Instructions

#### Accessing difference from calculations

- Locate your PivotTable on the worksheet (not the field well)
- Right-click a cell in the PivotTable column/row you wish to add a difference calculation to
- Either:
- Choose Value Field Settings > Show Values As > Difference From
- Choose Show Values As > Difference From

##### Difference from in columns

- For the Base Field, choose the name of a field you put in the rows area of your Pivot
- For the Base Item:
- To calculate the difference from the previous item, choose (previous)
- To calculate the difference from the next item, choose (next)
- To calculate the difference from a specific item, choose the item

##### Difference from in rows

- For the Base Field, choose the name of a field you put in the column area of your Pivot
- For the Base Item:
- To calculate the difference from the previous item, choose (previous)
- To calculate the difference from the next item, choose (next)
- To calculate the difference from a specific item, choose the item

##### Using % difference from

- Follow the steps for “Difference From” above, but choose “% Difference From” instead of “Difference From” in the “Show Values As” step

#### Hiding error values

- Calculating differences from cells with no values may yield a #NULL error for a subtotal
- To hide #NULL errors:
- Right click the PivotTable
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)

Lesson notes are only available for subscribers.