Locked lesson.

## 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.xlsx193 KB Percentage Difference From Calculations - Completed.xlsx

195.8 KB

## Quick reference

### Topic

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

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