Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.
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 the example below I show you how to show the Difference From the previous YEAR:
STEP 1: Insert a Pivot able by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet
STEP 2: 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 3: Now click on the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings
STEP 4: Now you need to select the Show Values As tab and from the drop down choose the Difference From
STEP 5: 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 6: 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. Coll hey!
STEP 7: To change the Sum of SALES2 name within the Pivot Table, you need to click on a cell in the Pivot Table which contains Sum of SALES2 and manually make the change and press Enter
STEP 8: 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:
HELPFUL RESOURCE:
