Excel Pivot Chart Drill Down: Explore Data in Excel

To drill down in an Excel PivotChart, select or right-click the chart data point and use an available Show Details command to display its contributing records; for a category hierarchy, use the chart’s expand controls or the PivotTable’s Expand/Collapse commands. If you need the most dependable way to see underlying rows, double-click the corresponding value cell in the PivotTable.

What “drill down” means in a PivotChart

People use “drill down” to describe two different actions in Excel. One is moving from a summarized category to a more detailed level, such as changing a chart from year to quarter or from region to city. The other is opening the individual source records that were summarized into a number, such as the orders that make up a region’s total sales. These actions are related, but the commands and results are not the same.

A PivotChart is connected to a PivotTable, which summarizes data using fields arranged in areas such as Rows, Columns, Values, and Filters. The chart visualizes that summary. As a result, a chart point represents an aggregate—perhaps total sales for one month—not necessarily one original row. Expanding a date hierarchy changes the summary level; showing details reveals records behind a summary value.

For example, a chart might show $48,000 in sales for the West region. Expanding the region into cities could reveal Seattle, Portland, and other city totals. Showing details for the $48,000 value instead would produce the underlying transaction rows included in that total, subject to the workbook’s data source and settings.

Show the records behind a chart value

In Excel for Microsoft 365 on Windows, first try the PivotChart’s context menu. The exact options depend on the chart point, data source, and workbook configuration, so use the PivotTable value cell when the chart does not offer a detail command.

Click the PivotChart, then click the data point or bar whose contributing records you want to inspect. Take care to select the point itself, not the chart area or legend.
Right-click the selected point. If Show Details appears, select it. Excel opens a new worksheet containing the source rows for that summarized value.
If the command is missing, locate the same value in the PivotTable connected to the chart. Double-click the numeric value cell. Excel normally creates a new worksheet with the corresponding detail records.
Review the new sheet and confirm its headings and filters. The result is a detail extract for that value, not a new PivotTable or a change to the source data.

On a Mac, use a secondary click or Control-click to open the context menu; a mouse or trackpad may be configured differently. Double-clicking the associated PivotTable value is also a useful fallback. Excel for the web can have a different or more limited context menu than desktop Excel, so if the detail option is unavailable, open the workbook in the desktop app and try the PivotTable value cell.

Expand a category hierarchy instead

If you want to move from a broad category to its subcategories while keeping a chart, expand the relevant field rather than showing detail records. A common example is a date field grouped into years, quarters, and months. Another is a location field arranged as country, state, and city. The chart then redraws using the more detailed summary level; it does not list every transaction.

Use chart expand controls: When available, the plus and minus buttons on a PivotChart expand or collapse hierarchy levels. If the buttons are not visible, select the chart and look for the chart field buttons or expand/collapse controls.
Use the PivotTable: Right-click a row or column label and choose an appropriate Expand/Collapse command, such as expanding a field or showing the next level. The connected PivotChart updates with the PivotTable.
Change the displayed field level: Use the field buttons or PivotTable field layout to show a different level, such as quarter instead of year. The field names and available commands depend on how the source fields are organized.

Expansion is usually reversible: collapse the level to return to the summarized view. If expanding a chart seems to affect more categories than expected, check whether the command expanded the entire field rather than only one item. The chart’s hierarchy behavior follows the connected PivotTable structure, so inspecting the table often makes the result easier to understand.

Check the PivotTable and source data

Because a PivotChart reflects a PivotTable, confirm that the chart is actually a PivotChart and identify the value cell behind the point you selected. A regular chart based on a cell range may look similar, but it does not have PivotTable drill-through behavior. Select the chart and look for PivotChart-related tabs and field buttons; then check the associated PivotTable and its fields.

Also verify what the source data represents. If each row is an order line, detail output can show order-line records. If the PivotTable uses a Data Model, an external connection, or another OLAP source, the source may not expose ordinary worksheet rows in the same way. Whether details are available can depend on the connection, provider, permissions, and model configuration. A chart cannot reveal records that the underlying source does not make available.

Refreshing matters when the source has changed. In desktop Excel, select the PivotTable or PivotChart and use PivotTable Analyze > Refresh; to update all PivotTables in the workbook, use Data > Refresh All. A PivotChart reflects its PivotTable’s current summary, which may be based on an older cache until refreshed. If newly added source rows are not included, check the PivotTable’s data source range or whether the source is an Excel table that expands with new rows.

Why Show Details may be missing

The absence of Show Details does not necessarily mean the chart is broken. First try the matching PivotTable value cell: the chart’s context menu is not identical in every situation, and a point can be difficult to select precisely. If the PivotTable also lacks the command, consider these common causes:

Details are disabled: In desktop Excel, select the PivotTable, open PivotTable Analyze > Options, choose Options if the menu presents it, and inspect the Data tab of PivotTable Options for Enable show details. Menu presentation can vary by build.
The source is not a standard worksheet list: Data Model and OLAP-based PivotTables may have restrictions on returning detail rows. The connection or model determines which drill-through actions are supported.
The selected item is not a value: A legend entry, axis label, subtotal, blank, or chart background may not correspond to a detail-bearing value cell. Select a specific summarized value instead.
Workbook or sheet restrictions apply: Protection, access rights, or organizational policies can prevent the operation or prevent the new detail sheet from being created.
The summary has no accessible records: Filters, deleted source rows, or a changed source can make the expected records unavailable or different from what the chart suggests.

When testing, use a simple value with a small number of source rows and note the active PivotTable filters. A detail sheet reflects the records included in the chosen summary value under the current PivotTable context; it is not necessarily a complete export of the dataset. If the data is sensitive, remember that creating a detail sheet copies records into the workbook and may expose information to anyone who can access it.

What the detail sheet contains—and what it does not

Show Details creates a worksheet of contributing source records. It is a convenient way to audit a total, identify an unexpected transaction, or pass a focused sample to a colleague. It does not change the original source rows, rewrite the PivotTable, or establish that the data is correct. Treat the output as a snapshot of the records available to the PivotTable when the command runs.

Excel worksheets have a limit of 1,048,576 rows. If a requested detail set is too large to fit, Excel cannot place an unlimited result into a single worksheet; behavior can depend on the source and connection. For large analyses, use the source system, Power Query, or a query designed to return a smaller filtered result rather than trying to extract every record through a chart point.

After creating the sheet, useful checks include comparing its row count with expectations, checking the relevant date or category columns, and confirming the PivotTable’s filters. For example, if a chart total represents the current quarter and only one sales region is selected in a report filter, the output should be interpreted in that filtered context—not as all sales for the year.

PivotChart drill-down compared with other ways to explore

Use hierarchy expansion when the question is “Which subcategories make up this category?” Use Show Details when the question is “Which source records make up this number?” Use a slicer or report filter when the question is “What does the chart look like for only this subset?” These operations can be combined, but each answers a different analytical question.

For example, suppose a PivotChart summarizes sales by year and region. Expanding a year into quarters changes the chart’s time granularity. Filtering to the East region limits the displayed summary. Showing details for one quarter’s value lists the transactions contributing to that selected result. Applying a filter first can change which records are included in a value, so read the chart title, field buttons, slicers, and PivotTable filters before interpreting the output.

If the goal is a repeatable report rather than an occasional inspection, consider adding useful fields to the PivotTable, using slicers, or creating a separate detail table with ordinary filters. Those approaches make the analysis easier for others to follow. A one-off Show Details sheet is best treated as an investigative aid, not as a permanently maintained reporting view.

Frequently Asked Questions

Can you drill down directly from an Excel PivotChart?
Sometimes. Right-click a specific data point and choose Show Details if that command is offered. If it is not, double-click the matching value cell in the connected PivotTable, which is the usual dependable route to the underlying rows.

Does double-clicking a PivotChart show the source data?
Do not assume that a double-click on a chart point will show records; chart interactions can depend on the selected element and may open formatting options. Use the point’s context menu if it offers Show Details, or double-click the corresponding PivotTable value cell.

What is the difference between expand and Show Details?
Expand reveals another level of summarized categories, such as months beneath a year. Show Details returns the individual source records behind a summary value. One changes the chart’s level of aggregation; the other creates a detail worksheet.

Why can I not see Show Details in Excel?
The command may be disabled, unavailable for the data connection, or missing because the selected chart element is not a value. Try the PivotTable value cell and check the PivotTable Options data setting. Some Data Model or OLAP sources also limit drill-through.

Does drilling down change my original data?
No. Show Details creates a separate worksheet containing records; it does not edit the source data. Expanding a hierarchy changes the PivotTable and chart view, not the original rows. Refreshing the PivotTable is a separate action.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.