With alternating background colors, it becomes easier to differentiate between each row. In this guide, we’re going to show you how to highlight alternate rows in Excel.

Download Workbook

You can set different colors in alternate rows using either of these two features:

  1. Excel Tables
  2. Conditional Formatting

Here is our sample data:

How to highlight alternate rows in Excel 01

Using Excel Tables to highlight alternate rows

All you need to do is to convert the data into an Excel Table.

  1. Just select a cell in the data and press Ctrl + T.
  2. Check My table has headers option if Excel your data has headers.
  3. Click OK.

How to highlight alternate rows in Excel 02

Excel’s default table style includes alternating colors by default. Feel free to apply built-in styles in the context tab. You can see this tab only when a table is selected.

You do not need to color new rows. Excel automatically highlights alternating rows as the table grows or shrinks.

For more features of Excel Tables: Tips for Excel Tables

Conditional Formatting

The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. If this condition is provided by a formula, Excel will check whether the formula returns TRUE before applying the formatting options. Therefore, we need a formula that will return TRUE when the condition is met within the selected range.

A formula for alternate rows can be created based on the row number. You can set a rule whether the row number is even or odd. Different rules (colors) for each is also possible. Choose between following formulas:

Even rows: =ISEVEN(ROW())

Odd rows: =ISODD(ROW())

  1. Select the data
  2. Open Conditional Formatting window by going to Home > Conditional Formatting > Add New Rule

  3. Select Use a formula to determine which cells to format
  4. Enter the formula
  5. Click Format button to edit formatting settings
  6. Click OK to continue and apply your settings

Once you click OK, you will see all even-numbered rows (due to ISEVEN) are colored.

How to highlight alternate rows in Excel 06

You can highlight odd-numbered rows as well by using the formula with ISODD.

Remember that you need to copy or remove formatting when your data grows or shrinks.