Convert Excel 2016 Pivot Tables to Values While Preserving Formatting
Question details
Convert PivotTable results in Excel 2016 to static values while retaining their appearance.

- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- The user wants to share PivotTable results without retaining PivotTable functionality or source data.
- Observed behavior
- Paste Special > Values transfers the displayed results but does not include the original PivotTable formatting.
Identify the full PivotTable result range, including any labels and totals you want to share. Choose a blank destination outside the PivotTable, preferably in a new workbook if you do not want to share the source data.
Paste PivotTable values and then paste their formatting
Use two Paste Special operations to create a static copy that retains the original cell formatting.
Select the required PivotTable cells, including labels and totals, and choose Home > Copy.
Select the top-left cell of a blank destination range, then choose Home > Paste > Paste Special > Values.
Return to the original PivotTable, select the same range, and choose Home > Copy.
Select the top-left cell of the pasted values and choose Home > Paste > Paste Special > Formats.
Compare the destination with the original PivotTable. Adjust column widths if needed, and repeat the process for each result range you want to share.

Create a formatted static PivotTable copy in WPS Spreadsheets
Open an Excel workbook in WPS Spreadsheets and paste PivotTable results as values, then apply the original formatting to the destination range.
- 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select the Excel workbook containing the PivotTable.
- 2. Copy the results: Select the PivotTable result range, including the labels and totals you need, and choose Copy.
- 3. Paste values: Select a blank destination cell, then use Paste Special > Values to create a static copy.
- 4. Apply formatting: Copy the original range again, select the first cell of the pasted range, and use Paste Special > Formats.
- 5. Review and save: Check the pasted results and column widths, then use File > Save As to save the destination workbook.

Frequently Asked Questions
Why does Paste Special > Values remove the PivotTable formatting?
Values pastes the cell results without applying the source formatting. Copy the original range again and use Paste Special > Formats on the destination.
Will the pasted results still refresh like a PivotTable?
No. Paste Special > Values creates static cells, so changes to the source data will not update the pasted results.
Does pasting values remove the source data from my workbook?
No. The source data and original PivotTable can remain elsewhere in the workbook. Paste the results into a new workbook and inspect its contents before sharing.
Why do the pasted columns look narrower than the original?
Pasting formats may not reproduce column widths. Select the destination columns and adjust their widths to match the original.




