Last week, we took a look at the new FORMULATEXT function in Excel 2013. Another one of the new features in Excel 2013 is the ISFORMULA function. Finally, there is a way to identify cells that contain a formula, without creating a User Defined Function to do the job.
The TYPE function was originally designed to show what a cell contained, such as text or a formula. It returns a number to show the type for a cell's contents, or a formula's result. Here's the list of results, and the data types:
In a few versions of Excel, the Help files incorrectly reported that a formula would return 8 with the TYPE function, but unfortunately, that’s not the case.
Check for a Formula
With the new ISFORMULA function, you can test a cell, to see if it contains a formula. In the screenshot below, the following formula is entered in cell B4, and copied across to cell D4:
The result in cells B4 and C4 is FALSE, because cells B2 and C2 have numbers typed in them. The result in D4 is TRUE, because cell D2 contains a formula.
Highlight Cells With Formulas
You can use the ISFORMULA function with conditional formatting, to highlight cells that contain formulas. In the screen shot below, cells in column C have a formula, and they are shaded grey.
For the details on how to apply this type of conditional formatting, and for more information on the ISFORMULA function, please visit my Contextures website: Excel ISFORMULA FUNCTION