Arrow left
Back to product guides
Excel Formulas

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.

How to use the FORMULATEXT function in Excel

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.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711