A PivotTable is a summary machine, and that is its strength. Yet a single cell can hide a lot. You see that Electronics sold 4,200, but which orders made up that number? Copying the raw data and filtering by hand is slow and error-prone. Drill-down removes that pain. With one action, you jump from any total straight to the exact rows behind it.
This guide shows every way to explore the detail in a pivot. First, it explains what drill-down really means. Then it covers double-click, expand and collapse, and Quick Explore. Real steps and a full troubleshooting section follow. By the end, you will move from summary to source in a single click.
What Drill-Down Means
Drill-down is the move from a total to its detail. A summary cell holds many source rows beneath it. Drilling reveals those rows so you can inspect them. The infographic below shows the basic idea.
This bridges the gap between overview and evidence. A manager sees the headline number first. Then a quick drill shows exactly what drove it. As a result, you answer follow-up questions on the spot.
Double-Click to See the Detail Rows
The fastest drill is a simple double-click. You double-click any value cell in the pivot. Excel then creates a new sheet with the underlying rows. So the detail behind that total appears at once.
Expand and Collapse Fields
Not every drill needs a new sheet. Often you just want more or less detail inline. The plus and minus buttons expand or collapse a field. Therefore you can open one year down to its quarters, as below.
Drill Down and Up in Hierarchies
Grouped dates and models create real hierarchies. You can move down and up through their levels. For instance, drill from Year to Quarter to Month. Consequently, you follow a trail without losing your place.
Quick Explore to a New Dimension
Quick Explore is a hidden gem for analysis. It lets you drill sideways into a related field. From one region, you can jump to its products. The infographic below shows this cross-dimension drill.
Control Who Can Drill
Drill-down is powerful, so sometimes you limit it. A shared report may not want users seeing raw rows. Fortunately, one option turns drilling off. You set it in PivotTable Options.
Troubleshooting Drill-Down
All three problems below are the most common. Each has a clear cause and a quick fix.
Double-click does nothing or edits the cell
First, check exactly where you are double-clicking. Drill-down works on a value cell, not a label. A double-click on a heading may just edit text. So aim for a number in the values area. If it still fails, open PivotTable Options and the Data tab. There, confirm that "Enable show details" is ticked. Once it is on, the double-click opens the detail sheet.
The detail sheet is out of date
Remember that a drill creates a static snapshot. It copies the rows as they were at that moment. So it never updates when the source data changes. First, refresh the PivotTable to load the latest data. Then double-click the value again to make a fresh extract. Also delete old detail sheets, since they can pile up quickly. That keeps your workbook tidy and your numbers current.
Expanding shows far too much detail
A full expand can flood the pivot with rows. This happens when you expand an entire field at once. First, collapse everything back to the top level. Then expand only the single branch you need. Use the plus button on one item, not the whole field. For a very deep model, drill down step by step instead. That keeps the view focused and easy to read.
Frequently Asked Questions
- How do I see the data behind a PivotTable total?Double-click the value cell you want to inspect. Excel then opens a new sheet with the source rows. Notably, that extract is a snapshot in time. So refresh and drill again for fresh detail.
- Does the drill-down sheet update automatically?No, the extract is a static copy of the rows. It does not stay linked to your source. So when the data changes, refresh the pivot first. Then double-click the value again for a new extract.
- What is Quick Explore in a PivotTable?Quick Explore drills sideways into a related field. For example, you can jump from a region to its products. Click a value, then the lens icon that appears. It works best with a Data Model of related tables.
- Can I stop users from drilling into details?Yes, open PivotTable Options and the Data tab. Then untick "Enable show details" and click OK. After that, a double-click no longer reveals rows. So you can share the summary safely.