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

VLOOKUP Example: Vlookup with a Drop Down List What does it do?Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.Formula breakdown:=VLOOKUP(lookup_value, table_array, col_index_num, )What it means:=VLOOKUP(thi...
Advanced Sumproduct Function: Sum the Top 3 Sales ... This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula.In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calcul...
Customize an Excel Slicer  Excel Slicers are very boring looking and there are only 14 different Slicer Styles to choose from, ranging from Light to Dark and with a few colors to choose from.What if you wanted to create your own Slicer using your creative mind with super cool colors and f...
Conditional Format a Cell´s Value A great way to highlight values within your data set, Excel Table or Pivot Table is to use Conditional Formatting rules.Formatting cells that contain a specific criteria, for example, greater than X or less than X, is a good way to visualize your results.When your criteri...