Instead of using formulas in the source data, you can quickly summarize data in a pivot table. Then, use the Excel GetPivotData function to pull specific data from the pivot table, into your customized report structure, like the Excel Profit and Loss statement in this example.
Author: Roger Govier
-- 1) Set up the Pivot Table
-- 4) Add Remaining Formulas
A pivot table is a quick and easy way to summarize large quantities of data, but sometimes the pivot table can't be organized in the layout you need.
In this tutorial, you'll see how to use the GetPivotData function, along with a pivot table, to get exactly the report that you need..
Benefits of Custom Reports from Pivot Table
Why would you create a custom report, based on a pivot table? What are the benefits of this technique?
Cautions - Custom Reports from Pivot Table
Here are a few cautions about this technique, to keep in mind:
Create Custom Report - Steps
There are four main steps to create a custom report:
-- 1) Set up the Pivot Table
-- 4) Add Remaining Formulas
To follow along with these steps, you can download the completed sample file, at the bottom of this page.
The Excel workbook has accounting records for a landscaping business, in a named Excel table, on a worksheet named Data.
Pivot Table Layout
In the sample workbook, a pivot table was created from the accounting data. The goal is to create a custom Profit and Loss statement, showing monthly totals for each Analysis category in the accounting records.
When creating the pivot table, I added three fields from the source data:
Above the pivot table, I inserted a pivot table Slicer, for the Type field. Each record in the data has eith a T, for Trading Analysis transactions, or a C, for Capital transactions
TIP: When you create a pivot table from your own source data, use a layout that summarizes the data you will need in your custom report.
Pivot Table Limitations
The pivot table gives a helpful summary of the overall totals for each month, and for each analysis category. However, there are limitations on how you can arrange the summarized data, and show subtotals.
To overcome these limitations, you can build a structure for a custom Excel report, with the specific layout that you need for row and column headings, subtotals, and grand totals.
In the sections below, see how to:
2) Set Up Custom Report Structure
For this custom report example, a landscaping company needs a Profit and Loss Statement report, like the example shown in the screen shot below.
There is no way that formatting like this can be achieved directly within a Pivot Table, so we'll need a custom report structure, to pull data from the pivot table.
TIP: You can practise this technique by using the sample workbook. Later, you can create the custom report structure that you need, and a pivot table to summarize your source data.
Custom Report Structure Steps
To complete a custom report, we need to do the following steps:
Custom Report Plan
The first step is to decide what you need in your custom report, and plan its worksheet layout.
This Profit and Loss Statement report needs the following features, which are not available in a pivot table:
TIP: You can see the full Profit and Loss Statement if you download the sample workbook.
Custom Report Headings
Next, enter all the required headings on an Excel sheet, and add the formatting that you want.
In the screen shot below, the headings have been set up for the Profit and Loss Statement.
Next, to get the pivot table data into the Summary Report, we can use GetPivotData functions.
In the sections below, there are 3 steps:
a) Insert GetPivotData Formulas
With the Generate GetPivotData option 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.
To start the custom report formulas, I did these steps:
The July Landscape amount appears in cell C7.
Arguments in GetPivotData Formula
To see the arguments in the GetPivotData formula, select cell C7.
Here is a colour-coded version of that formula:
Here is a brief explanation of those arguments, and you can learn more about the GetPivotData function, further down on this page.
As you can see in that formula example, the GetPivotData (GPD) function contais text values, that identify the pivot fields and the pivot itmes in those fields.
It gets data:
We could use the same method to create formulas in all of the other custom report, but that would take a long time, and it would be easy to create errors, by clicking on the wrong pivot table cell.
Instead, we'll change that formula, so it refers to the cells in the Month headings, and cells in the Analysis category headings.
After making that change, we will be able to copy the GetPivotData formula across the columns, and down the rows, and it will adjust automatically, to return the correct data in each cell.
Change to Heading References
In the formula, we can leave the following arguments as they are:
In the formula, we will change the following arguments to cell references:
To replace those text references, I'll use the cell addresses where those text items are located:
Those absolute references will allow the formula to always refer to the Analysis categories in column B, and the Month names in row 5.
Optional: Add IFERROR Function
Before you copy the revised formula to the other cells, you might wrap the formula with an IFERROR function.
For example, the following formula will show an empty string (''), instead of an error value, if there is any problem pulling specific data from the pivot table.
TIP: If you don't use IFERROR, it might be easier to spot problems quickly, such as a #REF! error value, because an item is missing from the pivot table.
Copy Revised Formulas to Other Cells
Next, you can copy the revised formula to other cells in the custom report structure.
One way to do that is by dragging:
Another method is to copy and paste:
4) Add Remaining Formulas
The final step in creating your custom report is to add the remaining formulas, such as the subtotals and totals.
For example, in the Profit and Loss statement:
TIP: In your custom report, enter the formulas that you need, to sum the results of the GetPivotData formulas, or to do other calculations.
Optional Topics and Steps
The following sections have additional information about the GetPivotData function, and creating the custom reports. If you're not interested in these topics, you can go to the Download section, to get the Profit and Loss samploe workbook.
-- C) INDEX/MATCH Alternative
About GetPivotData Function
The GetPivotData function returns data from a specific part of a specific pivot table - for example the April total amount for the Bark analysis category..
A helpful feature of this function is that, unlike any other Excel function, a GetPivotData formula can be automatically created for you, which makes it very easy to use.
Note: If the formula is NOT automatically created, go to the Generate GetPivotData section below, to see how to turn that feature on or off.
To see how that works, try these steps:
For example, Excel automatically created the following GetPivotData formula, when I typed an equal sign, then clicked on the April Bark amount in the pivot table.
Then, when I pressed the Enter key, to complete the formula, the result showed the April amount for the Bark analysis category.
GetPivotData Function Syntax
At first, the GetPivotData function might seem confusing. It has a syntax that is different from most other familiar Excel functions, and even its long name can look slightly intimidating!
To see the GetPivotData syntax:
Note: Optional arguments are inside square brackets
In the GetPivotData function syntax, there are the following 3 arguments. The first 2 arguments are required:
GetPivotData Example Syntax
For example, Excel automatically created this GetPivotData formula, when I typed an equal sign, then clicked on the April Bark amount in the pivot table. This formula has two pairs of field names and item names.
=GETPIVOTDATA("Amount",$B$5, "Analysis","Bark", "Inv_month","Apr")
WARNING: The items in the specified pivot table must be visible, or the GetPivotData formula will return a #REF! error value
Excel has an option setting, Generate GetPivotData, that automatically creates GetPivotData formulas, when you try to link to a pivot table Value cell.
By default, when you install Microsoft Excel on your computer, that Generate GetPivotData option setting is turned on.
However, you can easily turn that setting off, if you don't want any GetPivotData formulas to be created automatically.
Turn Generate GetPivotData Option On or Off
To see the steps for turning off the Generate GetPivotData, watch this short video.
There are written steps below the video, to change this setting with the Excel Ribbon command.
Use Ribbon Command
The easiest way to create the formula is to turn on the option for Excel to generate the formulas for you.
To change the setting, follow these steps:
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.
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
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
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
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.
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
NOTE: The routine to CreatePTNames doesn't need to be included on the sheet using GPD, as those range names are not required.
Private Sub Worksheet_Activate() Application.EnableEvents = False Application.ScreenUpdating = False ThisWorkbook.RefreshAll CreatePTNames Me.Activate Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Download either of the following zipped sample workbooks
No Macros: Get the Excel workbook with GetPivotData Reports using no macros. The zipped file is in xlsx format, and contains no macros. It shows both the GetPivotData solution, and the INDEX/MATCH solution.
With Macros: Get the Excel workbook with GetPivotData Reports using macros. The zipped file is in xlsm format, and contains macros. When you open the workbook, enable macros if you want to test the GetPivotData Reports macros in the workbook. It shows both the GetPivotData solution, and the INDEX/MATCH solution
Roger Govier is an independent IT consultant based in Wales, assisting businesses with their Business Information (BI) requirements, making extensive use of Excel and Visual Basic. Roger also conducts Excel training for corporate clients, and at conferences. In 2007, Roger was honoured to receive an Excel MVP award from Microsoft, and he has recognized with that award every year since then.
Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier
You can contact Roger at: firstname.lastname@example.org
Last updated: December 28, 2022 6:49 PM