Using GETPIVOTDATA in Excel
This GETPIVOTDATA tutorial is suitable for users of Excel 2013/2016/2019 and Excel for Microsoft 365.
Objective
Use the GETPIVOTDATA function in Excel to reference values in cells contained within a PivotTable.
In this guide, you’ll learn:
- What is GETPIVOTDATA?
- Video Tutorial – GETPIVOTDATA Excel
- How to Use GETPIVOTDATA in Excel?
- The Advantages of Using GET PIVOT DATA
What is GETPIVOTDATA?
GETPIVOTDATA is an Excel formula that belongs to the Lookup and Reference group of functions. It allows you to create a formula that references a cell inside an Excel PivotTable.
It could come as quite a surprise if you were expecting cell references. For example, if you are constructing a simple SUM calculation that references a total in a PivotTable cell, you may expect to see a cell reference like A2. Instead, when you click on the cell, you get a very long GET PIVOT function added to the formula.
Now, you can turn it off and use cell references. Still, it is worth noting that this setting is an application-level setting i.e., it will be turned off for you and any new workbooks you create, but it will not be turned off if you send the file to someone else unless they have also turned it off.
So, if it is long and cumbersome, why use it? Well, it can be quite useful, particularly when creating flexible, customized reports. It uses criteria to ensure that the correct data is returned even if the PivotTable layout changed.
To read more about the advantages of using GET PIVOT DATA, check out Brad Edgar’s post and video 3 Reasons Why You Need to Start Using Excel GET PIVOT DATA.
Now, let us look at how you can turn it off and on and how you can use it effectively when retrieving data from a PivotTable.
Video Tutorial – GETPIVOTDATA Excel
To see GETPIVOTDATA in action, please watch the following video tutorial.
How to Use GETPIVOTDATA in Excel?
How to Toggle GET PIVOT Off/On?
By default, when you reference a PivotTable cell in a calculation, a GETPIVOTDATA formula will be created. If you would prefer to use cell references instead, you can turn off this default setting.
- Click your mouse inside the PivotTable.
- From the PivotTable Analyze tab, in the PivotTable group, click Options.
- Tick or untick the Generate Get Pivot Data command depending on if you want to turn it off or on.
How to Use GET PIVOT DATA Inside a Formula?
In this example, I have created a PivotTable that is showing the monthly sum of profit for seven products. I want to extract the totals for two of the products (Burlington and Vermont) from the PivotTable and show them in the table on the right.
As I will be referencing PivotTable data, the GET PIVOT DATA function will be automatically added to the formula.
- In the table on the right-hand side, click in the first cell for Burlington/January
- Type =
- In the PivotTable, select the cell that contains the sum of profit for Burlington in January
The GET PIVOT DATA function is automatically invoked and added to the formula.
The formula references the upper left-most cell in the PivotTable (A3), the ‘Product’ of ‘Burlington,’ and the ‘Month Name’ of ‘January’ to return the correct result.
- Press the Enter key to accept the formula
Now, even if the layout of the PivotTable is changed and the fields re-organized, the formula will still return the correct result.
Copying the GET PIVOT DATA formula down
So far, so good. We have the correct total, and now we want to copy the formula down as usual to complete the rest of the calculations. However, if you use the Auto-fill handle to copy down, this will be the result.
The formula returns the result for Burlington/January in every single cell because it hard codes the criteria. If we look at the formula for July, it is the same as the formula for January. It does not change the month name to ‘July’ as the formula is copied down.
This can be resolved by changing the hard-coded field references to cell references.
Changing fields to cell references
- Press F2 to edit the formula
- Double-click “Burlington”
- Replace it with the cell reference for Burlington in the table you are working in
- Double-click “January”
- Replace it with the cell reference for January in the table you are working in
- Use the Auto-fill handle to copy down and across to complete the table
The Advantage of using GET PIVOT DATA
In this example, I have toggled off the GET PIVOT function and have attempted to complete the table using cell references. The results are correct.
However, if I change the layout of the PivotTable, I lose the results for Vermont. The cell references are still correct for Burlington despite the change of layout, so I see the right results, but the figures for Vermont have changed location.
Using GET PIVOT stops this from happening, ensuring you get the correct results even if the layout is changed.
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
Other Excel classes you might like:
- Pivot Table in Excel, How do You Create One?
- Excel Crash Course – Learn Pivot Tables in 1 Hour
- Designing Better Spreadsheets in Excel
- Introduction to Power Pivot & Power Query in Excel
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
