Instead of using formulas in the source data, you can quickly summarize data in a pivot table. Then, use the GetPivotData function to pull specific data from the pivot table, into your customized report structure
Thanks to Roger Govier, who created this tutorial and sample file.
A pivot table is a quick and easy way to summarize large quantities of data. However, there can be many instances where the pivot table cannot be organized in the layout or style you need.
In this tutorial, you will see how to pull a pivot table's summarized data into custom report layouts, with the help of the GetPivotData function. Instead of linking to specific cells, the GetPivotData function can automatically adjust to find the correct values for us, no matter where they might now be located.
In this example, the customized report will be a Profit & Loss statement, with special formatting, as shown below. You can use the same technique to create your own custom reports.
I am assuming in this article that you how to produce a Pivot Table from a set of source data. Click the links below if you need detailed instructions for:
As a first step, a pivot table has been created from the source data, and arranged with Months across the top, and analysis fields listed in column B.
A Slicer has been connected, and it is filtering the results to show only the Trading Analysis codes and exclude any Capital codes.
But what if we wanted a report that was set out in a different way, with Margins for different parts of the business and different percentage figures on the report with a layout like shown below.
There is no way that formatting such as this can be achieved directly within a Pivot Table, so we need to be able to extract data from the Pivot Table and pull the figures into the relevant cells of our Report, to be summed and have other calculations carried out to obtain the desired result.
This is where we need to use the GetPivotData function. (one of the things I don't like about this function is the length of its name which you have to type, so throughout the rest of the article I will refer to it as GPD. Equally I will refer to Pivot Tables as PT )
At first, it looks like a formidable function, with syntax which is somewhat different to that which we normally use. With a long name for the function itself, it leads to long and rather unwieldy formulae.
The general form for a PT with only one row field and one Column field is something like
The easiest way to create the formula is to turn on the Preference for Excel to generate the formulas for you. The command is on the Pivot Table Options tab as shown below
With that preference turned on, when you type "=" in any cell, and then point to the relevant value you want to retrieve, Excel will automatically generate the formula for you. For example, if you click on the cell that has the amount for Landscape in July, the following formula will be created:
I have coloured the various parts to make it easier to explain.
The "problem" with GPD is that it "hardwires" these values into the formula it creates, which makes it impossible to copy down and / or across without having to go in and edit each formula that's created.
Well, we can amend the first formula that GPD creates for us to make it a little more useful to us, and permit it to be copied and adjust automatically.
If we change it so that it refers to cells, as opposed to type entries, and then we make our Row field have an Absolute Column reference, and our Column field have an Absolute Row reference, we can then copy the formula across the page and down, and it will adjust accordingly.
=GETPIVOTDATA( "Amount" ,
'Data area field
' Location of PT
"Analysis" , $B7 ,
' Row field and location of Row field value to be selected
"Inv_month" , C$5 )
' Column field and location of Column field value to be selected
The formula has only been set out this way to make it easier to see what we are setting. In reality it would just be
=GETPIVOTDATA("Amount" , PT_Trad!$B$5, "Analysis", $B7, "Inv_Month",C$5)
So as we drag down, $B7 will become $B8 and refer to "Bark", and then $B9 referring to "Plants & Trees" and so on.
Similarly as we drag across the page. C$5 will become D$5 and refer to "Aug", E$5 and refer to "Sep" and so on.
It now only remains for us to wrap this whole formula in an IFERROR() function, so that if we have any blank rows or columns in our report, or if the Pivot Table has not yet been populated with values for all months of the year, we will not get an error.
=IFERROR(GETPIVOTDATA("Amount" , PT_Trad!$B$5, "Analysis", $B7, "Inv_Month",C$5),"")
Now we have a much more versatile version of the GPD formula which I think is a little easier to understand and far more useful as it can be copied easily down and across the sheet.
We can make even more of the arguments in the GPD function more flexible, by using cell references instead of "hard wiring" the names into the formula.
On the worksheet, there are entries in cells C1 and D1 of our main report sheet.
In cell C1 I have entered Analysis, and in D1 I have entered Inv_Month which are the names of the Row field and the Column field that we want. So, we can substitute these in our formula and GPD will quite happily accept them.
=IFERROR(GETPIVOTDATA( "Amount" , PT_Trad!$B$5 , $C$1 , $B7, $D$1 ,C$5),"")
Regrettably when PT's were introduced, Microsoft did not make Pivot Table name unique. You cannot have 2 PT's of the same name on the same sheet, but you can use the same PT name on other sheets within a workbook. With the newer Tables, they are unique so that you can refer to them by name and your formula will find them wherever they are located or moved to within a workbook.
Because the name may not be unique, you always have to give GPD the sheet location of the PT and a cell reference that falls within the PT (GPD uses the top left cell by default when it creates the formula, but in reality you can give any cell reference that falls within the PT). This is a petty because it would have been much nicer and easier if you could just give the PT name - in my example this is ptAccs and not have to concern yourself where that is located.
You can set up a Name called say myPivot and have it refer to PT_Trad!$B$5
The formula would then become
=IFERROR(GETPIVOTDATA( "Amount" , myPivot , $C$1 , $B7, $D$1 ,C$5),"")
For some reason, whether by design or just a quirk, you cannot put the name of the Data field in a cell, and use that cell reference in the GPD formula. It would have been so nice if we could have put "Amount" into cell B1 alongside our other cell references and then had an easier and more flexible way of switching the name of the field we had allocated to the Data area when setting up our PT. We can however set up another name, say myField and have it refer to "Amount"
We could then have an almost universal formula that we could use for any report that we could just copy to the first data cell of our report and copy across and down. By changing the parameters in B1 and C1 of our sheet, and amending the contents of the 2 named ranges myPivot and myField without having to even think about getting the syntax of the GPD formula correct. The formula would then be
=IFERROR(GETPIVOTDATA( myField , myPivot , $C$1 , $B7, $D$1 ,C$5),"")
a nice short formula with lots of versatility and flexibility.
An example of this flexibility, is if you wanted the Months in rows and the headings in columns, having changed the headings in your report, you would not need to alter your formula at all, because we have not "hard wired" any values. You would just need to swap the headings in C1 and D1, so that Inv_Month became the Row field heading in C1, and Amount became the column field heading in D1.
Equally, not that it would be appropriate in this example report, but if you wanted to have the values based upon the VAT Amount, you would just need to swap the fields in the Data area of your PT to Remove Amount, and bring in VAT Amount, change your Named field value or myField from "Amount" to "VAT Amount" and VOILA!!! - the job is done, nice and quick and very simple.
If you had a second PT set up in an identical structure, but using Pay_Date as the Column field, then you would just change the value in D1 from Inv_Date to Pay_Date, and change the Named field myPivot to the Sheet and cell location of your second PT, in my example file that would be PT_Cash!$B$5
Hopefully this has taken some of the mystery out of the GPD function and made it easier for you to understand and use.
You must be aware though, that if you change the Layout of your PT, then you may need to amend your GPD formula. In my example, the layout is unlikely to be altered as we are just wanting our Profit & Loss statement to be updated as more and more data is added through the year. There is no need for us to amend the layout, but if, for example, we did bring in another Row field and add Trader to the PT, then it would look like this
This later formula, with the extra parameters will still work even if we swap the order of the Row fields and put Trader first and Amount second. Again, this is because of the "intelligence" built into GPD and as long as the same fields exist within the table and the full location of the value required has been specified within the formula, then GPD will find it and bring it back to us.
Well, GPD is "intelligent enough" to realise that what we want is the Total figure for that Analysis code and will return a value of -1,660 for Bark for the month of April, the same as we had already seen in our report, and would not return the individual values of each trader.
If we had placed the Trader field before the Amount field then GPD would error (quite rightly as it would not have the correct parameters) and the Iferror() function would therefore display nothing.
Even with the layout as shown on the left, if we did specifically want the Amount that had been spent with Trader 007 on Bark for the Month of April, then the GPD formula would be
This is the full version of the formula and you can see that "Trader" has been added as a Row field with a value of "Trader 007".
If, in spite of the information above, you decide that GPD is not the method for you, then provided your PT structure does not alter, but just grows in dimensions as more data is added to your source data, then you can just use INDEX() and MATCH() in most circumstances.
If you can accept VBA, then I will show a short piece of code that will generate the 3 named ranges that you need to use Index and Match to return your results.
But for those who can't or don't want to use VBA, you could just define 3 dynamic ranges manually.
The first range we need is the range that covers the part of the Databody of the PT that we want to extract data from. I like to adopt a naming convention which is the PT name with "_T" appended to it, so for my example workbook where the PT name is ptTrad, the name I would use would be ptTrad_T and the Refers To would be
=PT_Trad!$B$6:INDEX(PT_Trad!$1:$1000000,MATCH("Grand Total",PT_Trad!$B:$B,0),MATCH("Grand Total",PT_Trad!$6:$6,0))
This is because my actual data body range starts in cell B6, and the end of the table will be the intersection of where Grand Total occurs in column B and Grand Total occurs in row 6 in this case cell O36
NOTE: You must have Pivot Table Options set to "Show Grand Totals" for both Rows and Columns for this to work.
We also need defined names for the first column of our table and the first row of our table. This can be achieved in various ways, but having defined our table range, the easiest way is to just Index this table.
So, for the Row I use the PT name & "_R" and the Column I use PT name and "_C", so
i.e the left column of our table and the top row
Now that we have those 3 names, we can use Index and Match to find the data we want for our report.
=IFERROR( INDEX( ptTrad_T, MATCH( $B7, ptTrad_T, 0), MATCH( C$5, ptTrad_C ,0)),"")
Instead of typing the addresses of the 3 ranges needed to use Index and Match, if you can accept VBA to generate these for you, then the following code will loop through all sheets in your workbook, and for each Pivot Table that it finds, it will generate the ranges based upon the Pivot Table name.
This code doesn't generate a dynamic range (although it could be modified to do so if required), but generates a range based upon the dimensions of the PT at the time it is run. As it runs fairly quickly provided you don't have a huge number of sheets or PT's, then I set it to run automatically (immediately after an automatic Refresh of my PT's) whenever I access the report sheet. This way not only can I be certain that the PT is up to date and accurately reflecting all of the data from my source, but that my ranges also include the whole of the PT.
There are various Properties of the PT that are exposed through VBA which enable us to get relevant ranges
The code below applies the necessary Offsets and Resizing so that we end up with the three ranges that we require for Index Match to work. The range created for ptName_T is one row longer than required, but this of no consequence as it will never be accessed.
Sub CreatePTNames() Dim i As Long Dim pt As PivotTable Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Activate If ActiveSheet.PivotTables.Count <> 0 Then For i = 1 To ActiveSheet.PivotTables.Count Set pt = ActiveSheet.PivotTables(i) ThisWorkbook.Names.Add _ Name:=pt.Name & "_T", _ RefersTo:=pt.TableRange1.Offset(1, 0) ThisWorkbook.Names.Add _ Name:=pt.Name & "_R", _ RefersTo:=pt.rowrange ThisWorkbook.Names.Add _ Name:=pt.Name & "_C", _ RefersTo:=WorksheetFunction _ .Index(pt.ColumnRange, 2, 0) _ .Offset(0, -1) _ .Resize(1, _ pt.ColumnRange.Columns.Count + 1) Next End If Next End Sub
You can rem out the outer loop if you wish, so that the code only runs for the Active Sheet rather than looping through the whole Workbook
The following Event code is inserted on both of the P & L Report sheets
Private Sub Worksheet_Activate() Application.EnableEvents = False Application.ScreenUpdating = False ThisWorkbook.RefreshAll CreatePTNames Me.Activate Application.EnableEvents = True Application.ScreenUpdating = True End Sub
The routine to CreatePTNAmes doesn't need to be included on the sheet using GPD, as those range names are not required.
Download one of the following zipped sample workbooks --
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.
Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier
You can contact Roger at:
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191
Last updated: December 5, 2016 11:04 AM
Contextures RSS Feed