Using Conditional Formatting to Shade Alternating Rows in Excel

When working with Excel spreadsheets, especially large ones with a lot of data, it is sometimes easier to use (and easier on the eyes!) to highlight every other row with a color. (It also makes your spreadsheets look cleaner and more professional looking.) Alternating row shading can be done by using Conditional Formatting.

First, highlight the range of cells where you would like the color to alternate (this area can include “empty” cells without data as well).

Click Format > Conditional Formatting. On the pull-down under 'Condition 1', click "Formula Is". Then enter this formula =MOD(ROW(),2)=1 on the data bar.

Then click the Format button to choose the color your alternating rows will be.

Click OK and you're done! In 2003 you can add up to three Conditions (click the Add>> button). To delete, simply click the Delete button and choose which condition to remove.

You can also format alternating color by using the AutoFormat function. With this method, Microsoft has done your Conditioning Formatting work for you already! Simply go to Format > AutoFormat and choose the style you like. (If you are using Excel 2007, you'll have to add the Auto Format menu to the Quick Access Toolbar. Click the Office Button > Excel Options > Customize > All Commands (under the Choose commands from) > then Click AutoFormat and Add). 2007 has an amazing selection of styles to choose from... I highly recommend playing around with it and getting comfortable using these commands.

You can also shade alternate rows by using a VBA macro. Please feel free to contact me for additional help.

Comments

[Ana Quillinan]

Kathy - you taught me something amazing just now. Since I'm using OpenOffice.org now on my Mac, I haven't been using Excel lately. Nonetheless, this is prompting me to really reconsider going back to Excel for schedule I'm designing for a client. Thank you for the great advise.

[Kathy Layne]

Glad I could help, Ana! OpenOffice.org is also a great resource. Let me know if you ever need any other inspiration or assistance... :)