Hello and welcome back to the Excel Tip of the Week! This week, we have a Creator post in which we look at the unusual function for extracting data from PivotTables, called GETPIVOTDATA.
You may want to refresh your Pivot knowledge with a look at TOTW #64.
Getting access to a GETPIVOTDATA function
I've written "getting access to" instead of "writing" in this heading for a reason. While GETPIVOTDATA can be written like any other function, much of the time it's easier to get one in another way.
Starting with any Pivot, you can write a reference to a value in that PivotTable, and you will see that Excel automatically writes an appropriate GETPIVOTDATA function for you:
You can start to see the syntax here, but let's break it down anyway:
=GETPIVOTDATA(data field, PivotTable, field item 1, item 1, field item 2, item 2...)
Data field - The name (in quotation marks or entered in its own cell) of the field that you are looking to extract. If the field is used multiple times, you will instead need to include the name of the data (e.g. "Sum of NET" in this example.PivotTable - The cell reference of the pivot - considered to just be the top-left cellField item 1 - The field first descriptor that will be used to filter the data drawn from the pivotItem 1 - The specific item that will be used in the filterField item 2 / item 2 etc. - If desired, multiple conditions can be entered
Customising your GETPIVOTDATA function
However, in most cases it is far easier to create a GETPIVOTDATA function by allowing Excel to create one automatically, and then adjusting as necessary.
For example, let's say we want to pull a value for a given Account name, which we will write in cell F5. All we need to do is replace the field for Item 1 - instead of having "Accruals" as a fixed value, we replace this with a reference to F5.
Note that a change in the layout or content of the Pivot may cause corresponding GETPIVOTDATA functions to become invalid. This function is best used with pivots that have a fixed, constant layout (although changes in the underlying data are still easily accounted for).
Switching off GETPIVOTDATA
The default mode for Excel is to replace direct cell references to PivotTable values with a GETPIVOTDATA function. If you don't want that, there are a couple of options.
Firstly, you can still use a traditional cell reference, by typing it manually. This method is simple but a change in the underlying data might cause the formula to go awry, as data is displaced.
Secondly, the Options menu actually allows you to switch off the auto-completing of GETPIVOTDATA functions:
You can have a play with a pivot and see a couple of functions in the attached workbook.
Previous post - VBA case study: Always open on landing pageTOTW index Next post - Migrating VBA projects to 64-bit Excel
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.