Pivot Table Drilldown
When you summarize your data by creating an Excel Pivot Table, each number in the Values area represents one or more records in the pivot table source data. If you have questions about the summarized data, a quick way to see the underlying records is to use the Show Details (DrillDown) feature. With the Show Details feature you can create a list of the related records on a new worksheet, then examine the data, to see where any problems occur.
Extract Pivot Table Records
In the sample pivot table shown above, new customer counts are shown by year and by state. To see the customer details for any number in the pivot table, use the Show Details feature.
To see the underlying records for a number in the pivot table:
In the Pivot Table, right-click the number for which you want the customer details.
In the pop-up menu, click Show Details
Name Show Details Sheets When Created
If you frequently use the Show Details command while working with a pivot table, your workbook can quickly fill up with the data details sheets. At the end of your working session, you probably want to clear out all the extra sheets, so they don’t clutter up the workbook.
With the following event code, you can automatically name the sheets created by the Show Details feature, so they are easy to identify. The code will add a prefix of “XShow_” to each drilldown sheet. Then, you can easily identify and delete them. Or, use the code in the following section, to automatically delete the sheets before closing the workbook.
Sort a Pivot Table in Excel
You can sort a Pivot Table in Excel horizontally or vertically. This allows you to see, at a glance, the rows or columns containing the greatest or the smallest values.
We will illustrate how to sort the values in a Pivot Table, using the example pivot table on the right, which shows a company’s monthly sales.
In order to sort a column or row of the Pivot Table, we use the Excel Sort Ascending and Sort Descending Excel commands which are represented by the following symbols in the Excel menu: