Excel -- Pivot Tables -- GetPivotData Generate GetPivotData GetPivotData Formula Using Cell References in GetPivotData Download the zipped sample file	Amazon.com

Generate GetPivotData In Excel 2002, and later versions, when you type an equal sign, then click on a cell in a pivot table, you may see a GETPIVOTDATA formula, instead of a cell reference. If you prefer to use a cell reference, you can: type the reference, e.g. =$B$5 or add the Generate GetPivotData button to a toolbar, to turn this feature off.

To add a Generate GetPivotData button:

On the PivotTable toolbar, click the Toolbar Options button Click the Add or Remove Buttons command Click PivotTable, to open the submenu Near the end of the commands list, click on Generate GetPivotData to add a check mark. Click on the worksheet, to close the menu.
On the PivotTable toolbar, click the Generate GetPivotData button to toggle this feature on and off. Watch the Video View the steps to customize the toolbar, in a short video clip.

GetPivotData Formula To extract data from a PivotTable, you can use the GetPivotData function. The function arguments depend on the version of Excel that you're using, so check Excel's on-line help for examples. The example at the right is for Excel 2002, and returns the number of Units sold in Ontario. In Excel 2002, if you have the Generate GetPivotData feature turned on, this formula will be created automatically, when you reference a cell in the Pivot Table.
In Excel 2000 or Excel 97, you have to create the GetPivotData formula manually, by typing an equal sign, the function name, and the required arguments, just as you would for any other function. This example shows the function arguments required for Excel 2000 or Excel 97, to return the number of Units sold in Ontario.
This example shows the function arguments required for Excel 2000 or Excel 97, to return the total number of Units sold.

Using Cell References in GetPivotData Instead of typing item or field names in the GetPivotData arguments, you can refer to worksheet cells. In this example for Excel 2002, cell A10 contains one of the arguments, and the formula refers to that cell.
In this example for Excel 2000, cell A10 contains one of the arguments, and the formula refers to that cell. A space is included at the end of "Units ". If this is omitted, the formula will return an #N/A error. In the next example, both arguments come from cell references, and a space character is concatenated in the middle, using the & operator.

1. Pivot Tables -- Dynamic Data Source 2. Pivot Tables -- Data Field Layout 3. Pivot Tables -- Show and Hide Items 4. Pivot Tables -- Clear Old Items 5. Pivot Tables -- Field Settings 6. Pivot Tables -- GetPivotData 7. Pivot Tables -- Grouping Data 8. Pivot Tables -- Multiple Consolidation Ranges 9. Pivot Tables -- Printing    10. Pivot Tables -- Custom Calculations  11. Pivot Tables -- Pivot Cache      12. Pivot Tables -- Protection        13. Pivot Tables -- Grand Totals

Home     Excel Tips     Excel Files      Blog    Contact
		The Excel Store	Debra Dalgleish