everydayExcel Newsletter Header

The Power of GETPIVOTDATA

In one of our trainings in September, a participant asked how she could turn off the GETPIVOTDATA function in pivot table. The auto-generated GETPIVOTDATA formula did not allow her to copy the reference down the column in the same way as a normal cell.

getpivotdata1

If you've ever asked the same question before, you might want to take note of our advice here. The GETPIVOTDATA formula is such a useful formula which you'll want to keep after knowing what it is able to do. Based on the above example, let me explain how it works:

=GETPIVOTDATA("Amount",$A$3,"Customer Name","CHEMICAL INDUSTRIES (FAR EAST) LIMITED.","Slsman No",7)

Let's start off with the first 2 input of the formula - 
=GETPIVOTDATA("Amount",$A$3,"Customer Name","CHEMICAL INDUSTRIES (FAR EAST) LIMITED.","Slsman No",7)

The first input actually refers to the field that is in the summation box. In our example, it is telling us that we are summarizing the "Amount" field.

The second input refers to the starting location of the Pivot Table, A3.

As for the subsequent fields, they are actually in pairs. In our example, the 3rd and 4th input actually refers to the field Customer Name and the specific customer (CHECMICAL INDUSTRIES (FAR EAST) LIMITED). And the subsequent pair refers to the field Slsman and the specific value 7.

=GETPIVOTDATA("Amount",$A$3,"Customer Name","CHEMICAL INDUSTRIES (FAR EAST) LIMITED.","Slsman No",7)

Combining all the inputs together, the GETPIVOTDATA formula is actually telling us that the value we are looking at the Total Amount within the Pivot Table, located from cell A3, for the customer CHEMICAL INDUSTRIES (FAR EAST) LIMITED and Salesman no 7.

With this reference, CHEMICAL INDUSTRIES (FAR EAST) LIMITED can move to other rows, but the formula will be able to track it down and refer back to the value 65477 which is the value the formula is originally linked to.

Like a typical Excel formula, you can actually use cell references as inputs in the GETPIVOTDATA formula. In our example, we can retain the field description "Customer Name" but change the specific customer to refer to a cell on the top of a list. And as you copy the formula down, it will pick up the customer names in the list and populate the data according to the names in the list as shown below.

getpivotdata2

=GETPIVOTDATA("Amount",$A$3,"Customer Name",A4,"Slsman No",7)

Now that you know the power of GETPIVOTDATA formula and that you will still be able to copy the reference down the column even when the formula is turned on, do you still want to turn it off?


Ask us an Excel question and get your question answered by our team of Excel experts

Question: Looping in Macro for replacing a few values

i would like to replace MAL as Malaysia / IND as Indonesia / CAM as Cambodia. Is there a way to loop and use IF condition? .... 

Click on this link to read the full question and answer.


Upcoming Courses

With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.

Hidden Secrets in Data Analysis with Excel 2007/2010 - 18 & 19 Nov 2013 

Interactive Dashboard Reporting with Excel 2007/2010 - 17 & 18 Oct 2013 

Learning the Magic of Macros 2007 (Excel VBA) - 7 & 8 Nov 2013 

Breakthrough Performance for HR in Excel 2007/2010 - 2014 

Dynamic Real-Time Forecasting with Excel 2007/2010 - 14 & 15 Nov 2013

For the details of the courses, please refer to our Excel courses page.


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.