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:

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

 

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!

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

Follow the step-by-step tutorial on How to Calculate Difference between Two Pivot Tables and download this Excel workbook to practice along:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Insert a Pivot Table by clicking on your data and going to Insert > Pivot Table 

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

 

STEP 2:In the Create PivotTable dialog box, Select Table range and then click on New Worksheet. Click OK.

Show The Difference From Previous Years With Excel Pivot Tables

 

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:

Show The Difference From Previous Years With Excel Pivot Tables

 

STEP 4: Now click on the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings

Show The Difference From Previous Years With Excel Pivot Tables

 

STEP 5: Now you need to select the Show Values As tab and from the drop-down choose the Difference From

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

show values as

 

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

select entire pivot table

Then you need to once again go to Pivot Table Tools > Analyze/Options > Select but this time select the Values

select 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.

format cells

 

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

name change

 

STEP 9: You need to select the whole column that contains the empty values and Right Click and select Hide

hide column

 

You now have your Pivot Table, all formatted and showing the Difference from the previous Year:

Calculate Difference between Two Pivot Tables

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 

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

 

STEP 2:In the Create PivotTable dialog box, Select Table range and then click on New Worksheet. Click OK.

Show The Difference From Previous Years With Excel Pivot Tables

 

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)

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

STEP 4: Right Click on the Sum of Sales2 column and select Show Value As > % Difference From.

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

STEP 5: In the Show Value As dialog box, Select Financial Year as Base Field and (previous) as Base Item. Click OK.

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

Sum of Sales2 will now display the difference in sales between 2 years in percentage!

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

STEP 6: Hide the blank Column C. Select Column C > Right Click and Select Hide.

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

 

And Voila! Your Pivot Table difference between two columns is now ready!

Show The Difference From Previous Years With Excel Pivot Tables | MyExcelOnline

 

 

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

728x90

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn