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.
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
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:
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
Once you click OK, you will see all even-numbered rows (due to ISEVEN) are colored.
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.