Calculate Differences in a Pivot Table

A pivot table is a great way to summarize data, and most of the time you probably use a Sum or Count function for the values. For example, in the pivot table shown below, the regional sales are totaled for each week. We can also use a built-in feature to calculate differences in a pivot table.

Add Custom Calculations

Instead of settling for a basic sum or count, you can get fancier results by using the built-in Custom Calculations.

• Right-click on a value cell in a pivot table
• Then click Show Values As, to see a list of the custom calculations that you can use. This list is from Excel 2010, and there is a slightly shorter list in older versions of Excel.

Calculate the Difference

One of my favourite custom calculations is Difference From. It subtracts one pivot table value from another, and shows the result.

In the pivot table below, two copies of the Units field have been added to the pivot table.

• The heading in the original Units field has been changed to Units Sold.
• The second Units field is showing the difference from each week’s sales to the previous week’s sales.

Custom Calculation Tips

If you’re using custom calculations, here are a few tips to make them more effective.

• To make the data easier to understand, you can change the headings. For example, change from “Sum of Units” to “Units Diff”.
• You can add another copy of the Units field to the pivot table, and show both the total sales and difference in weekly sales
• Experiment with the pivot table layout, to find the arrangement that will be easiest to read and understand.

Watch the Difference From Video

To see the steps for creating a Difference From custom calculation, watch this short video.

Download the Sample File

To test the Difference From custom calculation, you can download the sample file from the Custom Calculations page on my Contextures website.

____________________