Excel with alternate row colors

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

Use a Table and Style

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

RELATED: How to Create and Use a Table in Microsoft Excel

Convert and Style a Table

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.

RELATED: How to Insert a Total Row in a Table in Microsoft Excel

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

Use Conditional Formatting

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.

RELATED: How to Highlight a Row in Excel Using Conditional Formatting

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:

=MOD(ROW(),2)=0

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 colors 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:

=MOD(ROW(),2)=1

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.

RELATED: How to Manage Conditional Formatting Rules in Microsoft Excel

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 »