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.
PowerPivot helps you analyze large datasets efficiently but you do have to import data into PowerPivot to get started. In this movie I will show you how to bring data from an Excel workbook into PowerPivot. First thing you do is click the PowerPivot tab on the Ribbon and then in the Launch group click the PowerPivot Window button. Then inside of PowerPivot on the Home tab click the Ctrl that will let you get the data from the type of source that you want, and you have many different sources available. For example you can get your data from a database, SQL Server, Access, Analysis Services, or even another PowerPivot workbook.
You can also get your data from a report, and that would be something like a Microsoft SQL Server Reporting Services report. You can also download data from the Azure DataMarket, from RSS data feeds, from text files, or in this case we're going to use Other Text Sources because at the bottom of this list you'll find Microsoft Excel and that is the type of file that we want to bring our data in from. So I'll select Excel File and click Next.
Now I can indicate the file that I want to use and I know that the file I want has the first row as column headers. So I will check that box and now I can click the Browse button and use it to find the file that I want. In this case the file that I want is called WholesaleData. So I'll click it and click Open. Everything looks good, so I can click Next to move to the next page of the wizard and when I do Excel looks inside the file to see if there are any tables that it can use.
This second table is called Data. That is the one I want and to make sure I'll click Preview & Filter. That way I get a short preview of the data that's in that file and it will take just a moment to come up. After the data comes up, you can preview to make sure everything looks right and in this case I have OrderID, StoreID ,and all the data I expect. So if that looks good. I can click OK and then within the Wizard I can click Finish to import the data into PowerPivot.
There are over 900,000 rows of data in this file, so it may take a while to import, but to get started I will click Finish. After I do, PowerPivot indicates that the import is in progress and in a moment or two we should see that it has imported about 100,000 rows then 200 then 300,000 and so on. So now we are starting to get in our data. It looks like we're a little bit more than halfway through and coming up on 900,000. Almost there, and I should point out that at anytime during the transfer I could have clicked the Stop Import button to stop the import from occurring.
But now we were successful. 964,260 rows were transferred into PowerPivot, so I can click Close, and the data appears within PowerPivot. Now I want to save this data model, so I'll click the Save button. After I click Save, the PowerPivot window disappears. In this movie I showed you how to bring Excel data into PowerPivot. But the steps are almost exactly the same for most other data sources. Just select the type of source you want and go through the steps to bring it in for analysis.
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.