What does it do?

A formula that extracts data stored in a Pivot Table

Formula breakdown:

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

What it means:

=GETPIVOTDATA(return me this value from the Values Area, any cell within the Pivot Table, [and return me the value that pertains to this Field name, and this Field item],…)


 

The GETPIVOTDATA function in Excel returns data stored in a Pivot Table.  So essentially it extracts the Pivot Table data to enable a user to create customized reports.

Think of the Pivot Table like your data source, so anything you see in the Pivot Table report can be extracted with the GETPIVOTDATA function and put into a cell within your worksheet.

The GETPIVOTDATA function becomes powerful when you reference cells to create shell reports, which you can see from the tutorial below.

NB. Only the Fields and Items that are included in the Pivot Table report (Row/Column Labels and Values area) can be used to extract their values.

GETPIVOTDATA Function | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to enter the GETPIVOTDATA function:

=GETPIVOTDATA(

GETPIVOTDATA Function | MyExcelOnline

 

STEP 2: The GETPIVOTDATA arguments:

data_field

What is the value that we want to return?

Type in SALES as we want to return the sales value:

=GETPIVOTDATA(“SALES”,

GETPIVOTDATA Function | MyExcelOnline

 

pivot_table

From which pivot table?

Just reference a cell in the pivot table, let’s type in $A$1

=GETPIVOTDATA(“SALES”, $A$1,

GETPIVOTDATA Function | MyExcelOnline

GETPIVOTDATA Function | MyExcelOnline

 

[field1, item1]

What are the fields that would serve as our filtering criteria?

To get our target sales figure, we will need: Sales Region, Financial Year and Sales Quarter. To do this we will need 3 field-item pairs:

=GETPIVOTDATA(“SALES”, $A$1, “SALES REGION”, $A27, “FINANCIAL YEAR”, B$25, “SALES QTR”, B$26)

GETPIVOTDATA Function | MyExcelOnline

GETPIVOTDATA Function | MyExcelOnline

 

STEP 3: Do the same for the rest of the cells by copying the GETPIVOTDATA formula to the rest of the cells.

GETPIVOTDATA Function | MyExcelOnline

Now your new set of data is ready!

GETPIVOTDATA Function | MyExcelOnline

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn