Excel Tip of the Week #396 - Revisiting showing formulas as text

The task at hand

We have a formula which we want to document or otherwise write about:

We can easily see the formula in the formula bar by highlighting the cell in question, but what if we want to see a formula in a cell as text, without it being treated as a formula and actually calculating

There are a few ways to do this.

Copying the formula and marking as text

We can do this by copying the cell text and pasting it in another cell – we can mark it as text either by formatting the cell with the Text format, removing the = from the beginning of the function, or by inserting the text qualifier ‘ before the function:

The first example here is in a Text format cell – this causes problems down the line as the Text-iness will remain, causing problems if other data is entered in that cell later on. The other two both require editing the pasted text in some way, and all three are static – not updating if the original formula is changed later on. So instead we should consider the FORMULATEXT function:

This is a simple function which just returns the formula from another cell, as text. This is a great, simple way to display the formula, and it’s also dynamic – so it will update if the source cell’s formula is changed later on.

Finally, you can quickly check in on formulas in the calculation directly by switching to Show Formulas mode:

This temporarily shows the formulas in cells rather than their results, and also automatically highlights the input cells to a formula when it’s selected. You can switch this on from Formulas > Show Formulas, or by pressing Ctrl `.

• Previous post - Revisiting hidden rows and columns
• TOTW index
• Next time – Power Query – Looking up charge-out rates!