MS-Excel / Functions and Formula

---

Identify Formulas with Conditional Formatting

Once a formula is entered into a cell, you can tell whether the cell is a static value or a value derived from a formula only by clicking in each cell and looking in the Formula bar, or by pressing Ctrl-~ (tilde). This tutorial fills that gap with a custom function.

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.

You could select Conditional Formatting → NewRule (pre-2007, Format → Conditional Formatting → Formula Is) and use =CELL("Type",A1) in the "Use a formula to determine which cells to format" section, but you must be aware that this is a volatile function. This means that every time you make any changes at all in the workbook, or another workbook while the workbook containing the conditional formatting is still open, it will force all the cells using the CELL function to recalculate. These global recalculations can add considerably to your overhead in a large spreadsheet. This tutorial presents a better way.

To become a clever formula hunter, start by going to the Developer tab and selecting Code → Visual Basic (pre-2007, go to Tools → Macro → Visual Basic Editor) or Alt/Option-F11 and then select Insert → Module. Enter the following function into the window that appears:

    Function IsFormula(Check_Cell As Range)
     IsFormula = Check_Cell.HasFormula
    End Function

Close the window (use the Close button in the window's title bar). Nowthis 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 going to the Formulas tab, selecting Function Library → Insert Function (pre-2007, Insert → Function), 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 do this.

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 Home → Conditional Formatting → NewRule → "Use a formula to determine which cells to format," and enter the following in the "Format values where this formula is true" box (pre-2007, Format → Conditional Formatting... → change "Cell Value Is" to "Formula Is"):

=IsFormula(A1)

Click the Format option and choose any formatting you want to use to identify formula cells. Click OK, then OK again.

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 tutorial can make your spreadsheets a lot easier to deal with when it comes time to maintain or modify them.