When you're looking at an Excel spreadsheet full of data, a PivotTable lets you slice and dice the data so you can summarize, analyze, and explore key findings. But even the standard PivotTable may still be a bit cumbersome, making it difficult to extract the relevant data you need. That's where the GETPIVOTDATA function can save you time and make it easy to chart and format data. To help you learn how to use GETPIVOTDATA, we're walking you through the step-by-step instructions and when to use this function.
Follow along in the workbook GETPIVOTDATA.xlsx.
What is the GETPIVOTDATA function?
Use GETPIVOTDATA to extract specific information from a PivotTable. This way, you can retrieve summary data without having to do a manual search.
Why use it?
GETPIVOTDATA offers three key benefits:
- Accuracy: Your data is always updated and linked correctly to the PivotTable
- Dynamic reporting: You can build dynamic reports that automatically update whenever the PivotTable changes.
- Automation: Automatic updates reduce manual errors while simplifying the process of pulling specific information from complex PivotTables
How to use GETPIVOTDATA
When you open the sample spreadsheet, you'll see that 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 created a pivot table with Row Fields, Year and Vendor, and Column field, Type of Expense. You'll see 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, warranties and radar. Trying to find that data individually would take a pretty long time, but 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
Practical learning
Here is how to create the GETPIVOTDATA 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.
Build new skills with Becker CPE
Becker offers a wide variety of Excel CPE courses to help you learn new skills that will save you time, improve your accuracy, and increase your productivity! Check out the following CPE courses:
- Excel: Top 10 Functions for Accountants
- What AI brings to Excel That Makes Your Life Easier
- Excel: Solve Hard Problems in Corporate Finance
Get a FREE CPE course from Becker
With Becker, you can meet your CPE requirements, gain new skills, and stay aware of the latest updates and news. See why more accountants are choosing Becker with a free CPE course!
Related Articles
Share
