How to Use Excel's GETPIVOTDATA Function

A pivot table is a useful tool that can sort and total information contained in cells. To use Microsoft Excel's GETPIVOTDATA function, you first have to create a spreadsheet that contains data, then create a pivot table from the fields on the spreadsheet.

Difficulty: Moderate

Instructions

1. Understand the syntax of the GETPIVOTDATA function. The formula is =GETPIVOTDATA(pivot_table, name). Pivot_table references a cell in the PivotTable or a range of cells in the PivotTable, a label of a cell above the PivotTable or the name of the range that contains the PivotTable. "Name" is text enclosed in quotations marks that describes the data.

2. Create a new workbook to practice using pivot tables. Label columns A, B and C with the headers "Name," "Month" and "Sales." Under the header row, fill in several of the rows with data. Include the same name and month more than once.

3. Hold down your left mouse key and drag it across and down so that all the cells are highlighted. Click "Data" and select "PivotTable and PivotChart Report." Follow the wizard to create a PivotTable.

4. Click "Layout" in Step 3 of 3. Depress the left mouse key and drag the "Name" button to the Row field, the "Sales" button to the Data field, and the "Month" button to the Column field. Click "OK."

5. Choose an empty cell in column A and so that it gets entered in the box on Step 3. Choose "Existing Worksheet." Click "Finish."

6. Select the first cell of the pivot table. Click "Insert" on the menu. Click "Name," "Define" and enter a name for the table. Click "OK."

7. Pick an empty cell. "Type =GETPIVOTDATA." Insert a "(" followed by the name of the table, then the calculations to be shown in the cell. Type a single name and month to get the sales total for that month, or a single name or month to get the total sales for that name or month.