Pivot tables in Microsoft Excel are the world’s most used tool for slicing and dicing data. Often, a pivot table gives us more information than we need, requiring us to extract relevant data. Then, it becomes easier to chart and format the relevant data.
The GETPIVOTDATA Excel function makes it easy to extract desired data from a pivot table. In this article, we’ll share step-by-step instructions for how to use the GETPIVOTDATA Excel function and how the function can benefit your work as an accountant. Follow along in the workbook GETPIVOTDATA.xlsx.
How to use GETPIVOTDATA
In Columns E through I we have fictitious data on United States naval expenses. Each row contains information on a payment for the vendor, including type of expense, amount of payment, date of payment and year of payment.
To summarize this data in a bill of material (BOM), we use a pivot table with Row Fields, Year and Vendor, and Column field, Type of Expense. We find, for example, from cell P22 that Cher was paid $257,029,865 in 2019.
Suppose we want to extract payments for any year to Cher, Katy, and the Boss for missiles, warranty and radar. GETPIVOTDATA comes to the rescue. To start our GETPIVOTDATA exercise, go to a blank cell such as Q12 and point to cell P22. You will see the following formula:
=GETPIVOTDATA("Amount",$L$18,"Vendor","Cher","Type of expense","missiles","Year",2019).
With cell L18, this formula extracts the entry in the pivot table with vendor Cher, missiles expenses, in year 2019. No matter how things move around in the pivot table, this formula will return the correct value.
Figure 1 shows how using the GETPIVOTDATA function, we can extract any payments to Cher, Katy and the Boss for any year, including total payments for missiles, warranty and radar.
Figure 1 Results from using GETPIVOTDATA
Here is how to create this function.
- To begin, delete the formulas from the range M12:O14.
- Next, go to cell M12 and point to any cell in the pivot table to place a GETPIVOTDATA function in cell M12.
- Now, change the formula in M12 to
= GETPIVOTDATA("Amount",$L$18,"Vendor",$L12,"Type of expense",M$11,"Year",$L$9)
and copy the formula from M12 to M12:O14.
A few notes:
- By making the reference for Vendor $L12 (note the $ sign!), we ensure that in each row we pull data for the correct vendor.
- By making the reference for Type of Expense M$11 (again, note the $ sign!), we ensure that in each column we pull data for the correct type of expense.
- By making the reference for Year $L$9 (note the dollar signs!), we ensure that each formula pulls data for the year entered in cell L9.
You will be amazed and pleased with the power of GETPIVOTDATA.
How to turn off GETPIVOTDATA
Sometimes you want to turn GETPIVOTDATA off. To see why, go to any blank cell and point to any cell in the pivot table. You will obtain a GETPIVOTDATA formula, but if you drag the formula, you always get the same number. There are two ways to avoid this undesirable behavior:
- From the FILE menu choose OPTIONS and then FORMULAS. Then uncheck the following option under Working with Formulas:
Use GETPIVOTDATA functions for Pivot Table Functions
OR
- Suppose, for example, you want to drag down data from a pivot table beginning with cell Q21. Instead of pointing to cell Q21, type =Q21 and drag away.
Microsoft Excel is a powerful tool for an accountant in any functional area. Build your expertise and skillset in Excel with a Becker CPE course.
