Reveal Details in an Excel Pivot Table with Drill Down Reports

Even the most basic of Excel Pivot Tables provides powerful data summarization. However, beyond the basics, tons of useful tips and tricks can take you from casual user to Excel master in no time at all. One of these little known tools allows you, the user, to show details for original data included in the selected Pivot Table cell. These revealed details are commonly referred to as a “Drill Down Reports”.

It is important to understand that when you summarize your data by creating a Pivot Table, each number in the Values area represents one or more records in the original source data. Occasionally you will need to see the data that goes into those values. To reveal those Drill Down Reports, double click on any cell in the Values field, highlighted below. The extracted records are then opened in a new Worksheet.

Alternatively, you can reveal those same Drill Down Reports by right clicking on the Value cell and select “Show Details”. It is important to note that the extracted records are copies of the original records in the pivot table source data. They are not linked to the original records or to the pivot table.

Bonus Tip:

Does your Pivot Table have empty cells? Format empty cells in your Pivot Table by right clicking on any of the values in the Pivot Table and selecting Pivot Table Options. Inside Pivot Table Options, navigate to the Layout/Format Tab and change the “For Empty Cells” field to whatever value you would like to appear in empty cells.

“Sean Bugler is an Instructor at Learn iT! specializing in desktop class application usage. When he’s not teaching you can often find him reading up on the latest news in software and technology. Connect with him on Twitter @sbglr.”