Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
When you create a formula, it's usually a good idea to draw the formula's values from the original data source. That way if the source changes, your formulas result will update the next time Excel recalculates your workbook. But what you do if your PivotTable contains data drawn from an outside source and you can't get at the original tables? In that case you can refer to a cell within the PivotTable using a GetPivotData formula. Let's say that you want to use the data in a PivotTable cell in your formula and for that we will make it the value in C6, which is the value for January 2009 for FirmB.
To create this formula, you start typing another formula such as Sum, so click any cell outside the PivotTable and then type equal and then name of the function, Sum, and then a left parenthesis and now you click the cell that you want to use the data from inside the PivotTable. So in this case I will click cell C6 and you see that Excel creates a GetPivotData function, which has a bunch of information inside of it. This information refers to the cell that I just clicked inside the PivotTable and I will walk you through step by step to show you what it's doing to identify that cell.
The first thing it's doing is indicating the field that contains the value,. That field name is Revenue and you can see that the sum of Revenue field is what's summarized within the body of the PivotTable, so that's the first part. The second part is the cell A3. A3 is the first cell at the top left corner of the PivotTable. Year indicates the Year field and 2009 is the year that is applied to the cell in C6. So as you can see, the year is 2009 and cell C6 clearly falls within that part of the PivotTable.
Next the month is January and you can see that here it is in fact for January, and then finally you have the Company, which are the columns, and it is FirmB, and cell C6 is under FirmB so it is that company's data. Now you can close out the formula by typing a right parenthesis and then pressing Enter. Normally you wouldn't create such a simple formula as I have here. Instead you would use your GetPivotData function as part of a larger calculation, but I wanted to keep it simple just for demonstration purposes.
So I'll click a cell in the PivotTable to bring the PivotTable Field List back. When you pivot the PivotTable, as long as the cell that contains the data you want still appears within the PivotTable, then the formula won't change. So let's say that I put Company below Year and Month in the Row Labels area. When I do that, even though the arrangement of the PivotTable has changed the value of the formula remains the same, because the value is still displayed within the PivotTable. And you can see that value here. It's currently in cell B7.
And if were to pivot the PivotTable back by putting Company back in the Column Labels area, the value goes back to C6 instead of B7 and the formula remains the same. Even though you can pivot your PivotTable and have the formula retain its value, if you hide one of the fields that the GetPivotData formula uses to identify the proper cell within the PivotTable, then the formula will generate an error. So let's say for example that instead of having Company within the Column Labels or the Row Labels area that I remove it from the PivotTable. When I do, the formula in cell F1 which refers to the PivotTable cell generates a reference error and that's because it no longer has the information that it needs to identify the proper cell within the PivotTable, and also the value for January 2009 for FirmB no longer exists within this particular range within the PivotTable.
You can see that all we have is the sum of revenue for both companies and it's not broken out by individual company. If I drag the Company field header back to the Column Labels area, then the formula gets restored to its proper order and it can identify the cell it needs to return a value. Now if you find that clicking a PivotTable cell doesn't generate a GetPivotData formula, then it's possible you or someone else might have turned off the option that enables you to do that. To make sure you can use PivotTable data in the formula, click the File tab, then click Options, and then on the Formulas tab of the Excel Options dialog box, make sure that the GetPivotData functions for PivotTable References check box is selected.
When it is, you can create your GetPivotData functions the way that I did in this movie. The GetPivotData function makes it easy to summarize PivotTable data in your worksheets. If it's not practical to draw the data from the original source, you could always use the PivotTable reference instead.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.