How to Highlight Every Other Row in Excel (Quick and Easy)

Today we’re gonna have a look at how to highlight every other row in Excel, quick and easy, no matter the size of the data table.

Ready to start?

Would you rather watch this tutorial? Click the play button below!

How to Highlight Every Other Row in Excel

To highlight every other row in an Excel table, we need to select the applicable area first.

How to Highlight Every Other Row in Excel - the applicable area

Then in the Home tab, we select ‘Conditional Formatting’ and click on the option ‘New Rule’.

How to Highlight Every Other Row in Excel - conditional formatting - option new rule

A window appears where we need to click on ‘Use a formula to determine which cells to format’.

How to Highlight Every Other Row in Excel - Use a formula to determine which cells to format

Now, to highlight all the odd rows (rows 3, 5, 7, and so on), start typing the equal sign and then the formula ‘ISODD’. We want to highlight rows, so we carry on with an opening bracket, type in ‘ROW’, then we insert empty brackets and the closing bracket right after that. This is the whole formula to highlight every odd row. We included this formula in the list below, so you’ll have it at hand any time you need.

  • =isodd(row())
How to Highlight Every Other Row in Excel - the formula ‘ISODD’

How to Format Highlighted Rows

Now we need to specify how we want to highlight the rows. So, click on ‘Format’ and you’ll see all the possible formatting options for these rows.

How to Highlight Every Other Row in Excel - format

We just want to highlight rows, so we’ll go to the tab ‘Fill’ and choose, for example, this light blue. Of course, you can go for any colour you want – you’re not limited in your choice.

How to Highlight Every Other Row in Excel - fill - light blue

As soon as everything’s been set, we can click on OK twice and see the result! Every other row in the table has been highlighted according to the used settings.

How to Highlight Every Other Row in Excel - Every other row in the table has been highlighted

Highlighting rows through conditional formatting is great in a way that if you use the filter to reorganise the data in the table, every other row still remains highlighted. The highlighting does not move with the data and the table stays visually neat and clear.

How to Highlight Every Other Row in Excel - use the filter to reorganise the data

But what if we need to highlight every even row? That’s actually a cinch to do now.

Here’s how.

How to Highlight the Even Rows

Click anywhere into the data table, go to ‘Conditional Formatting’ and select ‘Manage Rules’. You can see the rule we’re using right now – the rule to highlight every odd row.

How to Highlight Every Other Row in Excel - How to Highlight the Even Rows
How to Highlight Every Other Row in Excel - the rule to highlight every odd row

So now, when we want to highlight the even rows, which are rows 4, 6, 8, and so on, we click on the rule and then click on the button ‘Edit Rule’. Then we simply overwrite ‘ODD’ with ‘EVEN’ in the formula.

How to Highlight Every Other Row in Excel - edit rule
How to Highlight Every Other Row in Excel - overwrite ‘ODD’

The rest of the formula remains as is. Again, click on OK and then OK one more time, and that’s all it takes! Each even row in the table has now been highlighted.

How to Highlight Every Other Row in Excel - Each even row in the table has now been highlighted

There’s one more thing worth mentioning before we wrap it up for today.

How to Remove the Formatting From the Table

To remove the formatting from the table, click on ‘Conditional Formatting’, then go to ‘Clear Rules’ and select ‘Clear Rules from Entire Sheet’, which will remove the highlighting in no time at all.

How to Highlight Every Other Row in Excel - clear rule
How to Highlight Every Other Row in Excel - clear rules from entire sheet

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!