Purpose
Return value
Syntax
=FORMULATEXT(reference)
- reference - Reference to cell or cell range.
Using the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string from a cell reference. The FORMULATEXT can be used to extract a formula as text from a cell reference. The text returned by FORMULATEXT is the same as displayed in the formula bar when a cell with a formula is selected. Once text is extracted with FORMULA text, it can be handled as text in another formula.
FORMULATEXT takes just one argument, reference , which is normally a cell reference like A1. If you use FORMULATEXT on a cell that doesn’t contain a formula, it returns #N/A. FORMULATEXT will handle formulas up to 8192 characters.
In Excel 365 , the FORMULATEXT function will return more than one result when given a range that contains formulas. These results will spill like other dynamic array formulas. In earlier versions of Excel, FORMULATEXT will return a single result from the upper left cell in the range.
To check if a cell contains a formula, use the ISFORMULA function . To temporarily display all formula in a worksheet with the keyboard shortcut Control + ` .
Examples
Assuming the formula =TODAY() in cell A1:
=FORMULATEXT(A1) // returns "=TODAY()"
With the formula =C1+B1 in cell D1:
=FORMULATEXT(D1) // returns "=C1+B1"
With the text “apple” in cell F1:
=FORMULATEXT(F1) // returns #NA!
One quirk of FORMULATEXT is that it will not display a circular reference error if given a reference to the same cell it resides in. For example, if the formula below is entered in cell A1:
=FORMULATEXT(A1) // returns =FORMULATEXT(A1)
The result is simply “=FORMULATEXT(A1)”.
Notes
- To test if a cell contains a formula or not, use the ISFORMULA function.
- FORMULATEXT will return #N/A if a cell does not contain a formula.
- FORMULATEXT will return #N/A when referencing another workbook that is not open.
- FORMULATEXT was introduced in Excel 2013.
Purpose
Return value
Syntax
=GETPIVOTDATA(data_field,pivot_table,[field1, item1],...)
- data_field - The name of the value field to query.
- pivot_table - A reference to any cell in the pivot table to query.
- field1, item1 - [optional] A field/item pair.
Using the GETPIVOTDATA function
Use the GETPIVOTDATA function to query an existing Pivot Table and retrieve specific data based on the pivot table structure. The advantage of GETPIVOTDATA over a simple cell reference is that it collects data based on structure , not cell location. GETPIVOTDATA will continue to work correctly even when a pivot table changes, as long as the field(s) being referenced is still present.
The first argument, data_field , names a value field to query. The second argument, pivot_t a ble, is a reference to any cell in an existing pivot table. Additional arguments are supplied in field/item pairs that act like filters to limit the data retrieved based on the structure of the pivot table. For example, you might supply the field “Region” with the item “East” to limit sales data to Sales in the East Region.
The GETPIVOTDATA function is generated automatically when you reference a value cell in a pivot table by pointing and clicking. To avoid this, you can simply type the address of the cell you want (instead of clicking). If you want to disable this feature entirely, disable “Generate GETPIVOTDATA” in the menu at Pivot TableTools > Options > Options (far left, below the pivot table name).
Examples
The examples below are based on the following pivot table:
The first argument in the GETPIVOTDATA function names the field from which to retrieve data. The second argument is a reference to a cell that is part of the pivot table. To get total Sales from the pivot table shown:
=GETPIVOTDATA("Sales",$B$4) // returns 138602
Fields and item pairs are supplied in pairs entered as text values . To get total sales for the Product Hazelnut:
=GETPIVOTDATA("Sales",$B$4,"Product","Hazelnut") // returns 62456
To get total Sales for the West region:
=GETPIVOTDATA("Sales",$B$4,"Region","West") // returns 41518
To get total sales for Almond in the East region, you can use either of the formulas below:
=GETPIVOTDATA("Sales",$B$4,"Region","East","Product","Almond")
=GETPIVOTDATA("Sales",$B$4,"Product","Almond","Region","East")
You can also use cell references to provide field and item names. In the example shown above, the formula in I8 is:
=GETPIVOTDATA("Sales",$B$4,"Region",I6,"Product",I7)
The values for Region and Product come from cells I5 and I6. The data is collected based on the region “Midwest” in cell I6 for the product “Hazelnut” in cell I7.
Dates and times
When using GETPIVOTDATA to fetch information from a pivot table based on a date or time date or time, use Excel’s native format , or a function like the DATE function . For example, to get total Sales on April 1, 2021 when individual dates are displayed:
=GETPIVOTDATA("Sales",A1,"Date",DATE(2021,4,1))
When dates are grouped , refer to the group names as text. For example, if the Date field is grouped by year:
=GETPIVOTDATA("Sales",A1,"Year","2021")
Notes
- The name of the data_field, and field/item values must be enclosed in double quotes (")
- GETPIVOTDATA will return a #REF error if any fields are spelled incorrectly.
- GETPIVOTDATA will return a #REF error if the reference to pivot_table is not valid.
