Everyone knows how absolutely cool Pivot Tables are but not everyone is aware of some 'hidden"pivot table features. Today I wanted to talk about comparing values in a pivot table. Frequently, you need to compare against a base budget number or you may want to compare last year against this year. The good news is that this is very easy to do with a pivot table.
Create your pivot table as you normally would
- Right-click on the pivot table
- Select Value Field Setting
- Click on the Show Value As tab
- Click the drop-down arrow and make your selection (% difference from, difference from,% of total)
In the example, below, I changed Normal to Difference from and then selected
The field you want to base the difference on must be included in the pivot table.Click OK
The resulting pivot table shows how 2008 and 2009 compare against 2007. Since 2007 is the base no numbers are displayed in the 2007 column.
While numbers show a lot, sometimes percentages are even more useful.
Below is the same pivot table only I selected % Difference from 2007.