Excel FORMULATEXT Function

What is FORMULATEXT function in Excel?

The FORMULATEXT function is one of the (Lookup & reference) functions of Excel

It returns a formula as a string.

We can find this function in Lookup & reference of insert function Tab.

How to use FORMULATEXT function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Lookup & reference category.

5. Select FORMULATEXT function.

6. Then select ok.

7. In function arguments Tab, you will see FORMULATEXT function.

8. Reference is a reference to a formula.

9. You will see the result in the formula result section.

Examples of FORMULATEXT function in excel

Here are 10 examples of the FORMULATEXT function in Excel:

1. Basic usage: =FORMULATEXT(A1) returns the formula in cell A1 as text.
2. Return formula from a named range: =FORMULATEXT("NamedRange") returns the formula in the named range “NamedRange”.
3. Return formula from a dynamic named range: =FORMULATEXT(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))) returns the formula for a dynamic named range that includes all non-blank cells in column A of Sheet1.
4. Use in data validation error message: ="The value must satisfy the following formula: " & FORMULATEXT(B1) creates a data validation error message that displays the formula in cell B1.
5. Display formula in a chart title: ="Chart Title (" & FORMULATEXT(A1) & ")" creates a chart title that includes the formula in cell A1.
6. Display formula in a cell comment: =IFERROR(COMMENT([@[Sales]]), "") & CHAR(10) & "Formula: " & FORMULATEXT([@[Sales]]) adds a comment to a cell that includes the formula used to calculate the value.
7. Include formula in a report header: ="Report Generated on " & TODAY() & " using the following formula: " & FORMULATEXT(A1) adds a report header that displays the formula in cell A1.
8. Use in a conditional formatting rule description: ="This cell is formatted based on the following formula: " & FORMULATEXT(B1) creates a conditional formatting rule description that displays the formula in cell B1.
9. Display formula in a pivot table value field caption: ="Sum of Sales (" & FORMULATEXT(B1) & ")" creates a pivot table value field caption that includes the formula in cell B1.
10. Include formula in an email body: ="Please see the attached spreadsheet for details. The data was calculated using the following formula: " & FORMULATEXT(A1) adds a message to an email body that explains the formula used to calculate the data included in the attachment.

What is the purpose of FORMULATEXT function?

The purpose of the FORMULATEXT function in Excel is to return a text representation of a cell’s formula.

It can be used to display the formula that is being used in a cell, which can be helpful for auditing and troubleshooting purposes.

The syntax for the FORMULATEXT function is as follows:

=FORMULATEXT(reference)

Here, “reference” refers to the cell or range of cells that contains the formula you want to display as text.

The function returns the formula as a text string, including any operators, references, and constants used in the formula.

What is the Return value  of FORMULATEXT function?

The Return value of the FORMULATEXT function in Excel is a text string that represents the formula contained within a specified cell or range of cells.

The returned text string includes any operators, cell references, and constants used in the formula.

It’s important to note that if the cell being referenced by the FORMULATEXT function does not contain a formula, but instead contains a constant value, an error message “#N/A” will be displayed in the cell where the formula was entered.

Therefore, the FORMULATEXT function should only be used on cells that actually contain formulas.

How many arguments does FORMULATEXT function have?

The FORMULATEXT function in Excel takes only one argument, which is the reference to the cell or range of cells that contains the formula you want to display as text.

The syntax for the FORMULATEXT function is as follows:

=FORMULATEXT(reference)

Here, “reference” refers to the reference of a cell or range of cells that contain the formula you want to display as text.

Only one argument is allowed with the FORMULATEXT function, and it must be a valid cell reference.

The argument of ABS function is required and not optional.

Which version of excel supports FORMULATEXT function?

The FORMULATEXT function was first introduced in Excel 2013, so it is available in all versions of Excel that were released after that, including Excel 2016, Excel 2019, and Microsoft 365.

If you are using an earlier version of Excel, such as Excel 2010 or earlier, the FORMULATEXT function is not available and you won’t be able to use it.

You can check if the FORMULATEXT function is available in your version of Excel by typing “=FORMULATEXT(” into a cell and observing whether or not it is listed in the function autocomplete menu.

Errors in FORMULATEXT function

There are a few different types of errors that can occur when using the FORMULATEXT function in Excel:

1. #N/A error: This error occurs if the cell being referenced by the FORMULATEXT function does not contain a formula. The FORMULATEXT function only works on cells that contain formulas, so if you try to use it on a cell that contains a constant value or is blank, you will get an #N/A error.
2. #REF! error: This error occurs if the reference provided to the FORMULATEXT function is invalid, such as if the cell reference is outside the range of the worksheet or if it refers to a deleted cell or worksheet.
3. #VALUE! error: This error occurs if the cell being referenced by the FORMULATEXT function contains a formula that returns an error value, such as a divide-by-zero error or a #NAME? error.

To avoid these errors, make sure you are referencing valid cells that contain formulas and that your cell references are accurate.

Also, be aware that the FORMULATEXT function is not supported in earlier versions of Excel, so if you are using an older version of Excel, you may not be able to use this function.

Avoid #N/A errors using the FORMULATEXT function?

To avoid #N/A errors when using the FORMULATEXT function, you should make sure that the referenced cell actually contains a formula.

If the referenced cell does not contain a formula, the function will return an error.

To handle errors gracefully, you can wrap the FORMULATEXT function in an IFERROR function.

The IFERROR function allows you to specify a value or action to take if the result of a formula is an error. Here’s an example:

=IFERROR(FORMULATEXT(A1),"")

This formula will display the formula in cell A1 as text if it exists, and will display an empty string if the cell is blank or contains anything other than a formula.

use the FORMULATEXT function on multiple cells at once

No, the FORMULATEXT function cannot be used on multiple cells at once.

You must specify the cell or range you want to display the formula for in each individual formula.

Concatenate FORMULATEXT function with other values?

To concatenate the results of the FORMULATEXT function with other text or values, you can use the ampersand (&) operator. Here’s an example:

Suppose you have a formula in cell A1, and you want to display the formula as text in cell B1, along with some additional text. You can use the following formula in cell B1:

="The formula in cell A1 is: " & FORMULATEXT(A1)

This will display the text “The formula in cell A1 is: ” followed by the formula in cell A1.

You can also include other text or values in the concatenation. For example:

="The result of the formula in cell A1 is: " & A1 & ". The formula used was: " & FORMULATEXT(A1)

This will display the value of cell A1 (the result of the formula), along with some additional text and the formula itself.

Combination FORMULATEXT with IF or SUM or SUMIF

Yes, the FORMULATEXT function can be used in combination with other functions such as IF or SUM.

For example, you could use the FORMULATEXT function within an IF statement to check if a cell contains a specific formula. Here’s an example:

=IF(FORMULATEXT(A1)="=SUM(B1:B10)","Cell A1 contains a sum formula","Cell A1 does not contain a sum formula")

In this example, the formula checks if cell A1 contains the formula “=SUM(B1:B10)”. If it does, the formula returns the text “Cell A1 contains a sum formula”. If it doesn’t, the formula returns the text “Cell A1 does not contain a sum formula”.

Similarly, you could use the FORMULATEXT function within a SUM statement to sum values from cells that contain a specific formula. Here’s an example:

=SUMIF(B1:B10,FORMULATEXT(A1),C1:C10)

In this example, the formula sums the values in cells C1:C10 only if the corresponding cell in column B contains the same formula as cell A1.

Note that when using the FORMULATEXT function within another function, you will need to enclose the FORMULATEXT function in parentheses. For example:

=IF(FORMULATEXT(A1)="=SUM(B1:B10)","Cell A1 contains a sum formula","Cell A1 does not contain a sum formula")

FORMULATEXT function with other values?

Yes, the FORMULATEXT function can be used in combination with other functions such as IF or SUM.

For example, you could use the FORMULATEXT function within an IF statement to check if a cell contains a specific formula. Here’s an example:

=IF(FORMULATEXT(A1)="=SUM(B1:B10)","Cell A1 contains a sum formula","Cell A1 does not contain a sum formula")

In this example, the formula checks if cell A1 contains the formula “=SUM(B1:B10)”. If it does, the formula returns the text “Cell A1 contains a sum formula”. If it doesn’t, the formula returns the text “Cell A1 does not contain a sum formula”.

Similarly, you could use the FORMULATEXT function within a SUM statement to sum values from cells that contain a specific formula. Here’s an example:

=SUMIF(B1:B10,FORMULATEXT(A1),C1:C10)

In this example, the formula sums the values in cells C1:C10 only if the corresponding cell in column B contains the same formula as cell A1.

Note that when using the FORMULATEXT function within another function, you will need to enclose the FORMULATEXT function in parentheses. For example:

=IF(FORMULATEXT(A1)="=SUM(B1:B10)","Cell A1 contains a sum formula","Cell A1 does not contain a sum formula")

Limitations of FORMULATEXT

There are some limitations and restrictions on how the FORMULATEXT function can be used:

1. The FORMULATEXT function cannot be used to display formulas that are stored as text. If a formula is entered as text (i.e. preceded by an apostrophe), the FORMULATEXT function will not be able to display it.
2. The FORMULATEXT function can only be used to display regular formulas that are entered directly into a cell. It cannot be used to display array formulas or conditional formatting rules.
3. The FORMULATEXT function is only available in newer versions of Excel. It was introduced in Excel 2013, so it may not be available in older versions of the software.
4. The FORMULATEXT function must reference a cell that contains a formula. If the referenced cell does not contain a formula, the function will return an error.
5. The FORMULATEXT function cannot be used on multiple cells at once. You must specify the cell or range you want to display the formula for in each individual formula.
6. The FORMULATEXT function cannot be used to display the values of cells that are the result of formulas. It can only display the actual formulas themselves.

Display a cell’s formula as text without using the FORMULATEXT function?

There is another way to display a cell’s formula as text without using the FORMULATEXT function.

Simply select the cell containing the formula, and then click on the Formula Bar at the top of the worksheet. The formula will be displayed as text in the Formula Bar.

You can also select multiple cells containing formulas and use the same method to display their formulas in the Formula Bar.

FORMULATEXT function as array formulas or conditional formatting rules

The FORMULATEXT function cannot be used to display array formulas or conditional formatting rules.

An array formula is a special type of formula that performs calculations on multiple values at once.

These formulas are entered using special syntax (enclosing the formula in curly braces) and are not supported by the FORMULATEXT function.

Conditional formatting rules are used to format cells based on specific criteria, such as cell value or cell color.

The rules are not stored as regular formulas, so they cannot be displayed using the FORMULATEXT function.

If you want to display an array formula or a conditional formatting rule as text, you will need to use a different method, such as selecting the cell and looking at the Formula Bar, or using VBA code to extract the formula/rule programmatically.

YEAR related functions

• Use MONTH function to return the month, a number from 1 (January) to 12 (December).
• Use DAY function to return the day of the month, a number from 1 to 31.
• Use DATE function to return the number that represents the date in Microsoft Excel date-time code.
• YEARFRAC

Related posts:

How to fix the #SPILL! error Most important reason why vlookup not working Excel MAXA Function Excel SUM Function