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 run a small business, you might maintain important business data in an Access database. If you do, you can create a PivotTable based on the original database table, instead of importing the data into Excel. In this movie, I'll show you how to do just that. To create a PivotTable, first you click the Insert tab on the ribbon, and then click the PivotTable button. Then in the Create PivotTable dialog box, select the Use an external data source option, and then click Choose Connection.
If the connection we want doesn't appear in the existing connections list, so we'll click Browse for More and then navigate to my Exercise Files folder, which is on my Desktop in the Exercise Files folder and in Chapter 01. And the database that I want to use is called PTDatabase, so I'll click it and click Open. Now we can create the PivotTable report on an existing worksheet and right now the location is a Sheet1 cell A1 and that is the highlighted cell here. That's fine.
So we can click OK and create a link to that data and create a PivotTable based on that data. And now I can create the PivotTable as I would normally. So in this case, I will have Company providing the values for the Row Labels area and then we'll put Quarter over in the Column Labels area and then we'll fill in the values or the sum of the sales for each Company for each of those Quarters in the Values area. Creating a PivotTable by connecting to an Access database table saves you the trouble of importing the data into your workbook.
However, you must be able to connect to the database either as a file on a same computer or over a network for your PivotTable have the most current data. If you think maintaining your connection to the database will be an issue, then you should import the data into Excel and create your PivotTable from that imported data. I should also point out that the techniques I showed you in this movie also let you connect to an online analytical processing or OLAP cube, such as those created by SQL Server Analysis Services.
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.