Is there any method for searching formula in conditional formatting? 'Find and replace' menu only can search formula in cells.

I want to remove a range of cells but i don't want to break a conditional format if that range is used in its custom formula. If i have a lot of conditional formatting then i have to check it manually one by one.

1 Answer 1


To quickly check whether a range contains cells that have conditional formatting, select the range and choose Format > Conditional formatting.

  • If there are no conditional formatting rules, the rule editor opens. Press Cancel to dismiss it.

  • If there are conditional formatting rules, they will be listed in the right-hand pane. If one of the rules uses a custom formula, the list will say so.

  • Thanks, but i'm not checking if the cell has conditional formatting. I'm checking if the the cell is used as rule by conditional formatting. For example in A1 i use custom formula =X100>0 . Using your method only can check A1 but not X100. But how do i know if X100 is used in formula. So before delete X100 i have to make sure it's not used by any conditional formatting of any other cells .
    – andio
    Aug 3, 2021 at 16:49
  • I do not think there is any easy way to check for that in the general case, because conditional formatting custom formula rules may apply to several cells, and may use relative addressing and thus not literally mention one of the predecessor cells. It would probably require a parser whose complexity of the same order of magnitude as the one used by Google Sheets itself. Aug 3, 2021 at 18:31

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.