EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel GETPIVOTDATA Function

By Jeevan A YJeevan A Y

Secondary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel GETPIVOTDATA Function

GETPIVOTDATA Function in Excel

GETPIVOTDATA Function in Excel (Table of Contents)

  • GETPIVOTDATA in Excel
  • How to Use GETPIVOTDATA Function in Excel?

Introduction to GETPIVOTDATA in Excel

As the name itself 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.

Start Your Free Excel Course

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

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

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 the simple example.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,275 ratings)

GETPIVOTDATA Formula Example

In the above function, we are looking for the Grand Total Amount of the Region East.

  • Data Field: in the above example, the data field means what is the heading total you are looking for. In the above example, we are looking for the Sales Amount Total. 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, is residing 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 GETPIVOTDATA Function in Excel by 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 column4. 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: There two ways you can apply the Getpivotdata function in excel.

  • One is by just clicking equal sign (=) in any cell (apart from pivot table cell) and select 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 a 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; under this, we are looking for Ramu.

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 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 characters of the syntax must begin with double quotes (“) and should 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. If the data is not visible, then the function will return a #REF error type.
  • This function will work across 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. Excel SEARCH Function
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download GETPIVOTDATA Function Excel Template

EDUCBA

Download GETPIVOTDATA Function Excel Template

EDUCBA

डाउनलोड GETPIVOTDATA Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Online EXCEL COURSE Learn More