FORMULATEXT Function in Excel: Explained

In this article, you will learn how to use the FORMULATEXT function in Excel.

‍

What is the FORMULATEXT formula in Excel?

The FORMULATEXT formula in Excel is a function that shows the formula of a cell as a text string. The FORMULATEXT function returns what is shown in the formula bar if you put a cursor on the referenced cell.

‍

When is the FORMULATEXT function beneficial in Excel?

The FORMULATEXT function in Excel can be beneficial in various situations where you need to view or extract the formulas used in a worksheet or workbook. Some examples include:

1. Auditing and troubleshooting formulas: If you are trying to identify errors or inconsistencies in a complex worksheet, the FORMULATEXT function can help you quickly view the functions used in various cells to identify any issues.
2. Documenting and sharing formulas: If you need to share a worksheet or workbook with others, the FORMULATEXT function can be used to write the formulas in the sheet, so others can understand the calculations being performed.
3. Extracting formulas for reuse: If you have a complex worksheet with many functions that you want to reuse in other worksheets or workbooks, you can use the FORMULATEXT function to extract the formulas and copy and paste them into other cells.

‍

How to use the FORMULATEXT formula in Excel

The syntax for the formula is as follows:

=FORMULATEXT(reference)

“reference” is the cell or range of cells for which you want to return the formula. You can refer to a cell on another worksheet or workbook.

‍

Note 1: When you reference a cell in another workbook, the workbook must be open. Otherwise, the formula returns the #N/A error value.

Note 2: If the input argument is a range or array, the FORMULATEXT gives you the formula in the top left cell of the selected range or array.

Note 3: The formula returns the #N/A error when (i) a cell referred to contains no formula, (ii) the formula in the selected cell includes greater than 8192 characters, (iii) a referenced cell or range is protected, (iv) a referred cell is on another worksheet which is not open

‍

For example, if cell A1 contains the formula =SUM(B1:B5), using the formula =FORMULATEXT(A1) would return the text string "=SUM(B1:B5)". In the picture, the FORMULATEXT is inserted in cell E2.

‍

Analyze your live financial data in a snap in Google Sheets

Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly. 

To learn more about LiveFlow, book a demo.