How to Use the FORMULATEXT Function in Microsoft Excel to Inspect and Audit Formulas

When reviewing financial spreadsheets, engineering workbooks, or large data models in Microsoft Excel, understanding the exact mathematical logic behind calculated figures is essential. While pressing Ctrl + ` (Show Formulas mode) displays formula strings across an entire sheet, doing so expands column widths drastically, disrupts sheet formatting, and hides the resulting values.

To inspect and document cell calculations alongside their calculated results, Microsoft Excel provides the FORMULATEXT function. FORMULATEXT extracts the exact formula string used in a target reference cell and returns it as plain text in an adjacent cell. This allows spreadsheet authors and financial auditors to display formulas dynamically for documentation, training manuals, quality assurance, and automated error auditing.

Understanding the FORMULATEXT Syntax

The FORMULATEXT function features a straightforward and uncluttered syntax:

=FORMULATEXT(reference)

The single argument reference is the cell address containing the formula you wish to display. The target reference can point to a cell on the current worksheet, a different sheet within the same workbook, or an external opened workbook.

Return Values and Error Conditions

• Valid Formulas: Returns the exact formula string as displayed in the Formula Bar, prefixed with the equal sign (e.g. "=SUM(B2:B10)").
• Static Data: If the target cell contains a static number, date, or text string rather than an active formula, FORMULATEXT returns the #N/A error.
• Blank Cells: Referencing an empty cell similarly produces an #N/A error.
• Closed External Workbooks: If referencing a cell in an external workbook that is closed, the function returns a #VALUE! error.

Basic Usage: Documenting Calculations Side-by-Side

The most common practical application of FORMULATEXT is creating a self-documenting audit column alongside calculated metrics.

1. Suppose cell C5 calculates an annual bonus using the formula:

   =IF(B5>=50000, B5*0.15, B5*0.05)

2. Cell C5 outputs the numeric monetary value, such as £7,500.00.
3. In cell D5, enter the auditing formula:

   =FORMULATEXT(C5)

4. Cell D5 immediately outputs the literal string:

   =IF(B5>=50000, B5*0.15, B5*0.05)

If you modify the formula in cell C5 later, the text displayed in cell D5 updates automatically, ensuring that audit documentation never goes out of date.

Handling Errors Gracefully with ISFORMULA and IFERROR

Because FORMULATEXT generates an #N/A error when evaluating cells that contain raw values instead of formulas, wrapping your lookup inside defensive functions creates a much cleaner audit sheet.

Using IFERROR to Suppress Errors

=IFERROR(FORMULATEXT(C5), "Manual Input (No Formula)")

If cell C5 contains a hardcoded figure like 1200, the cell cleanly reports “Manual Input (No Formula)” instead of displaying an unappealing error alert.

Pairing FORMULATEXT with the ISFORMULA Function

Excel also includes the complementary ISFORMULA function, which returns TRUE or FALSE depending on whether a referenced cell contains formula logic. You can combine both functions into an intelligent conditional statement:

=IF(ISFORMULA(C5), FORMULATEXT(C5), "Static Value")

Advanced Use Cases for FORMULATEXT

Beyond basic documentation, FORMULATEXT unlocks several advanced spreadsheet auditing techniques:

1. Detecting Hardcoded Values in Calculations

Financial modelling standards strictly prohibit embedding hardcoded numbers inside formula expressions (such as typing =A1*1.2 instead of linking to a designated tax rate cell). You can search your formulas for hardcoded numbers by combining FORMULATEXT with ISNUMBER and text parsing functions:

=IF(ISNUMBER(SEARCH("1.2", FORMULATEXT(C5))), "WARNING: Hardcoded Tax Rate Detected", "Compliant")

2. Checking for Volatile Functions

Functions like OFFSET, INDIRECT, NOW, and TODAY recalculate every time any cell in the entire workbook changes, leading to sluggish workbook performance. To audit an extensive spreadsheet model for volatile dependencies, use:

=IF(OR(ISNUMBER(SEARCH("INDIRECT", FORMULATEXT(C5))), ISNUMBER(SEARCH("OFFSET", FORMULATEXT(C5)))), "VOLATILE", "STABLE")

3. Highlighting Formulas with Conditional Formatting

You can flag cells that contain specific complex functions across an entire model:

1. Select the target data range (e.g. C2:C50).
2. On the Home tab, click Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the rule formula:

   =ISNUMBER(SEARCH("VLOOKUP", FORMULATEXT(C2)))

5. Choose a soft yellow fill colour and click OK.

Every cell in your column relying on legacy VLOOKUP syntax will immediately highlight, helping your team modernise older models to modern XLOOKUP formulas.

Key Auditing Functions in Microsoft Excel

FORMULATEXT forms part of Excel’s comprehensive suite of formula auditing utilities:

Function	Return Value	Primary Purpose
=FORMULATEXT()	Formula string as text.	Documenting, inspecting, and checking formula logic.
=ISFORMULA()	Boolean (TRUE / FALSE).	Verifying whether a cell contains active formulas or static inputs.
Ctrl + ` (Shortcut)	Global view toggle.	Temporarily viewing all formulas across the entire worksheet at once.

By leveraging FORMULATEXT in your reporting templates, you maintain rigorous data governance, eliminate hidden calculation errors, and make your Excel workbooks transparent and easy to audit.