How to Use the FORMULATEXT Function in Excel

The FORMULATEXT function is available in both Microsoft Excel and Google Sheets, and it is designed to display the formula contained in a cell as text. This proves handy for documenting, troubleshooting, and auditing spreadsheets by revealing the formulas behind cell values.

Basic Syntax

The syntax for the FORMULATEXT function is simple:

=FORMULATEXT(reference)

reference: Refers to the cell for which you want the formula displayed as text.

Excel Example

Consider a scenario in Excel where you have a simple addition formula in cell A1, which sums the values in cells A2 and A3:

A	B
=A2 + A3	5
2	3

Entering the formula =FORMULATEXT(A1) in cell B1 displays the text =A2 + A3 in that cell.

Google Sheets Example

FORMULATEXT functions identically in Google Sheets as it does in Excel. You can use it in exactly the same way to display formulas as text.

Use Cases

There are various scenarios where FORMULATEXT can be extremely beneficial:

Documenting spreadsheets by displaying the formulas next to their resulting values.
Verifying formulas for accuracy and identifying errors.
Conducting audits on spreadsheets to ensure appropriate formula application.

With a clear understanding of how the FORMULATEXT function operates in both Excel and Google Sheets, you are well-equipped to enhance your spreadsheet management and analytics practices.

More information: https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8

Other functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

AREAS

Returns the number of areas in a reference

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

COLUMNS

Returns the number of columns in a reference

FILTER

Filters a range of data based on criteria you define

GETPIVOTDATA

Returns data stored in a PivotTable report

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

INDIRECT

Returns a reference indicated by a text value

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation

SORT

Sorts the contents of a range or array

SORTBY

Sorts the contents of a range or array based on the values in a corresponding range or array

TRANSPOSE

Returns the transpose of an array

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match

XMATCH

Returns the relative position of an item in an array or range of cells