Creating conditional formatting formulas

Try Office 2010 In Excel 2010, conditional formatting has been improved.
Watch a video or try Office 2010!


Book cover


This article was adapted from Microsoft Office Excel 2003 Inside Out by Craig Stinson and Mark Dodge. Visit Microsoft Learning to buy this book.

Sample files    You can download sample files that relate to excerpts from Microsoft Office Excel 2003 Inside Out from Microsoft Office Online. This article uses the file Pacific Guitar Sales.xls.

The first drop-down list in the Conditional Formatting dialog box (on the Format menu, click Conditional Formatting) offers the Formula Is option, which you use when you want to enter your own conditional formatting formulas. You can create conditional formatting formulas to perform tasks such as:

  • Identifying dates that fall on specific days of the week
  • Specifying the smallest or largest value in a range
  • Highlighting specific text

For example, select a cell or range and then click Conditional Formatting on the Format menu. Select the Formula Is option from the first condition list, and then type the following formula into the edit box on the right:

=MOD(ROW(),2)=0

Click the Format button, select a color on the Patterns tab, and then click OK twice to close the two dialog boxes and apply the format. The MOD formula applies your selected color to every other row, as shown here.

Spreadsheet with alternating bands of color

 Note   The workbook used in this example, Pacific Guitar Sales.xls, is included in the sample files download.

When you use the Formula Is option, you can enter any formula that results in the logical values TRUE (1) or FALSE (0). For example, you could use a logical formula such as the following:

=N4>AVERAGE($N$4:$N$37)

This combines relative and absolute references to apply formatting to a cell when the value it contains falls below the average of the specified range. When you use relative references in this situation, the formatting formulas adjust in each cell where you apply or copy them, as regular cell formulas do.

 
 
Applies to:
Excel 2003