Mastering Cell Highlights: How to Tint Every Other Row in Your Excel Spreadsheet

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.

WPS Office Premium ( File Recovery, Photo Scanning, Convert PDF)–Yearly

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.

PearlMountain Image Converter

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

Click here

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

Jutoh is an ebook creator for Epub, Kindle and more. It’s fast, runs on Windows, Mac, and Linux, comes with a cover design editor, and allows book variations to be created with alternate text, style sheets and cover designs.

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.

Take advantage of PREMIUM features.
Create your texts / logos without any limitation.
No attribution required when downloading.
No advertising on the website.
TextStudio.com PREMIUM - Monthly Membership

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.