Contextures

How to Use ISFORMULA Function

The Excel ISFORMULA function returns TRUE if a cell contains a formula. The function is new in Excel 2013.

How to Use ISFORMULA

The ISFORMULA function can be used in formatting and auditing. For example:

  • Test a cell for a formula
  • Highlight cells that contain a formula
  • Show the formula, if cell contains one

ISFORMULA Syntax

The ISFORMULA function has the following syntax:

  • ISFORMULA(reference)
    • Reference can be a cell reference, a formula, or a name that refers to a cell.

ISFORMULA Tips and Traps

  • If the reference is not a valid data type, such as a defined name that is not a reference, ISFORMULA returns the #VALUE! error value

Example 1: Test a cell for a formula

The ISFORMULA function returns TRUE if there is a formula in a worksheet cell.

In the screen shot below, numbers are typed in cells B2 and C2, and there is a formula in cell D2.

The following formula is entered in cell B4, and copied across to cell D4. It shows the result of TRUE in cell D4, because there is a formula in cell D2.

=ISFORMULA(B2)

ISFORMULA function

The ISFORMULA result will show TRUE if there is a formula in the referenced cell, even if the result is an error. In the screenshot below, there is a VLOOKUP formula in cell B2, and its result is an #N/A! error.

The ISFORMULA function is used in cell B4, with a reference to B2, and the result is TRUE.

ISFORMULA function

However, if the reference is not a valid data type, the ISFORMULA result will be a #VALUE! error. In the example shown below, there is a named formula -- TaxRate. It is a percentage (=0.07), instead of a range reference.

The ISFORMULA function in cell B3 returns an error, because it references the name TaxRate, which is not a valid reference.

ISFORMULA function

Example 2: Highlight cells with a formula

You can use ISFORMULA with conditional formatting, to highlight the cells that contain formulas.

In the screenshot below, numbers are typed in columns A and B. A formula in column C multiplies those two amounts. For example, the formula in cell C2 is:

=A2*B2

ISFORMULA function

To apply conditional formatting that will highlight the cells with formulas:

  1. Select cells A2:C4, with cell A2 as the active cell.
  2. On the Excel Ribbon's Home tab, click the Condtional Formatting command
  3. Click New Rule
  4. Click Use a formula to determine which cells to format
  5. Enter and ISFORMULA formula, refering to the active cell -- A2:

    =ISFORMULA(A2)

  6. Click the Format button, and select a fill color for the cells with formulas -- gray in this example.
  7. Click OK, twice, to close the windows.

ISFORMULA function

Now, the cells with formulas are colored gray, and the cells without formulas have no fill color.

ISFORMULA function

Example 3: Show formula, if cell contains one

You can combine the ISFORMULA function with the FORMULATEXT function (also new in Excel 2013), to check for a formula in the referenced cell. If there is a formula, show the formula's text. If there is no formula, show a message, such as, "Not a formula"

In the screen shot below, there is a formula in cell B2, and a number typed in cell B3. The following formula is entered in cell C2, and copied down to cell C3.

=IF(ISFORMULA(B2), FORMULATEXT(B2), "Not a formula")

ISFORMULA function

The formula from cell B2 is displayed in cell C2, and because there isn't a formula in cell B3, the result in cell C3 is "Not a formula".

Ger the ISFORMULA Workbook

To see the formulas used in these examples, you can download the ISFORMULA function sample workbook. The file is zipped, and the ISFORMULA function will only work in Excel 2013 or later.

fast excel

Excel Functions Tutorials

SUM  

VLOOKUP  

INDEX and MATCH  

Count  

INDIRECT   

Get All the Excel News

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Excel Videos

MATCH Function Video

VLOOKUP Function Video

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

excel chart tools

 

 

Last updated: September 30, 2016 7:36 PM