Identify Formulas By Using Conditional Formatting

Category: Formulas | [Item URL]

How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag.

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.