Failure of Excel Conditional Formatting in Windows 11 upgrade

Question

Since being forced to update to Windows 11 from Windows 10 the conditional formatting that is applied in all my Excel spreadsheets is no longer working correctly.

In addition whenever I try to correct errors and enter a cell column / row number in "Applies to" in Conditional Formatting Rules Manager it automatically changes to an "absolute or fixed reference" even after I have gone to the trouble of removing $ signs using function F4 key - is there a FIX for this issue

Answer 1

It seems that you are experiencing issues with conditional formatting in Excel after upgrading to Windows 11. While the provided context does not specifically address problems arising from an upgrade to Windows 11, there are a few general troubleshooting steps you can consider:

1. Check for Updates: Ensure that your version of Excel is up to date. Sometimes, issues can be resolved with the latest patches and updates.
2. Repair Office: You can try repairing your Office installation. Go to Control Panel > Programs > Programs and Features, select Microsoft Office, and then choose the Repair option.
3. Clear Conditional Formatting: If the conditional formatting is corrupted, you might want to clear the existing rules and reapply them. You can do this by going to the Conditional Formatting Rules Manager and using the clear option.
4. Check Excel Options: Sometimes, settings in Excel can affect how conditional formatting works. Check the Excel options to ensure that nothing is misconfigured.
5. Test in Safe Mode: Start Excel in Safe Mode to see if the issue persists. This can help identify if the problem is caused by add-ins or other custom settings.
6. Use Absolute References: If the references are automatically changing to absolute references, ensure that you are correctly entering the range without the $ signs. You might also want to check if there are any settings or add-ins that could be affecting this behavior.

If these steps do not resolve your issue, consider reaching out to Microsoft Support for more tailored assistance regarding your specific situation with Windows 11.

---

References:

• Working with conditional formatting
• Excel Conditional Formatting Vulnerability - CVE-2008-0117

Answer 2

All very well giving me these 6 options - How about some information, guidance or instruction on how to do them?

Answer 3

Hi Hugh,

You are seeing expected behavior in the Conditional Formatting Rules Manager. The Applies to box always displays absolute addresses with $ signs. Relative behavior comes only from the formula inside the rule. Deleting $ in Applies to will be put back by Excel, but that does not stop the rule from working.

The fix is to rebuild each rule so the formula is written relative to the top left cell of the Applies to range, and then leave Applies to as Excel shows it.

Do this on one rule to confirm.

Select the entire range you want the rule to cover first, for example B2:E500. Open Home > Conditional Formatting > Manage Rules > New Rule > Use a formula to determine which cells to format. In the formula box, write the condition as if you were writing it for the top left cell of your selected range. Use F4 here to set relative or absolute parts.

For example, to shade whole rows when Column B is greater than Column C, type =$B2>$C2. If you only want to compare to a single cell such as F2, anchor that cell like =B2>$F$2. Click Format, choose the format, then OK.

In the manager you will now see Applies to as \$B\$2:\$E\$500 which is normal, and the formula will be applied relatively across that entire range based on the anchors you set in the formula.

If the highlighting is offset, edit the rule and make sure the formula references the correct top left cell of the Applies to range.

If the rule should apply to only one column, make the Applies to exactly that column range and keep the formula pointed at the correct relative cell in that column.

Try this on a copy of one sheet. If it still misbehaves, tell me the exact Applies to range and the formula you are using so I can adjust the anchors for you.