Identify formulas using Conditional Formatting

This clever technique was submitted by David Hager. It uses Conditional Formatting (available in Excel 97 or later) to apply special formatting to cells that contain formulas--something that's not normally possible. With this technique you can set up your worksheet so that all formula cells get a yellow background, for example, or so that negative values are in boldface.

Follow these steps:

1. Select Insert, Name, Define.
2. In the Define Name dialog box, enter the following in the 'Names in workbook' box
   
   CellHasFormula
   
3. Then enter the following formula in the "Refers to" box
   
   =GET.CELL(48,INDIRECT("rc",FALSE))
   
4. Click Add, and then OK.
5. Select all the cells to which you want to apply the conditional formatting.
6. Select Format, Conditional Formatting
7. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box (see the figure below):
   
   =CellHasFormula
   
8. Click the Format button and select the type of formatting you want for the cells that contain a formula.
9. Click OK.

After you've completed these steps, every cell that contains a formula and is within the range you selected in Step 4 will display the formatting of your choice.

How does it work? The key component is creating a named formula in Steps 2 and 3. This formula, unlike standard formulas, doesn't reside in a cell, but it still acts like a formula by returning a value -- in this case either 'True' or 'False'. The formula uses the GET.CELL function, which is part of the XLM macro language (VBA's predecessor) and cannot be used directly in a worksheet. Using a value of 48 as the first argument for GET.CELL causes the function to return 'True' if the cell contains a formula. The INDIRECT function essentially creates a reference to each cell in the selected range.