The FORMULATEXT Excel Function – 2 Best Examples
Note: This guide on the FORMULATEXT Excel function is suitable for Excel 2013 and later versions.
What is the FORMULATEXT Function?
The FORMULATEXT function converts and returns a given formula as a text string. Use it to display a formula as text in your spreadsheet. It returns a #N/A error if the reference cell doesn’t contain a formula.
In Excel versions of Office 365, you can use multiple references inside the FORMULATEXT function. In such cases, the returned values of the FORMULATEXT will spill into the Excel spreadsheet just like a dynamic array function.
The FORMULATEXT Excel function can handle formulas with up to 8192 characters.
In this guide I’ll cover:
- FORMULATEXT Excel Syntax
- How to use the FORMULATEXT Function in Excel?
- Things to Keep in Mind about the FORMULATEXT Function
Related:
How to Extract an Excel Substring? – 6 Best Methods
How to Superscript in Excel? (9 Best Methods)
How To Find Duplicates in Excel? (3 Easy Methods)
FORMULATEXT Excel Syntax
=FORMULATEXT(reference)Where reference is the cell reference of the formula you need to extract into text. Note that you can use multiple references inside the functions in the Office 365 version.
How to use the FORMULATEXT Function in Excel?
Before I tell you how to use the FORMULATEXT function, let me explain its purpose.
It is mainly used to document the formulas in a spreadsheet and check them for errors.
To use the FORMULATEXT function just enter the formula correctly inside an adjacent or suitable cell along with the cell reference of the formula you need to check. The FORMULATEXT function will extract and convert the formula into a text string.
Now, drag the formula to the entire data range if needed.
To better understand the uses of the FORMULATEXT function let’s consider these examples.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
FORMULATEXT Excel – Example 1
One of the common use cases of the FORMULATEXT function is to check formulas in a financial report for errors.
In this example, the FORMULATEXT function displays all the financial calculations alongside the actual values. This helps the user to cross-check them and find any possible errors.
FORMULATEXT Excel – Example 2
A unique property of the FORMULATEXT function is that it will not return any circular reference errors. That is even if we enter an argument which in itself contains a FORMULATEXT function as a reference, it will not return any errors.
In this example, the FORMULATEXT function returns the formula used in the original cell properly, without returning a circular reference error.
Things to Keep in Mind about the FORMULATEXT Function
The FORMULATEXT function returns a #N/A error for the following cases:
- The cell reference doesn’t contain a formula or is blank
- The reference leads to another Workbook that is not open.
- When the referred formula exceeds 8192 characters.
- If the worksheet is protected
The FORMULATEXT function returns a #Value error if an invalid data type other than a formula is used inside it.
To avoid such errors, it is recommended to use it with ISFORMULA which checks if a cell contains a formula.
Also, in non Office 365 versions, the function will return an error if multiple references are used.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
FAQs
How to display formulas in Excel?
To display formulas in Excel in a neighbouring cell, use the FORMULATEXT Excel function in the adjacent cell. It will convert the formula to a string and display it as text.
Why is Excel showing formula instead of result?
This might occur if you have accidentally activated the Show Formulas mode in Excel. To correct this you can use the shortcut Ctrl+”`” in the cell or deactivate the Show Formulas mode directly.
Another possible reason is you might have formatted the cell containing the formula as Text instead of General. Also, check for a single quote ‘ at the beginning of the formula. This will also cause the formula to be displayed directly.
Let’s Wrap Up
That’s all about the FORMULATEXT Excel function. If you have any questions about this or any other Excel feature, let us know in the comments. We are always happy to help.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.
