# How to Reference Pivot Table Data in Excel (with Easy Steps)

### Step 1- Creating a Pivot Table

We’ll use a sample dataset in cells B4:E40.

• Select the range of cells B4 to E40.

• Go to the Insert tab in the ribbon.
• Select PivotTable from the Tables group.

• The PivotTable from table or range dialog box will appear.
• Check that the range of cells is correct.
• Choose where you want to place your Pivot Table.
• Click on OK.

• You will see a PivotTable Fields dialog box will appear.
• Check all the options.
• Drag the Month and Year in the Rows section and Income and Expense in the Values section.

• Here’s the table.

### Step 2 – Calculate the Ratio of Expenses and Income for Three Different Years

• Select cell E4.
• Use the following formula.

`=GETPIVOTDATA("Sum of Expenses",\$A\$3,"Year",2010)/GETPIVOTDATA("Sum of Income",\$A\$3,"Year",2010)`

Alternatively:

• Select cell E4.
• Insert the equal(=) sign.
• Click on the sum of expenses for 2010 in the pivot table.
• Insert the divide (/) sign.
• Click on the sum of income for 2010 in the pivot table.

• Press Enter to apply the formula.

• Repeat to get the ratio of expenses and income for the years 2011 and 2012 in E5 and E6, respectively.
• You will get the following result.

### Step 3 – Calculate the Overall Ratio of Expenses and Income

• Select cell E7.
• Use the following formula.
`=GETPIVOTDATA("Sum of Expenses",\$A\$3)/GETPIVOTDATA("Sum of Income",\$A\$3)`

Alternatively:

• Select cell E7.
• Insert the equal (=) sign.
• Click on the grand total of the sum of expenses in the pivot table.
• Insert the divide (/) sign.
• Click on the grand total of the sum of income in the pivot table.

• Press Enter to apply the formula.

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 the GETPIVOTDATA Function

### Method 1 – Using PivotTable Analyze

Steps

• Select any cell in the pivot table.
• Go to the PivotTable analyze tab on the ribbon.
• Select the Options drop-down from the Pivot Table group.
• Uncheck the Generate GetPivotData option.

### Method 2 – Utilizing Excel Options

Steps

• Go to the File tab on the ribbon.
• Select Options. Select More if you don’t see Options on the list.

• This open up the Excel Options dialog box.
• Select the Formulas option.
• Uncheck Use GetPivotData functions for PivotTable references from the Working with formulas section.
• Click on OK.

## Things to Remember

• Even if you change the layout of the pivot table, the GETPIVOTDATA function shows the correct result. For a cell reference, however, it returns an error if you modify the layout.
• Excel produces the GETPIVOTDATA function whenever you click on the pivot table while editing formulas.

<<Go Back to Excel GETPIVOTDATA | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio