Forum Discussion
Conditional Formatting Rules randomly being deleted, not by a person, on shared workbook
I have an Excel workbook with multiple tabs that have different Pivot Tables based off of the same master table on its own sheet. I have been able to successfully create Conditional Formatting rules on a Pivot table using a formula before applying the rule. However, the rules that use formulas (not the ones for example based on a single cell value) are deleted from the sheet at what seems to be random times. I have confirmed that no individual is deleting this (on purpose). This doesn't seem to happen when I have my own sheet no matter how I change or update the data. i have also shared the sheet via OneDrive with another person and neither of us could get this to occur. But the live workbook, which is located in a Document Library in SharePoint (MS 365, several users also sync this library to OneDrive) and we have multiple users editing the master table and refreshing the pivot tables throughout the day either from the online version or in the desktop app. This seems to be specific to the sheets on SharePoint/OneDrive and that are using formulas in the conditional formatting rules. I can't find anything that talks about this in my searches so wanted to see if 1) anyone has also experienced this and 2) Any ideas if this can be solved or how to solve it?
Here's an example of how the rules are setup. The formula ones are the ones that get deleted at random. I've tried other variations of the formulas as well and am pretty sure at this point that its the use of a formula, not the structure of the formula.
Wanted to follow back up for anyone's interest or to benefit others. I believe I found a workaround/band-aid solution. See below "TLDR" to jump to this.
I haven't confirmed this with our IT department, but as far as I know everyone in our company is on the same 365 version and I would think we're all on the same desktop app version as that is all controlled by their policies. We do have a few users on Macs. But some of the above suggestions did get me thinking it had something to do with people editing the spreadsheet in the online/browser version vs the Desktop App, which is what I typically prefer to use. I tried to switch myself to the online/browser version and realized then that I couldn't even create a conditional formatting rule that used a formula for the condition or gives the option on the "Applies to" for Pivot Table options (see screenshot). So, I started to guess when a user refreshed all pivot tables in the sheet from the online/browser version, Excel would remove the rules that used either of these features.
TLDR: Workaround/"Solution" - I created a Welcome tab/page in the spreadsheet (see screenshot) and instructed users to open the sheet in the Desktop app. In the Browser View Options (File>Info), I deselected all of the other sheets except for this one. For the past couple of days, the formatting seems to be sticking and I haven't had any reports that anyone has been unable to edit/access the sheet. Fingers crossed this works, but certainly isn't an idea long term solution and would really be ideal if Microsoft could fix this.
4 Replies
Wanted to follow back up for anyone's interest or to benefit others. I believe I found a workaround/band-aid solution. See below "TLDR" to jump to this.
I haven't confirmed this with our IT department, but as far as I know everyone in our company is on the same 365 version and I would think we're all on the same desktop app version as that is all controlled by their policies. We do have a few users on Macs. But some of the above suggestions did get me thinking it had something to do with people editing the spreadsheet in the online/browser version vs the Desktop App, which is what I typically prefer to use. I tried to switch myself to the online/browser version and realized then that I couldn't even create a conditional formatting rule that used a formula for the condition or gives the option on the "Applies to" for Pivot Table options (see screenshot). So, I started to guess when a user refreshed all pivot tables in the sheet from the online/browser version, Excel would remove the rules that used either of these features.
TLDR: Workaround/"Solution" - I created a Welcome tab/page in the spreadsheet (see screenshot) and instructed users to open the sheet in the Desktop app. In the Browser View Options (File>Info), I deselected all of the other sheets except for this one. For the past couple of days, the formatting seems to be sticking and I haven't had any reports that anyone has been unable to edit/access the sheet. Fingers crossed this works, but certainly isn't an idea long term solution and would really be ideal if Microsoft could fix this.
I've seen this a couple months ago where the client had the issue but I did not. The difference was the build of M365 we had. Checking for updates might be the way to go.
I tend to avoid pivot tables where a formulae will do, so claim no particular expertise. From what I remember though, is that pivot tables grow and shrink and generally change shape as the source data changes or the view of the data is changed. This leads to a situation in which cells that were formerly used by the pivot table are released. My experience was that the released cells were formatted to the default 'normal' style rather than retaining the formatting rules that applied before the cells became part of the pivot table.
Take this:
- Check for Updates: Ensure that all users have the latest version of Excel and Office 365 updates installed. Sometimes, bugs that cause issues like this are fixed in updates.
- Save as .xlsx: Make sure the workbook is saved in the .xlsx format, as older formats like .xls might not handle conditional formatting rules as well.
- Disable Shared Workbook Features: If you're using shared workbook features, try disabling them and see if the issue persists. You can do this by going to the Review tab and clicking on "Share Workbook."
- Use Excel Tables: Convert your data ranges into Excel tables. Tables have better support for conditional formatting and might help maintain the rules.
- Check for Conflicting Rules: Sometimes, conflicting conditional formatting rules can cause unexpected behavior. Review all the rules to ensure there are no conflicts.
- Save and Reopen: After making changes, save the workbook and reopen it to see if the rules are still intact.
