# How to Reference PivotTable Data in Excel Formulas with GETPIVOTDATA?

*In this article, I have shown you how you can reference PivotTable Cell Data in Excel formulas using GETPIVOTDATA function. If you feel uneasy with the auto implementation of GETPIVOTDATA function in the Excel formulas, you can stop it!*

After you have created a pivot table, you may want to create formulas that will reference one or more cells within that pivot table. Here is the way how you can do that.

**Related: **Pivot Table Tutorials for Dummies: Learn Excel Pivot Table Step by Step

Page Contents

## Reference PivotTable Data Using GETPIVOTDATA Function

The following figure shows a pivot table. The pivot table displays income and expense information for the three years.

We entered a formula in column M, and this column is not part of our pivot table. Formulas entered into cells M3, M4, M5, and M6 will calculate the expense-to-income ratio for each year and grand total. To enter the formula in cell M3, we have selected cell M3 at first. Then we entered **“=”** sign, then selected cell **J3**, then entered **“/”** sign, and lastly selected cell **I3**. We should have **“=J3/I3”** in cell **M3**.

In fact, the formula in cell **M3** is

**=GETPIVOTDATA(“Sum of Expenses”,$H$2,”Year”,2010)/GETPIVOTDATA(“Sum of Income”,$H$2,”Year”,2010)**

When we have used the pointing technique (selecting cells with the mouse when entering formula) to create a formula, Excel replaced those simple cell references with a much more complicated function GETPIVOTDATA. If you type the cell references manually (instead of pointing to them), Excel will not use the GETPIVOTDATA function. Using the GETPIVOTDATA function helps ensure that the formula will continue to reference the pointed cells if the pivot table layout is changed. Following figure shows the complete work we have done so far.

The following shows the pivot table after expanding the years to show the month detail. You will see that the formulas in column M still show the accurate result even though the referenced cells in the formulas are in a different location. If I had used simple cell references like **“=J3/I3”**, the formula would not return the same result after expanding the years.

Caution

Using the GETPIVOTDATA function has one limitation: The data that it retrieves must be visible. If you modify the pivot table so that the value used by GETPIVOTDATA is no longer visible, the formula will return an error.

## How to stop auto using of GETPIVOTDATA function?

If you want to prevent Excel from using the GETPIVOTDATA function when you point to pivot table cells at the time of creating a formula, choose **PivotTable Tools ➪ Analyze ➪ PivotTable ➪ Options ➪ Generate GetPivot Data** command. Deselect the check mark to turn off GETPIVOTDATA function working when you point cells in the pivot table. This command is a toggle.

## Download working file

Download the working file that I’ve used to create this article from the link below:

pivot-table-cell-referencing.xlsx

Happy Excelling 🙂

**One hour course on Excel 2016 Pivot Tables** (* 100% free*)

I have referenced few cells of the pivot table in another file. Whenever I open that other file containing reference, I see “#REF#” in the cells where I have references to the pivot table. As soon as I’ll open the file containing the pivot table, those cells would automatically populate the correct data. How can I fix this problem, so that I don’t have to open multiple files to see the data in my report?