Table of Contents
Excel’s Pivot Tables are an incredibly powerful tool for summarising and comparing data. To calculate the difference between two Pivot Tables, you can use the built-in formulas for subtraction or subtraction of values in the pivot tables. You can also use the calculated field feature to create a column that subtracts a value from one table from the same value in the other. This allows you to quickly and easily compare the differences between the two tables.
The following example shows how to calculate the difference between two pivot tables in Excel.
Example: Calculate Difference Between Two Pivot Tables
Suppose we have the following two pivot tables that show the total sales and returns for different stores during two different years:
Suppose we would like to calculate the difference in the Sum of Sales and Sum of Returns columns between the two pivot tables.
To calculate the difference in the Sum of Sales columns between the two pivot tables for just store A, we can type the following formula:
=GETPIVOTDATA("Sum of Sales",$E$2,"Team","A")-GETPIVOTDATA("Sum of Sales",$E$10,"Team","A")
The following screenshot shows how to use this formula in practice:
Notice that this formula correctly calculates the difference between the Sum of Sales values between 2020 and 2019 for store A.
Difference = 38 – 16 = 22.
We can change the values in the GETPIVOTDATA formula to calculate the difference between each corresponding value in the two pivot tables:
Note: You can find the complete documentation for the GETPIVOTDATA function in Excel .