Subscriber only lesson.
Sign up to this course to view this lesson.
Using the GETPIVOTDATA function to extract specific fields from a Pivot Table.
Where/when to use the technique
You need to format a report in a layout that isn’t conducive to a PivotTable, but want to use data sourced from the PivotTable in that report.
Create a GETPIVOTDATA function
- Lay out the PivotTable so the value you need is showing
- Select the cell where you’d like the value
- Type = and click on the cell
Understanding the GETPIVOTDATA function
- GETPIVOTDATA’s syntax: =GETPIVOTDATA(data_field,pivot_table, [Field1, Item1]…)
- This means:
- Data_field: The column name for the field you wish to extract (i.e. Sales $)
- Pivot_table: A reference to top left cell of the Pivot table
- Field1: The name of the column/row field
- Item1: The name of the value that shows for that row/column field
- Note that you must include the Field and Item parts in pairs, separated by commas
Making GETPIVOTDATA dynamic
- Substitute the various fields and items in the formula with references to cells that hold valid values
Dealing with Errors
- Wrap the GETPIVOTDATA() formula in an IFERROR formula
- To display 0 instead of an error:
- To display text instead of an error:
- =IFERROR(GETPIVOTDATA(…),”Value not found. Are filters set correctly?”)
Key points to remember
- If the row/column you are targeting isn’t present, the formula will error
- Row/columns will not appear if:
- They are filtered out/removed from the pivot
- No relevant data is in the dataset when refreshed, causing the item to not show
Lesson notes are only available for subscribers.