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.
PowerPivot lets you manipulate large datasets efficiently, but you should only import the tables you need. Importing unnecessary data can slow performance and make it harder for you to find the data you want. If you discover that you didn't import a table you actually need, you can add that table to your data model in a few steps. So to start out I'll open PowerPivot and to do that I'll click the PowerPivot tab on the Ribbon and then click the PowerPivot Window button. Now to bring my new data into PowerPivot I will need to import it and in this case the data that I want to bring in is in an Access database, so I will click the From Database button on the Home tab of the PowerPivot Window and then click From Access.
The Table Import Wizard dialog box appears and I can click Browse and then the data that I want is in the MonthTable database. so I'll double-click it and when I do the database name and full path appear appear in the database name field. If this database were password- protected then I could type in my username and password and also save my password by checking this box if I wanted to. In this case the database is not password-protected so I can just click Next.
Now I can either select from a list of tables and views or I can write a query. Writing a query is beyond the scope of this course so I will leave the Select from a list of tables and views option selected and click Next, and then I get a list of tables and views. And in this case the only table in the database is one called Months. That is the one I want so I can make sure that it is selected and click Finish. There are only 12 rows of data in this table so the import happens very quickly.
Now that it's done I can click Close. After I close the Wizard PowerPivot displays the table in Datasheet view and you can see the two columns, MonthID and MonthName. Also as in an Excel workbook you can see here at the bottom left corner of the Program window that we have two tabs, the Months tab and the Data tab. Clicking the tabs moves between them so that you can see your data and work with them as desired. So don't worry if you overlook the table you wanted to import.
Just reopen the data source by using the controls in the Get External Data group and bring the table in when you want to add it. In the next movie I'll show you how to create a relationship between these two tables.
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.