What is the FORMULATEXT function?

The FORMULATEXT function returns a formula as a text string, it was introduced in Excel 2013. The FORMULATEXT function is great for troubleshooting formulas.

Does the FORMULATEXT function show array formulas?

Yes, it does.

Will the FORMULATEXT function display the text of a formula even if that formula is currently producing an error result?

Yes, it will.

What other tools in Excel let you troubleshoot formulas?

"Evaluate Formula" is an Excel feature located on the "Formulas" tab. It allows you to step through each part of a formula to see how it calculates and check for errors. It breaks down complicated formulas by evaluating each function, operator, cell reference, and value incrementally.

"Show formulas" tool toggles worksheets into a view mode that displays the underlying formulas in cells instead of the resulting values. This allows inspecting the complete formula contents and structure instead of only seeing formula outputs. This tool is also located on the "Formulas" tab.

 

1. FORMULATEXT function Syntax

FORMULATEXT(reference)

2. FORMULATEXT function Arguments

The FORMULATEXT function has only one argument.

reference

Required. The cell you want to display the formula.

3. FORMULATEXT function Example

The FORMULATEXT function in cell C3 displays the formula in cell B3.

Formula in cell C3:

Cells B7 to B11 all return different error values, however, the FORMULATEXT function shows their formulas despite being invalid in some form or other. This is great for debugging formulas that requires your attention.

Back to top

4. FORMULATEXT function not working

The FORMULATEXT function returns an #N/A error if cell is:

• empty
• text value
• number value
• boolean value

The formula in cell C3 returns a #N/A error because cell B4 contains the boolean value TRUE, cells B5 and B6 contain numbers and the corresponding cells on the same row in column C also returns the error.

Back to top

5. Monitor cell ranges for formula errors

The Excel 365 formula in cell E3 scans cell range B3:B11 for errors, if an error is found the cell reference and the formula is shown for easy debugging.

Formula in cell E3:

=LET(x,B3:B11,FILTER(ADDRESS(ROW(x),COLUMN(x),4)&" : "&IFERROR(FORMULATEXT(x),""),ISERROR(x)))

This formula refreshes automatically, in other words, it is dynamic meaning it shows errors as they show up. It is also an Excel 365 formula and it spills values to cells below automatically if needed.

Explaining formula

Step 1 - Find errors in given cell range

The ISERROR function returns TRUE if a cell contains an error.

Function syntax: ISERROR(value)

ISERROR(B3:B11)

becomes

ISERROR({0;TRUE;2;1;#DIV/0!;#N/A;#VALUE!;#NAME?;#REF!})

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Extract formula text from cell range B3:B11

The FORMULATEXT function returns a formula as a text string.

Function syntax: FORMULATEXT(reference)

FORMULATEXT(B3:B11)

returns

{"=IF(A1=""1"",1,0)"; #N/A; #N/A; #N/A; "=1/0"; "=MATCH(0,1,0)"; "=INDEX(A1,-5)"; "=@dbdsb()"; "=INDEX(B2:C7,5,5)"}

Step 3 - Replace errors with nothing

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR(FORMULATEXT(B3:B11),"")

becomes

IFERROR({"=IF(A1=""1"",1,0)"; #N/A; #N/A; #N/A; "=1/0"; "=MATCH(0,1,0)"; "=INDEX(A1,-5)"; "=@dbdsb()"; "=INDEX(B2:C7,5,5)"},"")

and returns

{"=IF(A1=""1"",1,0)"; ""; ""; ""; "=1/0"; "=MATCH(0,1,0)"; "=INDEX(A1,-5)"; "=@dbdsb()"; "=INDEX(B2:C7,5,5)"}

Step 4 - Extract column numbers from cell range

The COLUMN function returns the column number of the top-left cell of a cell reference.

Function syntax: COLUMN(reference)

COLUMN(B3:B11)

returns 2.

Step 5 - Extract row numbers from cell range

The ROW function calculates the row number of a cell reference.

Function syntax: ROW(reference)

ROW(B3:B11)

returns

{3; 4; 5; 6; 7; 8; 9; 10; 11}.

Step 6 - Create cell references for each cell in given cell range

The ADDRESS function returns the address of a specific cell, you need to provide a row and column number.

Function syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

ADDRESS(ROW(B3:B11),COLUMN(B3:B11),4)

becomes

ADDRESS({3;4;5;6;7;8;9;10;11},2,4)

and returns

{"B3"; "B4"; "B5"; "B6"; "B7"; "B8"; "B9"; "B10"; "B11"}.

Step 7 - Join cell ref and formula

The ampersand character lets you concatenate strings in an Excel formula.

ADDRESS(ROW(B3:B11),COLUMN(B3:B11),4)&" : "&IFERROR(FORMULATEXT(B3:B11),"")

becomes

{"B3"; "B4"; "B5"; "B6"; "B7"; "B8"; "B9"; "B10"; "B11"}&" : "&{"=IF(A1=""1"",1,0)"; ""; ""; ""; "=1/0"; "=MATCH(0,1,0)"; "=INDEX(A1,-5)"; "=@dbdsb()"; "=INDEX(B2:C7,5,5)"}

and returns

{"B3 : =IF(A1=""1"",1,0)";"B4 : ";"B5 : ";"B6 : ";"B7 : =1/0";"B8 : =MATCH(0,1,0)";"B9 : =INDEX(A1,-5)";"B10 : =@dbdsb()";"B11 : =INDEX(B2:C7,5,5)"}

Step 8 - Filter concatenated values based on errors

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(ADDRESS(ROW(B3:B11),COLUMN(B3:B11),4)&" : "&IFERROR(FORMULATEXT(B3:B11),""),ISERROR(B3:B11))

becomes

FILTER({"B3 : =IF(A1=""1"",1,0)";"B4 : ";"B5 : ";"B6 : ";"B7 : =1/0";"B8 : =MATCH(0,1,0)";"B9 : =INDEX(A1,-5)";"B10 : =@dbdsb()";"B11 : =INDEX(B2:C7,5,5)"}, {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

{"B7 : =1/0"; "B8 : =MATCH(0,1,0)"; "B9 : =INDEX(A1,-5)"; "B10 : =@dbdsb()"; "B11 : =INDEX(B2:C7,5,5)"}

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

FILTER(ADDRESS(ROW(B3:B11),COLUMN(B3:B11),4)&" : "&IFERROR(FORMULATEXT(B3:B11),""),ISERROR(B3:B11))

x - B3:B11

LET(x,B3:B11,FILTER(ADDRESS(ROW(x),COLUMN(x),4)&" : "&IFERROR(FORMULATEXT(x),""),ISERROR(x)))

Back to top

 

Useful links

FORMULATEXT function - Microsoft

Functions in 'Lookup and reference' category

The FORMULATEXT function function is one of many functions in the 'Lookup and reference' category.

Excel function categories

Excel categories

Leave a Comment