Extract data from a pivot table in Excel

To extract data from an Excel pivot table, copy the pivot and Paste Special -> Values for a static snapshot. To get the source records behind one number, double-click the value cell and Excel will create a Show Details sheet with the underlying rows.

Pivot tables are great for analysis and frustrating to extract from. The values you see aren't "in" the cells the way regular numbers are — they're projections of the underlying data. Here are the four reliable ways to get data back out, ordered by how often they're the right answer.

1. Copy values, paste special

Select the PivotTable range, copy it, then use Paste Special → Values in a fresh sheet. You lose filters, field buttons, drill-down, and refresh behavior, but the visible numbers become ordinary cells. Use this for a handoff, audit snapshot, or upload into a system that cannot read PivotTables.

Before exporting, switch the report to the layout the recipient needs. Tabular Form produces a more database-like shape than Compact Form. Repeat item labels or fill down the blank category cells after pasting, remove blank separator rows, and keep an explicit column for units when the report displays values in thousands or millions.

2. GETPIVOTDATA

Type = into any cell, then click a value in the pivot — Excel auto-builds a GETPIVOTDATA formula that pulls that specific aggregation. Useful when you want a dashboard cell to update as the pivot updates:

=GETPIVOTDATA("Revenue", $A$3, "Region", "EMEA", "Quarter", "Q3")

Annoyingly verbose but worth it for live-updating reports. Turn off auto-generation under PivotTable Analyze → Options if you want plain cell references instead.

Microsoft documents GETPIVOTDATA as returning visible data from a PivotTable. It can return #REF! when a requested field or item is not visible under the current filters, so use stable field names and test the formula after slicers change. Prefer GETPIVOTDATA over direct cell addresses when the pivot can expand, collapse, or reorder.

3. Show Details (drill-down)

Double-click any value cell in the pivot. Excel creates a new sheet with the underlying rows that rolled into that number. This is the fastest way to get from an aggregate back to the source records — useful when a number looks wrong and you need to audit it.

Show Details may be disabled by workbook settings, the data source, or the type of calculation. It also returns the rows contributing to the selected cell, not the entire PivotTable source. Check filters, dates, and calculated columns before treating the drill-down sheet as a complete export.

4. Convert pivot back to a flat table

If you want every combination as its own row for re-charting or another tool, return to the source data and use Power Query: Data → Get & Transform, then Transform → Unpivot Columns or Unpivot Other Columns. Microsoft describes unpivoting as flattening attribute-value intersections into columns. Keep identifier columns such as customer, region, or account selected and unpivot the changing period columns around them.

When the pivot is in someone else's file you can't open

Sometimes you receive only a PDF, slide, or screenshot of the PivotTable. Excel cannot drill into rendered pixels, but an image data extractor can reconstruct the visible row labels, column labels, values, subtotals, and grand total into a flat spreadsheet. This is useful for board decks, emailed reports, and archived dashboards.

Validate a screenshot or PDF extraction

1. Record the displayed unit and currency before extracting; 2.4 may mean 2.4 million rather than 2.40.
2. Preserve the hierarchy of indented row labels instead of flattening parent and child categories into the same level.
3. Compare extracted row subtotals and column totals with the rendered PivotTable.
4. Check cells with blanks, dashes, errors, or suppressed values separately from numeric zero.
5. Keep the source filename and page number beside each extracted table so discrepancies can be audited later.

Ask for a long table when the result will feed a database: row_category, column_category, measure, value, unit, and source_page. Ask for a wide table only when the goal is to reproduce the visible report. The long shape is easier to filter, aggregate, and append across months.