WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » GetPivotData in Excel

GetPivotData in Excel

GetPivotData Excel Function

GetPivotData function in excel is a query function in excel which is used to fetch values from a pivot table based on some specific criteria such as the structure of the pivot table or the reference provided to the function, this function can be only applied on the pivot table and not on the normal table arrays.

Formula

GetPivotData Function Formula

There are three parameters that are used in GetPivotData.

  • Data_field: Name or value that you are looking for.
  • Pivot_Table: Any cell reference or range of cells that are in the pivot table.
  • [field1],[item1],[field2],[item2],etc: Up to 126 pairs of fields and item names can be found. This is an optional part of getpivotdata.

Note: The values must be present in the below details:

  • The number can be directly entered.
  • Use the date in the serial number or use the date function in Excel.

How to use the GetPivotData Function in Excel? (Examples)

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

Example #1

Suppose you have a region in the first column, Month in the second column, agent names in the third column, sale in the fourth column, and target in the sixth column. Now you have to simply find the total sale by using GETPIVOTDATA. Below is the data; you can copy it and paste it into your excel for practice.

Example 1

Now simply create a pivot table.

Example 1-1

Below is the Image as well for your understanding.

Example 1-2

Below is the example that how to use the GetPivotData function to find out total sales.

=GETPIVOTDATA(“Sale”,C4)

=GETPIVOTDATA(“Sum of Sale”,C4)

Example #2

The next question is to find out the total target by using the GETPIVOTDATA function.

(Use same data which mentioned above)

=GETPIVOTDATA(“Target”,D4)

=GETPIVOTDATA(“Sum of Target”,D4)

Example #3

In the next question, By using the same data, let’s find out the sale of Mar month.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

GetPivotData Function Example 3

Now there are two ways to find the sale of Mar Month. First, you can type item of field names in the GETPIVOTDATA in Excel function, and in the second one, you can refer to worksheet cells.

=GETPIVOTDATA(“Sale”,C4,”Month”,”Mar”)

=GETPIVOTDATA(“Sale”,C4,A4,A9)

Example #4

In the next question, By using the same data, let’s find out the Target of Mar month.

GetPivotData Function Example 4

=GETPIVOTDATA(“Target”,D4,”Month”,”Mar”)

=GETPIVOTDATA(“Target”,D4,A4,A9)

Here you need to know important things

If you change the first argument, data_field, to a reference F4, the result is a getpivotdata #REF! error

GetPivotData Function Example 4-1

So, always remember to use text in the data_field syntax for ignoring errors.

Example #5

Now Let’s move to another task that how to use Dates in the GetPivotData function.

If you use dates in the GETPIVOTDATA formula, there might be a few errors. Below are a few examples.

In the below-given table, we have a date in the first column and sale in the second column.

GetPivotData Function Example 5

Now just create a pivot table.

create a pivot table

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

To get a correct answer while typing a date.

=GETPIVOTDATA(“Sale”,B3,”Date”,”5/26/2018″)

Things to Remember

There are two kinds of errors found in the GetPivotData function.

  • GETPIVOTDATA will return a #REF error if any supplied fields name puts incorrectly.
  • The GetPivotData function always accepts the name of the data_field and field/item values in the double-quotes.

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 on Excel along with practical examples and downloadable excel templates.

  • Excel Pivot Table Add Column
  • Pivot Table Group by Month
  • Pivot Table in Excel
  • VLOOKUP in Pivot Table
  • Drawing in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download GetPivotData Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More