Conditionally format a cell if it contains formula
Building on brettdj's answer, because I found the linked article quite difficult to follow:
- Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
- Insert the following formula:
=ISFORMULA(INDIRECT("rc",FALSE))
- 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
- Define a Range Name IsFormula =GET.CELL(48,INDIRECT("rc",FALSE))
- 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.