How to Apply Color to Alternating Rows in Microsoft Excel

One way to make spreadsheet data easier to read is to color alternate rows. Microsoft Excel provides several ways to alternate row color so you can choose the method that works best for you.

Use a table and a style

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

Convert and style a table

If your data is not formatted as a table, you can convert and style it at the same time. Select all the data you want to format and go to the Home tab.

Click the Format as Table drop-down arrow and choose an alternate row style.

Table Formatting Styles

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

Confirm cell range for a table

Your data is then formatted as a table with an alternating row style.

Table with alternating rows in Excel

How to Change Worksheet Tab Colors in Excel

Modify an existing table style

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

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

Banded lines box checked

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

Banded rows table in Excel

Use conditional formatting

You may not want to format your data as a table. In this case, you can use conditional formatting to apply alternate row colors.

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

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

New rule for conditional formatting

At the top of the pop-up window under Select Rule Type, choose “Use a formula to determine which cells to format.”

Use formula for conditional formatting rule

In the bottom section under Edit Rule Description, enter the following formula in the “Format values ​​where this formula is true” box:

=MOD(ROW(),2)=0

Added formula for formatting rule

Select “Format” and choose the color you want to use for the alternate rows in the “Fill” tab. You can also use a pattern with or without color if you wish. You will see a color preview at the bottom. If you are satisfied, click “OK”.

Fill tab with colors

You then return to the New Formatting Rule box. Again you can confirm the color in the preview at the bottom. Click “OK” to apply the rule.

Setting up a conditional formatting rule

This method makes it possible to color the even lines alternately.

Alternate row colors in Excel

If you also want to apply color to odd rows, create another new conditional rule and use this formula:

=MOD(ROW(),2)=1

Formatting rule for odd lines

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

Alternate Row Colors in Excel

You can edit or delete conditional formatting rules at any time 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.

To learn more, watch how to add cell borders after coloring alternate rows in Excel.

Leave a Comment