There are two broad ways to achieve this without VBA. The first is to create a 'helper column' next to each pivot table. In that column, starting at row 1 and dragged down, type (Assuming the name is in column A and the ID is in column B)
=A1&B1Then with that done in both tables, use the MATCH function to determine if any of the NAME&ID strings matches, like so, starting in cell D1 on sheet1 (assuming the NAME&ID string is in column C):
=match(C1,Sheet2!C:C,0)If it shows a number, that means there was a match found in the other sheet. If it shows N/A#, then there was no match found. To clean it up a bit, wrap that in the function ISERROR, like so:
=iserror(match(C1,Sheet2!C:C,0))If it says TRUE in a cell, that means the MATCH function found no matching data, which creates an error. If it says FALSE, that means there was a match found, meaning there is a duplicate value.
Alternatively, you can do this without creating helper columns, but I don't recommend if you are unfamiliar with array formulas.
Put this formula in Sheet1, cell C1, and drag down (when the formula is typed, confirm by pressing CTRL + SHIFT + ENTER, instead of just ENTER):
=iserror(match(A1&B1,sheet2!A1:A100&sheet2!B1:B100))Note that with an array function you must specify how far down your column goes, or performance will lag significantly.
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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create Tables in Excel
Excel: How to Group Values in Pivot Table by Range
How to Group by Month and Year in Pivot Table in Excel
Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the Calculate Difference between Two Pivot Tables option.
You can show the values as the Difference From previous months, years, day, etc. This is just great when your boss asks you how you are tracking to the previous months, years, days.
In this tutorial you will learn how to show:
Let’s look at both of these methods one-by-one!
Calculate Difference Between Two Columns
You can use the Pivot Table difference between columns function to calculate Year on Year variance in absolute values. Below is the data that you will be using:
In the example below I show you how to show the Difference From the previous YEAR:
Watch it on YouTube and give it a thumbs-up!
Follow the step-by-step tutorial on How to Calculate Difference between Two Pivot Tables and download this Excel workbook to practice along:
STEP 1: Insert a Pivot Table by clicking on your data and going to Insert > Pivot Table
STEP 2:In the Create PivotTable dialog box, Select Table range and then click on New Worksheet. Click OK.
STEP 3: In the ROWS you have to put the Months field, in the COLUMNS the Years field and in the VALUES area the Sales field twice, I explain why below:
STEP 4: Now click on the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings
STEP 5: Now you need to select the Show Values As tab and from the drop-down choose the Difference From
STEP 6: You need to select the Base Item: (previous) and Base Field: Financial Year and press OK. So it will read the “Difference from the previous Financial Year”
STEP 7: To format the values you need to select the Pivot Table and go to Pivot Table Tools > Analyze/Options > Select > Entire Pivot Table
Then you need to once again go to Pivot Table Tools > Analyze/Options > Select but this time select the Values
Now press CTRL+1 to bring up the Format Cells dialogue box and make your formatting changes within here and press OK.
NB: This will fix the number format permanently and any new field that get added into the Pivot Table will have this format.
STEP 8: To change the Sum of SALES2 name within the Pivot Table, you need to click on a cell in the Pivot Table that contains Sum of SALES2 and manually make the change, and press Enter
STEP 9: You need to select the whole column that contains the empty values and Right Click and select Hide
You now have your Pivot Table, all formatted and showing the Difference from the previous Year:
This will provide you with a Pivot Table year over year comparison in absolute terms!
Calculate % Difference Between Two Columns
You can also show values as % Difference From. This will display the variance between two years in form of percentage instead of absolute value!
Follow the steps below for pivot table calculated field difference between two columns:
STEP 1: Insert a Pivot Table by clicking on your data and going to Insert > Pivot Table
STEP 2:In the Create PivotTable dialog box, Select Table range and then click on New Worksheet. Click OK.
STEP 3: Drag and down the following fields in the PivotTable Field dialog box:
- Sales Month in Rows Area
- Financial Year in Columns Area
- Sales in Values Area (Twice)
STEP 4: Right Click on the Sum of Sales2 column and select Show Value As > % Difference From.
STEP 5: In the Show Value As dialog box, Select Financial Year as Base Field and (previous) as Base Item. Click OK.
Sum of Sales2 will now display the difference in sales between 2 years in percentage!
STEP 6: Hide the blank Column C. Select Column C > Right Click and Select Hide.
And Voila! Your Pivot Table difference between two columns is now ready!
HELPFUL RESOURCE:
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!