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.


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, please watch this short video tutorial.


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.


You may also like...

6 Responses

  1. Sergio Cantu says:

    How can I sum or total the Difference From Previous week value???

  2. Walaa says:

    Hi, I would like to ask about option i made before but now i forgot it, hot to subtract a field from grand total in pivot table.


  3. Alessandro says:

    Hi! I tried both in office 2013 and 2016 and it seems to me that Excel doesn’t calculater % difference from (previous) for subtotals. Am I doing something wrong? If not, is there a workaround? thanks!

  4. Alessandro says:

    and oups, sorry, I didn’t mean subtotals. I mean:
    Rows: continent,country
    Columns: Quarter, reseller/direct
    values: Revenue, RGrowth (Show value as % difference from previous quarter)

    so for every line I have I have the % difference from correct for revenue growth for partner, for direct but not for its sum. Any way I can attach a screenshot?

    • Alessandro says:

      Hi Debra, just found your video “Subtotals for Calculated Fields”, thanks for this! but in my case the custom section is grayed out :(

Leave a Reply

Your email address will not be published. Required fields are marked *