How to highlight alternate rows in Excel

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:

Excel Tables
Conditional Formatting

Here is our sample data:

Using Excel Tables to highlight alternate rows

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

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

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())

Select the data
Open Conditional Formatting window by going to Home > Conditional Formatting > Add New Rule
Select Use a formula to determine which cells to format
Enter the formula
Click Format button to edit formatting settings
Click OK to continue and apply your settings