## About this lesson

When you need to display values that are a percentage of difference from another value in a Pivot Table. 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/365**.

## Exercise files

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

Percentage Difference From Calculations.xlsx197.1 KB Percentage Difference From Calculations - Completed.xlsx

198.5 KB

## Quick reference

### Percentage Difference From Calculations

Calculating percentage differences from other fields in PivotTables.

### Where/when to use the technique

When you need to display values that are a percentage of 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 PivotTable
- 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 PivotTable
- 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

#### 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.