Conditionally format a cell if it contains formula

Building on brettdj's answer, because I found the linked article quite difficult to follow:

  1. Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
  2. Insert the following formula: =ISFORMULA(INDIRECT("rc",FALSE))
  3. If you want the rule to apply to the whole worksheet, $1:$1048576 as the range to apply to. Otherwise, you can enter any range.

The formula INDIRECT("rc",FALSE) returns the reference of the current cell. If I ever use this in a sheet, I create a Defined Name called something like ThisCell and use that in the formula, just in case I ever come back years later and think "what the hell is this for?".


You can use conditional formatting to do this by using XLM and Range Names

I have a longer article on Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content

  1. Define a Range Name IsFormula =GET.CELL(48,INDIRECT("rc",FALSE))
  2. Apply a conditional formatting cells testing for the formula, ie =IsFormula with a colour fill

There is a very simple way to do this, tested in Excel 2016.

Highlight your range you wish this to apply to, let's say from A3:W20. Go into conditional formatting and select NEW RULE | USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.

Put in =isformula(A3) and pick the format you want to apply.

A3 is obviously a reference to the first cell in your range but this formatting then applies to all. The result is that within your range, any cell that is a formula is conditionally formatted.