Excel Tips & Tricks #471 – Refreshing GETPIVOTDATA

This was first covered in Tip #333. This tip builds on PivotTables - if you need a refresher, then check out Tip #190

GETPIVOTDATA is a function that allows you to extract information from a pivot table. If you try and make a direct cell reference to a value inside a PivotTable, then Excel usually automatically writes a GETPIVOTDATA for you instead:

This behaviour is automatic and for good reason. Unlike normal Excel cells, values in PivotTables can move around as they are updated and refreshed - PivotTables can add new rows or columns, for example, as the underlying data is added to or changed. Therefore, a traditional cell reference will often go wrong - all it would take is adding one more row to the data, for example, to move all the values down and make a reference pointing to the wrong value. However, a GETPIVOTDATA is based on the actual field name, and so will persist even under these circumstances.
Let's break down the synxtax:

=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

Data_field is the value field you are interested in. This is a text value, so it has to be either entered between quotation marks or made by reference to an external cell. In the above example there's only one field, so just saying “Units” is sufficient; if you have the same field used in multiple ways instead you should clarify which you want with e.g., “Sum of Unit”.

Pivot_table identifies which Pivot you want to extract a value from. This should just be a reference to the top-left cell of the PivotTable - you can see that the default mode is to use a dollar sign-fixed reference for this, which is a particularly good idea!

Field1, 2, etc. and Item1, 2, etc. - After identifying the output we want and the Pivot it's from, you then need to specify pairs of fields and items until the value is uniquely identified. The syntax is the same for both row labels and column labels. If you want to identify the total of a row/column, then you only need to provide the necessary field and item information to identify which one.
It’s important to note that errors can occur if the pivot_table reference does not relate to a PivotTable and if the field details are not displayed in the specified PivotTable. 
Another common error relates to dates. If an item contains a date, it should be in date format or serial number when used in the GETPIVOTDATA function.

If you want to use GETPIVOTDATA, the easiest way is to just click on the cell which has the information you want, let Excel fill in the default function for you, and then edit that function as needed.

For example, here's an edited GETPIVOTDATA that can look up the total number of units of an item in a specific location:

All that I had to do to make this function was enter = and then click on a random value in the Pivot, then replace the "Item" elements of the resulting GETPIVOTDATA from fixed text values to be cell references to the two dropdown cells on the left. This then makes a function that will look up the appropriate value from the Pivot even if later data entry changes the order or number of items in the Pivot.

GETPIVOTDATA is good to use in instances where you need to reference specific data fields in a PivotTable that is regularly updated and changed. However, this isn’t always the case! For example, if you want to reference data fields from a PivotTable into a formula or want to refer to multiple rows of cells in a PivotTable and then drag and fill, then GETPIVOTDATA can get in the way. More on this is touched on in the webinar ‘What’s new in Excel from last year’ (at around the 20-minute mark). In such cases, you can stop auto-writing GETPIVOTDATA functions. There are two ways to do this.

When you add a PivotTable to your spreadsheet, in the ‘PivotTable Analyze’ under the ‘PivotTable’ and ‘Options’ dropdown, you can uncheck the option to ‘Generate GETPIVOTDATA’. This will be applied to all PivotTables in your workbook.

To change this default setting, you can uncheck the option to ‘Use GETPIVOTDATA functions for PivotTable references’ from the ‘Options’ menu.