Restore R1C1 Style Cell Referencing To Pivot Tables in Excel.

Reset R1C1 Style Cell Referencing To Pivot Tables in MS Excel Spreadsheet With Example.
 

When you refer to a cell in a Pivot Table in Microsoft Excel, you will see a GETPIVOTDATA formula instead of a normal R1C1 style cell reference by default (e.g. =D5) as shown in below image.

GetPivotData

You can easily turn off this GetPivotData feature, If you want to use normal cell referencing.
 

WARNING! A major disadvantage of using R1C1 cell referencing is R1C1-style references does not adjust if the Pivot Table layout changes.

How to enable RICI Referencing in MS Excel Pivto Table? or How to disable / turn off the GetPivotData Function in Excel?


To enable R1C1 referencing follow the below steps...

1) Click any cell in the PivotTable and from PivotTable Tools click the Options tab;

2) In the PivotTable group, click the small arrow on the right side of the Options command. You will see a checkmark next to the 'Generate GetPivotData' option;

3) Click the 'Generate GetPivotData' to turn the option off. Note that this is a global setting that will remain off for all PivotTables until you turn it on again.

GetPivotData option On / Off

Now you can reference data in PivotTables using normal R1C1-style referencing with the Generate GetPivotData option turned off.

How to turn the option GetPivotData back on in MS Excel Pivot Table?

R1C1 Option disable

To turn the option back on, click the 'Generate GetPivotData' option again.

Remember :- when you switch bewteen GETPIVOTDATA and R1C1, previously created references to the Pivot Table do not change.