Little Known Excel Shortcut #15
By BankersBall on Jan 29, 2008 in Excel, Excel Shortcuts
03 and 07 users, rejoice.
SHORTCUT:
Excel ‘03: Customize Toolbars > Commands > Data (end of menu) > Generate GetPivotData button. Click the button on/off within a Pivot Table.
Excel ‘07: Options > PivotTable group (Options) > uncheck Generate GetPivotData.
PURPOSE: The default setting for Excel 03 and 07 of formulas that reference PivotTable cells is to generate a GetPivotData formula. To have relative cell references, follow the steps above.
FURTHER EXPLANATION: GetPivotTable is good in that if the cells move around when you refresh the references follow. But every now and then if you just want to make some quick calculations, just turn of GetPivotData.
On Jan 29, 2008, Matt said:
You don’t need to do this step in any version of excel (post ‘97) - simply do an initial drag on the cell you want to reference, hit F2, backspace to keep the cell reference (or just type it in manually) - costs you an extra second, but gives you flexibility without bothering with a setting you’re going to forget is on or off.