Introducing GETPIVOTDATA

In the previous week, you learned about pivot tables and pivot charts. Now, pivot charts have some limitations. And these limitations include the fact that you - there are any number of charts that you can actually use with pivot tables. And, if the pivot table doesn’t contain the data or the calculation that you need, well then, it can’t be included in the chart. Now we seen this in the previous course and we were able to create helper columns from our data set to chart things off. And we’re going to do the same with pivot tables. Because it’s the helper columns that will allow us to do the advanced charting or use charts that aren’t normally available with pivot data.

So in this video, we are going to recreate this sales by sales rep chart here. And you seen this earlier on this week and you are presented with this chart. And you are asked, if you could recreate it. And you are giving two sets of data - you are given the actual sales and you were given the monthly or the budget sales. And it’s from here that we wanted to combine these and to create this sales by sales rep. So, I’m going to get stuck straight in and the trick to doing this, the trick to using helper columns with pivot tables is get pivot data. They get pivot data.

You are also introduced to in the previous week but we’re going to look at a little bit differently this week. Whereas last week when we looked at of - we talked about how could be slightly annoying and in the way. But this week, you’re really going to need it. So, if you haven’t turned off, I would suggest you now turn it back on. So I’m going to set up a new sheets. I haven’t used sheet with a copy of our images here so we can see what it is that we want. And I’m going to go to our data set. And from our data set, I’m going to say insert pivot table. I’m going to select this range of data.

So it selected the table because I’ve selected the table. And I’m going to put it on an existing worksheet, and I’m going to place it straight up here. So now what I’m going to do is I am going to put in our sales rep because it’s the rep that we want. And I am going to take our sales. So now, we have the sales for our actuals. So we can do exactly the same then to our budget. We go to our budgets and from our budgets, insert. And we’re going to insert a pivot table. I’m going to put it on an existing worksheet and jump back there and just place it beside the other one.

Put in rep and I’m going to put in my budget sales. So now, we have the two lots of values that we need for our chart. And from here, all we have to do is create our helper columns. So, I’m going to close my pivot table field list for the moment. And I’m going to start by taking a copy of all the names that are available. And I’m going to place them in these cells here. So, I have a unique list of names. Now, you could use the unique function, if you’re familiar with a dynamic arrays. I have Excel 365, but for simplicity I’ve just pasted the name in here. Now, we’re going to have an actual.

[types] We’re going to have a budget. [types in her keyboard] We’re going to have the percentage variance and then we’re going to have labels. So we put this in for labeled. So, our actual. Well, our actual - if we go across here and we equal to the actual sales for Amber in our pivot table, we get this - get pivot data. Now, when using this get pivot data, if I then go and try and copy this formula down, I get the same value. So why does this happen? Let’s break this down and let’s actually understand this. So, we’re going to press F2 to actually edit. So first of all, it’s saying, well, the pivot table sales starts in cell A2.

So that’s this cell appear. So, look in this pivot table. And for the rep, return the value for Amber. So we see Amber there is actually hard coded in. So we can amend this formula. So, I’m going to delete out Amber from there in the inverted commas. And I’m going to linked to cell G3. So now, if I fill the formula down, we can see that the formula is actually working. You see, this get pivot data kind of works like an index and match function. So, basically, here it’s saying go to the rep and return the match of this cell here, which is G3 which is Amber. So that’s what it’s going to do.

So let’s look at that again for the budget. So, equals. And we’ll just reference the cell over here and it’s saying it’s going to the budget table. The budget table is starting in cell D2. And we’re looking up the rep for Amber but it’s not. We want the rep for Amber to be variable, not fixed. So, we’ll delete that. And select the name from arranged. And then, we can fill that formula down. Now, to get our percentage difference, while we can get our actual. [types] Actual minus our budget figure. And divide that by our budget figure. And we can fill that down as well and their percentages. So, I’m going to change them to a percentage.

And then, for our labels, because we are going to create dynamic labels in here that will show the actual variances as we can see in this chart. So, let’s go back here so you can see better. You see this variance and it gives the actual variance amount. So we do this using a formula. [END]