How to Automatically Color Alternate Rows in Excel

When working with a massive, data-heavy spreadsheet in Microsoft Excel, reading across a single row without your eyes accidentally drifting up or down a line is incredibly difficult. This phenomenon is why old-school accounting ledgers used “green bar” paper. You can easily replicate this classic visual aid in Excel by automatically coloring every alternate row, creating a clean, highly readable “zebra stripe” effect.

Using the Format as Table Feature

The absolute fastest way to apply alternating colors to your data is to utilize Excel’s built-in table formatting tools.

1. Click and drag your mouse to highlight all the data in your spreadsheet (or simply click any cell within your data and press Ctrl + A to select everything).
2. Ensure you are on the Home tab in the ribbon menu at the top of the screen.
3. Locate the “Styles” section and click on the button labeled Format as Table.
4. A dropdown menu will appear displaying dozens of different color schemes. Notice that almost all of the “Light” and “Medium” options feature alternating shaded rows.
5. Click on the color scheme you prefer.
6. A small dialogue box will pop up confirming your data range. If your data has headers (like “Name,” “Date,” “Amount” at the top), ensure the box labeled My table has headers is checked. Click OK.

Your data will instantly transform into a formatted table with perfect alternating row colors. The best part about this method is that it is dynamic. If you add a new row of data to the very bottom, Excel will automatically apply the correct color shade to maintain the alternating pattern.

How to Remove the Banding

If you decide later that the striped effect is too distracting, you do not need to manually change the background color of every cell.

1. Click anywhere inside your formatted table.
2. A new tab will appear in the ribbon menu at the top of the screen labeled Table Design. Click on it.
3. In the “Table Style Options” section, look for the checkbox labeled Banded Rows.
4. Uncheck the box. The alternating colors will instantly disappear, leaving you with a clean, white background while retaining your table structure.