## About this lesson

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

## Exercise files

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

Difference From Calculations.xlsx193.4 KB Difference From Calculations - Completed.xlsx

195.4 KB

## Quick reference

### Topic

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 Pivot. 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 Pivot
- Choose PivotTable Options
- Check the box next to “For error values show:” (and leave the field blank)

Lesson notes are only available for subscribers.