How to Prevent Conditional Formatting Rules from Disappearing in Shared Excel Workbooks
Question details
Users need a way to stop formula-based conditional formatting rules applied to PivotTables from vanishing when sharing workbooks.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sharing a workbook containing PivotTables and formula-based conditional formatting rules via SharePoint, OneDrive, or Excel for the web.
- Observed behavior
- Conditional formatting rules unexpectedly disappear or break when the shared workbook is refreshed or edited in an online environment.
Ensure your workbook is saved in the modern .xlsx format, as older formats like .xls lack full support for advanced conditional formatting rules and dynamic ranges.
Force Users to Open Shared Workbooks in the Excel Desktop App
Excel for the web has limited support for formula-based PivotTable conditional formatting. Directing users to the desktop app prevents rules from dropping.
Because Excel for the web may strip or fail to render complex formula-based formatting rules upon refresh, the most effective solution is to ensure anyone editing the document uses the full desktop version of Excel.
Add a new worksheet named 'Welcome' and include clear text instructing users to click 'Open in Desktop App' to view the data properly.
Go to the 'File' menu, click on 'Info', and select 'Browser View Options'.
Under the 'Show' tab, change the setting to only display the 'Welcome' sheet in the browser. This compels users to switch to the desktop application to access the actual PivotTables.
Update Software and Resolve Formatting Rule Conflicts
Outdated software builds or conflicting, overlapping conditional rules can cause formatting to drop unexpectedly.
Use WPS Office for Reliable PivotTables and Formatting
If cloud-based spreadsheet tools continuously drop your complex conditional formatting rules, consider switching to WPS Office. It provides a robust, lightweight desktop alternative with exceptional compatibility for Microsoft formats, ensuring your PivotTables and rules remain intact without requiring costly subscriptions.
Frequently Asked Questions
Why does my conditional formatting disappear when I refresh an Excel PivotTable?
PivotTables dynamically change shape and size when refreshed. If your conditional formatting 'Applies to' range isn't configured to dynamically cover the PivotTable's changing dimensions, the formatting may drop from cells that are released from the table structure.
Does Excel for the web support all conditional formatting rules?
No. Excel for the web has limited support for advanced, formula-based conditional formatting and PivotTable-specific rules. Viewing or editing these complex files in a browser can cause the formatting to stop working. Opening the file in the desktop version usually restores full functionality.
How can converting my data source to an Excel Table prevent formatting issues?
Formatting source data as a structured Excel Table ensures that any new rows or columns added are automatically included in the PivotTable's data cache. This stability prevents the underlying data from fracturing, which helps maintain the integrity of your conditional formatting rules.
