Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One often overlooked aspect of Pivot Tables is that they make it easier for you to locate individual data points. As an example, suppose you have a Pivot Table that summarizes sales over a series of years. If you want to display the data row that provides the value displayed in a Pivot Table cell, double-clicking that cell causes Excel to drill down into the data source and create a new worksheet that contains a copy of the appropriate row. For drill down to work, you must ensure that the proper option is turned on. To do that, click any cell on the Pivot Table and then on the PivotTable toolbar click Table Options.
In the Table Options dialog box ensure that the Enable drilldown checkbox is selected. If it is, click OK and you are ready to go. Drilling down to the underlying data source is simply a matter of double-clicking the cell that you want to display. For example, if I wanted to display the row providing the value for cell D5, I could double-click the cell and have Excel create a new worksheet that contains only that row. So for year 2008, Quarter number one, Month, Company, and Revenue.
Unfortunately, this drilldown capability only works for one cell at a time. That said, it is possible to drill down into a data source even if the Pivot Table draws its data from an external source. You can do so because Excel draws the row it displays from the program's internal representation of the data source, called the Pivot Cache, instead of the source table itself. After you have displayed the row providing the value for Pivot Table cell, you can click Edit>Delete Sheet to get rid of the worksheet. Confirm you want to get rid of it, and it's gone. You can also drill down into Grand Total or Subtotal rows.
So, for example, if I wanted to display all of the rows providing values summarized in this cell, which finds a subtotal for FirmB in 2008, I can double-click it and Excel provides those rows as well. Once again, Edit>Delete Sheet, OK and it's gone. Drilling down into the original data source provides context for the data that appears in your Pivot Tables. Rather than finding the right rows among hundreds or even thousands of rows in the source table by hand, you can display just the ones you want by double-clicking the cell that contains the values of interest.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74328 Viewers
80 Video lessons · 129623 Viewers
52 Video lessons · 63882 Viewers
59 Video lessons · 49669 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.
Your file was successfully uploaded.