FORMULATEXT Function in Excel: How to Use It

If you’re looking for a quick and easy way to extract the formula from a cell in Excel, you should be using the FORMULATEXT function. This function returns the formula as a text string, making it super easy to copy and paste into another cell. FORMULATEXT is very basic, but it can come in handy when you need to see the exact formula that’s being used in a cell. Let’s take a look at how to use FORMULATEXT in Excel!

What Does The FORMULATEXT Function Do?

This is a simple, straightforward formula. The FORMULATEXT function returns a formula as a text string from a given reference. In other words, it extracts the formula as text from a cell.

This can come in handy if you need to see the exact formula that’s being used in a cell, or if you want to copy and paste the formula into another cell. You will rarely use this as part of another formula or process.

Where To Find It?

Option 1:

The FORMULATEXT function can be found in the Formulas tab of Excel under Lookup & Reference

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

The Formula

=FORMULATEXT (reference)

Reference (required) – A cell or cell range holding a formula that you want to view

Let’s Take a Look at a Real-World Example

Excel Workbook

Workbook_FORMULATEXT_Function Download

Example

This is a super-duper easy formula to work with. Only one argument and no nesting to work with.

In our first example, we want to use FORMULATEXT to pull the formula from a single cell. We have set up a table of RANDBETWEEN formulas in E7:E13 to reference. Let’s return the formula in cell E7. We enter FORMULATEXT(E7) which returns =RANDBETWEEN(500,5000).

In our second example, let’s pull an entire range. In Office 365 (used for the example), the formula will spill all of the formulas in the range. For other versions of Excel, the formula will return the top left formula in the range. Let’s return the formulas in the range E7:E8. We enter FORMULATEXT(E7:E8) which spills to two cells and returns =RANDBETWEEN(500,5000) as well as =RANDBETWEEN(600,6000).

Example of using the FORMULATEXT Function in Excel

Your turn: Use the workbook and FILTER to follow the examples in blue and finish the practice formulas highlighted in green

Tips and Tricks

Circular References Aren’t a Problem

Unlike almost every other formula in Excel, FORMULATEXT will not return a circular reference error. If you reference the cell containing the function, it will return the text of the formula.

Why Did I Get #N/A

If you get #N/A you either referenced a cell that doesn’t contain a formula, or you referenced a closed workbook.

Can’t Find FORMULATEXT In Excel

Not all versions of Microsoft Excel have this function. Microsoft only introduced FORMULATEXT in Office 2013. Earlier versions do not include this function. If you get a #NAME error, your version of Office doesn’t have this function.

Also, note that only Office 365 allows you to pull the formulas for an entire range. In other versions, a range will return the top left formula.

Let’s Recap

FORMULATEXT is a very simple function in Excel that returns the text of a formula from a given reference. This can be useful if you need to see the exact formula that’s being used in a cell, or if you want to copy and paste the formula into another cell.

Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.