Highlight Rows in Excel | How to Highlight Every Other Row in Excel?

How to Highlight Every Other Row in Excel?

When we are working in excel we often look at the ways of designing the data and make that data look beautiful to the viewers or for ourselves. There are many ways of designing the data and shading or highlighting every other row in excel or every N^th row is one of the ways.

In this article, I am going to demonstrate the techniques of highlighting every N^th row by using formatting.

There are a couple of ways we can highlight every other row in excel.

Using Excel Table
Using Conditional Formatting.
Custom Formatting

Follow this article to learn and explore each one of them.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

You can download this Highlight Row Excel Template here – Highlight Row Excel Template

Method 1 – Highlight Rows Using Excel Table

In excel by default, we have a tool called “Excel Table”. This is the quick way to highlight every other row in excel.

Now take a look at the raw data.

In this data, we need to highlight every other row in excel. Using this method we can highlight every other row. We do not need any special skills.

Step 1: Select the data.

Step 2: Press Ctrl + T (shortcut to create table). This will open up the below box.

Step 3: Click on OK. This will create the table like this.

This will automatically highlight every other row.

Go to Design > Table Styles

Here we have many different types of highlighting every other row by default.

If you uncheck Banded Rows box it will remove the highlighting option.

Method 2 – Highlight Rows Using Conditional Formatting

If you aware what is conditional formatting it is easy for you to understand. Let me give you a simple condition formatting example.

I have number list from A1 to A10. I want to highlight the number 5 in this range with a yellow colour.

Step 1: Select the data range from A1 to A10.

Step 2: Go to Home tab > Conditional Formatting > New Rule >

Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.

Step 4: In the formula section mention = $A1 = 5.

Step 5: Once the formula is entered Click on Format.

Step 6: Go to Fill and select the colour you want.

Step 7: Click OK. This will highlight all the cells containing number 5 from A1 to A10.

In this way based on the user condition excel to format the particular cells for us.

Method 3 – Highlight Every other Row in Excel Using Custom Format

Similarly, we can highlight every alternative row by using Conditional Formatting.

Step 1: Select our data (data that we have used in example 1). Do not select the heading because formula will highlight that row as well.

Step 2: Go to Home tab > Conditional Formatting > New Rule >

Step 3: Click on New Rule it will open a separate dialogue box. Select Use a formula to determine which cell to format.

Step 4: In the formula section mention = MOD (ROW (), 2) =1

Step 5: Once the formula is entered Click on Format.

Step 6: Go to Fill and select the colour you want.

Step 7: Click OK. This will highlight every alternate row.

Breaking Down the Formula

Ok, let me break down the formula.

The formula reads =Mod (Row (), 2) = 1

MOD function returns the remainder of the division calculation. For example, =MOD (3, 2) returns 1 as the result. When we divide the number 3 by 2 we will get 1 as remainder. Similarly, ROW function Excel will return the row number and whatever the number returned by ROW function will be divided by 2. If the remaining number returned by MOD function is equal to number 1 then excel will highlight the row by the mentioned colour.

If the row number is divisible by 2 then the remainder will be zero. If the row number is not divisible by 2 then we will get the remainder that is equal to 1.
Similarly, If we want to highlight every 3^rd row we just need to change the formula to =MOD (ROW (), 3) =1.
If we want to highlight every 2^nd column we can use =MOD (COLUMN (), 2) = 0.
If we want to highlight every 2^nd column starting from the first column then we need to use this formula. =MOD (COLUMN (), 2) =1

Things to Remember

If the data needs to be printed you need to use light colors to highlight. Because dark colors will not show the fonts after printing.
If the header is selected while applying conditional formatting then it will treat the header as the first row as well.
If we want to highlight every 3^rd row we need to divide the row by 3.
Similarly, we can apply this formatting for columns using the same formula.
You cannot change the color of the row once the conditional formatting is applied.

Highlight Every Other Row in Excel