Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first chapter of each course.
Start Free
What is the ISFORMULA Function?
The ISFORMULA Function[1] is categorized under Excel Information functions. It will test a specified cell to see if it contains a formula. If it does contain a formula, then it will return TRUE. If not, then it will return FALSE.
The ISFORMULA function was introduced in MS Excel 2013. The purpose of the function is to show the formula, if any, contained in the cell.
Formula
=ISFORMULA(reference)
The ISFORMULA function uses the following argument:
Reference (required argument) – This is a reference to the cell we wish to test. The reference in the formula can be a cell reference, a formula, or a name that refers to a cell.
How to use the ISFORMULA Function in Excel?
It is a built-in function which can be used as a worksheet function in Excel. To understand the uses of this function, let’s consider a few examples:
Example 1
Let’s say we are given the data below, and we wish to find out if any formula was used (or not used) in the data.
The formula would be ISFORMULA “cellreference” as shown below:
The results would be:
So, the function told us which of the cells contained a formula.
Example 2
Let’s see how this formula can be used with conditional formatting. If we are dealing with large amounts of data and we wish to highlight cells that contain a formula, we can do that using ISFORMULA along with conditional formatting.
For example, assume we are given the quarterly rent paid and we’ve calculated monthly rent paid in the adjacent column by using the ISFORMULA function.
To apply conditional formatting that will highlight cells with formulas, we need to take the following steps:
Select cells D2:D7, with cell D2 as the active cell.
After that, click the ‘Conditional Formatting’ command (on the Home tab).
Now click on ‘New Rule.’
Click on ‘Use a Formula’ to determine which cells to format.
Enter ISFORMULA formula, referring to the active cell – D2:
=ISFORMULA(D2)
Now click on the ‘Format’ button, and select a fill color for the cells with formulas – Peach in this example.
Now, click ‘OK’ to close the windows.
Example 3
Let’s assume that in Example 1 you don’t wish to get just TRUE or FALSE, but require the formula to return ‘No formula used.’ This can be done by inserting the formula:
=IF(ISFORMULA(B2), FORMULATEXT(B2), “No formula used”)
The results would be:
Example 4
Let’s now see how we can use the FORMULATEXT, ISFORMULA, and TEXTJOIN functions together. Suppose we are given the following data:
We can find out which cells contain a formula. So, FORMULATEXT, ISFORMULA, and TEXTJOIN functions all were used simultaneously.
How does the ISFORMULA Function in Excel work?
First, for the (fRange) A1:B13 we provided, we gave ADDRESS(ROW(fRange),COLUMN(fRange)). So, Excel returned an array of cell addresses for the desired range.
Next, FORMULATEXT(fRange) returned an array of formulas for that range.
The above two formulas were concatenated with a colon, so we got an array that looked like this:
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.