OmniDocHow to Get Data Out of a Pivot Table
Learn how to extract data from a pivot table in Excel by pasting its results as plain values, with checks that confirm the numbers are correct.
If you have ever tried to move pivot table results into a report, an email or another program, you have probably hit the same problem: the numbers look right on the pivot, but they do not behave like normal cells. They change when someone refreshes the pivot, they shift when fields are rearranged, and they can disappear when the pivot is deleted. This guide is for anyone who needs the figures themselves, not the pivot.
The short answer is to copy the pivot results and paste them as values. Pasted values are ordinary numbers and text, so they stay put when the pivot changes. The sections below cover how to extract data from a pivot table in Excel, what to check first, and how to confirm the copied numbers match the source.
How do I extract data from a pivot table in Excel? The short answer
Copy the pivot table cells, then paste them as values into a new sheet so the numbers no longer depend on the pivot.
| Your situation | What to do | Why |
|---|---|---|
| You need the numbers in a report or another workbook | Copy the pivot body and paste as values | Pasted values no longer change when the pivot is refreshed or rearranged |
| You want the rows behind one total | Double-click that value cell | Excel opens a new sheet with the source records that make up the number |
| Another program needs the table | Paste as values, then save the sheet as CSV | CSV stores cell contents only, with no pivot layout or formatting |
| You need a label on every row | Switch to tabular layout and repeat item labels before copying | Each row gets its own labels, so the pasted rows make sense on their own |
| The source data will change | Refresh the pivot, then copy and paste again | Pasted values are a snapshot of the pivot at the time you copied them |
Before you start
- Refresh the pivot table. A pivot stores a copy of the source data, so if rows were added or edited in the source, the pivot shows old numbers until you refresh it. Copying before a refresh carries those old numbers into your new sheet.
- Decide whether you need a live link. Pasting values breaks the connection to the pivot. If the figures must update automatically, use a formula that reads from the pivot instead, such as GETPIVOTDATA, and keep the pivot in place.
- Set the layout first. Compact layout puts row labels in one column with indentation, which pastes poorly. Tabular layout gives each field its own column, and repeating item labels fills in blank cells, so the copied table is easier to use.
- Know whether you want totals. Subtotals and grand totals copy along with the body. If the next tool will add up the column again, you will count the totals twice, so remove them or leave them out of the selection.
- Plan the destination. Paste into a blank sheet or a new workbook, not over the pivot itself. Pasting into the pivot's area can change the pivot or cause Excel to show an error.
Copying pivot table results as values, step by step
- Refresh the pivot table. Refreshing rebuilds the pivot from the current source rows, so the figures you copy match the data as it is now.
- Set the layout. Switch to tabular layout and turn on repeated item labels so every row shows its full set of labels.
- Select the pivot body. Select the value cells and the row and column labels, but not the filter area above the table unless you need it.
- Copy the selection. Press Ctrl+C on Windows. Excel copies the cells as they appear on screen.
- Open a blank destination. Click a blank cell on a new sheet or in a new workbook, so nothing in the pivot gets overwritten.
- Paste as values. Open Paste Special and choose Values. On Windows, you can press Ctrl+Alt+V and then V. This pastes numbers and text with no link to the pivot.
- Clean up the headers. Rename any generic header, delete grand total rows if you do not want them, and reapply number formats such as currency or percentages, since pasting values does not carry the pivot's formatting.
- Save or export. If another program needs the table, save the sheet as CSV. CSV saves only the active sheet, so keep the data you need on that sheet.
Check the result
Run these checks before you pass the numbers on. They take a few minutes and catch the problems that often cause rework later.
| Check | How to see it | If it's wrong |
|---|---|---|
| Totals match the source | Compare the pasted grand total with a SUM of the same column in the source data | Refresh the pivot, then copy and paste again |
| No pivot formulas came along | Click a pasted cell and read the formula bar; it should show a plain number or text, not a function that refers to the pivot | Undo the paste and repeat it with Paste Special, Values |
| Every row has its labels | Scan the first column for blank cells in the middle of a group | Turn on repeated item labels in the pivot, then copy again |
| Numbers look right | Spot-check two or three values by double-clicking the value cell to see the source rows | If the rows do not add up, check the filters on the pivot, since hidden items are not copied as visible |
| The values are static | Change one value in the source data, refresh the pivot, and confirm the pasted copy does not change | This is expected; repeat the copy and paste steps whenever the source changes |
❓ FAQ
1. Why does my copied pivot include a Grand Total row I don't want?
Grand totals are part of the pivot body, so they are copied with it. Either delete the total row after pasting values, or turn off grand totals in the pivot's layout settings before you copy.
2. Does pasting values keep the number formats?
No. Paste Special, Values keeps the numbers and text but not the formatting. Reapply formats such as currency, percentages or decimal places to the pasted cells.
3. Will the pasted data update when I change the source?
No. Pasted values are a fixed copy. To update them, refresh the pivot, copy the body again and paste it over the old copy on a new sheet.
4. How do I get pivot data into a format other than an Excel workbook?
Paste the values into a sheet, then save or export that sheet in the format you need. CSV is a common choice for other spreadsheets and databases, and a plain text file works when a program only reads text.
📝 Summary
To extract data from a pivot table in Excel, refresh the pivot, set a tabular layout, copy the body and paste it as values on a new sheet. Check the totals against the source before you use the numbers. If the source changes, repeat the steps to keep the copy current.
