How to How to Use FORMULATEXT to Display Formulas in Excel in Excel - Practical Guide

Learn how to use the FORMULATEXT function to display the actual formula text in a cell instead of its result. This tutorial covers syntax, practical applications, and troubleshooting to help you audit spreadsheets, document calculations, and create dynamic formula references for analysis and transparency.

Why This Matters

FORMULATEXT is essential for auditing complex spreadsheets, documenting formulas, and creating transparent calculation workflows. It helps teams understand formula logic without editing cells.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Excel 2013 or later (function availability requirement)

Step-by-Step Instructions

Open Excel and create a sample formula

Launch Excel and enter a formula in any cell (e.g., =SUM(A1:A10) in cell C1). This formula will be the target for FORMULATEXT.

Click on an empty cell to display the formula text

Select a different cell (e.g., D1) where you want the formula text to appear as output.

Type the FORMULATEXT function

Enter =FORMULATEXT(C1) where C1 is the cell containing your original formula. Do not use quotes around the cell reference.

Press Enter to execute

Hit Enter to confirm the formula. Cell D1 will now display =SUM(A1:A10) as text instead of the calculated result.

Format the result cell as needed

Right-click the cell > Format Cells > Number tab, or use Home > Number Format dropdown to adjust display (text alignment, font color) for better readability.

Alternative Methods

Using Auditing Tools

Navigate to Formulas > Show Formulas (Ctrl+`) to display all formulas in the worksheet. This reveals formulas without needing FORMULATEXT, though it shows them in-cell rather than as separate output.

Copy formula as text via Paste Special

Copy a cell with a formula, then use Paste Special (Ctrl+Shift+V) > Paste Link to create a text link to the formula instead of displaying it with FORMULATEXT.

Tips & Tricks

✓Use FORMULATEXT to create formula documentation sheets that track all calculations used in a workbook.
✓Combine FORMULATEXT with conditional formatting to highlight complex formulas that need review.
✓FORMULATEXT only works with cell references; it cannot display formulas entered directly as text strings.

Pro Tips

★Nest FORMULATEXT inside CONCATENATE to create audit trails: =CONCATENATE("Formula in C1: ", FORMULATEXT(C1)).
★Use FORMULATEXT in a helper column alongside your main calculations to automatically generate a formula changelog for transparency and debugging.
★Combine with IFERROR to gracefully handle errors when referencing cells without formulas: =IFERROR(FORMULATEXT(A1), "No formula").

Troubleshooting

FORMULATEXT returns #NAME? error

This occurs in Excel versions before 2013 or when the function is not available in your region. Verify your Excel version supports FORMULATEXT (2013+) and update if necessary.

FORMULATEXT returns #VALUE! error

The referenced cell does not contain a formula, only a value. Ensure you are referencing a cell with an actual formula (starting with =), not plain text or numbers.

FORMULATEXT result appears blank or truncated

Widen the column by double-clicking the column border in the header, or manually drag to expand. Long formulas require more space to display fully.

FORMULATEXT shows formula with different syntax than expected

Excel may display formulas using your locale's function names. Check region settings in File > Options > Language; FORMULATEXT output reflects your system language.

Related Excel Formulas

INDIRECT CONCATENATE IFERROR CELL EVALUATE

Frequently Asked Questions

Can FORMULATEXT work with formulas in other worksheets?

Yes, you can reference formulas across worksheets using the syntax =FORMULATEXT(Sheet2!C1). This displays the formula text from Sheet2, cell C1, directly on your current worksheet.

Does FORMULATEXT update automatically when the source formula changes?

Yes, FORMULATEXT is dynamic and updates in real-time whenever the referenced cell's formula is edited. Any changes to the formula in the source cell will immediately reflect in the FORMULATEXT output.

Can I use FORMULATEXT with array formulas or dynamic arrays?

Yes, FORMULATEXT works with array formulas and dynamic arrays (Excel 365). It will display the complete array formula syntax, including curly braces if applicable, making it useful for auditing complex dynamic calculations.

Is there a way to convert FORMULATEXT output back into a working formula?

No, FORMULATEXT returns text only. To convert it back to a formula, you must manually re-enter it or use VBA to parse the text, then paste it as a formula in the target cell.

Why does FORMULATEXT not work with some cells I select?

FORMULATEXT only works on cells containing actual formulas (starting with =). If a cell contains only text, numbers, or is empty, FORMULATEXT will return a #VALUE! error because there is no formula to display.