PivotTables are a treasure trove of features and one that has been brought out of the dungeons in more recent Excel versions is ‘Show Values As’.
In Excel 2007 it was hidden 3 clicks away in the Value Field Settings dialog box (see below):
Then in Excel 2010 it was given prime position in the right-click Context Menu, and rightly so:
In Excel 2010 there are also some new functions like Rank and % Running Total In to name a couple.
In this tutorial I’m going to show you how to calculate Year on Year variances, both absolute values using Show Values as > Difference From:
And as a percentage using Show Values As >% Difference From:
Here is a sample of my data:
Here is my PivotTable with Order Amount summarised by Ship Mode and Year:
Now I want to add columns for the year on year change (YoY Change).
Step 1: Drag another instance of the Order Amount field to the Values area in the field list, so now you have it there twice:
Step 2: In the PivotTable right-click any of the cells containing the second Sum of Order Amount > Show Values as > Difference From:
Step 3: Choose Years as the Base Field and Previous as the Base Item in the dialog box that pops up:
Step 4: Next we can rename the Column Labels so they’re more meaningful. To do this just type straight in one of the cells containing the column label you want to change, like I’ve done in cell C5 below (Tip: the name must not be the same as any of the source data column headers):
This will update all of the column labels and the name in the field list:
Voila, your PivotTable now shows the year on year change in Order Amounts by Shipping Mode:
Year on Year Percentage Change
We can also show values as % Difference From. The steps are the same except in step 2 you choose % Difference From:
Tip: You don’t need to have the Order Amount column in your PivotTable to display the YoY Change:
Since the YoY Change is not calculated using the actual values you see in the PivotTable, it is calculating using the source data.
Which means you could plot just the Year on Year Change in a PivotChart if you wanted.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.