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
When you first set up a pivot table, the fields that you put into the Values area will automatically have these settings:
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:
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,
On this page, we'll take a close look at two of the Show Values As calculations:
For information on the other settings, use these links:
This short video shows the steps for creating a Difference From custom calculation, and there are written steps below the video.
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
The examples below will show how to use those options, for the best results.
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:
NOTE: This is similar to the option for comparing to the Next week.
In the pivot table,
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:
So, in this layout, the "Previous" week is actually a later calendar date.
And in the next screen shot
With this layout, the "Previous" week for the Jan 15th data is Feb 20th.
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:
In the pivot table,
As expected, every other week had a lower number of units sold, when compared to Jan 15th.
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.
In the pivot table, the West column is empty, because it can't be compared to itself.
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:
NOTE: This is similar to the option for comparing to the Next base item.
In the pivot table,
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:
In the screen show shown below, the inner field (Date) is the base field.
In the pivot table,
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,
If you want to remove the Difference From or % Difference From setting, follow these steps
Here are a few tips to use when working with Difference From and % Difference From in Excel pivot tables.
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 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.
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.
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.
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.
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:
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:
This is a very wide message, so I've rearranged the text in this screen shot, so you can read it.
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.
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:
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:
To avoid that error, use PivotFilters, instead of AutoShow-- there's a sample macro on the Pivot Table Top 10 Filters page.
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.
Last updated: May 17, 2023 3:46 PM