Conditional and Auto Formatting in Excel

Formatting Microsoft Excel documents with alternate row colors makes it easy to read, especially if there are several rows with varied information. Formatting in Excel can be done by one of the following methods:

• Using AutoFormat

• Using Conditional Formatting

Using AutoFormat

Excel has a standard AutoFormat feature that enables one to format in one of the predefined styles listed. To use AutoFormat, select Format > AutoFormat from the Excel menu. AutoFormat window appears with the available styles.

Select the style to use. In this example, ‘List1’ is selected (to color alternate rows).

Click <OK> button to apply. Note that the list has alternate row colors.

The disadvantage of this method is that the formatting style is not applied to new rows inserted. This limitation can be overcome by using the Conditional Formatting method.

Using Conditional Formatting

This method involves the use of formulas to apply formatting style. Using conditional formatting you can apply styles based on the result of some conditions. For example if the value is > 100 but < 150, format with a certain color. In this example, conditional formatting is applied to get alternate row color. In Excel 2003, one can specify upto three conditions; however, this restriction is removed in Excel 2007.

• Select Format > Conditional Formatting from the menu. Conditional Formatting window appears.

• Select ‘Formula Is’ in the ‘Condition 1’ field. Enter the following formula as shown
  

=MOD(ROW(),2)

• Click the ‘Format’ button. ‘Format Cells’ window appears.

• Open the ‘Patterns’ tabbed region.
• Select the ‘color’ to apply.
• Click <OK> twice

The advantage of a conditional format is that if a row is inserted or deleted within the initial range, the formatting style is retained. For example, insert a row within the initial range and note how the formatting style is retained. Experiment by changing the numerical value given at the end of the formula. A value of ‘3’ formats every third row, 4 formats every fourth row differently and so on.

Reference

Microsoft Article: Creating conditional formatting formulas

Related posts:

1. Excel 2007 Alternate Row Color
2. Repeating Header Row in Excel 2007
3. Create Macro Button in Excel 2007
4. Publish Excel 2007 data onto SharePoint