You often need to know if a cell contains a formula or a static value. This is common when auditing a workbook or troubleshooting unexpected results. The ISFORMULA function provides a direct way to check this. This article explains how to use ISFORMULA to identify formula cells.
Key Takeaways: Using ISFORMULA in Excel
- ISFORMULA function: Returns TRUE if the referenced cell contains a formula and FALSE if it contains a static value.
- Conditional Formatting with ISFORMULA: Visually highlight all cells containing formulas in a selected range.
- Filtering with a helper column: Create a column of ISFORMULA results to quickly sort and isolate formula cells.
What the ISFORMULA Function Does
The ISFORMULA function is an information function in Excel. It checks a single cell reference and returns a logical value. The result is TRUE if the cell contains a formula. The result is FALSE if the cell contains a number, text, date, or error value. This function is available in Excel 2013 and later versions for Windows and Mac.
The syntax for the function is simple: =ISFORMULA(reference). The ‘reference’ argument is required. It can be a direct cell reference like A1, a named range, or a reference to another worksheet. ISFORMULA only checks the top-left cell if you reference a range like B2:D10. It will return TRUE only if cell B2 contains a formula.
Common Use Cases for ISFORMULA
Workbook auditing is a primary use. You can quickly scan a sheet to find all calculated cells. This helps verify that key metrics are driven by formulas and not hard-coded numbers. Another use is in template design. You can protect formula cells while allowing users to input data into static cells. Troubleshooting is also easier. If a cell shows an unexpected value, checking ISFORMULA confirms if it’s a calculation or manual entry.
Steps to Use the ISFORMULA Function
You can apply ISFORMULA in a few different ways. The basic method is to use it in a cell. The advanced method involves Conditional Formatting for visual scanning.
Method 1: Using ISFORMULA in a Cell
- Select a cell for the result
Click on an empty cell where you want the TRUE or FALSE result to appear. This is often in a new helper column. - Enter the ISFORMULA function
Type the equals sign, the function name, and an opening parenthesis: =ISFORMULA( - Reference the cell to check
Click on the cell you want to inspect, or type its address like C5. Then type a closing parenthesis. - Complete the formula
Press Enter. The cell will display TRUE if the referenced cell has a formula, or FALSE if it does not. - Copy the formula down a column
Use the fill handle to drag the formula down adjacent rows. This checks a whole list of cells at once.
Method 2: Highlighting Formula Cells with Conditional Formatting
- Select the range to audit
Click and drag to select the cells, rows, or columns you want to check for formulas. - Open the Conditional Formatting menu
Go to the Home tab on the ribbon. Click Conditional Formatting in the Styles group. Select New Rule. - Create a rule using a formula
In the New Formatting Rule dialog, select ‘Use a formula to determine which cells to format’. - Enter the ISFORMULA rule
In the formula box, type =ISFORMULA(A1). If your top-left selected cell is not A1, use that cell’s address instead. - Set the highlight format
Click the Format button. Choose a fill color, font color, or border to make formula cells stand out. Click OK twice to apply the rule.
Common Mistakes and Limitations
ISFORMULA Returns FALSE for a Cell That Looks Like a Formula
A cell might show a calculated value but ISFORMULA returns FALSE. This usually means the cell contains the result of a formula that was pasted as a value. The formula is no longer present. The cell now holds a static number or text. Check the formula bar. If it’s empty of a formula, ISFORMULA correctly returns FALSE.
Referencing an Entire Range Only Checks One Cell
The function =ISFORMULA(B2:D10) only tests cell B2. It will not check every cell in the B2:D10 block. To check multiple cells, you must use the function in an array formula or copy it down a column. In modern Excel, you can use =ISFORMULA(B2#) if B2 is the start of a spilled array from a dynamic formula.
Conditional Formatting Rule Does Not Apply Correctly
If your highlight is not appearing, check the rule’s formula reference. It must use a relative reference for the active cell. If you selected range C5:C20 and used =ISFORMULA($C$5), only cell C5 will be evaluated. Use =ISFORMULA(C5) without dollar signs so Excel adjusts the reference for each cell in the range.
ISFORMULA vs. Manual Inspection and Other Methods
| Item | Using ISFORMULA Function | Manual Inspection (Formula Bar) |
|---|---|---|
| Speed for many cells | Fast with copying or Conditional Formatting | Very slow, requires clicking each cell |
| Accuracy | Perfect, automated logical test | Prone to human error |
| Visual output | TRUE/FALSE text or colored highlights | No visual summary, only formula bar text |
| Works on protected sheets | Yes, if cells are not locked for editing | No, cannot select cells to see formula bar |
| Can be used in other formulas | Yes, result can drive IF or FILTER functions | No, purely manual |
You can now reliably identify formula cells in your worksheets. Use ISFORMULA in a helper column to filter or sort your data based on calculation status. For a more advanced technique, combine ISFORMULA with the IF function to display custom messages. Try =IF(ISFORMULA(A1), “Calculated”, “Enter Value”) to create clear cell type labels.