Locked lesson.

Upgrade

About this lesson

Using the GETPIVOTDATA function to extract specific fields from a PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

GETPIVOTDATA.xlsx
211.5 KB GETPIVOTDATA - Completed.xlsx
211.9 KB

Quick reference

GETPIVOTDATA

Using the GETPIVOTDATA function to extract specific fields from a PivotTable.

Where/when to use the technique

You need to format a report in a layout that isn’t conducive to a PivotTable, but want to use data sourced from the PivotTable in that report.

Instructions

Create a GETPIVOTDATA function

  • Lay out the PivotTable so the value you need is showing
  • Select the cell where you’d like the value
  • Type = and click on the cell

Understanding the GETPIVOTDATA function

  • GETPIVOTDATA’s syntax:  =GETPIVOTDATA(data_field,pivot_table, [Field1, Item1]…)
  • This means:
    • Data_field:           The column name for the field you wish to extract (i.e. Sales $)
    • Pivot_table:          A reference to top left cell of the Pivot table
    • Field1:                  The name of the column/row field
    • Item1:                  The name of the value that shows for that row/column field
  • Note that you must include the Field and Item parts in pairs, separated by commas

Making GETPIVOTDATA dynamic

  • Substitute the various fields and items in the formula with references to cells that hold valid values

Dealing with Errors

  • Wrap the GETPIVOTDATA() formula in an IFERROR formula
  • To display 0 instead of an error:
    • =IFERROR(GETPIVOTDATA(…),0)
  • To display text instead of an error:
    • =IFERROR(GETPIVOTDATA(…),”Value not found.  Are filters set correctly?”)

Key points to remember

  • If the row/column you are targeting isn’t present, the formula will error
  • Row/columns will not appear if:
    • They are filtered out/removed from the PivotTable
    • No relevant data is in the dataset when refreshed, causing the item to not show
Login to download
  • 00:04 Now, as you no doubt agree, pivot tables are pretty awesome and
  • 00:07 amazing for being able to quickly summarize data.
  • 00:09 But one of the challenges we do run in with pivot tables is that they always give
  • 00:14 us data in a rectangular form that has a lot of stuff around it.
  • 00:18 So take, for example, this pivot table, I've drilled down to get just to my
  • 00:22 burgers and my draft beer for each individual week.
  • 00:25 But the thing is, what I'm really trying to do is I'm actually trying to fill
  • 00:29 a nice little dashboard that shows me the key sales items for
  • 00:32 the fundraisers I'm doing for a specific week.
  • 00:34 So ideally, what I really want to see here for
  • 00:37 Week 4 is I'd like to see my burger sales, which in this case are $154.35.
  • 00:43 So how can I get that?
  • 00:45 Do I have to use a pivot table?
  • 00:46 The answer is if the pivot table is backing it, I can actually go and
  • 00:51 say equals, go to my report and select the 154.35.
  • 00:55 And when I go and hit Enter you'll see that it comes in quite nicely.
  • 00:59 But what's going on in here?
  • 01:00 What we get is we get this terrifying formula called GETPIVOTDATA.
  • 01:05 But it actually doesn't need to be that terrifying at all.
  • 01:07 Basically what's happening here is it's asking for the measure or
  • 01:11 what is the field that we've put on the pivot table, so this is the amount field.
  • 01:15 And then it says where's the top left corner of the pivot table?
  • 01:17 That's this Report$A$2, if we go back and take a look,
  • 01:21 that's where the pivot table actually starts.
  • 01:24 After that, we have a series of pairs that are being used to pull these things out.
  • 01:28 So the category is Burgers, week is for 4, Category2 is Lunch, Category3 is Food.
  • 01:35 And if I start playing around with this the key thing is, if I look at category
  • 01:39 burgers, I can see that there's only one burgers on this entire pivot table.
  • 01:44 Do I need to have lunch or food if I know that this is burgers?
  • 01:48 We'll never going to see that in any of these other areas.
  • 01:50 So I can probably come back here and say these things here for
  • 01:53 Category2 Lunch all the way through Category3 Food,
  • 01:56 do I need these intersections on the pivot table to get this right?
  • 02:00 And the answer in this case is, no, I can actually delete this and
  • 02:04 hit Enter and it's still going to work, so that's pretty nice.
  • 02:08 Now, if I had Burgers in multiple categories that I would need to actually
  • 02:12 add these extra layers in there, but right now I don't.
  • 02:15 Can I make this dynamic?
  • 02:16 Could I say, hey, the category here is going to be equal to say,
  • 02:21 rather than "Burgers",
  • 02:22 grab the cell right here, that's A4 and hit Enter.
  • 02:26 Sure, it still works, could I change the week number say to Week 4,
  • 02:31 and maybe make this absolute by pressing F4 to lock it down?
  • 02:35 Absolutely, everything looks great.
  • 02:38 Now, that looks pretty cool, I'm pulling the amount field from my report making
  • 02:42 sure that category equals what's on the left hand side here and
  • 02:45 the week equals what's up here.
  • 02:46 So in theory I should be able to grab this formula and pull it down.
  • 02:50 And you'll see that it doesn't work, I'm getting #REF error, why?
  • 02:55 Well, if we take a look at the formula,
  • 02:57 everything seems to be pulling from the right places, so why didn't this work?
  • 03:01 And this is actually super important.
  • 03:03 If you go to the Report, the name of the piece that we're looking for
  • 03:08 is not beer, it's Draft Beer.
  • 03:10 And this is a key thing, this has to be exactly correct.
  • 03:14 So if I come back and retype this in as Draft Beer,
  • 03:17 you'll notice that now it actually works.
  • 03:20 Why is that super, super important?
  • 03:22 Well, there's two things that are happening here.
  • 03:24 Number one, it couldn't find the item.
  • 03:25 Well, that item where it couldn't find it.
  • 03:28 What happens if I now go and filter into just alcohol?
  • 03:31 We've actually removed our food items from the pivot table all together and
  • 03:36 we get a #REF error.
  • 03:37 So in this case, we want to do a little bit of future proofing for
  • 03:40 our GETPIVOTDATA formula.
  • 03:42 What we're going to do is we're going to wrap this guy in IFERR statement, so
  • 03:46 let's say IFERR, and what it'll do is and try to run the GETPIVOTDATA.
  • 03:51 If it works, it will return that value.
  • 03:53 If it doesn't, we can put in an alternate result we can put it in say 0 or
  • 03:58 we could say, product not found, or something similar, between quotes.
  • 04:04 And when I do that, we can now see the product isn't found.
  • 04:07 If I go and drag this down, there we go, it looks like it's fine,
  • 04:12 if I come back, clear the filter from class and bring back my pivot.
  • 04:17 You can see the data's all working.
  • 04:18 Is it a dynamic?
  • 04:19 Sure, let's take it to Week 5.
  • 04:22 Boom, there we go, everything looks great.
  • 04:24 The only thing that's left really, is the formatting.
  • 04:27 Now, the key thing to remember about this when you're using GETPIVOTDATA is probably
  • 04:32 to build your pivot table and then to right-click and say Hide the worksheet,
  • 04:36 so that people can't start interacting with it.
  • 04:38 Because if you do let them interact, you're probably going to run into one of
  • 04:42 those situations where something gets filtered out and
  • 04:44 it doesn't work correctly anymore.

Lesson notes are only available for subscribers.

Gift this course