ICAEW.com works better with JavaScript enabled.

Excel tips & tricks

Excel Tips & Tricks #471 – Refreshing GETPIVOTDATA

Author: Bani Lamba

Published: 12 May 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Hello all and welcome back to Excel Tips and Tricks! This week, we have a Creator level post where we’re refreshing our coverage of the PivotTable extraction function 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:

Screenshot from an Excel spreadsheet

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.

Writing your own GETPIVOTDATA

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:

Screenshot from an Excel spreadsheet

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.

Screenshot from an Excel spreadsheet

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

Screenshot from an Excel spreadsheet
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.