The FORMULATEXT function was officially introduced with Excel 2013, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007 & 2010 versions. It works exactly as its original Microsoft counterpart.
Description
The FORMULATEXT function returns a formula as a text string from a referenced cell. This is extremely useful for:
- Documentation: Displaying formulas next to their calculated results for better readability and auditing.
- Learning: Seeing how complex formulas are constructed without having to click into each cell.
- Troubleshooting: Quickly checking if a cell contains a formula or a hard-coded value.
- Reporting: Creating self-explanatory reports that show both the logic and the outcome.
Syntax
=FORMULATEXT(reference)
| Argument | Description |
|---|---|
reference | A reference to a single cell containing a formula. |
Examples
Basic Usage
If cell A1 contains the formula =SUM(B1:B10), then:
=FORMULATEXT(A1)
Returns: "=SUM(B1:B10)"
With Other Functions
You can combine FORMULATEXT with other functions to create dynamic documentation:
="The total is calculated as " & FORMULATEXT(A1) & " and equals " & A1
This could return: "The total is calculated as =SUM(B1:B10) and equals 1540"
Error Handling
If the referenced cell does not contain a formula, FORMULATEXT returns the #N/A error.
=IFERROR(FORMULATEXT(A1), "No formula found")
Practical Use Cases
| Scenario | Benefit |
|---|---|
| Auditing spreadsheets | Quickly see which cells contain formulas vs. hard-coded values |
| Training materials | Show formulas alongside their outputs for learners |
| Quality control | Verify that all cells in a range contain expected formulas |
| Documentation | Auto-generate formula lists for complex models |
Notes
- ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2013.
- ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
- ℹ️ If the reference is to a cell that does not contain a formula,
FORMULATEXTreturns#N/A. - ℹ️ The function works with both relative and absolute references, returning the formula as it appears in the formula bar.