If you’re presenting an Excel formula for auditing, troubleshooting, or educational purposes, the FORMULATEXT function is very handy.

It lets you display the formula from a cell (or range) as text, while updating automatically whenever there’s a change.

Needless to say, it’s far better than the manual exertion of copying and pasting the formula each time!

Have a watch of this video:

In the example, =FORMULATEXT(A2) displays the formula in A2, which outputs a spilled range of 10 random numbers (but that’s irrelevant here).

However, there are a couple of limitations:

Alignment issues

By default, text starts on the leftmost side of a cell. If you hit the Centre button, though, everything becomes centred, which isn’t necessarily what you want.

That’s because you’re used to seeing left-aligned code blocks (Excel formulas included). However, if you have multiple formulas in the same column of different shapes and sizes, positioning them in the centre makes the most sense visually.

To half combat this problem, you can keep the formula text left-aligned but use the Increase Indent button to push it as a block into the centre.

With a careful eye, you may well do a decent job of nudging it. But as soon as you resize the column, it fails to adapt, staying firmly in its absolute position.

Selective colour

The formula editor uses colour to help us understand formulas better.

Notice how TAKE has a red cell reference at the end, SORTBY has red opening and closing brackets, whereas the SEQUENCE and RANDARRAY brackets are purple, with a blue cell reference in each.

But what does FORMULATEXT return? Plain old black text.

Sure, you can change the font and apply bold formatting to the whole cell. You can even change the colour, but you can’t alter individual elements of the output as it’s formula-based.

Final words

Formulas and formatting have always operated independently, but maybe if FORMULATEXT had rich text formatting built in, it would open the door to other possibilities, too.

For now, let’s just imagine we could do this…

What do you think?