Pivot Table Issues

How to Fix Pivot Table Formatting Lost After Refresh

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user's custom formatting is discarded and reverts to default whenever they refresh the pivot table data or change the source, making it difficult to maintain the spreadsheet's presentation.

How to Fix Pivot Table Formatting Lost After Refresh

Product: Spreadsheet software
Device & OS: not provided
Scenario: Updating or refreshing the data source linked to an existing pivot table.
Observed behavior: Custom cell formatting, such as colors, borders, and fonts, disappears entirely after the pivot table is refreshed.

Before you start

Before adjusting any pivot table settings, make sure to save your current workbook. If you later need to share the file for advanced troubleshooting, remember to replace any sensitive information with randomized dummy data.

Solution 1Recommended

Enable the Preserve Cell Formatting Option

Adjusting the built-in layout settings is the most direct way to ensure your custom cell formats are locked in place during data refreshes.

Spreadsheet applications often default to overwriting custom formatting during a refresh to maintain structural consistency. By enabling a specific setting in the pivot table options, you can force the application to retain your manually applied styles.

Access PivotTable Options

Right-click anywhere inside your active pivot table and select 'PivotTable Options' from the context menu that appears.

Navigate to Layout & Format

In the PivotTable Options dialog box, click on the 'Layout & Format' tab at the top.

Check the Preserve Option

Locate the checkbox labeled 'Preserve cell formatting on update' and ensure it is checked.

Apply and Test

Click 'OK' to save your changes. Right-click the pivot table and click 'Refresh' to verify that your custom formatting remains intact.

Enable the Preserve Cell Formatting Option

Pro Tip: While on the Layout & Format tab, you can also uncheck 'Autofit column widths on update' to prevent your carefully sized columns from shifting when the data changes.

Solution 2

Create a Dummy Sample File for Advanced Troubleshooting

If enabling the preserve formatting option does not resolve the issue, there may be underlying structural complexities requiring targeted support.

Powerful Data Management

Preserve Pivot Table Formatting Easily in WPS Spreadsheet

WPS Office provides robust and highly compatible data analysis tools. You can effortlessly retain your customized formats upon refreshing pivot tables in WPS Spreadsheet with just a few clicks.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your pivot table.
2. Open Options: Right-click anywhere inside the pivot table and select 'Options' from the drop-down menu.
3. Switch Tabs: In the dialog box that appears, navigate to the 'Layout & Format' tab.
4. Enable Formatting Preservation: Check the box next to 'Preserve cell formatting on update'.
5. Save Settings: Click 'OK' to apply the setting. You can now refresh your pivot table without losing any custom colors, fonts, or borders.

Fully compatible with Microsoft Excel (.xlsx) pivot tables and formats.Dedicated settings to preserve cell formatting and column widths upon data refresh.Free, lightweight, and fast office suite available for Windows, Mac, and mobile.Familiar user interface requiring zero learning curve for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my pivot table columns resize automatically when I refresh the data?

By default, pivot tables are configured to automatically fit the width of the new data upon refreshing. To stop this, right-click the pivot table, go to PivotTable Options, select the Layout & Format tab, and uncheck 'Autofit column widths on update'.

Will conditional formatting disappear when a pivot table is refreshed?

Conditional formatting can sometimes break if applied only to static cell ranges. To prevent this, apply the conditional formatting rule directly to the pivot table's structural elements. When creating the rule, look for the option at the top of the conditional formatting dialog to apply the rule to 'All cells showing [Field Name] values'.

How can I safely share my spreadsheet for technical support without exposing sensitive data?

You should create a separate copy of your workbook. In this copy, delete or overwrite all sensitive, personal, or financial data with randomized 'dummy' data while leaving the pivot tables and structure intact. Upload this anonymized file to a secure cloud drive and share a view-only link with the support team.

Excel & Data Problems