About conditional formats

You can monitor formula results or other cell values by applying conditional formats (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.). For example, you can apply green text color to the cell if sales exceed forecast and red shading if sales fall short.

When conditions change     If the value of the cell changes and no longer meets the specified condition, Microsoft Excel clears the formatting from the cell, but leaves the condition applied so that the formatting will be automatically reapplied when the condition is met.

Shared workbooks     In a shared workbook, conditional formats that are applied before a workbook is shared continue to work; however, you cannot modify the conditional formats or apply new ones while the workbook is shared.

PivotTable reports     If you try to apply conditional formats to cells in a PivotTable report, you will get unpredictable results.

You can compare the values of the selected cells to a constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) or to the results of a formula. To evaluate data in cells outside the selected range or to examine multiple sets of criteria, you can use a logical formula to specify the formatting criteria.

• Use the value in a cell as the condition     If you select the Cell Value Is option and compare the values of the selected cells to the result of a formula, you must start the formula with an equal sign (=).
• Use a formula as the condition     If you select the Formula Is option, the formula you specify must return a logical value of TRUE (1) or FALSE (0). You must start the formula with an equal sign (=). The formula can evaluate data only on the active worksheet. To evaluate data on another sheet or in another workbook, you can define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) on the active worksheet for the data on another sheet or workbook, or enter a reference to the data in a cell of the active worksheet. Then refer to that cell or name in the formula. For example, to evaluate data in cell A5 on Sheet1 of the workbook Fiscal Year.xls, enter the following reference, including the equal sign (=), in a cell of the active sheet: =[Fiscal Year.xls]SHEET1!$A$5

The formula can also evaluate criteria that is not based on worksheet data. For example, the formula =WEEKDAY("12/5/99")=1 returns a value of TRUE if the date 12/5/99 is a Sunday. Unless a formula specifically refers to the selected cells you are formatting, the cell values do not affect whether the condition is true or false. If a formula does refer to the selected cells, you must enter the cell references in the formula.

• Use cell references as the condition     You can enter cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) in a formula by selecting cells directly on a worksheet. Selecting cells on the sheet inserts absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.). If you want Microsoft Excel to adjust the references for each cell in the selected range, use relative cell references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.).
• Use dates     Dates and times are evaluated as serial numbers. For example, if you compare the cell value with the date January 7, 2001, the date is represented by the serial number 36898.

Example 1: Use data in a cell

Example 2: Use a formula and external cell references

Example 3: Use a formula and a cell reference

Verify a conditional format before applying it     An easy way to ensure that formula references are correct is to apply the conditional formatting first to one cell in the range. Then select the entire range, click Conditional Formatting on the Format menu, and then click OK. The conditional formatting you applied to the first cell is applied to the entire range, with the formula correctly adjusted for each cell.