excel

How to temporarily turn off Conditional Formatting rules in Excel for only selected cells


I was curious how to do temporarily turn off Conditional Formatting in excel for a subset of my cells and I didn't see an answer so I'll post a solution and see if others have a better solution.


Solution

  • This is what worked for me:

    1. I just highlighted all the cells which will bring up all the rules applicable to all the cells. So just made a 'square' selection of the cells. Its fine if it has a mix of Conditional Formatted cell and regular non-formatted cells (the Manage Rules will only list the cells which actually have Cond Formatting rules applied to them).
    2. click ConditionalFormatting->Manage Rules
    3. Add a rule at the very top which applies to all these cells.
      a. set the 'Rule' text to '=TRUE()' (without the apostrophe's)
      b. set the 'Format' to 'No Format Set' (aka by setting a white background)
      c. leave the 'Applies to' section alone since you already selected the cells via a selection but if you need to fix you can do here.
      d. check the 'Stop If True'
    4. Click Apply/OK
    5. (optional) Repeat with another selection area if needed (if the square selection didnt cover all the areas you wanted).

    This should work. If you need to unset it... highlight the cells and remove this top 'override' rule or set it to '=FALSE()' (without the apostrophe's) if you think you'll be switching formatting off/on constantly vs just temporarily.

    Excel example