The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.

The FORMULATEXT function returns a text string containing the formula of a cell. See cell B4 in the image below.

This is a great help when creating Excel training manuals or webinars where you typically paste the formula with a leading inverted comma to show the formula as text in another cell.

Identify SUM Formula

In terms of normal Excel use you can use the new function to identify SUM functions so that you can apply a bold format to the cell using a conditional format.

In the image below, the range A1:A4 has the following conditional format applied

The formula is

=SEARCH("SUM(",FORMULATEXT(A1))>0

An alternate formula is

=LEFT(FORMULATEXT(A1),5)="=SUM("

The first formula will work if there is a SUM anywhere in the formula. The second will only apply if the formula starts with the SUM function.

They both use the relative reference A1, so that the condition is checked in each separate cell in the whole range. A1 is the first cell in the selected range.

If the cell doesn’t contain a formula the #N/A error is displayed. See cell B1 in the image below.

Hence you can identify formula cells by using the following formula. This has been entered in cell C1 in the image below and copied down.

The formula is

=NOT(ISNA(FORMULATEXT(A1)))

Note: Excel treats any cell starting with = as a formula. Hence =5+5 is treated as a formula even though it will not change after a calculation.