Excel Tip: Using GETPIVOTDATA (part 1)

As regular readers will know, I am a huge fan of pivot tables and find them an incredibly useful and powerful feature of Excel. If you don't know how to create a Pivot Table, take a look at my free video.

For all of the power of Pivot Tables, sometimes the final format is not want you want and you need to use that data elsewhere. Management Accounts are a prime example of this - a pivot table can give you the numbers but you usually want these to then populate an ordinary Excel template, set out in your Management Accounts format. This is where the GETPIVOTDATA formula comes in.

GETPIVOTDATA allows you to specify the data you want to pull from the Pivot Table. The format is as follows:
=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
Where:
Name of data field to return is a text field, being the name of the pivot table field as it appears on the pivot table.
location of pivot table is a reference to a cell anywhere within the pivot table, it is often best to use the top left cell of the main body of the pivot table, as this will remain in a constant position no matter how much the data changes.
field1, item1,field2,item2,etc. are pairs of field and item names from the row, column or page (filter) areas of the pivot table. You can use as many pairs as you like. The field name comes from the original column heading in the data, the item name is the entry in that column that creates a unique row, column or page in the pivot table.

I think an example might be useful here:

To return the figure 15,813 for Matthew's sales for the South East, the formula would be:

=GETPIVOTDATA("Sum of Net",A3,"Salesman","Matthew","Region","South East")

This is useful when you are wanting to report one number, however you can really make use of this formula when you realise that any of the text arguments (in inverted commas) can be replaced with references to cells where the text can be found. You can then use this to populate any report layout, but I will leave this for a later post.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.