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
203.6 KB GETPIVOTDATA - Completed.xlsx
203.8 KB

Quick reference

Topic

Using the GETPIVOTDATA function to extract specific fields from a Pivot Table.

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 pivot
    • No relevant data is in the dataset when refreshed, causing the item to not show
Login to download
  • 00:04 Sometimes we don't really wanna display our results exactly
  • 00:08 the way the pivot displays them.
  • 00:10 But we do want to actually use the PivotTable to collect those
  • 00:14 results for us.
  • 00:15 And the case in point might be where we have a PivotTable that looks like this,
  • 00:18 summarizing our weeks.
  • 00:19 And I've got these subtotaled by week for the alcohol and food, and
  • 00:23 we've got it filtered down to burgers and draft beer.
  • 00:26 And what I'd like to do is create this little dashboard that pulls in just
  • 00:31 the items that contribute to the fundraisers that we do on a regular basis,
  • 00:36 so burgers and beer.
  • 00:37 Now, what I'd like to do is I'd like to grab the 15435 for
  • 00:42 my burgers, and throw it into my dashboard over here.
  • 00:46 So how am I going to do it?
  • 00:47 Well, I'm gonna just type a formula.
  • 00:49 Equals, go to the report, click on it and you'll see
  • 00:53 right off the bat that I get this massive get pivot data formula show up.
  • 00:57 And I hit enter.
  • 00:58 And I've now extracted a single cell from this table.
  • 01:02 That's kinda cool.
  • 01:03 Let's just take a look at the components here.
  • 01:05 What do we have?
  • 01:06 Starts off with get pivot data amount.
  • 01:09 If I go back to my report, you'll see that this is in this area sum of amount and
  • 01:14 I can see that by showing the field list.
  • 01:17 I've got sum of amount, which is coming from the amount here.
  • 01:18 So the first parameter is amount.
  • 01:21 The second is A2 of the report worksheet,
  • 01:24 which is the top left-hand corner of the PivotTable.
  • 01:29 You can see that, report A2.
  • 01:33 After that, it works in pairs.
  • 01:35 Category, comma, burgers.
  • 01:37 If I go back over to the report, we can see that we have burgers
  • 01:41 is the category field, if I'm also in this burgers category.
  • 01:45 That's coming from category, which is showing up here, and
  • 01:48 it's giving us the burgers line.
  • 01:51 It then gives us another set of pairs: week four.
  • 01:55 And if we go back and we look and we say, okay well, this is week four so
  • 01:59 the origin of burgers at week four.
  • 02:02 And hopefully that should be enough actually.
  • 02:04 It did feed it, because this is the only burgers in the table.
  • 02:08 So, let's go back and take a look.
  • 02:09 We got a bunch of extra stuff that's showing up category2, lunch.
  • 02:12 Well, that could mean lots of things, right?
  • 02:15 Category2, lunch could have,
  • 02:17 if we unfiltered all of this, multiple items in this table.
  • 02:21 You'll notice that this still works when it's unfiltered.
  • 02:24 Let's take category2, lunch out.
  • 02:30 We still have a number, and if we run back here,
  • 02:34 it looks like this is the only burgers, and that's why it's showing up, so
  • 02:38 we'll go back and grab burgers and draft beer again.
  • 02:42 It's still working.
  • 02:43 That's cool.
  • 02:43 What about category3, food?
  • 02:45 We can probably knock that guy off, as well.
  • 02:49 And it still works.
  • 02:50 Now what if I wanna make this dynamic?
  • 02:54 Well, week number 4, let's change it to the cell reference.
  • 03:01 It still works.
  • 03:02 Let's change burgers, instead of using this, to burgers, and Enter.
  • 03:09 And that looks like it still works.
  • 03:10 And the last thing I'm going do is just put a dollar sign in front of
  • 03:13 the row here.
  • 03:14 Because I might want to copy this down to say can I get to beer.
  • 03:17 Let's drag it down.
  • 03:21 #REF!.
  • 03:22 Well, why is that?
  • 03:23 Well, we're looking for category A4.
  • 03:25 What's A4?
  • 03:27 Hang on a second.
  • 03:28 No we're not. We're looking at category A5.
  • 03:30 What's A5, beer.
  • 03:31 If I go back to the report, do I see beer?
  • 03:34 No. I see draft beer, so this is important.
  • 03:38 The name actually means to match and at that point,
  • 03:42 I can now get this to work, and I've got a dynamic formula that's working here.
  • 03:46 But here's the kicker.
  • 03:48 When I go back to my report,
  • 03:49 if I filter Draft Beer out, I'll just go into Burgers, I now get an error.
  • 03:55 The way to deal with this is to come back and say,
  • 03:58 all right, let's put an IFERROR function around our get pivot data.
  • 04:03 If we want to put a value in we can put in something that says zero,
  • 04:06 because sometimes items aren't in the data set.
  • 04:08 And that would return a zero if there was something filtered out.
  • 04:12 Or we could put in text that says, Can't find item.
  • 04:19 This is something that's really important,
  • 04:21 is when you use get pivot data if you've got people that are playing with your
  • 04:24 filters, you could end up losing the items that you're looking for.
  • 04:28 They have to be visible in the PivotTable.
  • 04:30 The cool thing, though, is that with this, we now have the ability
  • 04:35 to change to week 5, and we now get different numbers showing up.
  • 04:39 243, is that what we're looking for?
  • 04:42 243.20?
  • 04:44 There it is.
  • 04:44 And what about our draft beer?
  • 04:46 Our draft beer was $264 and that's what's showing.
  • 04:51 Now you know you can use get pivot data to extract values from tables and
  • 04:55 use them in different areas where you may not need the entire pivot.

Lesson notes are only available for subscribers.

Gift this course