How to extract data from a pivot table in Excel

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 pivot range, copy, then Paste Special → Values into a fresh sheet. You lose the pivot machinery and get a static snapshot. This is what 80% of people actually want when they say "extract from a pivot."

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.

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.

4. Convert pivot back to a flat table

If you want every combination as its own row (for re-charting or feeding another tool), the unpivot route is best done in Power Query: Data → Get & Transform → load the source, then Transform → Unpivot Other Columns. This gives you a long-format table with one measurement per row, which most downstream tools prefer over wide pivot output.

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

Sometimes you receive the pivot as a PDF or an image in a report and need the numbers. Excel can't help you there — but ExtractFox's PDF or image extractor can read the rendered table back into a flat spreadsheet. Useful for board decks and emailed reports.