Highlight every other row in excel

Let’s say you have a dataset of multiple rows and you want highlight every other row in excel so that the readability of your worksheet becomes better. The way to do this is pretty simple, we’ll be using conditional formatting to do the same.

Say you have a database just like below. Let’s see how you can shade every other row in excel:

Excel Highlight Alternate Rows 0.1

Now we want to color every other row in excel in this worksheet.

Excel Color Alternate Rows

Let’s see how to color every other row in excel:

  1. Select the entire dataset
    Excel Highlight Alternate Rows 1

  2. In Home Tab, open Conditional Formatting. From the sub-menu select New Rule. You can also use the shortcut for new rule in conditional formatting. (ALT + O + D)
    Excel Highlight Alternate Rows 2

  3. A new dialog box will appear. Click on “Use a formula to determine which cells to format” option.
    Excel Highlight Alternate Rows 3

  4. In Edit the Rule Description, enter the following excel formula:
    =MOD(ROW(),2)
    Excel Highlight Alternate Rows 4

  5. Select the Format option to set the formatting as you wish.
    Excel Highlight Alternate Rows 5

  6. Click OK
    Excel Highlight Alternate Rows 6

Voila! You just shaded every other row in excel.

Excel Highlight Alternate Rows 7

How does the formula work?

We used a combination of 2 formulas MOD & ROW.

MOD function returns the remainder of a division. ROW gives us the row number. For row number 3, MOD(3,2) is 1. 3 is divided by 2 and gives the remainder of 1.
For row number 6, MOD(6,2) is 1, as 6 is divided by 3 and the remainder we get is 0.

Because of this all odd number rows return 1 and even rows return 0.
1 also stands for (TRUE) in excel, hence condition becomes true and excel highlights all odd number rows, which highlight every other row or shade alternate rows.

Here are some more things you can try:

  1. Highlight every 2nd Use =MOD(COLUMN(),2)=0
  2. Highlight every 3rd Use =MOD(ROW(),3)=1

Alternate way to color every other row

First, you have to convert the data into an Excel Table. Now you’ll find an option in the Design Tab named Banded Rows, you just have to check this option.

Excel Highlight Alternate Rows 8
Excel Highlight Alternate Rows 9

Conclusion

I hope this article was helpful to you and explained you in depth how to highlight every other row in excel.

If you have any questions or tips, I’d love to hear them in the comment section below.

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Ask a Question or Leave a Reply

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Learn Army Logo
Learnarmy.com Copyright © 2020 All Rights Reserved