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.

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to enter the GETPIVOTDATA function:

=GETPIVOTDATA(

 

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”,

 

pivot_table

From which pivot table?

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

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

 

[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)

 

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

Now your new set of data is ready!

gpd

HELPFUL RESOURCE:

728x90

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

Share on Google+

Related Posts

Drill Down To Audit Using a Pivot Table When you are using a Pivot Table in Excel and want to know what data makes up a certain value, all you have to do is double click on that cell.This will open up a brand new Sheet with all the rows of data that make up that value.NB. This is an extraction of your data sour...
How To Fix Pivot Table Empty Cells I am sure that you have come across a Pivot Table which has empty cell values and thought "What the hell is happening here?"This is because your data source has blank cells for certain items, which happens from time to time.This can be fixed in your Pivot Table and you ca...
Cleaning Data with Excel’s TRIM Formula What does it do?Removes unneeded spaces in your text, except single spaces in between wordsFormula breakdown:=TRIM(text)What it means:=TRIM(text that you want extra spaces to be removed)In the quest for cleaner data, one of the common scenarios is remo...
Show Averages With an Excel Pivot Table A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button.The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Produ...