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:
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:
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 `.
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.