FORMULATEXT to Display the Formula in a Cell
Here is a function that displays the formula in another cell: FORMULATEXT(reference).
The argument, “reference”, is the cell address that contains the formula you want to display.
This is most useful for documentation purposes. For example, when you want to print out a spreadsheet with its formulas. If you have spent much time on this site, you might have noticed that we use it extensively.
1. Display the Cell Address and the Formula:
A little more sophisticated, would be a formula that also includes the cell address, like
=CELL(“address”, A12) & “: ” & FORMULATEXT(A12)
which produces a display like
$A$12: =MIN(A5:A7)
If we want to get rid of the unnecessary dollar signs, it gets more complicated: The formula
=SUBSTITUTE(CELL(“address”, A13), “$”, “”) & “: ” & FORMULATEXT(A13)
produces the cleaner display:
A13: =MIN(A5:A7)
2. FORMULATEXT Examples
In this example, we have FORMULATEXT formulas in both columns B and C, each referring to the column on its left. Thus, the formula in cell B5 displays the formula in cell A5, and the formula in C5 displays the formula in B5. This can look confusing.
FORMULATEXT works only on formulas. Where it references anything that is not a formula, such as the blank cell A4, it returns the #N/A error.
To avoid this, we can wrap it in an =IFERROR formula, for example, =IFERROR(FORMULATEXT(A15), A15). This will show the formula, if there is one. But, instead of $#N/A for errors, it displays the cell contents. See rows 14:15 in the example.
As we see on rows 12:13 in the example, you can concatenate FORMULATEXT in a formula with any other text and text formulas.
3. Alternatives to FORMULATEXT
FORMULATEXT is special in that, being a formula, it updates automatically whenever the formula it references, changes. Whether you insert or delete rows or columns, move the formulas around, or edit them, it will always display the formula it references.
This makes it your best choice for documenting formulas so that you have an external record of what they are.
However, there are other, clumsier, methods to document formulas:
4. Formula Auditing Mode: Ctrl+`
(The shortcut uses the back apostrophe, usually top left on the main keyboard, on the ~ button)
This displays formulas instead of their answers. It also widens the columns, in an apparent attempt to show more (but not always all) of each formula. You can then print the spreadsheet with the formulas for your records.
Use Ctrl+` to turn this mode off again. Columns should revert to their previous widths.
5. Documenting Formulas with Macros
One can build macros that will write copies of selected formulas into other cells of your choice. Here are some possibilities:
Sub DocFormula1()
' Document Formula in a Column (Place it one cell right).Dim S$
S$ = "'" & ActiveCell.Formula
ActiveCell.OffSet(0, 1).Formula = S$ ' Document 1 Cell right
ActiveCell.OffSet(1, 0).Select ' Move 1 down
End Sub
Sub DocFormula2()
' Document Formula in a Column (Place it 2 cells right).
' 23/02/1999 by Rick Raubenheimer.
' Keyboard Shortcut: Ctrl+q
'
Dim S$
S$ = "'" & ActiveCell.Formula
ActiveCell.OffSet(0, 2).Formula = S$ ' Document 2 Cells right
ActiveCell.OffSet(1, 0).Select ' Move 1 down
End Sub
Sub DocFormulaRow()
' Document Formula in a Row (Place it 8 cells Down).
' 23/02/1999 by Rick Raubenheimer.
' Keyboard Shortcut: Ctrl+Shift+q
'
Dim S$
S$ = ActiveCell.Address
S$ = Replace$(S$, "$", "") ' Remove "$" (better) ' RIR 080318
S$ = S$ & ": " & ActiveCell.Formula
ActiveCell.OffSet(8, 0).Formula = S$ ' Document 8 Cells below
ActiveCell.OffSet(0, 1).Select ' Move 1 right
End Sub
Each macro documents one cell, then moves one cell further, ready to run again. For more automation one could put any of them inside a loop, stopping when it hits a blank cell.