Excel with alternate row colors

To shade every other row in Excel, highlight your dataset and go to Home > Format as Table, then choose from the menu that appears the alternating color style you want. Alternatively, use conditional formatting to apply alternating row colors to your entire spreadsheet.

One way to make spreadsheet data easier to read is by applying colored shading to alternating rows. Microsoft Excel offers a couple of ways to alternate row color so you can whichever method works best for you.

Shade Every Other Row With a Table and Style

By using a table with an alternating row color style, you can apply shading with little effort. You can do this by converting your data to a table or choosing a style for an existing table.

Convert and Style a Table With Alternating Colors

If you don’t have your data formatted as a table, you can convert it and apply a style at the same time. Select all of the data you want to format and head to the Home tab.

Click the Format as Table drop-down arrow and pick an alternating row style.

Format as Table styles

Confirm the cell range for the table data and click “OK.”

Confirm the cell range for a table

You’ll then have your data formatted as a table with an alternating row style.

Table with alternating rows in Excel

Change an Existing Table Style

If you already have a table for your data, select any cell within it and go to the Table Design tab.

To show styles with alternating row colors, check the box for Banded Rows in the Table Style Options section of the ribbon.

Banded Rows box checked

Then expand the Table Styles box and choose a style with alternating row colors from the Light, Medium, or Dark color schemes.

Banded row table in Excel

RELATED: How to Name a Table in Microsoft Excel

Use Conditional Formatting to Color Every Other Row

Maybe you don’t want to format your data as a table. In this case, you can use conditional formatting to apply alternating row colors.

If you only want to apply the color to a certain cell range, select those cells. To color all alternating rows in the whole sheet, click the Select All (triangle) button on the top left of the sheet.

Go to the Home tab, select the Conditional Formatting drop-down arrow in the Styles section, and choose “New Rule.”

New Rule for Conditional Formatting

At the top of the pop-up window below Select a Rule Type, pick “Use a Formula to Determine Which Cells to Format.”

Use a formula for the conditional formatting rule

In the bottom section below Edit the Rule Description, enter the following formula in the “Format Values Where This Formula is True” box:


Formula added for the formatting rule

Select “Format” and choose the color you want to use for the alternating rows on the Fill tab. You can also use a pattern with or without a color if you like. You’ll see a preview of the color at the bottom. If you’re happy with it, click “OK.”

Fill tab with colors

You’ll then be back to the New Formatting Rule box. Here again, you can confirm the color in the Preview at the bottom. Click “OK” to apply the rule.

Conditional formatting rule set up

This method shades alternating even rows.

Alternating row colors in Excel

If you want to apply a color to the odd rows as well, create another new conditional rule and use this formula:


Formatting rule for odd rows

Follow the same steps to choose the color and apply the rule.

Alternating row colors in Excel

You can edit or remove conditional formatting rules anytime by going to Home > Conditional Formatting > Manage Rules.

Using alternating row colors for your spreadsheet gives you and your audience a clear way to read and analyze data.

For more, look at how to add cell borders after you color the alternating rows in Excel.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »