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.
One often overlooked aspect of PivotTables is that they make it easier for you to locate individual data points. As an example, suppose you have a PivotTable that summarizes sales data over a series of years. If you want to display the data row that provides the value displayed in a PivotTable cell, double-clicking that cell causes Excel to drill down into the data source and create a new worksheet that contains an Excel table with a copy of the appropriate row. So for example, let's say that I wanted to see the row in the data source for this PivotTable that provided the value for FirmA 2009 in the month of March.
That's the cell that I selected inside the PivotTable. To do that, I double-click the cell and Excel creates a new worksheet and it shows an Excel table with the entire row providing the data for that cell in the PivotTable. Now this drill-down capability only works for one cell at a time, but it is possible to drill down into a data source even if the PivotTable draws its data from an external source, such as a database table or another workbook. You can do that because Excel draws the row it displays from the program's internal representation of the data source, which is called the Pivot Cache, instead of the source table itself.
After you have displayed the row providing value for PivotTable cell, you can press Ctrl+Z to try to delete the worksheet. When Excel indicates that data may exist in the sheet that you have selected for deletion, then click the Delete button in the dialog box and Excel gets rid of the sheet. Drilling down into the original data source provides context for the data that appears in your PivotTables. Rather than finding the right row among hundreds or even thousands of rows in the source table, you can display just the one you want by double-clicking the cell you want to investigate.
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.