Conditional formatting not working correctly

Question

I am using a MacBook Air laptop computer with the latest software (macOS Big Sur v 11.6). I just recently upgraded to Microsoft 365. I have an existing Excel workbook that I had been using without any issues. However, since the upgrade to Microsoft 365 and the transfer of my data to One Drive, I am now having issues with conditional formatting in Excel.

The details of the conditional formatting that I had created are as follows: It compares two cells (let’s say cells K2 and L2). I want cell L2 to be formatted with yellow shading if the value in it does not equal the value in cell K2. So, I created a conditional formatting rule based on the following formula: =$K2<>$L2. So now, let’s say the value of cell K2 is 25 and the value of cell L2 is 30. That means, as a result of this conditional formatting, cell L2 displays yellow highlighting. Once I change the value of cell K2 to 30 or I change the value of L2 to 25, then the yellow formatting of cell L2 disappears and cell L2 displays only normal formatting.

This conditional formatting rule worked perfectly when I was using Excel 2016 for Mac and backing up my files to iCloud. Now that I have upgraded to Microsoft 365 and am backing up my files to One Drive, the above-mentioned conditional formatting does not always work.

Here is how this conditional formatting is now behaving (after the upgrade to Microsoft 365 and backing up to One Drive): I might enter (or paste-values) a value into cell K2 that is different than the value in cell L2. But, the formatting on cell L2 does not always change. Then, I save the workbook and close it. When I re-open it, then cell L2 is formatted as I would expect to indicate that the contents of that cell is different than the contents of cell K2.

Why do I have to save the workbook, close it, and then re-open it to get the conditional formatting to work? Shouldn't it be instantaneios as it had been behaving before the upgrade?

Answer 1

Hi Lawrence,

The change you make in your Excel should be instantaneous. I recommend you open a blank sheet and insert conditional formatting formula to check if the same behavior happen

For the moment, I would like to collect the following information for my troubleshooting:

• Your MacOS version
• Confirm your Excel for MacOS version. For the version, go to Excel > About Excel and provide a screenshot (remember to hide your personal information for your privacy.
• For more troubleshooting, I have sent a private message to collect file from you, you can upload file there so it can protect your privacy and data. please access the private message via this link <hyperlink>.

Regards,
Christophe

Answer 2

Hi,

I am following up on this thread to know if you have seen my reply. Feel free to share your updates in your convenient time, and we will be happy to help you.

Regards,
Christophe

Answer 3

That's strange - the change should be instantaneous. I have only seen if fail in workbooks that contained many complicated conditional formatting rules applied to huge numbers of cells - Excel sometimes cannot cope with that.

Answer 4

Same result if I save the workbook on a local drive not synced to a cloud.

Answer 5

What happens if you store the workbook on a local disk only (i.e. in a folder not synced with OneDrive)?