Excel: See Detail Behind One Number in a Pivot Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: One number in my pivot table seems to be wrong. Air Canada does not typically buy XYZ, yet it is shown with that product in the report.

  1. Air Canada should not have any sales for this product.

    Strategy: You can see the detail behind any number in a pivot table by double-clicking on the number. Click on the $22,804 for Air Canada XYZ. A new worksheet is inserted to the left of the current sheet, showing all the records that make up the $22,804.

  2. Excel inserts a new sheet with the drill-down detail.

    Additional Details: If you double-click on a number in the total row or total column, you will see all the records that make up that number. You could even drill down on the Grand Total cell to get a copy of all the original records.

    Gotcha: Each drill-down creates a new worksheet. The new worksheet is just a snapshot in time of what made up the original number. If you detect a wrong number in the drill-down report, you need to go back to the original data to make the correction.