EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel GETPIVOTDATA Function
 

Excel GETPIVOTDATA Function

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

GETPIVOTDATA Function in Excel

Introduction to GETPIVOTDATA in Excel

As the name suggests, GETPIVOTDATA means, Get the Data from Pivot Table. It is a kind of pivot table lookup function. It is categorized under the Lookup and Reference function. This function helps in extracting data from specified fields in a Pivot Table. Pivot Table is an analysis tool which summarises a large amount of data in a readable manner.

 

 

Getpivotdata can query a pivot table and retrieve specific data based on the table structure instead of the references.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

GETPIVOTDATA Formula in Excel

The Formula for the GETPIVOTDATA Function in Excel is as follows:

GETPIVOTDATA Formula

This function consists of Data Field, Pivot Table, [Field1, Item1], [Field2, Item2], [Field3, Item3].

Before explaining, the syntax one by one, let us looks into a simple example.

GETPIVOTDATA Formula Example

We are looking for the Grand Total Amount of Region East in the above function.

  • Data Field: in the above example, the data field means the heading total you are looking for. In the above example, we are looking for the total sales amount. This is not an optional argument but a required argument.
  • Pivot Table: This is a reference to your Data Field cell. In the example shown above, the data filed, i.e. Sales Amt, resides in cell A3, which is why it is taking a reference as A3.
  • [Field1, Item1]: We are looking for a Grand Total of Sales Amt for the Region (i.e. [Field1]) East (i.e. Item1).

How to Use GETPIVOTDATA Function in Excel?

GETPIVOTDATA Function in Excel is very simple and easy to use. Let us understand the working of the GETPIVOTDATA Function in Excel with Some Examples.

You can download this GETPIVOTDATA Function Excel Template here – GETPIVOTDATA Function Excel Template

Example #1

If you have Region in column 1, Project in column 2, Sales Person in column 3, and Sales Values in column 4. You need to get the total of Mr. Sanju using Getpivotdata. Before we apply the function Getpivotdata, firstly, we need to create a pivot table for the below data. Go ahead and apply the function.

Example 1

After applying the pivot table, your table should look like this.

GETPIVOTDATA Example 1.2

Pro Tip: You can apply the Getpivotdata function in Excel in two ways.

  • One is by clicking the equal sign (=) in any cell (apart from the pivot table cell) and selecting the desired cell in the pivot table field.
  • Secondly, manually enter the formula just like other formulas in Excel.

Type 1: Click on any cell and select the desired result cell in the pivot table. This will give you a value of 2,16,444.

GETPIVOTDATA Example 1.3

Type 2: Enter an equal sign on any cell and enter the Getpivotdata function.

GETPIVOTDATA Example 1.5

Now in the Data_Field section, type “Sales Amt”. In the Pivot_Table section, type “I1”(reference cell where your Sales Amt resides, in my case, it is I1). In the [Field 1]section, type “Sales Person”, and in [Item1] section, type “Sanju”. This will give you a value of 2,16,444.

GETPIVOTDATA Example 1.4

Example #2 – Get Pivot Table Sub Totals

Use the same data table but insert the below kind of pivot table to use the multi-criterion Getpivotdata function. Your pivot should look like this.

Example 2

Now the requirement is to get the value of Mr. Ramu for Project 2.

=GETPIVOTDATA(“Sales Amt”,$A$20, “Project Code”, “Project2”, “Sales Person”, “Ramu”)

GETPIVOTDATA Example 2.1

Data_Field: Sales Amt is the data filed.

Pivot_Table: Cell reference of Sales Amt.

[Filed1] & [Item1]: Project Code is Filed we are looking for, and under project code, we are looking for Project 2.

[Filed2] & [Item2]: Sales Person is filed; we are looking for Ramu under this.

That means we are looking for the total sales amount of Mr. Ramu for Project 2.

GETPIVOTDATA Example 2.2

Please note the amount of the overall sales for Mr. Ramu is 3,92,051, but for Project 2, it is 3,52,519.

GETPIVOTDATA Example 2.3

Example #3

Below is the monthly sales data for Company XYZ. With the use of a pivot table, find the Total Sales Amount for the date 26-02-2018.

Example 3

Pivot Table Fields & Values

  • Apply pivot table for the above data.
  • Row data should be Date.
  • Value Field should be Sales Amt.

Pivot Table will look like this.

Pivot Table Example 3.1

By using this table, find out the sale of 26th Feb 2018.

To get a correct answer while typing a date, below is the Getpivotdata Formula.

GETPIVOTDATA Example 3.2

The sale of 26th Feb 2018 is 643835.

GETPIVOTDATA Example 3.3

Things to Remember

  • You cannot change the first argument of the function, i.e. Data_Field, to a cell reference; if so result will throw an error as #REF.

REF ERROR 1

  • By default, Excel will take the date format as DATE (2018,2,26), but you change this by just entering the date in “dd-mm-yyyy” format, i.e. “26-02-2018”.
  • A function will return a value of #REFerror in case of an incorrect supply of data_field. You cannot give a cell reference to this section.
  • All the syntax characters must begin with double quotes (“) and end with the same.
  • Apart from data_field for all the remaining fields, we can give a cell reference. This also will give the same result.

GETPIVOTDATA Error 2

  • Calculated fields or items and custom calculations are part of this function.
  • All the fields should be visible to get the desired result. The function will return a #REF error type if the data is not visible.
  • This function will work across the Grand Total & All the Sub Total of the Pivot Table.

Recommended Articles

This has been a guide to GETPIVOTDATA in Excel. Here we discuss the GETPIVOTDATA Formula in Excel and how to use GETPIVOTDATA Function in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. SUMIF Function in Excel
  2. Excel TREND Function
  3. SUBSTITUTE Function in Excel
  4. SEARCH Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download GETPIVOTDATA Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download GETPIVOTDATA Function Excel Template

EDUCBA

डाउनलोड GETPIVOTDATA Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW