Excel: A Better FORMULATEXT

The Excel function FORMULATEXT shows you the formula of a cell. This is useful because you can see the formula without having to click into the cell. However, it shows you a bunch of cell references. You may wonder: what is in those cells?! If the cell references are arguments to a function, you may wonder: which parameters is each value for?! Your eyes dart around, and maybe you click into a few cells until you finally have an idea of what’s going on. If you’re in a presentation or meeting, someone will invariably ask “Can you please click into cell XX:22 please?” Why? Because it isn’t obvious what the cell is really doing.

But what if there was a better way?

This article shares how to create a function similar to FORMULATEXT but it will replace cell references with their values and optionally use labels for each cell reference.

Below is a screenshot…

Custom Lambda Function: FORMULAINLINEREF

The magic Lambda function is below…

=LAMBDA(cell,[labels],[brackets],[dp],
LET(
use_labels, IF(ISOMITTED(labels), FALSE, labels),
use_brackets, IF(ISOMITTED(brackets), FALSE, brackets),
dp_value, IF(ISOMITTED(dp), 2, dp),
formula_as_text, FORMULATEXT(cell),
characters_to_put_space_around, {"=","{","}","(",")","+","-","/","*",",","^"},
add_spaces, REDUCE(formula_as_text,characters_to_put_space_around,LAMBDA(acc,value,SUBSTITUTE(acc,value," "&value&" "))),
split_by_space, TEXTSPLIT(add_spaces, " "),
replace_references, MAP(split_by_space, LAMBDA(item,
    LET(
        cell_value, INDIRECT(item),
        rounded_cell_value, IF(ISNUMBER(cell_value), ROUND(cell_value, dp_value),cell_value),
        label, OFFSET(INDIRECT(item), 0,-1),
        replace_spaces_in_lablels, SUBSTITUTE(label, " ", "_"),
        add_label, IF(use_labels, replace_spaces_in_lablels&"="&rounded_cell_value,rounded_cell_value),
        add_brackets, IF(use_brackets, "["&add_label&"]", add_label),
        IFERROR(add_brackets, item)
    ))
),
join_by_space, TEXTJOIN(" ", TRUE,replace_references),
remove_spaces, SUBSTITUTE(join_by_space," ",""),
put_space_after_comma, SUBSTITUTE(remove_spaces,",",", "),
put_spaces_back_in_labels, SUBSTITUTE(put_space_after_comma,"_"," "),
put_spaces_back_in_labels))

How does it work?

I won’t go into too much detail with the code, but rather describe it at a high level.

Spaces are added before and after formula syntax, e.g. parentheses and math operators. The idea is to indirectly put spaces around cell references so that they can be accessed by themselves. The entire formula is split into a list of text-chunks; split by a space character. Should a chunk be a cell reference, then it’s contents are retrieved. If the option for using labels is ‘true’, then the cell to the left is accessed and treated as the label. If the option for using brackets is ‘true’, then the result is surrounded with a pair of ‘[‘ and ‘]’. Finally the list is converted back into a single sentence by joining on a space character.

When to Use This

Use this if you are teaching or presenting and you want to share how a formula works.

Sign off

Hope you found this useful and allows you to skip over the cell rabbit hole :-) Please let me know if you end up using it or if can see a way to improve it.

—

Post Publish Update…

Updated the formula to accept the number of decimal places, it defaults to 2 dp.