26

Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?

2
  • 1
    If 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.
    – yoyo
    Aug 15, 2018 at 15:49
  • 1
    The best solution has changed in newer versions of Excel - make sure to look at multiple answers.
    – katriel
    Jun 10, 2021 at 14:48

6 Answers 6

16

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.

3
  • 1
    +1 Apply to Excel 2013 onwards
    – wilson
    Apr 17, 2018 at 9:56
  • This works in Google Sheets too. +1.
    – Ryan
    Jul 9, 2022 at 12:50
  • Works, but would like to understand why a static A3 value is interpreted as "current cell" instead just A3 when the formula is evaluated.
    – Fede Mika
    Aug 19, 2023 at 11:23
16

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?".

2
  • This is the correct answer for Excel 2013 and later, see support.office.com/en-us/article/…
    – anishpatel
    Jan 3, 2017 at 21:53
  • 2
    Instead 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
9

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
4
  • Sorry, I haven't tested your answer until now. This trick is simple and beautiful. Accepted! Thanks. (By the way, the link is broken.)
    – wilson
    Oct 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.ma
    Dec 20, 2016 at 11:15
  • 1
    The 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.ma
    Dec 20, 2016 at 11:22
  • You used to be able to use xlm without any macro warnings, MSFT changed the security approach.
    – brettdj
    Oct 20, 2017 at 11:23
4

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:

  1. Highlight column A
  2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format
  3. Use the ff. formula: =IsFormula(A1)

Non-VBA

  1. Press F5 or Ctrl + G
  2. Click Special.
  3. Choose Formulas and click OK. This highlights all cells in the worksheet that contains formulas.
  4. 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.
1
  • 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.
    – wilson
    Nov 1, 2011 at 2:09
1

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.

-1

You can also use conditional formatting:

  1. Select the range of cells you want to apply your conditional formatting to
  2. In Home -> Conditional formatting -> New rule: Use a formula to detect which cell to format
  3. In Format values when this formula is true put: =HasNoFormula
  4. Select the format you want

Tested using Excel 2010.

2
  • Your answer is incomplete. I don't think you have tested.
    – wilson
    Oct 30, 2013 at 7:12
  • 2
    -1, this doesn't do anything. What is =HasNoFormula supposed to do?
    – toryan
    Mar 27, 2014 at 23:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .