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
Year from the Base Field and selected as 2007 as the Base item.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.
Try concatenating month and year and have the year first. For example, to compare March 2012 to March 2011 concatenate columns such as 201203 and 201103- that way the columns will be in order.
ReplyDelete