Forum Discussion
Save Excel file - Values only keeping format of tables
Hi everyone,
I have a workbook with multiple worksheets and Power Pivots. I am looking for a way to save the workbook while retaining the values (data) and the formatting (color, layout, etc.) of the pivot tables, but without keeping the queries, connections, or data model. Is there a solution for this?
Thanks
1. Copy and paste the value: Select the cell or table area where the value needs to be retained.
Right-click and select Copy
Right-click on the target location and select “Paste as Value” which is usually displayed as a “Value” or “123” icon.
This will keep the data but remove the formula.
2. Use the “Selective Paste” function:
After copying data, right-click on the target location and select “Selective Paste”.
In the pop-up dialog box, select “Value” and click “OK”.
If you need to retain the format, you can select “Value and Source Format” in “Selective Paste”.
3. Realized by VBA script:
If you need to operate in batch, you can use VBA script to replace the values of the whole worksheet to retain only the values, while retaining the format.
Example script:
vba
Sub ConvertToValues()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”) ' Change to your worksheet name
ws.UsedRange.Value = ws.UsedRange.Value
End Sub
After running the script, all formulas will be replaced with values, but the formatting will remain the same.
4. Save as a new file:
After completing the above, save the file as a new file (e.g. “filename_value.xlsx”) to ensure that the original file is not affected.
