Coloring Beneath the Surface: A Guide to Shading Alternating Cells in MS Excel

Quick Links

• Shade Every Other Row With a Table and Style
• Use Conditional Formatting to Color Every Other Row

Key Takeaways

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.

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

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

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.

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

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.”

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

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

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.”

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.

This method shades alternating even rows.

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

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

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.

Also read: