How to Use the FORMULATEXT Function in Excel

This guide will explain how to start using the FORMULATEXT function in Excel.

Displaying the formula used in a specific cell can be useful in some instances. When calculating a new column in a report, it is beneficial to let other users know what formula was used to arrive at that result.

Excel’s FORMULATEXT function allows you to easily display the formula used in any cell in your spreadsheet. 

In this guide, we will provide a step-by-step tutorial on how to use the FORMULATEXT function to determine the functions or formulas used in a specific cell.

The Anatomy of the FORMULATEXT Function

The syntax of the FORMULATEXT function is as follows:

=FORMULATEXT(cell)

Let’s look at each argument to understand how to use the FORMULATEXT function.

• = the equal sign is how we start any function in Microsoft Excel.
• FORMULATEXT() refers to our FORMULATEXT function. This function accepts a cell reference and outputs the formula found in that cell.
• cell refers to the cell reference input you want to find the formula of.
• If the cell does not contain a formula, the function will return a #N/A error.
• The FORMULATEXT function also returns an error if the referenced cell is protected. References to cells on another unopened worksheet will also result in an error.

A Real Example of Using the FORMULATEXT Function in Excel

Because the FORMULATEXT function allows you to return formulas from target cells, the function can be useful when auditing a workbook or troubleshooting spreadsheets with problematic formulas.

The FORMULATEXT function may also be necessary when creating spreadsheets meant for other users. Revealing the formulas for various calculated cells and ranges will enable users new to the spreadsheet to understand how everything is calculated.

Using FORMULATEXT to Reveal a Formula

Let’s explore a simple example where we can use the FORMULATEXT function in Excel.

In the sheet above, we have a cell that calculates the expiry date of a particular promotional voucher or coupon. We want users of the sheet to know that the expiry date is calculated and not a fixed value.

One way to do this is by revealing the formula used to calculate the expiry date. We can use the following formula:

=FORMULATEXT(B1)

Using the FORMULATEXT function, we’ve determined that the expiry date was calculated using the TODAY and EOMONTH functions.

Using FORMULATEXT and ISFORMULA

The ISFORMULA function allows you to check if a given cell contains a formula. The function returns TRUE when the input cell reference contains a formula. This feature, when combined with the IF function, can help set up more advanced troubleshooting checks.

For example, we can use the following formula to return the formula used in a given cell or indicate whether that cell contains a formula:

=IF(ISFORMULA(A2), FORMULATEXT(A2), "No formula found")

Using this custom formula, we’ve determined that two of our cells in column A (A4 and A6) are the result of a formula.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the FORMULATEXT function yourself in Excel.

How to Use the FORMULATEXT Function in Excel

1. Select an empty cell where you want to output the formula of another cell.
   
2. Type the text “=FORMULATEXT(“ to start the FORMULATEXT function.
   
3. Type out the cell reference of the cell you want to return the formula from. You can also use your cursor to select the cell and add it to the formula bar.
   
   In this example, we’ll select cell B1 as the cell to extract the formula.
   
4. Hit the Enter key to evaluate the FORMULATEXT function.
   
5. If the cell to check does not contain a formula, the FORMULATEXT function will return a #N/A error.
   

FAQs

1. What will happen if the FORMULATEXT function is given a cell without a formula in it?
   If the user gives FORMULATEXT a cell with no formula, the function will return a #N/A error.
   
2. How can I determine if a given cell contains a formula?
   Instead of using FORMULATEXT, we recommend using the ISFORMULA function. The ISFORMULA function returns either TRUE or FALSE depending on whether the input cell reference contains a formula.
   
3. Is it possible to show all formulas used in a given spreadsheet?
   Since FORMULATEXT only returns the formulas found in the provided input, it can be difficult to use this function to check every cell with a formula in your spreadsheet. An easy workaround to check all formulas in a spreadsheet is through the keyboard shortcut Ctrl + Grave Accent key (`). This shortcut allows you to toggle between showing all formulas and showing all results. 

To learn more about troubleshooting formulas, you can read our post on how to fix formulas that are not updating automatically in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!