OmniDocCopy or Convert a Pivot Table Without Losing Formatting
Learn how to copy a pivot table, paste it as values or a plain table, and keep its formatting, with checks to run before you share the result.
If you have built a pivot table that looks right, you may need it somewhere else: in a report, an email, a slide, or another workbook. A normal paste often shifts the formatting, breaks the layout, or leaves the table tied to its source so it changes the next time someone refreshes. This guide shows how to copy pivot table without losing formatting, so the result looks the way you intended.
The right method depends on what the copy needs to do. A static snapshot that keeps its look is a different job from a pivot that must stay live. This article covers the first case: copying or converting a pivot table into values or a plain table while keeping its appearance. Fixing formatting that changes on refresh is a separate problem and is not covered here.
You will get a method for each path, a checklist to run before you paste, and a way to verify the result.
How do you copy a pivot table without losing formatting? The short answer
Paste the pivot as values into a new location, then paste the original's formatting and column widths on top, so you get a static table that looks like the original.
| Your situation | What to do | Why |
|---|---|---|
| You need a static copy for a report or email | Paste as values, then paste formatting | Values stop the table from changing; formatting carries the look over |
| You want the copy to stay a pivot in the same workbook | Copy the pivot and paste it on another sheet | A pivot pasted into the same workbook keeps its link to the source |
| You want plain cells you can sort and edit freely | Convert the pivot to a range, then check formatting | Removes pivot behavior, though some pivot styling may need reapplying |
| You are moving the table to a different workbook | Paste as values rather than as a pivot | Cross-workbook pivot links can break or point to the wrong source |
| The table will go into a PDF | Finish formatting in the spreadsheet, then export | The exported file keeps the look you checked |
Before you start
- Decide whether you need a live pivot or a static table. A live pivot updates when the data refreshes, while a static table never changes. Choosing the wrong one is a common cause of surprises later.
- Refresh the pivot and check the totals. Copying a stale pivot copies stale numbers, so confirm the figures match the source before you select anything.
- Note the layout settings. Subtotals, grand totals, banded rows and the layout form all affect how the table looks. Some of these do not carry over as cell formatting, so write them down or take a screenshot.
- Pick a destination with empty space. Pasting over existing data overwrites it without a warning, and a pivot needs enough free columns and rows to fit.
- Save a backup copy of the workbook. Converting a pivot is hard to reverse once you have saved and closed the file, so keep the original untouched.
Converting a pivot table to values while keeping its look, step by step
- Refresh the pivot table and confirm the totals. The copy will freeze whatever is showing now, so start from current data.
- Select the full pivot range. Include the headers, row labels and grand totals, since a partial selection drops rows or columns.
- Copy the range, then click an empty cell where you want the static table to start. A clear destination prevents accidental overwrites.
- Paste as values. This stores plain numbers and text with no link to the source, so later refreshes cannot change the copy.
- Copy the original pivot range again and paste formatting only onto the new table. This carries over fonts, fills, borders and number formats.
- Set the column widths to match the original. Pasting values usually resets widths, and many spreadsheet apps offer a paste option that keeps the source column widths.
- Remove leftover helper rows or filter buttons you do not want in the final table. The pasted range can include blank rows or header cells from the pivot layout.
To convert the pivot in place instead, use your spreadsheet app's option to convert a pivot table to a range. It removes the pivot behavior and usually keeps the cell formatting, but pivot-style colors and banding can change. Run the checks below either way.
Check the result
Run these checks before you share or export the table.
| Check | How to see it | If it's wrong |
|---|---|---|
| Numbers match the source | Compare the grand total in the new table with the original pivot | Refresh the pivot, confirm filters, and copy again |
| Number formats | Click several numeric cells and look at how the value is displayed | Paste formatting again, or set the format by hand |
| Fonts, fills and borders | Scroll the whole table, including the grand total row | Paste formatting from the original and reapply the header style |
| Column widths | Look for cells showing ### or cut-off text | Widen the columns or autofit the selected range |
| Link to the source is gone | Change a value in the source data, then refresh; the copy should not move | If it moves, you pasted a pivot, not values. Undo and paste as values |
❓ FAQ
1. Why does my pasted pivot table change when I refresh the data?
A normal paste keeps the pivot link, so the copy still follows the source. Paste as values to break that link and get a table that stays fixed.
2. Can I keep the pivot's colors after pasting as values?
Yes. Copy the original pivot range again and paste only its formatting onto the values. Check the grand total row afterward, since it is the row most often missed.
3. Does converting a pivot table to a range remove its formatting?
Usually not all of it. Cell formatting typically stays, but some pivot-style colors and banding can disappear, so check the table after converting.
4. Can I do this in OmniDoc?
OmniDoc's listed features do not include pivot-table editing, so do the copy in your spreadsheet app. If you have a PDF version of a table and need an Excel file, the PDF editor in the OmniDoc desktop client can export to Excel.
5. Why does my table show ### instead of numbers?
The column is too narrow for the number format, so Excel-style apps display ### in place of the value. Widen the column until the full number fits.
📝 Summary
To copy a pivot table without losing formatting, paste it as values and then paste the original's formatting and column widths onto the new table. Run the checks in the table above before you share the result, and keep a backup so you can undo a conversion.
