The VBA code in this custom
function (also called a user-defined function)
enables you to identify cells that contain formulas without having to
click through 10,000 cells and examine each one.
To become a clever formula hunter,
start by selecting Tools → Macro → Visual Basic
Editor (Alt/Option-F11) and then select Insert → Module.
Enter the following function:
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
the window (press Alt/&command;-Q, or use the Close button in the
Now this function is available in any cell on any
worksheet in this workbook when you enter the formula
=IsFormula($A$1). You also can access the
function by selecting Insert → Function, then selecting
UserDefined from the Category option and choosing
IsFormula from the functions displayed.
The formula returns
TRUE if the reference cell houses a formula and
FALSE if it does not.
You can use this Boolean result in conjunction with
conditional formatting so that all formulas are highlighted
automatically in a format of your choice.
One of the best things about using this method is that your
spreadsheet's formula identification capabilities
will be dynamic. This means that if
you add or remove a formula, your formatting will change
accordingly. Here we explain how to
Select a range of cells on your spreadsheet—say,
A1:J500—and incorporate some extra cells in case more formulas
are added at a later stage.
Avoid the temptation of selecting an entire worksheet, as this can
add unnecessary overhead to your spreadsheet.
With these cells selected, and with A1 the
active cell of the selection, select Format → Conditional
Formatting.... Under Cell Value Is, select Formula Is and enter
=IsFormula(A1) in the Formula box. Click the
Format button and choose any formatting you want to use to identify
formula cells. Click OK, then OK
Sometimes, when entering formulas into conditional formatting, Excel
will try to put quotation marks around the formulas after you click
OK. This means Excel has recognized
what you entered as text, not as a formula.
If this happens to you,
go back into the Conditional Formatting dialog, remove the
quotation marks, and click OK.
At this point, the specified formula
should be applied to all cells on your worksheet that contain
formulas. If you delete or overtype
a cell containing a formula, the conditional formatting will
disappear. Similarly, if you enter a new formula into any cell within
the range, it too will be highlighted.
This simple conditional formatting hack can make your spreadsheets a
lot easier to deal with when it comes time to maintain or modify