Share this page:

MS Excel: Automatically alternate row colors (one shaded, one white) in Excel 2010

Question: How can I set up alternating row colors in Microsoft Excel 2010? I don't want to have to change the row colors every time I insert, delete, or move a row.

Answer:Special thanks to Leo for bringing this formatting trick to our attention.

If you wish to set up alternating row colors in Excel, first highlight the cells that you wish to apply the formatting to. In this example, we've selected A1:B6.

Select the Home tab in the toolbar at the top of the screen. Then in the Styles group, click on the Format as Table drop-down and select one of the options.

Microsoft Excel

The Format as Table window will pop-up for you to confirm the cell range that will be formatted and the option for you to apply the format to the header rows (ie: My table has headers). Click the OK button to apply the format.

Microsoft Excel

Now when you return to the spreadsheet, the alternating row color will be applied. You can insert, delete, and move rows, and you don't have to worry about reapplying formatting.

Microsoft Excel

What a wonderful formatting trick!