GETPIVOTDATA Formula Excel – How to use GETPIVOTDATA Function with Pivot Table




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Syntax of GETPIVOTDATA Formula
Example of GETPIVOTDATA Formula
Making GETPIVOTDATA Formula Dynamic

GETPIVOTDATA formula is used with a pivot table to fetch values from it.

GETPIVOTDATA Formula Syntax

The GETPIVOTDATA Formula has the following syntax:

=GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, … FieldName N, Item N)

However before we delve any deeper in the GETPIVOTDATA formula, let’s get back to basics for a minute.

Basic structure of a pivot table

A pivot table is based on a collection of data organized as columns (and rows). Each of these columns needs to have a label which acts as the identifier for the entire column. Each of these identifies can be moved around and placed in four “slots”. There are four of these slots in a pivot table:

GETPIVOTDATA-pivot-table-structure

Each Field in the pivot table would have one or more items. For example in this case, the field “Region” has the names of states (such as Alabama, Alaska, Arizona, Idaho, Illonois etc) Each row of the pivot table now shows the value of “Actual Sales” that took place in a particular region.

Keeping this structure of the pivot table in mind, lets dive straight into getting to know the GETPIVOTDATA formula a little better. A we saw previously, the syntax of the GETPIVOTDATA formula is :

=GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, … FieldName N, Item N)

where:
DataField = Name of the Field present in the data slot of the pivot table whose value we want to retreive (Ex. Sum of Sales etc.)
PivotTableCell = Address of any cell located inside the pivot table. This helps the application know which pivot table to use while returning the values.
FieldName = Name of a particular Field present in the Pivot Table
Item = Name of a particular Item present in the Pivot Table

Please keep in mind that any string values must be enclosed in quotes (Ex. “Month”, “Location” etc). Cell references should be used without quotes.

Example of GETPIVOTDATA formula

The easiest way to write a GETPIVOTDATA formula is to simply select a cell and then after pressing the = sign, select a cell located within the pivot table whose value you want to read as shown below.

getpivotdata-formula-in-excel

In the above example the GETPIVOTDATA formula works out be something like this:
=GETPIVOTDATA(“ActualSales”,$B$12,”Month”,DATE(2008,8,31),”Region”,”Alabama”)

Now if you study this formula closely, you would see that the first parameter is “ActualSales”. Why because that in the name of the field from which we would like to extract the data. The second parameter is the value of any cell in the pivot table. Typically excel will make it the very first cell of the pivot table but there is no restriction and you can change this to your liking till the time the cell resides inside the pivot table. After the first and the second parameter, the remaining parameters are just pairs of field name and items – in our case the first pair is “Month” & date (31-Aug-08) while the second pair is “Region” with item as “Alabama”.

Essentially the GETPIVOTDATA formula above can be read as:
Fetch value of “ACTUAL SALES” where “Month” = 31-Aug-08 AND “Region” = “Alabama”.

Important Point:
Keep in mind that GETPIVOTDATA will return only those values which can be located on screen (in one particular cell) – which means that if a particular combination is possible but is not available in the present structure of the pivot table and hence is not summarized in a single cell, it will return an error. In other words, although a combination may be viable, if the entire combination cannot be identified as a single individual cell (although you may be able to get the value by summing multiple cells), GETPIVOTDATA will not give correct results. Hence for GETPIVOTDATA to work correctly, you may need to rearrange the table.

Let’s take an example. Suppose you wanted to get the total value of sale for the product category “Electronics” for the month of Aug. In the first table, we can find the value but indirectly – we will have to sum of the individual values of the product across all regions. Which also means that if we wanted to locate a single cell that has the value we are looking for, we would fail. Hence the GETPIVOTDATA formula shown below would fail.

getpivotdata-ref-error

Now let’s rearrange the pivot table a bit. Let’s move the product to the first position in the row fields and the regions to the second. Now we do have a single cell which can give us the value we are looking for. Hence the GETPIVOTDATA function in the second case returns a proper value without any error.

getpivotdata-without-error

Modifying the GETPIVOTDATA formula to make it even more dynamic

By default, the GETPIVOTDATA formula will update whenever the pivot table gets updated with fresh data points. However due to its structure, the GETPIVOTDATA formula possesses can be made much more versatile. If you look at the structure of the GETPIVOTDATA formula, it has a number of (Field Name, Item) keys. Now both these parameters can be changed on the fly but let’s focus on the second param – Item. Let’s see how we can make a single GETPIVOTDATA formula return different values based on different criteria. For example let’s replace “Alabama” with a cell reference say, B2 in the following formula:

GETPIVOTDATA(“Sum of ActualProfit”,$A$5, “Region”, “Alabama”, “) changed to
GETPIVOTDATA(“Sum of ActualProfit”,$A$5, “Region”, B2)

getpivotdata-update-with-drop-down

As we can see, the same formula returns different values based on what the use choose in the drop-down.

Now whenever the value in cell B2 changes, the GETPIVOTDATA formula will return a new value. Till the time we can ensure that B2 will always provide a valid value for a Region (from the list of regions that we have in our pivot table), the formula will work without a hitch.

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. zein wrote:

    thank you for this example

  2. Ajay wrote:

    @zein – You are welcome :-)

  3. Kirk wrote:

    I’m going nuts trying to figure out why GETPIVOTDATA doesn’t work for me. I have a very simple pivot–a Job Title column followed by several columns: total headcount for that title, plus a number of utilization numbers (billable, admin, etc). GETPIVOTDATA works as you’d expect for all of them–except the job title column. There, I simply get a cell reference when I click on a cell in that column. The source data for that column is itself a calculated field…the only difference I can find.

  4. Marek wrote:

    Thanks for the nice tutorial. I actually would like to do something similar – I need to have a “getpivotdata” result that is based on a pivottable, but I need to input a range of exact dates (eg. 1st Jan to 2nd Feb) & summarize the data from the pivot table that sumarizes the data by other characteristic.

    I have hours spent on different clients in list & these are processed into a pivot table by client & by dates (month so far).
    This is then translated to another sheet, that outputs only 1 month data based on an external drop-down cell. What I need is to be able to input the date range and summarize data inbetween those date ranges…

    Any suggestions?

    Thansk

  5. Carlene wrote:

    Thanks very much, that was a really helpful explanation and I managed to nail the formulas that I needed in my spreadsheet :)

  6. Lisa wrote:

    Great website .. very clear and solved my problem !!

    Grand !!

  7. caro wrote:

    Does someone know how to reference the data field in order to extract data from a pivot, not being a text but referencing it to a cell??

    In the below example, I would need to reference “Sum of ActualProfit” to a cell (f.ex.C5) instead of writing the whole text every time…but when I try to do that I get an error message…

    GETPIVOTDATA(“Sum of ActualProfit”,$A$5, “Region”, B2)

    Thanks !

  8. Nathan wrote:

    Hi all, great article but I want to know the syntax to use when I want to add two values from one data field without having to create two getpivotdata calcs with an addition symbol between, e.g. from an example above i might want totals for alabama and alaska only.

    GETPIVOTDATA (DataField, PivotTableCell, “State”, “Alabama”,”Alaska”).

  9. Bdon wrote:

    Thanks, quite simple when you understand it. This is the best explanation on the net and I looked at a lot of explanations.

  10. David Declerck wrote:

    You are missing an important element/trick
    if you change the first argument, data_field, to a reference cell, the result is a #REF! error
    To fix this problem, you can concatenate an empty string ( “” ) at the beginning or end of the cell reference.
    With this you can realy make your formulas dynamic

  1. Excel Dashboard using Pivot Table | Excel & VBA - da Tab Is On wrote:

    [...] to do is to use the GETPIVOTDATA formula to fetch values pertaining to each of these items. The GETPIVOTDATA formula can provide us with different values based on the values specified to the parameters. Hence , we [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel