Microsoft Office Tutorials and References
In Depth Information
To avoid this behavior, you can enter the entire formula by manually typing
it on the keyboard. Typing =E9
=E9 in a cell forces Excel to create a relative ref-
erence to cell E9. You are then free to copy the formula to other cells.
There is also a way to turn off this behavior permanently:
11. Select a cell inside an active pivot table.
22. The Pivot Table Tools tabs displays. Select the Analyze tab. From
the PivotTable group, select the Options drop-down and then select
the Generate GetPivotData icon. The behavior turns off.
33. Enter formulas by using the mouse, arrow keys, or keyboard without
generating the GETPIVOTDATA function.
Microsoft made GETPIVOTDATA the default behavior because the function is
pretty cool. Now that you have learned how to turn off the behavior, you
might want to understand exactly how it works in case you ever need to use
The GETPIVOTDATA function returns data stored in a pivot table report. You
can use GETPIVOTDATA to retrieve summary data from a pivot table report,
provided that the summary data is visible in the report. This function takes
the following arguments:
data_field — This is the name, enclosed in quotation marks, for the
data field that contains the data you want to retrieve.
pivot_table — This is a reference to any cell, range of cells, or
named range of cells in a pivot table report. This information is used
to determine which pivot table report contains the data you want to
• field1, item1, field2, item2,...
field1, item1, field2, item2,... — These are one to 126 pairs of field
names and item names that describe the data you want to retrieve. The
pairs can be in any order. Field names and names for items other than
dates and numbers are enclosed in quotation marks. For OLAP pivot
table reports, items can contain the source name of the dimension as
well as the source name of the item.
Calculated fields or items and custom calculations are included in