Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91279 Viewers
80 Video lessons · 138219 Viewers
59 Video lessons · 57046 Viewers
52 Video lessons · 70697 Viewers
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.