How to Highlight Every Alternate Row in Excel

Have you ever faced with a need to make your excel dataset table banded? By branded table, I mean that every second, third, fourth, etc. row is colored. You may feel with this need when you want to group your data in the blocks of 4, or 5, or 10, and so on and so forth. In this blog, we would unlock the technique to highlight every alternate row in excel i.e. every second row in the Excel dataset. You can use the same technique to highlight the rows at any interval. To highlight every alternate row, we would use the conditional formatting functionality of excel.

Let us begin with this technique.

Sample Dataset Table

Suppose we have a sample dataset as shown in the screenshot and the purpose here is to highlight alternate rows (i.e. Rows 3, 5, 7, 9 and so on)

Sample Data - Highlight Alternate Row Excel

Highlight Alternate Rows in Excel

Consider the below step by step procedure to achieve the objective:

Step 1: Firstly, select the dataset (A2:C13) as demonstrated in the screenshot below :

Cell Range Selection

Step 2: Go to the “Home” tab and click on the option “Conditional Formatting”. You can find this option under the ‘Styles’ group

Conditional Formatting Navigation

Step 3: From the drop-down options under the “Conditional Formatting”, select the option “New Rule”

Conditional Formatting New Rule

As a result, the “New Formatting Rule” dialog box will open.

Step 4: In the “Select a Rule Type” section of this dialog box, select the option “Use a formula to determine which cell to format” from the list of rules. As soon as you select this option, you would notice that an input box appears in the lower half of the dialog box.

New Formatting Rule Dialog Box

Step 5: In the input box as highlighted in the screenshot above, write the below formula :

=MOD(ROW($A2)-1,2)=0

Then, click on the “Format” button to open the dialog box – “Format Cell”.

In the “Format Cell” dialog box, go to the tab “Fill” and select the color of your choice from the range of colors. Additional colors are also available. You can find those by clicking on the button “More Colors”.

I have chosen Yellow color.

Selecting Fill Color

Finally, click “OK”.

This would take you back to the “New Conditional Formatting” dialog box. There as well, click the “OK” button to exit and activate the set conditional formatting.

Result

You will notice that the alternate rows in the dataset got highlighted with yellow color.

Highlight Alternate Row Result

Similarly, if you want to highlight every third row, then, in that case, all the steps as explained above remain the same. Just the formula will change =MOD(ROW($A2)-1,3)=0.

As a result, every third row in the dataset gets highlighted.

Highlight Every Third Row - Result

This brings us to the end of this blog. I hope that you have found this blog useful. Please share your valuable views and comments in the comment section below.

Leave a Comment