💫Use GETPIVOTDATA to extract information from a Pivot Table – Essential Pivot Table Skills☝️

Hello everybody and welcome to today's video. The first thing I should point out is I apologize for my voice. I'm really struggling to talk today. I've woken up not feeling the greatest. I've got a little bit of a cold or flu or COVID. Who knows what it is? But regardless, I'm here with another video. That's dedication. And today we're going to be talking about the GetPivotData formula. And this is something that I have had requested underneath one of my other videos, because I find that GetPivotData is one of those formulas that people don't really understand and can't really see why it's useful. So we're going to take a look at it. We're going to dissect it. I'm going to show you how you can make it dynamic. And I'm also going to show you why it can be useful to you. So what is the GetPivotData formula? Well, it basically allows us to extract information from a pivot table. So let me show you what I mean by that. Now I've gone in and I've created a pivot table based off of this sales data just here. And my pivot table is just this over here. If we take a look at the pivot table field, so you can see what it's doing. I basically have the segment, which is consumer, corporate and home office. I then have the years 2015 to 2018. And I can see the sum of the sales for the segment and the years. So a very basic pivot table. Now it might be that I need to extract some data from this pivot table. For example, maybe I want to create some kind of summary sheet that shows the sum of sales for the different segments. So maybe I just want it to say consumer, the amount of sales, corporate, the amount of sales and home office and the amount of sales. Well, I could do this using the GetPivotData formula. And it's worth noting that GetPivotData is turned on by default. How do we know? Well, if I click in a random cell and type equals, as soon as I try and click on a cell within the pivot table, take a look at what happens. Excel automatically puts that long GetPivotData formula into the cell instead of just the cell reference. Now, what exactly is this formula doing? Let's go in and break it down. Well, we have GetPivotData. And then it's telling us that we're grabbing our data from the sales field. So that would be right. We're using the sales field in the pivot table. We then have a cell reference of A3. And A3 is where we currently have row labels. Now, this cell reference just here might be any cell within the table. But the default is to pick the topmost corner cell in the table, because in general, that's the most reliable one. If the pivot table moves or changes, we're always going to have that top corner cell. So it's just a way of really referencing where the table is. We then have segment followed by consumer. And you can see that is exactly what we're referencing here. We're referencing the sum of sales for the segment consumer. So that's the formula. If we hit Enter, it's going to pull back the sum of sales for the consumer sector. So this is very similar to linking to cells, except we're linking to pivot table data instead. Now, the problem that people have with this formula is that it is very rigid. You can see it's exactly referencing the sales for the segment consumer. So if I was to try to copy this formula down in any way by dragging the handle, it's basically just going to repeat the same number over and over again, because this isn't particularly flexible at the moment where we have this get pivot data formula. And that's kind of why people don't really like this. They find it very inflexible. But we can easily make this formula dynamic. I'm going to show you why this method is so useful. So I'm going to delete out everything that we've just done. And what I'm going to do is I'm just going to type out consumer, corporate and home office. So we have those three segments. And I want to pull back the sales for each of these segments. So as we've already seen, I can type in equals, I can grab the sales and hit Enter. And we've seen that this doesn't work when we pull it down because it's a very rigid formula that currently isn't dynamic. This is basically an absolute formula that's locking it to a specific cell. So how can we make this formula more dynamic? Well, what I could do is I could replace the word consumer with a cell reference because I have it written out just here. So now when I hit Enter and drag this down, that is now working simply because I've replaced the hard coded segment with a cell reference. So I can then go in and apply my formatting. So maybe I want some accounting formatting and let's also take those decimal places down. So you might be thinking to yourself, that's all well and good, but why is this useful? Well, let's click in the pivot table and go to pivot table analyze. And I'm going to click the options dropdown. Now notice here we have generate get pivot data. So this is the little option that needs to be turned on for that get pivot data formula to be generated as opposed to the cell reference. If I was to deselect this and basically try and do the same thing again. So let's grab these and I'm just going to copy them down here so that we have a similar thing. So now that I've turned off get pivot data, when I type in equals and try and link to the same thing, I just get the cell reference instead. So let's hit Enter. Let's drag this down and see what happens. Well, it doesn't work because it's moving to the next one down. But what I could do is simply type equals like that and equals and like that. And I have the correct answers. And this formula just here is a lot shorter than the get pivot data formula. So why would we want to use get pivot data as opposed to just linking to the actual cell? Well, the reason why is that pivot tables have a tendency to change. We might move the fields around. We might get rid of certain fields and that data can move into different cells. So let me show you what I mean. If I open up the field list, what about if I was to remove the years field? Check out what happens over here. So now I don't have the years. I just have the breakdown of the segment where I've used the get pivot data formula. Those are still working. Whereas when I've just linked to the cells, the first one's working, but the other two are not. Because the data has moved, there is now nothing in cell B9. The same for the one underneath. There's now nothing in cell B14. So get pivot data is a much more robust formula, which will update and adjust when changes are made to the pivot table. Whereas if you're just linking to the data and then that pivot table changes layout, your formulas are going to get broken. So that's a very quick explanation of the get pivot data formula and why you might want to use it when you're trying to extract or create summaries from pivot table data. If you like this video, give me a thumbs up and a follow, and I will see you in the next one.