Pivot Table Show Difference

How to show difference between columns in a pivot table, or the percent difference between columns. Written steps, videos, free workbook. No calculated field is needed - use a built-in pivot table feature!

Author: Debra Dalgleish

Introduction

When you first set up a pivot table, the fields that you put into the Values area will automatically have these settings:

• Summarize Values By - Sum or by Count
• Show Values As - No Calculation

In this pivot table, the Units field is in the Values area, showing a "Sum of" for each region and date.

To see the current settings:

• Right-click one of the Value cells
• Point to Summarize Values By or Show Values As

Change the Settings

For a different view of the data in the pivot table, you can change the Summarize Values By, or the Show Values As, settings at any time.

For example,

• Change the Summarize Values By setting to Min or Max, to see the lowest or highest values
• Change the Show Values As setting to Running Total, to see the total accumulate down the column

On this page, we'll take a close look at two of the Show Values As calculations:

• Difference From
• % Difference From

For information on the other settings, use these links:

• To see examples for the Summarize Values By settings, go to the Summary Functions page
• To see all the settings for Show Values As, go to the Show Values As page. These settings are also called Custom Calculations.

Difference From

This short video shows the steps for creating a Difference From custom calculation, and there are written steps below the video.

Difference From - Options

If you want to subtract one pivot table value from another, and show the result as a number, use the Difference From calculation

When setting up the Difference From calculations, you will need to select

• a Base Field -- field name for pivot field in which you want to compare items
• a Base Item -- the pivot item in that field, to use for comparison

The examples below will show how to use those options, for the best results.

Difference From Previous Date

For the first example, the Value field is Units. We'll show that value as a difference from the number of units sold on the Previous Date in the pivot table layout

To set up the Difference From calculation, follow these steps:

1. Right-click one of the Units value cells, and point to Show Values As
2. Click Difference From
3. We want to compare sales per week, so for the Base Field, choose Date.
4. We want to compare each week's sales to the previous week, so for the Base Item, choose (previous)
5. Press OK

NOTE: This is similar to the option for comparing to the Next week.

In the pivot table,

• The row for the first week is empty, because there's no previous week to compare it to.
• For the remaining weeks, there is a number that shows the difference from the number of units sold in the previous week.

Previous Item in Layout

When you use the Previous or Next option for the Base Item setting, the comparison is made to the previous or next item in the pivot table's current layout.

For example, in this screen shot:

• Dates are in the Row area, sorted in Descending order
• Each date's number of units is compared to the week above it in the layout.

So, in this layout, the "Previous" week is actually a later calendar date.

And in the next screen shot

• Dates are in the Row area, sorted in Descending order
• Jan 29th date is hidden.

With this layout, the "Previous" week for the Jan 15th data is Feb 20th.

Difference From Specific Date

Another option is to show each value as a difference from a specific item from the base field.

In this example, there was a clearance sale during the week of Jan. 15th. We'll compare units sold on all the other dates, to that specific date

To set up the Difference From calculation, follow these steps:

1. Right-click one of the Units value cells, and point to Show Values As
• TIP - Right-click on one of the Jan 15th values, and that date will automatically be selected as the item for comparison
2. Click Difference From
3. We want to compare sales per week, so for the Base Field, choose Date.
4. We want to compare each week's sales to the previous week, so for the Base Item, choose the specific item to use for comparison - Jan 15th.
5. Click OK

In the pivot table,

• The row for the week of Jan 15 is empty, because there's it won't be compared to itself
• For the remaining weeks, there is a number that shows the difference from the number of units sold in the previous week.

As expected, every other week had a lower number of units sold, when compared to Jan 15th.

Difference From Specific Region

It's most common to compare values based on a date field, but you can use any row or column field as the base field.

In this example, West is the newest region, so this pivot table compares Unit sales in the other regions, to Unit sales in the West region.

• Base Field: Region
• Base Item: West

In the pivot table, the West column is empty, because it can't be compared to itself.

% Difference from

The % Difference From setting works in the same way as the Difference From setting, except that the result shows the difference as a percentage change, instead of the number.

For the first example, the Value field is Units. We'll show that value as a % Difference From the number of units sold on the Previous Date in the pivot table layout

To set up the % Difference From calculation, follow these steps:

1. Right-click one of the Units value cells, and point to Show Values As
2. Click % Difference From
3. We want to compare sales per week, so for the Base Field, choose Date.
4. We want to compare each week's sales to the previous week, so for the Base Item, choose (previous)
5. Click OK

NOTE: This is similar to the option for comparing to the Next base item.

• Base Field: Date
• Base Item: (previous)

In the pivot table,

• The row for the first week is empty, because there's no previous week to compare it to.
• For Jan 15 and Jan 29, there is a percent difference from the number of units sold on the previous date
• For Feb 20, there is a #NULL! error, because there is no East region data for that date
  • See the suggestions in the Errors section below, for fixing that problem
• For Feb 28, the East cell is empty, because the previous date has no data

Multiple Row Fields

If there are multiple fields in the pivot table Row area, the differences will be calculated on the base field that you select. If necessary, rearrange the row fields, so the differences are easy to understand.

For example, in the two examples shown below:

• Region is the outer Row field
• Date is the inner Row field.

Base Field - Inner

In the screen show shown below, the inner field (Date) is the base field.

In the pivot table,

• Each date is compared to the previous date, within the same region.
• The calculations start over again, at the next region.

Base Field - Outer

In the next screen shot, the outer field (Region) is the base field.

Now, the Show Values As calculations look different in the pivot table,

• Each date is compared to the same date, in the previous region.
• The calculations start over again, at each date.

Remove Difference From Setting

If you want to remove the Difference From or % Difference From setting, follow these steps

• Right-click one of the Value cells
• Point to Show Values As
• Click on No Calculations

Difference From Tips

Here are a few tips to use when working with Difference From and % Difference From in Excel pivot tables.

Headings

To make the data easier to understand, change the column headings from "Sum of Units" to "Units Change", or "% Change".

It also helps to put a descriptive heading on the worksheet, above the pivot table.

Another Copy of Field

Another tip is to add a second copy of the value field to the pivot table, with No Calculation in the Show Values As setting.

That makes it easy to see the original values, and the differences, at a single glance.

Change Pivot Table Layout

Also, when using the Difference From or % Difference From setting, play with the pivot table layout, to find an arrangement that is easy to read and understand.

In this screen shot, the Date and Region fields were switched. With the Date at the top, it seems easier to followt the changes.

Difference From Errors

When you use the Difference From and % Difference From settings, errors might appear in the pivot table. Or, you might see Excel error messages, when you try to make some changes to the pivot table.

The following examples show those errors, why they appear, and what you can do to address them.

-- Errors in Pivot Tables

-- Excel Error Messages

-- Error with AutoShow Macro

Errors in Pivot Table

Some custom calculations might result in errors, like the #NULL! error shown below. This error was caused by a lack of data for East Feb 20.

The pivot table will also show errors if the Base Field or Base Item. used in the Difference From, or % Difference From, is removed from the pivot table.

Option Setting to Hide Errors

In some cases, it's best to see the error values, so you can quickly see that there si a problem with the pivot table, and correct the problem.

However, if you want to hide the error values, follow the steps in this video, to change an option setting. There are written steps below the video.

Hide the Errors

To change the option setting for error values, follow these steps:

• Right-click any cell in the pivot table, and click PivotTable Options
• On the Layout & Format tab, add a check mark to “For error values show”
• In the box, type the text that you want, instead of the errors. For example:
  • Type a space character, to hide the error values
  • Or, type N/A, or other characters, to show that information is not available
• Click OK, to close the Options window.

Excel Error Messages

If you try to sort the values in a pivot table, where Difference From or % Difference From has been applied, this error message might appear:

• AutoSort and AutoShow can't be used with custom calculations that use positional references. Do you want to turn off AutoSort/AutoShow?

This is a very wide message, so I've rearranged the text in this screen shot, so you can read it.

What Error Message Means

That error message appears when you try to sort pivot table values, where the Base Item is Previous or Next - a positional reference.

That message is confusing, because it uses terms from older versions of Excel.

• Custom Calculations - now called Show Values As
• AutoSort - now called Sort Options
• AutoShow - now called Top Ten Filters

Old Settings: AutoSort and AutoShow

In Excel 2003, in the PivotTable Field dialog box, you could click the Advanced button.

In the PivotTable Field Advanced Options dialog box, you could:

• Change the AutoSort settings
• Turn on the Top Ten AutoShow feature
• Select the Show and Using Field options.

Macro Error with AutoShow

In Excel 2003, you could apply the AutoShow settings in a macro. Here is a code snippet from the Excel 2003 version of my Pivot Table Recipes book.

The macro was to show Top 10 iitems over a set amount

    If piCount > 0 Then
        If piCount = pf.PivotItems.Count Then
            MsgBox "All items exceed entered amount"
        Else
            pf.AutoShow xlAutomatic, xlTop, piCount, df
        End If
    Else
        MsgBox "No items exceed entered amount"
    End If

In Excel 2007, the AutoShow feature was replaced by the Top 10 Filter. Now, if you try to run a macro that uses AutoShow, Excel will show an error message:

• Run-time error '1004':
• Application-defined or object-defined error.

To avoid that error, use PivotFilters, instead of AutoShow-- there's a sample macro on the Pivot Table Top 10 Filters page.

Get the Sample File

To see the sample data used for these examples, get the zipped Difference From sample Excel file. The file is in xlsx format, and does not contain any macros.