Best Microsoft Excel Bloggers

Tuesday, March 2, 2010

Pivot Tables- Show Differences

PIVOT TABLES - SHOW DIFFERENCES
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.
 

9 comments:

  1. Hi,
    What about if you have a Year and month column and you want to compare the current period data with the previous period data? It works well when the comparison is within the year itself. However, the issue arises when the data stretches across the year. How can this issue be resolved?

    ReplyDelete
  2. 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
  3. Is there an easy way of then removing the blank column for your base year (e.g. 2007 for your example)?

    ReplyDelete
  4. Unfortunately I do not know of a way. If you click on the column drop-down arrow beside Year and select delesect that year, all the calculations change to #N/A. You can right-click on the the year 2007 and move the field item up or down within the pivot table though.

    ReplyDelete
  5. Is there a way to apply the difference from feature to multiple fields at once? For instance, if I have sales, orders, and # of customers, can I apply the difference from option to all three fields at once, or do you have to do each one individually?

    ReplyDelete
  6. Sorry- I do not believe that you can apply the differences to multiple fields at the same time unless you were using one as the base field and that would just be two fields.

    ReplyDelete
    Replies
    1. No worries. Thanks for getting back to me.

      Delete
  7. Hi, I keep doing the same thing on top and I keep getting an error #N/A.

    right click on pivot value column > summary data by > more options > tab: show values as > difference > basefield (date) > base item (previous)

    Always getting an error, same steps as posted. using excel 07

    could you assist? thank you.

    ReplyDelete
  8. Hi Cesar-
    If your pivot table had row heading of salesperson and column heading of years and the data was sales then you would need to select the row or column headings in "Difference from". If you select the data values such as sales, you will get #N/A.

    ReplyDelete


Ms. Excel- Resident Excel Geek