13

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can use the ISFORMULA() function to determine a cell contains a formula.

e.g. I can apply conditional formatting to cell B7 using the custom formula =ISFORMULA(B7).

How do I format an entire range using this formula? I do not wish to manually type this formula for every cell.

Improve this question

2 Answers 2

Reset to default
17

You can use a range as a parameter for the ISFORMULA() function.

e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and apply it to the range A1:J10.

Use the formula <code>=ISFORMULA(A1:J10)</code> and apply it to the range <code>A1:J10</code>

Note that this works with normal ranges (e.g. B2:F30), but not with infinite ranges (e.g. B:F).

Improve this answer
2
  • 3
    I just tried it and it works like a charm. It's a bit of a shame it doesn't just assume the current range and that we have to effectively specify it twice.
    – David Brossard
    Dec 29, 2018 at 9:41
  • 1
    @DavidBrossard, Cladelpino posted an answer so that you don't have specify the explicit range twice: webapps.stackexchange.com/questions/79846/…
    – wisbucky
    Jul 26, 2021 at 18:02
2

Note that it is also possible to use conditional formatting as in (for your case):

=isformula(a1)

This kind of specification is more flexible than the whole range answer. For example, lets say in your case that you want to format the whole row based on if the cell in column A contains a formula or not, then:

=isformula($a1)

would do the trick. Of course, in both cases the formatting range would remain A1:J10

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.