15

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
1
  • You had the ISFORMULA part correct, you just needed to apply it to the entire range, starting from B7
    – Blindspots ♦
    Commented Jun 21, 2023 at 20:06

3 Answers 3

Reset to default
21

There is a related question on Stack Overflow that has a helpful answer:

Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell".

So, for example, if you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1) and apply it to the range A1:J10. The important part is that the cell reference in your formula (e.g. A1) matches the upper-left cell in the range you are formatting (e.g. A1:J10).

Use the formula =ISFORMULA(A1) and apply it to the range A1:J10

Note also that for the ISFORMULA() function in particular, it is possible to specify either a cell or a range for the parameter, though regardless whether you use a cell or range, it only looks at the first cell anyway:

ISFORMULA returns TRUE if cell is a cell that contains a formula. If cell contains a range of cells then TRUE will be returned if the first cell in the range contains a formula. All other values will return FALSE.

So, you could also use the formula =ISFORMULA(A1:J10) to format your range. Note that this works with normal ranges (e.g. B2:F30), but not with infinite ranges (e.g. B:F).

Improve this answer
2
  • 1
    I updated the answer with additional information. The ISFORMULA() function accepts either a cell or a range, but works the same way in either case.
    – Ryan Kohn
    Commented Jul 15, 2023 at 20:49
  • 1
    Appreciate the update and the documentation. Answer is comprehensive ✓
    – Blindspots ♦
    Commented Jul 15, 2023 at 21:13
4

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
0
-1

=ISFORMULA(B1:B)

Interesting but if you start with infinite referances it does not work, but if you start as normal range and finish as infinite range it is working.

Improve this answer
0

Your Answer

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

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