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.