Formatting cells based on specific conditions
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.
Formulas as formatting criteria
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.
Example 1: Use data in a cell
In the following example, conditional formats applied to the range B2:E3 analyze quarterly contributions to the yearly total. Quarterly results that contribute 30 percent or more to the total are displayed in bold and green. Quarterly results that contribute 20 percent or less are displayed in bold and red.
The following table summarizes the conditional formats applied to the range B2:E3. Microsoft Excel adjusts the relative portion (the row number) of the cell reference $F2 in the formula so that each cell in the range B2:E3 is compared with the corresponding total in column F.
||Cell Value Is
||Greater than or equal to
||Bold, green font
||Less than or equal to
||Bold, red font
Example 2: Use a formula and external cell references
|Green cell shading
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.