How to preserve formatting after refreshing pivot table?
When working with pivot tables in Excel, it is common to apply custom formatting such as font colors, cell shading, or number formatting to make your summary data easier to interpret. However, you may sometimes find that this formatting is lost after you refresh the pivot table, especially if the Preserve cell formatting on update option was accidentally unchecked in the pivot table settings. This issue can cause inconvenience and require repeated formatting efforts every time the data is refreshed, particularly with complex dashboards or regularly updated reports.
Understanding how to consistently keep your custom pivot table formatting in place not only saves time but also helps maintain reporting accuracy and professional appearance. This tutorial introduces practical ways to ensure your formatting remains intact after each refresh, whether your setup is simple or involves more complex reporting tasks.
Preserve formatting after refreshing pivot table
Preserve formatting after refreshing pivot table
If your custom formatting is lost each time the pivot table is refreshed, it is likely that the built-in “preserve formatting” setting has been turned off. Fortunately, this option can easily be enabled to keep your applied styles after each refresh.
1. Click any cell inside your pivot table, then right-click to display the context menu.
2. Choose PivotTable Options from the context menu to open the settings window, as illustrated below:
3. In the PivotTable Options dialog box, switch to the Layout & Format tab. Under the Format section, ensure the Preserve cell formatting on update checkbox is selected. Refer to the screenshot below:
4. Finally, click OK to close the dialog box. From now on, any cell formatting you apply, such as number formatting, background color, or fonts, will be retained after the pivot table is refreshed.
Tips and notes: The Preserve cell formatting on update option works best for most simple formatting needs and is recommended for general use. However, keep in mind:
- If you make structural changes to pivot fields—such as adding or removing fields—the formatting in those affected areas may still revert to default.
- If you move or completely rebuild your pivot table, or use “Clear All” in the PivotTable Tools, previous formatting is typically lost and must be reapplied.
- Cell-level formatting (such as bold fonts or color shading on individual cells) is more robustly preserved than range-based formatting with merged cells or conditional formatting rules, which may not always be maintained on refresh.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Related articles:
How to lock column width in pivot table?
How to refresh pivot table on file open in Excel?
How to refresh pivot table when data changes in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in