ThisCalculator

Everyday

Excel IF Cell Contains Formula? How to Check

Updated · Standard formula, shown below

Excel IF Cell Contains Formula? How to Check

This article explains how to check whether a cell in Excel contains a formula, rather than a static value, and how to use that information in your own formulas. You will learn the built-in functions, a simple VBA approach, and how to handle common issues like array formulas and conditional formatting. By the end, you can reliably identify formula cells and build your own checks.

Why You Might Need to Know If a Cell Has a Formula

Excel does not have a direct worksheet function named ISFORMULA in all versions, but it does have one since Excel 2013. Before that, users had to rely on VBA or a combination of other functions. Knowing whether a cell contains a formula matters for several reasons:

  • Auditing spreadsheets – you want to ensure that certain cells are calculated, not hard-coded.
  • Conditional formatting – you might want to highlight cells that contain formulas to avoid overwriting them.
  • Data validation – you may want to restrict input to cells that are not formulas.
  • Troubleshooting – if a value looks wrong, you want to know if it is a formula error or a typing mistake.

How to Check with ISFORMULA (Excel 2013 and Later)

Starting with Excel 2013, Microsoft added the ISFORMULA function. It returns TRUE if the referenced cell contains a formula, and FALSE if it contains a constant (number, text, date, or blank). The syntax is simple:

=ISFORMULA(reference)

For example, if cell A1 contains =SUM(B1:B10), then =ISFORMULA(A1) returns TRUE. If A1 contains the number 42, it returns FALSE.

You can use this directly in a cell, in conditional formatting, or inside other functions like IF:

=IF(ISFORMULA(A1), "Formula", "Value")

How to Check in Older Excel Versions (2010 and Earlier)

If you are using Excel 2010 or earlier, ISFORMULA is not available. You have two main alternatives:

  • Use VBA – write a small user-defined function that checks the HasFormula property of a cell.
  • Use the built-in “Go To Specialâ€� – this is not a formula, but it lets you select all formula cells manually.

For VBA, press Alt+F11 to open the VBA editor, insert a new module, and paste this code:

Function IsFormula(cell As Range) As Boolean
    IsFormula = cell.HasFormula
End Function

Then you can use =IsFormula(A1) in your worksheet. This works in all Excel versions.

Using ISFORMULA with Conditional Formatting

One of the most common uses is to highlight formula cells so you can see them at a glance. To do this:

  1. Select the range you want to check (e.g., A1:E10).
  2. Go to Home → Conditional Formatting → New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter the formula: =ISFORMULA(A1) (adjust the reference to the top-left cell of your selection).
  5. Set a format (e.g., a yellow fill) and click OK.

Now every cell that contains a formula will be highlighted. This is extremely useful for reviewing a complex spreadsheet before sharing it.

Common Problems and Misconceptions

There are a few pitfalls when checking for formulas:

  • Array formulas – ISFORMULA works with array formulas, but if you use the older CSE (Ctrl+Shift+Enter) method, the formula is stored in multiple cells. ISFORMULA will return TRUE for each cell in the array range.
  • Cells with errors – a cell containing =1/0 returns #DIV/0! but ISFORMULA still returns TRUE because the cell contains a formula, not a constant.
  • Blanks – a blank cell that has no formula returns FALSE. A cell with a formula that returns an empty string (e.g., =IF(A1="","",A1)) is still a formula, so ISFORMULA returns TRUE.
  • Volatile functions – ISFORMULA is not volatile, but it does recalculate when the workbook recalculates. If you change a cell from a value to a formula, the result updates.

Alternatives: Using Excel’s Built-in “Go To Special�

If you only need to find formula cells once, without creating a formula, use the built-in feature:

  1. Press F5 (or Ctrl+G) to open the Go To dialog.
  2. Click Special.
  3. Select Formulas and click OK.

Excel will select all cells containing formulas. You can then apply a fill color or review them. This is a quick, manual method that works in every version.

Comparing ISFORMULA with Other Functions

Sometimes you might want to know if a cell contains a specific type of value, such as a number or text. The ISNUMBER, ISTEXT, and ISBLANK functions return TRUE based on the result of the cell, not whether it is a formula. For example:

  • =ISNUMBER(A1) returns TRUE if A1 contains a number, whether it is typed or calculated.
  • =ISTEXT(A1) returns TRUE if A1 contains text.
  • =ISBLANK(A1) returns TRUE if A1 is empty.

These are not substitutes for ISFORMULA because they look at the result, not the origin. If you need to know if a cell is “hard-coded� (a constant), you can combine ISFORMULA with NOT: =NOT(ISFORMULA(A1)).

Frequently Asked Questions

Can I use ISFORMULA on a range of cells?

No, ISFORMULA accepts a single cell reference. If you give it a range, it will return a #VALUE! error. To check multiple cells, use it in a helper column or use conditional formatting with a relative reference.

Does ISFORMULA work with formulas that return an error?

Yes. Even if the formula results in an error like #N/A, the cell still contains a formula, so ISFORMULA returns TRUE.

How do I count how many cells in a range contain formulas?

You can use an array formula like =SUMPRODUCT(--ISFORMULA(A1:A10)). This works because ISFORMULA returns an array of TRUE/FALSE, and the double unary converts them to 1/0.

Is there a way to highlight formula cells in Google Sheets?

Google Sheets does not have an ISFORMULA function. However, you can use the Find and Replace feature or a Google Apps Script to check the getFormula() method. The built-in Conditional formatting with a custom formula also does not have a direct equivalent.

Can I use ISFORMULA in data validation to prevent overwriting?

Yes, you can create a data validation rule that allows only non-formula cells. Select the range, go to Data → Data Validation, choose Custom, and enter =NOT(ISFORMULA(A1)). This will block any entry into cells that contain formulas.

Does ISFORMULA work with defined names that refer to formulas?

Yes, if a defined name refers to a cell that contains a formula, ISFORMULA will return TRUE for that cell reference. However, if the defined name itself is a formula (e.g., a name that calculates a value), ISFORMULA will not directly tell you that; you would need to inspect the name definition.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.