The Excel ISFORMULA function returns TRUE if a cell contains a formula. The function is new in Excel 2013.
The ISFORMULA function can be used in formatting and auditing. For example:
The ISFORMULA function has the following syntax:
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.
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, 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:
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 will only work in Excel 2013 or later.
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.
Last updated: September 30, 2016 7:36 PM