How to Use the FORMULATEXT and ISFORMULA Functions in Google Sheets to Audit Spreadsheets

Maintaining financial models, operational dashboards, and collaborative data trackers in Google Sheets requires strict verification. As spreadsheets grow in complexity, hardcoded values accidentally typed over formulas can compromise entire workbooks. Manually clicking on each cell to check whether it contains a dynamic calculation or a static value in the formula bar is slow and error-prone. To streamline auditing and quality control, Google Sheets provides two indispensable inspection functions: FORMULATEXT and ISFORMULA.

In this guide, you will learn how to extract formula strings, detect hardcoded entries, build automated audit checks, and conditionally highlight calculated cells in Google Sheets.

1. The FORMULATEXT Function

The FORMULATEXT function extracts the formula from a referenced cell and returns it as a readable text string.

Syntax

=FORMULATEXT(cell)

Argument

• cell (Required): The single cell reference you wish to inspect (e.g., C10).

Key Behaviour and Rules

• If the referenced cell contains a formula (e.g., =SUM(A2:A10)), FORMULATEXT returns the exact string starting with the equals sign: "=SUM(A2:A10)".
• If the referenced cell contains static text, a manual number, or is completely blank, FORMULATEXT returns an #N/A error.
• It only accepts a single cell reference. Supplying an array (like A1:A10) without wrapping it in ARRAYFORMULA or MAP will only evaluate the first cell.

Practical Example: Generating Dynamic Documentation

When training colleagues or documenting financial models, showing both the calculated result and the underlying logic side-by-side clarifies calculations:

Cell	Calculated Output	Formula (FORMULATEXT)
B10	£4,520.00	=FORMULATEXT(B10) → =SUM(B2:B9)
B11	£542.40	=FORMULATEXT(B11) → =B10*0.12

2. The ISFORMULA Function

The ISFORMULA function performs a logical check to determine whether a target cell contains a formula, returning a simple boolean value.

Syntax

=ISFORMULA(cell)

Argument

• cell (Required): The cell you want to test.

Key Behaviour

• Returns TRUE if the cell contains an active formula (even if the formula currently returns an error such as #DIV/0! or #N/A).
• Returns FALSE if the cell contains hardcoded text, manual numbers, dates, or is empty.

Advanced Auditing Workflows

Workflow 1: Flagging Hardcoded Overwrites with IF

In budget templates, users sometimes overwrite automated formula rows with hardcoded estimates. You can create an automated alert column using ISFORMULA:

=IF(ISFORMULA(D2), "Automated", "WARNING: Hardcoded Value")

Drag this down your summary column to flag any row where a team member replaced a calculation with static input.

Workflow 2: Auditing an Entire Column with ARRAYFORMULA

Rather than dragging single formulas, audit an entire column dynamically:

=MAP(C2:C50, LAMBDA(cell, IF(ISBLANK(cell), "", IF(ISFORMULA(cell), "Formula", "Static Input"))))

This array formula instantly categorises every non-empty cell in the range without cluttering blank rows.

Workflow 3: Conditional Formatting Based on Formulas

Visually distinguishing between user-editable input cells and automated calculation cells prevents accidental data destruction:

1. Highlight the dataset range (e.g., B2:F50).
2. Click Format in the top menu bar, then select Conditional formatting.
3. Under “Format rules”, select Custom formula is from the drop-down menu.
4. Enter the rule formula:

   =ISFORMULA(B2)

5. Set a soft background fill (such as light blue or pale grey) and set text formatting to italics.
6. Click Done.

Every cell in the range that contains a calculation will automatically update its styling. Any cell that is overwritten with a static number will revert to standard white, immediately signaling an override.

Handling Errors with IFERROR and FORMULATEXT

Because FORMULATEXT throws an #N/A error on non-formula cells, wrap your formulas in IFERROR when parsing mixed datasets:

=IFERROR(FORMULATEXT(A2), "No formula present")