Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Leveraging the GETPIVOTDATA() function in order to extract specific data points from a Pivot Table
Download this lesson’s related exercise files.Extracting data points with GETPIVOTDATA().xlsx
52.3 KB Extracting data points with GETPIVOTDATA() - Completed.xlsx
Extracting Data Points with GETPIVOTDATA()
Using an Excel formula to pull specific values from PivotTables.
When to use
When you don’t want to show the entire PivotTable on your dashboard, or only want a specific data point, the GETPIVOTDATA() function is your best friend.
Creating a GETPIVOTDATA() function
- Select the cell where you’d like the data, press = and select the cell you want from a Pivot Table
- This will create a GETPIVOTDATA function for you!
Understanding the GETPIVOTDATA() function
- Syntax = GETPIVOTDATA(Data_field, Pivot_table, [Field1], [Item1],…)
- Data_field is the name (in quotes) of the column you want. Ie. “Sum of Sales”
- Pivot_table is the address of the top left cell of the Pivot Table
- [Field1],[Item1] are pairs that represent the field (from rows or columns) and the value you want to pull
- You may add as many pairs of filter items as you like
- Example function: =GETPIVOTDATA("Sum of Sales",Data!$A$3,"Category","Beer")
Preparing for missing data points
- IF someone filters a PivotTable to exclude the fields your GETPIVOTDATA() function relies upon, it will return a #VALUE! Error
- Guard against errors by wrapping the GETPIVOTDATA in an IFERROR() function
- Eg. =IFERROR(<original GETPIVOTDATA>, 0)
- Eg. =IFERROR(<original GETPIVOTDATA>, “Value not found!”)
Hints & tips
- It is wise to always protect your GETPIVOTDATA functions within an IFERROR() function
- Create Pivot Tables to act as a source for your GETPIVOTDATA functions
- Make sure the GETPIVOTDATA’s source Pivot Table is not hooked to slicers
- It is a good idea to hide the GETPIVOTDATA’s source Pivot on another worksheet (so that users can’t play with it and inadvertently break your GETPIVOTDATA function)
- If linking to your Pivot Table doesn’t generate a GETPIVOTDATA function, go to PivotTable Tools --> Analyze --> Options and check the setting for Generate GETPIVOTDATA
Lesson notes are only available for subscribers.