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.
After you import data into your Excel, you might find you want to summarize your data in a way that's not included in the original table. For example, your table might include a Price column and a Quantity column, but not a column that multiplies those values together to find the total for each row. If you do want to multiply those values together, you can create what's called a calculated column. You can also summarize your data using a measure, which you can use within a PivotTable. In this movie I'll show you how to create both calculated columns and measures.
To begin I'll jump into PowerPivot, so I'll click the PowerPivot tab on the Ribbon and then the PowerPivot Window button to display my data within PowerPivot and now to create a calculated column I click over in the Add Column column and then I can start typing my formula. In this case I'll type an equal sign and you can see the formula here on the Formula bar, and then I'll type the names of the fields that I want in my calculation. So in this case I want the price, so I'll type a (P. The Price field's name is highlighted in blue, so I'll press Tab to accept it.
Then type an * and then a left square bracket and then I want the Quantity field, so I'll type Q, Press Tab to accept the quantity and press Enter. When I do PowerPivot adds a calculated column to my table. I can rename that column by double- clicking the column name and then when it's highlighted in blue typing a new name and in this case I will call it Total Order and press Enter.
One thing to note is that calculated columns summarize data at the row level within your PowerPivot data. So as you can see from the formula I'm multiplying the Price column's value by the Quantity column's value for each row to derive a new value. If you want to summarize your data at a high level, you can create what's called a measure and you create measures within PivotTables. So on the Home tab of the PowerPivot window's Ribbon, I'll click the PivotTable button and then the select new worksheet in the Create PivotTable dialog box and then click OK.
Now I'm back in Excel in the worksheet and I can use the controls on the PowerPivot tab of the Ribbon to create my measure. So I'll go over here in the Measures group and click the New Measure button and now I can create my measure. The table name is Data. That's the table from the PowerPivot data model and now I can type in the name for this measure and I'll make it AverageSale.
Now down in the Formula box I can create the formula and in this case the formula will be =Average( and then the name of the column and that column is from the Data table, left square bracket, and TotalOrder. That's the calculated column that I just created. So I'll highlight it in the list, press the Tab key to accept it, type a right parenthesis, everything looks good and now I can click OK. And when I do, PowerPivot creates a PivotTable with the AverageSale field in the Values area and then within the body of a PivotTable you can see that the AverageSale value for all sales was a bit over $131.4.
Now let's start adding some divisions into the PivotTable. So let's say that I only care about years. So I'll drag the OrderYear field down to the Row Labels area and you can see that the average sale for 2007, 2008, 2009 and 2010 appear inside of the PivotTable. Creating a calculated column doesn't add a column to your original data source, but it does let you summarize your data using calculations you might not have in the original table. Calculated columns generate their values on a row by row basis, but a measure summarizes all the data referred to in a PivotTable cell.
That value could be from a single table row, but more than likely it summarizes part of your data. In that case you have to use a measure instead of a calculated column.
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.