Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?
-
1If you just want to quickly see which cells contain formulas you can type Ctrl-` (that's a back-tick, on the same key as ~ on English keyboards) to display formulas. Press the key again to toggle.– yoyoAug 15, 2018 at 15:49
-
1The best solution has changed in newer versions of Excel - make sure to look at multiple answers.– katrielJun 10, 2021 at 14:48
6 Answers
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.
-
1
-
-
Works, but would like to understand why a static A3 value is interpreted as "current cell" instead just A3 when the formula is evaluated. Aug 19, 2023 at 11:23
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?".
-
This is the correct answer for Excel 2013 and later, see support.office.com/en-us/article/… Jan 3, 2017 at 21:53
-
2Instead of using
INDIRECT("rc",FALSE)
, use relative referencing with the top left cell in your conditional formatting "Applies to" range. For example, if your "Applies to" range is=$A$3:$F$300
, then use=ISFORMULA(A3)
. This works because conditional formatting supports relative referencing, see this Aug 9, 2017 at 12:36
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
-
Sorry, I haven't tested your answer until now. This trick is simple and beautiful. Accepted! Thanks. (By the way, the link is broken.)– wilsonOct 30, 2013 at 7:14
-
Tested and it works! I had to find the correct definition for the Italian version of Excel 2007. If you have Italian Excel you must use =INFO.CELLA(48;INDIRETTO("rc";FALSO)) (please note semicolon in place of commas, too)– Kar.maDec 20, 2016 at 11:15
-
1The bad part is you need to save it as a macro file (e.g. .xlsm), and when you open the file the coloring does not show until you enable macros.– Kar.maDec 20, 2016 at 11:22
-
You used to be able to use
xlm
without any macro warnings, MSFT changed the security approach.– brettdjOct 20, 2017 at 11:23
You can try these:
VBA
Create a custom function with the following code:
Function IsFormula(ByVal Ref As Range) As Variant
If Ref.Cells.Count > 1 Then
IsFormula = CVErr(xlErrNA)
Else
IsFormula = Ref.HasFormula
End If
End Function
Example:
To check if any cells in column A have any formulas:
- Highlight column A
- Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format
- Use the ff. formula:
=IsFormula(A1)
Non-VBA
- Press F5 or Ctrl + G
- Click Special.
- Choose Formulas and click OK. This highlights all cells in the worksheet that contains formulas.
- Set up the format you'd like to use.
Or
Go to Cell Styles (under the Home tab) and pick a style that you want to associate with formula-containing cells. To change the look-and-feel, right-click on the style that you selected and click Modify. All cells given this style will automatically be updated.
-
although i personally don't prefer to use VBA, the VBA solution is better in this case because the cell formatting can be changed dynamically when formula is entered into a cell (or vice versa). thanks.– wilsonNov 1, 2011 at 2:09
The newer help sites recommend a User-Defined Function:
Function IsFormula(cell) as boolean
IsFormula = cell.HasFormula
End Function
Then use that function as your condition
And in fact, Excel 2013 and later has IsFormula
as a standard function.
You can also use conditional formatting:
- Select the range of cells you want to apply your conditional formatting to
- In Home -> Conditional formatting -> New rule: Use a formula to detect which cell to format
- In Format values when this formula is true put:
=HasNoFormula
- Select the format you want
Tested using Excel 2010.
-
-
2