ICAEW.com works better with JavaScript enabled.

Tip of the Week

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

Author: David Lyford-Smith

Published: 01 Jun 2021

Hello all and welcome back to the Excel Tip of the Week! This week, we have a General User level post in which we’re going to take a new look at how to show formulas and other such values as text, useful any time you want to demonstrate or inspect a formula without actually having it calculate. This was first covered in a more limited way in TOTW #165.

The task at hand

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

Excel screenshot of sample formula

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:

Excel screenshot of text-formatted cell
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:
Excel screenshot of forumla text

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:

Excel screenshot of 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 `.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid