Excel 365 conditional formatting not updating

Question

I'm having an issue where the conditional formatting isn't updating. Line 20 is correctly showing F20> G20. Line 12 was the same, but I was able to edit it so F21<G21 and the formatting didn't change. Lines 22 and 23 didn't update

I'm working on Excel 365 which auto saves to Onedrive. I checked the properties and conditional formatting is turned on. F9 also doesn't cause the formatting to update.

Anyone have any ideas on how to fix this or is it a known bug?

Answer 1

Hi

Thank you for posting the thread on our forum.

Based on your description, it seems that the Excel 365 conditional formatting not updating in your file. (If my understanding is incorrect, please feel free to post back)

For the situation you encountered, we do understand the inconvenience it has made and apologize for it.  We feel your pain due to this and we’re really feel sorry for it.

1.Try to create a new file then copy all the data in the original file to the new one to check if the conditional formatting can be updated well.

2. Save a copy of the file then remove all conditional formatting, then try adding back all your formatting conditions to check the behavior.
3. Try to press the CTRL+ALT+SHIFT+F9 key combo on an "unfixed" sheet to check if it can work .
4. Please also try the following steps to check the behavior:

• Just set up your conditional formatting in the cell's you want.
• Make sure you activate the "Developer" tab in the menu.
• Do this by clicking on the Office button, click on Excel options and in the "Popular" section check the box ""Show developer tab in the Ribbon".
• Click on the Developer menu and go to the Visual basic view.
• Click on the menu View and select "properties Window".
• In that list you can find the "EnableFormatConditionsCalculation".Set this value to "True" and the auto refresh will work.
• Perform this action for all tabs in your sheet.

We appreciate your understanding that sometimes the initial suggestions may not resolve the problem very soon. However, we can work together to narrow down and resolve the situation. So, Please kindly provide more information below to let us work further.

• May I know if all fiel have this problem or just the specific file have the problem?
• If it's possible, can you share a copy of the file to us and tell us the conditional formating you used so that we can do a tes in our end?

Hope the suggestion can help you. And if there have any unclear or misunderstanding, please feel free to post back and we’ll continue to help you all the time!

Your understanding and patience will be highly appreciated! Hope you have a good day and keep safe!

Best regards,

Stacey

Answer 2

Hi Stacey,

I tried sharing the file with one of my co-workers. Strangely, the conditional formatting updated when viewing the file through the web app, but didn't update when viewing the file from the desktop app. Copying the file into a new document also didn't work. Here is the version of the desktop app I am using

Answer 3

Also, I would be happy to share a copy of my document. Can you tell me how to upload it? I'm not seeing an attach button

Answer 4

Hi

Thank you for your quick response and we also appreciate that you spent your precious time doing these tests and provided the detailed information to us.

Sorry to hear that the suggestion in above reply can't help you a little. But please don’t worry, we can work together to narrow down and resolve the situation.

Since you mentioned that conditional formatting work well when open through the web app, maybe the problem was related to the desktop, so please kindly share the file to us so that we can have a test in our end to find the root cause, you can upload the file to the OneDrive then share the link in here to us . Or you can share the file to us by the link Private Messages - Microsoft Community

We're looking forward to your reply. And we'll continue to help you all the time!

Really appreciate your patience and understanding.

Best regards,

Stacey