FORMULATEXT function

FORMULATEXT returns the exact formula text from a referenced cell, allowing you to view or audit spreadsheet logic as a plain-text string.

=FORMULATEXT(reference)

Generate a FORMULATEXT formula

Describe what you need. The generator will reach for FORMULATEXT where FORMULATEXT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How FORMULATEXT reads its arguments
referencerequiredFORMULATEXT →
ArgumentRequiredDescription
referenceRequiredA single-cell reference (e.g., A1) of any type; if the cell contains a formula the text is returned, otherwise an error is produced.

Returns

A single-cell text string containing the formula as written in the referenced cell.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Show the SUMIF formula that totals Truck A's service costs

VehicleOdometerService DateCostGarage
Truck A452002024-03-15350North Garage
Van B312002024-04-02210East Garage
Car C185002024-02-28120South Garage
=FORMULATEXT(G2)

Result: =SUMIF(A:A, "Truck A", D:D)

Cell G2 contains the formula =SUMIF(A:A, "Truck A", D:D) that adds up all Cost values for rows where Vehicle equals "Truck A". FORMULATEXT simply reads that cell and returns the exact text of the formula, which is useful for documentation or debugging.

2. Display the TODAY-minus-date formula that calculates days since service

VehicleOdometerService DateCostGarage
Truck A452002024-03-15350North Garage
Van B312002024-04-02210East Garage
Car C185002024-02-28120South Garage
=FORMULATEXT(H2)

Result: =TODAY()-C2

In column H we placed a helper formula =TODAY()-C2 to compute how many days have passed since each service date. By wrapping that reference with FORMULATEXT, the sheet shows the literal expression rather than the numeric result, making the calculation transparent to reviewers.

3. Retrieve a formula via a dynamic reference with INDIRECT

VehicleOdometerService DateCostGarage
Truck A452002024-03-15350North Garage
Van B312002024-04-02210East Garage
Car C185002024-02-28120South Garage
=FORMULATEXT(INDIRECT("G2"))

Result: =SUMIF(A:A, "Truck A", D:D)

INDIRECT converts the text "G2" into an actual cell reference, so FORMULATEXT receives the same input as in the first example. This technique lets you pull formula text from a location that may change based on other calculations, such as a drop-down that selects which vehicle’s total you want to audit.

Common errors

Which FORMULATEXT error are you seeing?
FORMULATEXT returned an error#N/A
Reference a cell that actually holds a formula, or wrap the call in IFERROR to handle the #N/A gracefully.
#REF!
Adjust the reference to a valid cell address within the worksheet’s bounds.
#VALUE!
Supply a single-cell reference, or use an aggregation function like TEXTJOIN together with ARRAYFORMULA if you need multiple formulas displayed.
ErrorWhy it happensHow to fix it
#N/AThe referenced cell (e.g., D2) contains a constant value rather than a formula, so FORMULATEXT cannot return any text.Reference a cell that actually holds a formula, or wrap the call in IFERROR to handle the #N/A gracefully.
#REF!The reference points to a non-existent cell, such as =FORMULATEXT(Z100) on a sheet that only has columns A-F.Adjust the reference to a valid cell address within the worksheet’s bounds.
#VALUE!A range larger than one cell is supplied, for example =FORMULATEXT(A2:D4); FORMULATEXT requires a single-cell argument.Supply a single-cell reference, or use an aggregation function like TEXTJOIN together with ARRAYFORMULA if you need multiple formulas displayed.

Tips and when to use something else

  • Pair FORMULATEXT with IFERROR to suppress #N/A when some rows only contain raw data.
  • Use the result of FORMULATEXT in data-validation lists to let users pick from existing formulas.
  • Remember the function returns text, so you cannot nest it directly inside arithmetic operations.
  • When you need the evaluated result instead of the formula itself, reference the cell normally or use GETPIVOTDATA for pivot-derived values.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.

Frequently asked questions

How can I list every formula used in a column of my maintenance log?
Enter =FORMULATEXT(A2) in a helper column next to the original data and copy it down. Each row will show the exact formula from column A, or #N/A if the cell holds a constant. Wrap the column in IFERROR to hide the error messages for non-formula cells.
Why does FORMULATEXT give me #N/A for some of the cost cells?
Because those cost cells (e.g., D2, D3, D4) contain static numbers, not formulas. FORMULATEXT only works on cells that have an underlying formula. To avoid the #N/A, either reference only formula cells or use IFERROR to replace the error with a friendly message such as "No formula".
Can FORMULATEXT retrieve the text of an array formula or a spilled range?
Yes, if the array formula resides in a single top-left cell, FORMULATEXT will return the array expression (e.g., =FILTER(A2:A10, B2:B10>0)). However, if you try to reference the entire spilled range, Excel will raise #VALUE! because the function expects a single cell, not a multi-cell array.
Is FORMULATEXT available in Google Sheets and does it behave the same as in Excel?
Google Sheets supports FORMULATEXT with identical syntax and most of the same error conditions. It returns the formula string for a given cell, produces #N/A when the cell has no formula, and #REF! for invalid references. The only notable difference is that Google Sheets does not generate a #VALUE! error for a multi-cell range; instead it returns the formula from the top-left cell of the range.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator