Search Contextures Sites

Excel Pivot Table Tutorial -- GetPivotData
Generate GetPivotData Turn Off Generate GetPivotData Excel 2007 Turn Off Generate GetPivotData Excel 2003 Watch the Excel Pivot Table Tutorial Video GetPivotData Formula Using Cell References in GetPivotData Download the zipped sample file for this Excel pivot table tutorial	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. In this pivot table tutorial you'll see how to work around that problem, or prevent it from occurring. If you prefer to use a cell reference, you can: type the reference, e.g. =B5 or use the Generate GetPivotData command to turn this feature off. There are instructions below, for finding this command

Turn Off Generate GetPivotData Excel 2007 In Excel 2007 and Excel 2010, you can turn off the Generate GetPivotData command by using a command in the Excel Ribbon. Select any cell in a pivot table. On the Ribbon, under PivotTable Tools, click the Options tab In the PivotTable group, click the drop down arrow for Options Click the Generate GetPivotData command, to turn the feature off or on.

Turn Off Generate GetPivotData Excel 2003 In Excel 2003, you can turn off the Generate GetPivotData command by adding a button to the PivotTable toolbar.

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 Generate GetPivotData Video View the steps to customize the Excel 2003 toolbar, in a short pivot table tutorial video clip.

GetPivotData Formula To extract data from an Excel Pivot Table, 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 2010, and returns the total for file folders. In Excel 2002, and later versions, if you have the Generate GetPivotData feature turned on, this formula will be created automatically, when you reference a cell in the Excel 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, cell E2 contains a product name, and the formula refers to that cell. The formula will return the total for the Paper product..
Excel 2000 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.

Using Cell References For the Data Field in GetPivotData Cell references work well for the pivot fields and pivot items, but can cause problems if you try to refer to a data field. In this example, cell E2 contains the word "Qty", and you'd like to refer to that cell, instead of having "Qty" in the GetPivotData formula.
However, if you change the first argument, data_field, to a reference to cell E2, the result is a #REF! error   =GETPIVOTDATA(E2,$A$3,"Product","Paper")

Add an Empty String To fix this problem, you can concatenate an empty string ( "" ) at the end of the cell reference:  =GETPIVOTDATA(E2&"",$A$3,"Product","Paper") With this simple change to the formula, it returns the correct result. Download the zipped sample file for this Excel pivot table tutorial

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish