A quick trick to highlight alternating rows in Excel

October 11, 2017

Highlighting alternating rows is a great way to make your tables easier to read. It allows you to easily follow each row with you eyes rather than having to trace each line with your finger or use a ruler.

Here is a quick and easy way to do this without altering your data (there is a link to a YouTube video at the end):

First select the data in your table and select Home > Conditional Formatting > New in the ribbon.


When the conditional formatting menu appears select “Use a formula to determine which cells to format” and enter the formula shown “=MOD( ROW(), 2) = 0”.

For those who are curious, this is how the formula works: The MOD formula gives you the remainder when you divide two numbers by each other. In this case, we are dividing the current row number by 2. If the row number is even, then the remainder will be zero, and the conditional formatting will be applied. If the row number is odd, then the remainder will not be zero, and the formatting will remain unchanged.

After you have entered the formula, then click the “Format” button. Select the “Fill” tab, and choose the color you would like for your rows. In this example, we’re using a light red color.

Click the “OK” button through all of the menus, until you return to your table. If you have done this successfully, then all even numbered rows will be highlighted.

A nice benefit of using this method is that you can add or delete rows in your table without messing up the formatting. For example if you deleted row 9 from your table, you wouldn’t end up with two shaded rows right next to each other. The formatting rule will only highlight data if it sits in an even numbered row.


I hope this was helpful for you, and please visit our store  to download more free and premium tools.



Leave a Reply

Your email address will not be published. Required fields are marked *