Microsoft Office Tutorials and References
In Depth Information
Identifying Formula Cells
To set up conditional formatting, follow these steps:
1. Choose Formulas
Define Name to display the New Name dialog box.
2. In the New Name dialog box, enter the following line in the Name field:
3. Enter the following formula in the Refers To field:
4. Click OK to close the New Name dialog box.
5. Select all cells to which you want to apply the conditional formatting.
Generally, they comprise a range from A1 down to the lower right corner of the used area
of the worksheet.
6. Choose Home
New Rule to display the New Formatting Rule
7. In the top part of the dialog box, select the option labeled Use a Formula to Determine
Which Cells to Format.
8. Enter this formula in the bottom part of the dialog box (see Figure 196-1):
9. Click the Format button to display the Format Cells dialog box and select the type of
formatting you want for the cells that contain a formula.
An unused fill color is a good choice.
10. Click OK to close the Format Cells dialog box and click OK again to close the New
Formatting Rule dialog box.
After you complete these steps, every cell that contains a formula and is within the range you
selected in Step 5 displays the formatting of your choice. In addition, if you enter a formula within
the range that has the conditional formatting, the cell immediately displays the formatting. This
formatting lets you easily identify a cell that should contain a formula but doesn’t.
The formula you enter in Step 3 is an XLM macro. Consequently, you must save the
workbook as a macro-enabled file (using an XLSM extension). If you save the
workbook as an XLSX file, Excel deletes the CellHasFormula name.