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
Copyright 2007 referpages
Related posts:
[...] alternate row colors.In Excel 2007 the formatting style is also applied to new rows inserted. In Excel 2003, the formatting style is not applied to newly inserted rows.Using Formulas in Conditional [...]