You may need to show a formula in a cell without Excel executing it. This is common for creating documentation, building templates, or teaching spreadsheet logic. Excel automatically calculates any formula you type, starting with an equals sign. This article explains how to prevent that calculation and display the formula as static text. You will learn several methods to stop Excel from evaluating your formulas.
Key Takeaways: Displaying Formulas as Text
- Leading apostrophe (‘): Forces Excel to treat the cell content as text, showing the formula exactly as typed.
- Format cells as Text: Changes the cell’s data type so anything entered, including formulas, is not calculated.
- Show Formulas mode (Ctrl + `): Toggles the worksheet view to display all formulas in their cells instead of results.
Understanding Excel’s Formula Evaluation
Excel is designed to calculate. When you type an equals sign (=) followed by cell references and functions, Excel immediately processes it and shows the result. To display the formula itself, you must interrupt this default behavior. The core methods involve either changing the data type of the cell to Text before entry, or using a special character to escape the formula. A third method changes the entire worksheet view, which is useful for auditing but does not store the formula as text in a single cell.
Cell Formatting vs. Content Prefix
Two primary approaches exist: formatting the cell and prefixing the content. Setting a cell’s format to Text tells Excel to treat every entry as a string of characters. This must be done before you type the formula. Alternatively, adding an apostrophe at the very beginning of your entry is a direct override. Excel stores the apostrophe in the cell but does not display it in the worksheet, showing only the formula text.
Methods to Show a Formula as Text
Choose the method based on whether you need to prepare a cell in advance or convert an existing entry. The apostrophe method is the quickest for one-off entries. Formatting as Text is better for ranges where you will enter multiple formulas as text. The Show Formulas command is ideal for checking all formulas in a sheet at once.
Using a Leading Apostrophe
- Select the target cell
Click on the cell where you want the formula text to appear. - Type an apostrophe followed by the formula
Enter a single quote mark (‘), then type your formula including the equals sign. For example, type: ‘=SUM(A1:A10) - Press Enter
Excel will place the text ‘=SUM(A1:A10) in the cell but display only =SUM(A1:A10). A small green triangle in the cell’s corner indicates it is stored as text.
Formatting Cells as Text Before Entry
- Select the cell or range
Highlight the cells where you plan to enter formula text. - Open the Format Cells dialog
Right-click the selection and choose Format Cells, or press Ctrl + 1. - Set the cell format to Text
In the Format Cells dialog, click the Number tab. Select Text from the Category list and click OK. - Type your formula
In the formatted cell, type your formula, such as =A1+B1. Excel will display it as text without calculating.
Toggling the Show Formulas View
- Activate Show Formulas mode
Go to the Formulas tab on the ribbon. In the Formula Auditing group, click Show Formulas. Alternatively, use the keyboard shortcut Ctrl + ` (the grave accent key, usually located above the Tab key). - View all formulas
Every cell containing a formula will now display the formula itself instead of its result. Column widths will automatically expand. - Return to normal view
Click the Show Formulas button again or press Ctrl + ` to switch back to seeing calculated results.
Common Mistakes and Limitations
These methods are simple but have specific constraints. Understanding them prevents frustration when your formula text does not behave as expected.
Formatting a Cell as Text After Entering a Formula
If you type a regular formula like =2+2 and press Enter, Excel calculates and stores the result (4). Changing the cell’s format to Text afterward will not convert the value 4 back into the text “=2+2”. The format change only affects new entries. To fix this, you must re-enter the formula after the cell is formatted as Text, or use the apostrophe method.
The Apostrophe Appears in the Formula Bar
When you use the leading apostrophe method, the apostrophe is visible in the formula bar at the top of the Excel window. This confirms the content is stored as text. However, it is not printed or included if you copy the cell’s visible content and paste it as values elsewhere. This is normal behavior and not an error.
Show Formulas Mode Affects the Entire Sheet
The Ctrl + ` shortcut is a global worksheet toggle. You cannot use it to display just one formula as text while others show results. It is a viewing tool, not a cell-level formatting option. For displaying a single formula, use the apostrophe or Text format methods.
Formula as Text vs. Formula Result: Key Differences
| Item | Formula Displayed as Text | Active Formula with Result |
|---|---|---|
| Cell Content | Text string beginning with = | Executable formula |
| Appearance in Cell | Shows the formula syntax | Shows the calculated value |
| Editing | Edit like any text string | Edit in formula bar; cell updates |
| Use in Other Formulas | Treated as a text value, not a number | Its result can be referenced by other formulas |
| Best For | Documentation, templates, instructions | Live calculations and data analysis |
You can now prevent Excel from calculating a formula. Use an apostrophe for a quick entry or format cells as Text for prepared ranges. Remember that the Show Formulas command is a temporary view for the whole sheet. For advanced use, try the FORMULATEXT function. It can display the formula from another cell as a text string, which updates automatically if the source formula changes.