How to drill down into a pivot table

After you've built a pivot table, and spent some time looking at the data from various angles, you will likely discover interesting observations, connections, and insights that weren't visible in the raw data itself. In these cases, you might want to see the detail behind the data that appears in the pivot table. Pivot tables have an interesting feature that allows you to do just that.

Let's take a look.

Here we have a pivot table that contains product sales organized by year and city.

Notice that whenever you hover over a label or value in the pivot table, you’ll see a pop-up window that shows the details for that field at that location in the pivot table. But what if you want to see the data behind that number?

Whenever you want to see the data behind a summarized value, you can simply double click that value to get the full set of data that backs up that number.

For example, suppose you want to see the data behind the Boston 2012 number of 5,395. When you double-click that value, Excel builds a new worksheet that contains every row of source data used for that subtotal. In this case, we have 68 entries for sales in Boston for 2012. These rows are an exact copy of the rows that appear in the source data.

Likewise, if you click on the grand total for 2012, you'll get a new sheet with every sale in 2012. In this example, a total of almost 1,000 rows of data.

Note that when you drill down to get data in a pivot table, the sheets that Excel creates are completely unconnected to the pivot table and its source data. You can change, copy, or move this data as you like without affecting the existing pivot table.

You could also use this extracted data as the source for another pivot table.

When you have no further use of the drill-down data, you are free to delete the worksheets altogether.