Select any cell in the pivot table. Go to the Design tab on the Ribbon. Select the Grand Totals option. Choose the option that is appropriate for your pivot table (usually On for Rows Only). The GETPIVOTDATA Function is categorized under Excel Lookup and Reference functions. The function helps to extract data from specified fields in an Excel Pivot Table. The pivot table is used often in financial analysis to facilitate deeper analysis of given data.
- Excel Mac Pivot Table › excel pivot table training pdf › spreadsheets to practice pivot tables › excel pivot table tutorial pdf. Videos Course online, free.
- An Introduction to Excel GETPIVOTDATA. Excel Tables Tutorial: Beginners Guide for Windows & Mac - Duration: 16:09. Excel Campus - Jon 104,813 views.
This GETPIVOTDATA in Excel 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.
What is GETPIVOTDATA?
GETPIVOTDATA is a function in Excel 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.
GETPIVOTDATA 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 GETPIVOTDATA function added to the formula.
Now, you can turn off GETPIVOTDATA 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 GETPIVOTDATA off.
So, if GETPIVOTDATA is long and cumbersome looking, 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 GETPIVOTDATA, check out Brad Edgar’s post and video 3 Reasons Why You Need to Start Using Excel GETPIVOTDATA.
Let us look at how you can turn GETPIVOTDATA off and on and how you can use it effectively when retrieving data from a PivotTable.
Video Tutorial
To see GETPIVOTDATA in action, please watch the following video tutorial.
Method
Toggle GETPIVOTDATA 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 GetPivotData command depending on if you want to turn it off or on.
Using GETPIVOTDATA in 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 GETPIVOTDATA 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 GETPIVOTDATA 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.
Excel Getpivotdata Drag
- 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 GETPIVOTDATA 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 the GETPIVOTDATA function hard codes in 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.
Excel 2016 Getpivotdata
Changing fields to cell references
- Press F2 to edit the GETPIVOTDATA 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 GETPIVOTDATA
In this example, I have toggled off the GETPIVOTDATA 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 GETPIVOTDATA 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:
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.