1

I have multiple conditional formatting rules in one of my Google Sheets, I would like to disable a few of those rules for some time and would like to enable them again. But I don't see any such feature.

Is it possible to natively do this either in Google Sheets or in Excel (without using a helper cell)?

The main idea behind this question is to remove the processing of the conditional formatting formula's so that they don't get calculated at all. This helps in reducing the processing load of the sheet significantly (I have a sheet which has a lot of conditional formatting)

Improve this question
3
  • Is it possible No. Disabling the conditional formatting rule removes this rule. Make a copy of worksheet and remove formattings which you do not need.
    – Akina
    Apr 17, 2019 at 9:44
  • I don't prefer doing that as this is a sheet that is shared with multiple people. Creating a copy would change the link of the file. This may not be a problem for SharePoint Excel files, but it is an issue with Google Spreadsheets.
    – Gangula
    Apr 17, 2019 at 9:46
  • Add another condition to check the contents of a cell that is not used. When that cell Contains something in makes your condition false and the formatting does not get applied. If the cell is empty/blank the condition becomes true and provided the remainder of your conditional formatting is true the formatting gets applied
    – Forward Ed
    Apr 17, 2019 at 11:24

3 Answers 3

Reset to default
1

  • there isn't such functionality to switch off conditional formatting but you can do so by inserting ' in front of the formula:

    0

    0

Improve this answer
1
  • Thank you @user0. This works, but this means that I have to refer to cell C23 in all my conditional formatting formula. I'm looking for a way to do this without any helper cell
    – Gangula
    Mar 18, 2022 at 7:45
1

I have implemented custom formatting where the formula uses an ON/OFF field value to start so something like this:

=if($I$35 <> "OFF", )

Not sure if it helps but thought I would throw it out there

Improve this answer
1
  • Thank you @Derek. This works, but this means that I have to refer to cell I35 in all my conditional formatting formula. I'm looking for a way to do this without any helper cell
    – Gangula
    Mar 18, 2022 at 7:45
0

I know this answer is very old by now, but I got around this issue by changing the formatting effect to be the same or nearly indistinguishable from the normal text. That way the rule continues being updated as others edit the sheet, but it doesn't show the formatting effect until you change it back.

I'm only using this fix for cell and font color changes so I'm not sure how well it would work for other formattings, but I thought I would share on the off chance anyone else was looking for work-arounds!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.