#Excel Short and Sweet Tip #12 (Documenting Formulas and Highlighting Those Formulas With Conditional Formatting) by David Hager

One way to document a worksheet cell is through a cell comment. That was demonstrated recently by the following article.

However, there is another way to add a comment to a cell containing a formula. The N function returns a value of zero if the argument is a string (i.e. – =N(“Hi”) equals zero), so it does not affect the value of the main formula.

So, the formula to add the comment to is:

=MATCH(TRUE,ISNUMBER(FIND(“s”,\$A\$1:\$A\$20)),0)

which is an interesting formula in its own right but a discussion of it is not germane to this article.

This is the formula with the comment attached.

=MATCH(TRUE,ISNUMBER(FIND(“s”,\$A\$1:\$A\$20)),0)+N(“This formula created 05/11/17 by me.”)

In order to locate formulas of this type, a special conditional formatting formula is needed.

The formula needed to create the conditional format for cells containing formulas & documented by the N function is shown below (defined with D5 as the active cell).

HasFormulaComment=FIND(“+N(“””,FORMULATEXT(D5))

A notable feature of this formula is that since a quote is to be part of the lookup string +N(“ then a triple quote is required by Excel at the end of the string.

In the figure below, this conditional fomat is applied to column D.

For D5, this formula returns a value of 47. If it returns an error or 0, then the CF condition is FALSE. So, only cell D5 in column D is highlighted.