See how to use the Microsoft Excel ISFORMULA function to check if a cell contains formula, in Excel 2013 or later. Use ISFORMULA with conditional formatting, to highlight cells that have formulas.
The ISFORMULA function can be used in formatting and auditing. For example:
The ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if the cell does not contain a formula.
The ISFORMULA function has the following syntax:
This function is in the group of Excel Information functions
The ISFORMULA function is one of the Microsoft Excel IS functions. These functions check a specific value, and return a logical value of TRUE or FALSE, or they might return an error value under some conditions.
To see examples of other IS functions, such as ISBLANK, ISERROR, ISTEXT, and ISNUMBER, go to the Excel IS Functions page on the Microsoft site.
Also, see the list of Excel Information functions, on the Microsoft site, with version markers on the newer functions, to show when they became available.
The ISFORMULA function returns TRUE if there is a formula in a worksheet cell. If the cell does not contain a formula, the ISFORMULA result is FALSE
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.
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.
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.
You can use ISFORMULA with conditional formatting, to highlight the cells that contain formulas.
In the screenshot below,
For example, the formula in cell C2 is:
To apply conditional formatting that will highlight the cells with formulas:
Now, the cells with formulas are colored gray, and the cells without formulas have no fill color.
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")
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".
To see the formulas used in these examples, you can download the ISFORMULA function sample workbook. The file is zipped, and the ISFORMULA function and FORMULATEXT function will only work in Excel 2013 or later.
Last updated: June 11, 2022 3:49 PM