Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
If you've ever tried to create a PivotTable from a file containing hundreds of thousands of rows of data, you might have noticed some performance issues. PowerPivot, a new technology introduced alongside Excel 2010, uses advanced data handling techniques to let you manipulate large data sets more efficiently. In this movie, I will give you an overview of how to analyze data in PowerPivot and then later in this chapter we will dive into specifics of bringing data into PowerPivot. So this is what a PowerPivot datasheet looks like. I have several different columns of data. As you can see at the bottom left-hand corner this PowerPivot data set contains over 964,000 rows.
Now normally in Excel that would take a lot of time to work with, but let's say that I want to filter it so that I only show orders of Quantity 14. So I can click the filter arrow and create my filter. Click OK and Excel filters the rows and you can see we're down to just under 50,000. Creating another filter, say for store number one, limits the data even further. Now we are down under 500. And then I will just clear the filters.
As you can see, working with large data sets within PowerPivot happens much more quickly than it would if you were just using a regular PivotTable and an Excel worksheet. So now let's create a PivotTable off of this data, and I'll just create it here in my workbook, and then just as filtering that data happens much more quickly in PowerPivot than it would in Excel, I can create a PivotTable even with this massive data set much more quickly within PowerPivot. So I'll just put my StoreID in Row Labels, the Year in the Column Labels, and Total order in the Values area, and there is my data.
Again, over 960,000 rows summarized just that quickly. The technology behind PowerPivot lets you manage large datasets much more efficiently than is possible using conventional PivotTables. In the next couple of movies I'll show you how to install PowerPivot, bring your data in, and then work with it.
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.