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 PowerPivot data model, you can sort, filter, and create a PivotTable to summarize your data. You can also create your own summaries using Data Analysis eXpressions or DAX. DAX expressions are similar to Excel worksheet formulas that you use to summarize values in Excel tables. So just for a quick comparison, if I wanted to find the 2008 sales in the table to the left, I would type =sum( and then we're looking at the values in D3 through D8.
So type a right parentheses and press Enter and there is the formula for just those cells. If I wanted to find a total of all sales in the Sales column of the table then I would type =sum(, and then the name of the table, which is Table1, so T-A-B-L-E1 and then a left square bracket so that I can start naming the column I want to use, and in this case that is Sales, so I'll type in S and then in the formula AutoComplete list I see that sales is highlighted, so I can press Tab to accept it.
Type a right square bracket to close the table column reference and then a right parentheses to close the sum formula. Press Enter and I get my result. And if I highlight that cell again, I can see that I have the table reference followed by the column reference all as an argument inside of the SUM formula. Now let's shift over to PowerPivot and create a calculated column so that you can see how it compares. So I'll click the PowerPivot tab on the Ribbon and then click the PowerPivot Window button, and here I have my PowerPivot data model.
What I want to do is create a new column that finds the total for each line item, so I will be multiplying Quantity by Price. So I will click in the first cell in the Add Column column, and then type equal. , You'll see the typing here in the Formula Bar as opposed to in the cell. So type equal and then a left square bracket and then I can use the Down Arrow key to select the first field I want, and that's Quantity. Press Tab and then in PowerPivot the program encloses the field name in square brackets, so you don't need to type it in yourself.
Now type an asterisk for multiply, then a left square bracket and we're multiplying by Price, so I'll type a P and the highlighted item is Price so I'll press Tab. Everything looks good. And when I press Enter you'll see the PowerPivot creates a calculated column called CalculatedColumn1 and its values are Quantity times the Price. DAX lets you extend the basic capabilities of PowerPivot by creating expressions that summarize your data. In the rest of this chapter I'll go into more detail on how to use DAX to summarize your data.
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.