Pinterest Pixel

GETPIVOTDATA Function

What does it do? A formula that extracts data stored in a Pivot Table Formula breakdown: =GETPIVOTDATA(data_field, pivot_table, [field1,... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

GETPIVOTDATA Function | MyExcelOnline GETPIVOTDATA Function | MyExcelOnline

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.

See also  101 Excel Pivot Tables Examples
GETPIVOTDATA Function | MyExcelOnline

Download excel workbookGETPIVOTDATA-Intro_.xlsx

 

STEP 1: We need to enter the GETPIVOTDATA function:

=GETPIVOTDATA(

GETPIVOTDATA Function

 

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

 

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

GETPIVOTDATA Function

 

[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

GETPIVOTDATA Function

 

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

GETPIVOTDATA Function

Now your new set of data is ready!

See also  Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

GETPIVOTDATA Function

 

If you like this Excel tip, please share it
GETPIVOTDATA Function | MyExcelOnline GETPIVOTDATA Function | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...