# 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:

- Select
*Insert, Name, Define.* - In the Define Name dialog box, enter the following in the 'Names in workbook' box
CellHasFormula

- Then enter the following formula in the "Refers to" box
=GET.CELL(48,INDIRECT("rc",FALSE))

- Click
*Add*, and then OK. - Select all the cells to which you want to apply the conditional formatting.
- Select
*Format, Conditional Formatting* - 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

- Click the
*Format*button and select the type of formatting you want for the cells that contain a formula. - 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.

### Search for Tips

### All Tips

### Browse Tips by Category

### Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers