Category Archives: pivot table

03/10/2011
Leave a comment
getpivotdata, lookup, pivot table

GETPIVOTDATA

GETPIVOTDATA is a fabulous little function! Try it out, get used to it, learn its quirks and then use it. Frankly it’s about as exciting as worksheet functions get. Check it out for yourself!

You’ve built a good spreadsheet, maybe drawn a few graphs and then added a handy pivot table summarising all the stuff you need to know. Then you realise you want to pull some numbers out of that pivot table. So you select an empty cell, press the equals sign and then click on the cell in the pivot table that you want to use. What happens? You end up with a formula like this ..

=GETPIVOTDATA(“Sales”,$A$3,”Month”,”Jul”)

It’s strange but people seem scared of this. Even without seeing the spreadsheet you can probably guess that it’s looking up sales for the month of July! However people seem to prefer something like this instead:

=B5

..where B5 is the current cell in the pivot table containing sales for July. So why is the long-winded version so much better? Well a few reasons really:

  1. Pivot tables are very user-friendly. It’s very easy for people to play with them. That means users will reorganise columns and rows, add things and take things away. In fact you can almost guarantee that whatever it was you were originally interested in is going to move. With GETPIVOTDATA that doesn’t matter.
  2. Pivot tables can be filtered. If I used filters to remove “Jul” from my pivot table in the above example then I would want to be warned that this data wasn’t available any more. GETPIVOTDATA does that too with a #REF! error. If your formula is =B5 then whatever is in B5 will be used, whether it’s right or not.
  3. Then when you amend the filter to put “Jul” back in, how do you know your answer is going to appear in B5 again? What if the user has added some more data? Guess what – GETPIVOTDATA deals with it.
  4. You can add multiple conditions in GETPIVOTDATA. I often see people creating all sorts of unique identifiers by tacking things like surname, initials, date of birth, etc together into one “word”. Why bother when GETPIVOTDATA makes it so easy to express in a logical way?

Get to know GETPIVOTDATA. It can be a very good friend.