15

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

  • 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 '18 at 15:49
8

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
  • 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 '13 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 '16 at 11:15
  • 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 '16 at 11:22
  • You used to be able to use xlm without any macro warnings, MSFT changed the security approach. – brettdj Oct 20 '17 at 11:23
9

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

  • This is the correct answer for Excel 2013 and later, see support.office.com/en-us/article/… – anishpatel Jan 3 '17 at 21:53
  • 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 – Micah Lindström Aug 9 '17 at 12:36
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.
  • 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 '11 at 2:09
3

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.

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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