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:
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:
- Select the entire dataset
- 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)
- A new dialog box will appear. Click on “Use a formula to determine which cells to format” option.
- In Edit the Rule Description, enter the following excel formula:
- Select the Format option to set the formatting as you wish.
- Click OK
Voila! You just shaded every other row in excel.
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:
- Highlight every 2nd Use =MOD(COLUMN(),2)=0
- 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.
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.
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.