How to use GETPIVOTDATA in Excel 2016: Pivot Tables Excel 2016

Показать описание

Do you want to advance your career? Learn Excel!

Right, let’s summarize what we have seen so far in this exercise.
We started working with a raw SAP extraction in Excel. We carried out a mapping exercise and obtained two new columns – one containing months and another one showing years. Then, we were able to build a structure based on the data we had in our extraction and on what we wanted to show in the report we are building. After that, we formatted the output sheet nicely and added the necessary formulas that will calculate subtotal, total, absolute and percentage variances, etc.
Finally, we inserted a Pivot Table that will allow us to extract the necessary numbers. The next two lessons will make working with Pivot Tables so much more valuable for you!
Our goal here is to extract the data from the Pivot Table and populate the cells of the output sheet.
Let’s start by filling the first cell in the report – Volume for 2015. I’ll link to one of the cells of the Pivot Table that we already have.
We already know how GetPivotData works. The first argument shows the field for which we are extracting data. In this case, we are extracting data for Volumes.
The second argument, which in this case is the cell A3, is the first cell of the Pivot Table to which we are linking.
And then, all of the other arguments of the function give us directions to which cell exactly in the Pivot Table we are linking.
Let’s take a look at the formula while we are in the sheet containing our Pivot Table.
We have “Month” equal to 1, meaning we are extracting volume data for January. The year is 2015 and the brand is Buratino. You can see how easy it is to understand this formula. Each of the names has an easy to understand meaning. Sometimes, we can even work with this function without looking at the source sheet. And this is simply great, as it reduces the likelihood of mistakes significantly.
But, that’s not everything. We can substitute each of these hard inputs with a reference. And this is when GetPivotData becomes awesome!
For example, I will type 2015 in this row here and will then replace the hard input with a reference to this cell and everything will remain as it was, before we made the replacement. This is how we can make the function flexible and re-use it for the rows and columns in the entire table.
I will simply type 2016 on the right. Copy and paste the function to the right. And this will provide us the volume in 2016. Very well.
But wait, these are not the volumes of all the firms we want to show here, but just the ones for the Buratino brand. If we erase this criterion, we will stop specifying we are looking for a particular brand and Excel will provide us with the sales volume of the entire company.
And in the same way, we need to eliminate the “month” field, as right now, the formula extracts volumes only for January. I’ll erase these two parameters of the function.
Ok. So this is the volume of sales of the entire firm in 2015. As we have already specified the numbers are in thousands, let’s divide this figure by 1000. It will look much neater. 
All right. The question is: are we going to be able to paste this function in all blank cells that have to be filled in this report?
The answer is that we are not ready to do that … yet.
I’ll need to fix the row reference of the “Year” item, which will prevent it from changing its location when pasting the function downwards.
The other thing that needs to be taken care of is the fact we still have one hard input at the beginning of the function – its first argument, which reads “Sum of Volume”. We need to find a way to allow us to change this argument for each of the rows we have below. So, for example, when we paste the function in the row below, the first argument will have to be “Sum of Gross Sales”.
…because this is how it is named in the Pivot Table from which we are extracting data.
So, why don’t we substitute this bit with an “&” function? I can separate the first part with parentheses, which indicate this is text and then add the & function and link to the cell B6.
………..…And here’s the proof that this approach functions. The first argument of the function will change dynamically when we paste it downwards and to the right.
However, we forgot to add a dollar sign in front of the column reference of this argument. Without the dollar sign in front of it, it will change its column position when pasting to the right. I’ll add the dollar sign now.
All right, perfect. Now we can paste the function for all blank cells in our report.
GetPivotData is truly great, isn’t it?