Home > Pivot > Calculation > GetPivotData

Create Reports With GetPivotData

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

Create Custom Reports from Pivot Table

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..

• This example builds a Profit & Loss statement, like the one in the screen shot below.
• You can use the same technique to create other types of custom reports.

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?

• Pivot table is quick and efficient way to summarize a large amount of data
• GetPivotData function does not slow down a large workbook, unlike some Excel functions
• Manually building the custom report structure, ensures the layout does not change unexpectedly
• Custom report structure can be formatted exactly the way you need it
• Formulas refer to row and column headings, for flexible data summaries

Cautions - Custom Reports from Pivot Table

Here are a few cautions about this technique, to keep in mind:

• Pivot tables do not update automatically, if the source data changes.
  • You can set a pivot table to automatically refresh when the workbook opens
  • You can manually update a pivot table, after you finish updating the data
• If GetPivotData formula refers to a pivot table amount that is not visible, the formula will return a #REF! error value
• If new categories are added in the source data, the custom report structure will not automatically include them.
  • Be sure to add new headings to the custom report structure, when needed
  • Otherwise, key data could be omitted from your custom report
• Because multiple formulas are used in a custom report, there is an increased risk of error
  • Create a few error-checking formulas in your workbook, to alert you if custom report totals do not match the source data totals

Create Custom Report - Steps

There are four main steps to create a custom report:

-- 1) Set up the Pivot Table

-- 2) Set Up Custom Report Structure

-- 3) Add GetPivotData Formulas

-- 4) Add Remaining Formulas

1) Set Up Pivot Table

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:

1. Analysis categories are at the left, in the Rows area
2. Invoice Month names are across the top, in the Columns area
3. Amount for each invoice is summarized in the Values area

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

• In the Slicer, "T" code is selected, so pivot table shows Trading Analysis data, and not "C" (Capital)

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:

• create a custom report structure
• add formulas to pull summarized data from the pivot table.

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:

1. Plan: Plan your custom report layout
• What headings, subtotals and grand totals do you need?
• What information is needed in each section of the report?
2. Headings: Next, set up headings for a summary report in the workbook, like the example shown above
• Do not enter anything in the amount cells yet - leave those blank for now
3. GetPivotData: Next, in cells where values from the pivot table are needed, create GetPivotData formulas
• In the sections below, you'll see how to create those formulas
4. Totals: Finally, in the subtotal and total rows and columns, add other formulas, if required

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:

• Summary for each business section - Landscape, Fencing, Other
• Margin for each business section, and Total Margin
• Expense categories, with total for each category, and Total Expenses
• Gross Profit calculation per month, and overall
• Percentage calculations for each category and item

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.

• Month headings start with July, the first month in the fiscal year
• Analysis headings are grouped into sections, with rows for Margin calculations
• Rows were added for Total Margin, and Total Expenses
• Some headings were formatted with bold text
• Borders were added around sections, subtotals and totals.
• Blue font colour was used in one row, and one column, where turnover percentage will be calculated.

3) Add GetPivotData Formulas

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

--b) Replace Values With References

--c) Make GetPivotData formulas flexible

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:

• In the custom report layout, click on cell C7, where the Landscape total for July should appear
• Type an equal sign, to start the formula
• Next, go to the pivot table sheet
• In the pivot table, find Landscape, in the Analysis headings at the left
• Go across the Month columns, to find the July column
• Click on the July Landscape cell, and Excel automatically creates the GetPivotData formula
• Press the Enter key, to complete the formula

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:

• =GETPIVOTDATA("Amount",
  PT_Trad!$B$5,
  "Analysis", "Landscape",
  "Inv_month" ,"Apr")

Formula Explanation

Here is a brief explanation of those arguments, and you can learn more about the GetPivotData function, further down on this page.

• "Amount " is the name of the field from the Data area of the Pivot Table (PT) that you want to retrieve
• PT_Trad!$B$5 is the sheet location of the PT, and the cell location of the first cell in the top left corner of the PT
• "Analysis" is the name of the Row field from the PT with "Landscape" being the value from the Row field that you require
• "Inv_month" is the name of the Column field from the PT with "Jul" being the value from the Column field that you require

Replace Values With References

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:

• from Amount field,
• for the Landscape item in the Analysis field,
• and the Jul item in the Inv_month field

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:

• "Amount" - all formulas should return data from this field
• PT_Trad!$B$5 - all formulas should return data from this pivot table
• "Analysis" - all formulas should return data for an item in this field
• "Inv_month" - all formulas should return data for an item in this field

In the formula, we will change the following arguments to cell references:

• "Landscape" - each formulas should return a different category in the Analysis field
• "Jul" - all formulas should return a different month in the Inv_month field

To replace those text references, I'll use the cell addresses where those text items are located:

• "Landscape" - is in cell B7 -- use an absolute reference to the column - $B7
• "Jul" - is in cell C5 -- use an absolute reference to the heading row - C$5

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.

• =IFERROR(GETPIVOTDATA("Amount" , PT_Trad!$B$5, "Analysis", $B7, "Inv_Month",C$5),"")

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:

• First, seelect cell C7
• Next, point to its fill handle, at the bottom right of the cell
• Then, press the RIGHT mouse button, and drag across or down, to cells where you want to add the formula
• Release the right mouse button, and click the Fill Without Formatting command

Another method is to copy and paste:

• select cell C7, and press Ctrl+C to copy that cell
• Then, select another group of cells, where you want to see amounts from the pivot table
• In the screen shot below, I've selected cells C8:N10
• Then, right-click on one of the selected cells
• In the pop-up menu, in the Paste Options section, click on Formulas

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:

• There is subtotal row for each section, that uses the SUM function.
• The Total Margin and Total Expenses rows sum the category sections
• Grand Total row sums the Total Margin and Total Expenses
• Grand Total column sums the monthly amounts
• In row 28, and column P, formulas calculate the % Turnover.

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.

-- A) About GetPivotData Function

-- B) Make Report More Flexible

-- 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.

Try GetPivotData

To see how that works, try these steps:

• Go to a worksheet where there is a pivot table
• Click on a blank cell, away from the pivot table
• Type an equal sign in the blank cell
• Then, click on any cell in the pivot table's Value area, where a number is showing

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.

• =GETPIVOTDATA("Amount",$B$5,"Analysis","Bark","Inv_month","Apr")

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!

• Tip: See the GetPivotData Examples introduction page, for more tips and examples.

To see the GetPivotData syntax:

• Type an equal sign in a blank cell
• Next, type the function name, followed by an open bracket:
  • =GETPIVOTDATA(
• Below the formula, a tooltip appears, showing the function syntax arguments

Note: Optional arguments are inside square brackets

In the GetPivotData function syntax, there are the following 3 arguments. The first 2 arguments are required:

• data_field: Name of pivot table field in the Values area, that has the data you need.
  • Enter a text string, inside double quotes, or refer to cell that contains the pivot field name
• pivot_table: Reference to any single cell in a pivot table, or a range of cells, or a named range of cells
• field1, item1: (optional) From 1 to 126 pairs of pivot field names and pivot item names, to pull specific data from the data field.
  • Names are typed inside double quotation marks

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")

• data_field: "Amount" is the name of the value field in the pivot table
• pivot_table: $B$5 is the cell address of a pivot table cell
• field1, item1: (optional) Get data from "Analysis" field, and "Bark" item in that field
• field2, item2: (optional) Get data from "Inv_month" field, and "Apr" item in that field

WARNING: The items in the specified pivot table must be visible, or the GetPivotData formula will return a #REF! error value

Generate GetPivotData

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.

• NOTE: This is an application-level setting in Excel, and changes the Generate GetPivotData setting for ALL workbooks, not just the active workbook.

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:

• Select any cell, in any pivot table
• On the Excel Ribbon, click the PivotTable Analyze tab, (or the Pivot Table Options tab)
• At the left end of the tab, click the Options drop down arrow
• If the Generate GetPivotData command has a check mark, that option is turned ON
  • To turn it OFF, click on the Generate GetPivotData command
• If the Generate GetPivotData command does not have a check mark, that option is turned OFF
  • To turn it ON, click on the Generate GetPivotData command

(optional) Make Report More Flexible

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

• =GETPIVOTDATA("Amount",$B$5, "Trader", "Trader 007", "Analysis", "Bark", "Inv_month","Apr")

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".

INDEX/MATCH Alternative to GetPivotData

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.

-- 1) Create Dynamic Ranges Manually

-- 2) Using Code to Create Named Ranges

1) Create 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

• ptTrad_R is defined as = INDEX(ptTrad_T,0,1)
  • AND
• ptTrad_C is defined as = INDEX(ptTrad_T,1,0)

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)),"")

2) Use Code to Create 3 Named Ranges

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

• .RowRange gives us the range of the first column of the Databody range, starting with the cell which holds the Name of our Data field, which is what we want for ptName_R
• .TableRange1 gives us the Databody range of the whole PT, ignoring any Page Fields but this start 1 row above the real range the we require for ptName_T so this needs to be resized. ( The TableRange2 property would include the Page Fields as well.)
• .ColumnRange however gives us a range which is 2 rows deep, and starts 1 column to the right of the range we need for ptName_C, so this needs resizing as well

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.

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 the Sample File

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

About the Developer

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: roger@technology4u.co.uk

Roger Govier

More Tutorials

Pivot Table Introduction

GetPivotData Examples

Summary Functions

Clear Old Items in Pivot Table

Sample Files - Roger Govier