How to Automatically color alternating rows in Excel
This video is a tutorial on using the Conditional Formatting function of Excel to automatically color alternating rows, which helps your eye to follow across the row. This can be done manually with the ‚ÄúFill Color‚Äù button, but if you add or delete rows after applying the shading, the whole purpose is defeated.
There is an easy way to format a range of cells so that alternating rows are automatically colored, and the shading adjusts when cells are added or deleted. This works the same way in Excel 2007 and earlier versions, with slight differences.
In Excel 2003, select the cells where you want to apply the shading. Go to the ‚ÄúFormat‚Äù menu and select ‚ÄúConditional Formatting‚Äù. In the drop-down menu select ‚ÄúFormula Is‚Äù and type in ‚Äú=mod(row(), 2)‚Äù. (This formula works by combining two different functions, and is explained in detail at the end of the video.) When the conditions of the formatting are true, the formatting is applied. Click on ‚ÄúFormat‚Äù and change the background pattern, click OK, and the alternating rows are now colored. The formatting can easily be changed or removed, and you can also apply it to columns rather than rows.
In Excel 2007, select the range of cells, making sure you are in the ‚ÄúHome‚Äù menu. The ‚ÄúConditional Formatting‚Äù command is in the ‚ÄúStyles‚Äù section. Select ‚ÄúNew Rule‚Äù, then select ‚ÄúUse a formula to determine which cells to format‚Äù. In the formula box type the formula as before, select your formatting and click OK. You can go back to the ‚ÄúConditional Formatting‚Äù command if you want to clear the formatting from selected cells or the entire sheet, or you can change the formatting or the formula with the ‚ÄúManage Rules‚Äù command.