How to Use the Excel ISFORMULA Function to Audit Complex Spreadsheets

The Hidden Danger of Hardcoded Numbers

When you inherit a massive, complex Excel spreadsheet from a colleague, the greatest danger doesn’t lie in the formulas—it lies in the numbers that look like formulas.

Imagine an accounting spreadsheet detailing the quarterly budget for 50 different departments. You click on a cell in the “Total Expenses” column, expecting to see a formula like =SUM(B2:F2). Instead, you see a static, hardcoded number: $45,000.

This is a major red flag. If someone manually typed a number into a cell that is supposed to be a dynamic calculation, the entire spreadsheet is broken. If you update the budget in column B, the Total in column G will not update because the formula was overwritten.

In a sheet with thousands of rows, manually clicking every single cell to check the formula bar is impossible. To quickly audit a spreadsheet and separate the dynamic formulas from the hardcoded manual entries, you need the ISFORMULA function.

Step 1: The Basic Concept of ISFORMULA

ISFORMULA is an information function. It does not perform math; it simply acts as an inspector. You point it at a target cell, and it asks one question: “Does this cell begin with an equals sign (=)?”

The syntax is incredibly simple:
=ISFORMULA(reference)

If you type =ISFORMULA(A1), Excel will output exactly one of two words:

• TRUE: If cell A1 contains a formula (e.g., =A2+A3 or =VLOOKUP(...)).
• FALSE: If cell A1 contains static text, a hardcoded number (e.g., 500), or is completely empty.

Step 2: Creating an Audit Column

The fastest way to audit a suspicious column is to create a temporary helper column next to it.

Assume Column G contains your “Total Expenses” for 50 rows (G2 to G51). You want to ensure every single cell in Column G is a legitimate calculation.

1. Click on cell H2 (next to the first total).
2. Type =ISFORMULA(G2) and press Enter.
3. Double-click the small green square in the bottom right corner of cell H2 to drag the formula down to row 51.

You now have a clean list of TRUE and FALSE values. You can apply a quick filter to Column H and select only the “FALSE” values. Excel will instantly hide all the correct formulas and reveal only the rows where someone maliciously or accidentally typed a static number over a calculation.

Step 3: Visual Auditing with Conditional Formatting

Helper columns are useful, but for a truly professional dashboard, you want the errors to highlight themselves automatically.

You can combine ISFORMULA with Conditional Formatting to instantly turn any hardcoded number bright red.

1. Highlight the entire range of cells that should contain formulas (e.g., G2:G51).
2. On the Home tab of the ribbon, click Conditional Formatting.
3. Select New Rule…
4. Choose “Use a formula to determine which cells to format”.

Now, you need to write the logical test. We want the cell to turn red if it is NOT a formula.

In the formula box, type:
=NOT(ISFORMULA(G2))

(Make sure you use the first cell of your highlighted range, and do not use dollar signs $ to lock the reference, so the formatting can slide down the column).

5. Click the Format… button.
6. Go to the Fill tab and select a bright red color.
7. Click OK twice to apply the rule.

Instantly, any cell in that column that contains a hardcoded number (or is blank) will glow red. If a user deletes a formula and types “500”, the cell will instantly turn red, warning them that they have broken the spreadsheet’s logic.

Step 4: Advanced Auditing: Ignoring Blank Cells

The conditional formatting rule above has one slight annoyance: ISFORMULA returns FALSE for blank cells. This means if you highlight extra rows at the bottom of your sheet for future data entry, they will all turn red because they are empty.

To fix this, you must tell Excel: “Turn red IF the cell is NOT a formula, AND the cell is NOT blank.”

You can do this by wrapping the logic in an AND function.

Edit your Conditional Formatting rule to read:
=AND(NOT(ISFORMULA(G2)), NOT(ISBLANK(G2)))

Now, the audit is perfect. Valid formulas remain normal. Empty cells remain normal. But the moment someone types static text or a hardcoded number into that column, the cell will instantly flag itself as an error.