How to Highlight Every Other Row in Excel (3 Easy Ways) (2024)

Cells can be highlighted in Excel using the Fill Color option. Rows and columns are no different. As it always stands, dealing with a small dataset is no problem at all but the problem gets bigger with the bigger datasets.

We just want systematic selection and highlighting. It's not too much to ask for. If you're cracking your knuckles on how to make something so simple work Excel-ly, we have this detailed guide just so you can spare your knuckles (and of course so you can highlight alternate rows).

Our objective in this tutorial is to highlight every other row in our dataset and we have few good ways of getting that done. Let's get highlighting! Alternately.

Table of Contents

Method #1 – Using Manual Approach

Other than holding down the Ctrl key and clicking every other row, there's another manual method that involves filtering and the ISEVEN function. The ISEVEN function returns TRUE if the specified number is even.

The process starts with numbering all the rows in a separate column. Then using the ISEVEN function in another column which will return TRUE for alternate rows. By filtering, all TRUEs (or FALSEs) can be grouped and highlighted. After the filter is removed, every alternate column will be highlighted. The two new test columns involving the numbering and the ISEVEN function can then be deleted. Now let's show you the detailed steps.

Along the last column in your dataset, type 1 in the first row and 2 in the next row. Select both these cells, click and drag to fill the column with consecutive numbering.

In the next column, enter the ISEVEN function:
=ISEVEN(E3)

For ease of referring, we will name these two columns "No." and "ISEVEN".

From the Home tab, in the Editing section, click the Sort & Filter From the drop-down menu, select Filter. Alternatively, use the keyboard shortcut Ctrl + Shift + L to turn on filtering for the table.

By clicking on the little arrow above the ISEVEN column, uncheck the Select All checkbox, then filter the column to any one group (either TRUE or FALSE).

Select the filtered data and highlight it.

Now clear the Filter from the "ISEVEN" column. You can then also remove the filters by clicking anywhere in the table and pressing Ctrl + Shift + L or selecting the Filter option again from the ribbon menu.

Expanding back to the original dataset shows you that every other row is highlighted.

Select both the test columns (columns E and F) and delete them.

Similarly, instead of having a separate column for numbering the rows, the ROW function can be fed into the ISEVEN function like so:

Method #2 – Using Conditional Formatting

Now we can take things to the next level and make use of Excel's conditional formatting feature to highlight alternate rows. In this process, we are going to conditionally format our data using a formula based on the ROW function such that the formula returns TRUE for every alternate row.

To accomplish this we can use the formula that we saw in the section above or we can build another formula based on the MOD and ROW functions together.The MOD function returns the remainder after a number is divided by another number (e.g. number 5, divided by 2, MOD will return the remainder 1).

Sounds easy, doesn't it? It is when you know what to do so here is what to do:

Select the dataset requiring highlighted rows.
From the Home tab, in the Styles section, click the Conditional Formatting From the drop-down menu, select the New Rule option. This will open the New Formatting Rule window.

From the Select a Rule Type: section, select Use a formula to determine which cells to format. This option will allow us to use the MOD and ROW functions to conditionally format our selected data.
In the formula bar, enter any one of the formulas below (both formulas will return the same result):

=MOD(ROW(),2)=0

=ISEVEN(ROW())

Next, click the Format… button below to launch the Format Cells window.

We are aiming to highlight the rows so we will go to the Fill tab and select the color for highlighting.

Then click OK in the Format Cells window and also the New Formatting Rule window to apply the conditional format.

Presenting successfully highlighted alternate rows:

Job well done!

Highlighting Every 3rd, 4th, or nth Row With Conditional Formatting

In the above section, we saw how to shade every other row using conditional formatting in Excel.

What if we wish to shade every 3rd, 4th, or nth row?

We can definitely accomplish this and to understand how to get this done we first need to get our heads around the MOD and ROW function-based formula that we used above. Once we've learned how it works, we can tweak the logic and can highlight every nth row!

Now let's go inside the brain of the formula. The formula we used was

=MOD(ROW(),2)=0

In this formula – We have set the divisor as 2 and the remainder as 0. This means that the number of the row has to be divisible by 2 so that the remainder can equal to 0. Essentially, that means every even-numbered row. Then, whichever row fulfills this criterion will be eligible to be highlighted.

The formula starts by checking the first row from the selection which is row 3. Divided by 2, the number 3 leaves a remainder of 1 which does not fulfill the criteria. The conditional formatting will not apply to this row.

The next row checked by the formula is row 4. Divided by 2, the number 4 leaves a remainder of 0 which fulfills the condition of our supplied formula. Row 4 makes it to conditional formatting and will be highlighted.

In this way, each row will be checked and essentially, every even-numbered row and hence alternate rows will be highlighted.

That means we can work around with this formula and have it select every nth row in this way:

For highlighting every 3rd row, use the formula

=MOD(ROW(),3)=0

For highlighting every 4th row, use the formula

=MOD(ROW(),4)=0

Also, depending on the row where your dataset begins, play around with the remainder in the formula to set where you want the highlighting to begin in the table. E.g. Since our data selection began from row 3, if we were to put 1 as the remainder in the formula, the highlighting would have started from row 3 instead of row 4, with every alternate cell highlighted because of '2' as the divisor.

Do note that if you are to sort and filter the data after highlighting the rows, the data will group differently; the highlighted rows will remain highlighted and group together for the applied sorting and filtering.

If you want to sort and filter data without changing the format as alternately highlighted rows (and exactly which rows are highlighted is not a problem as long as alternate rows are highlighted), then you're better off using table styles which brings us to our next segment.

Method #3 – Using Table Styles

For retaining the format of alternately highlighted rows where the highlighting remains unchanged despite sorting, filtering or changes in the dataset (addition or deletion), learn how to use table styles to format your dataset even if it has no formatting from the get-go. Follow the steps below:

Select any cell in the dataset. From the Home tab, in the Styles section, click the Format as Table In the drop-down menu, select any table style with banded rows to exhibit the effect of alternately highlighted rows.

Once you've selected a style, a Create Table window will pop up with Excel's guess about the range of your dataset. The guess is also depicted by a marching ants border around the dataset on the sheet. Adjust the range here if needed. Leave the My table has headers checkbox checked if your data contains headers. Click OK.

According to the style selected, the rows in the table should be highlighted alternately.

This method not only highlights every other row but also makes a regular dataset a dynamic Excel table which has several helpful features. If you still don't see alternate rows highlighted, please ensure thattheBanded Rows checkbox in the Table Style Options group is checked.

Recommended Reading: How To Add Total Row In Excel Tables

Method #4 – Using VBA

While the above methods are simple and far less daunting, there also stands another option that is VBA. VBA is Visual Basic for Applications where you can use codes to get tasks done in Excel. We'll use VBA for highlighting alternate rows and for what it's worth, here are the steps:

Select the range to be highlighted and press Alt + F11 to launch the VBA window.

Click the Insert tab, then click the Module. In the module window, paste the following code:

Sub HighlightAlternateRows()
Dim Rng As Range
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -2
 Set myRow = Rng.Rows(i)
 myRow.Style = "60% - Accent6"
Next i
End Sub

Execute the code using F5 key and every alternate row in your selected range gets the new shade as expected.

Now, how about some insight on the code.

The above code takes the range selected on the worksheet and using a For Loop iterates over the range. The iterations happen to start from the last row of the range and the step increment value is set to -2. This means instead of iterating over the rows one by one we iterate over every other row from the selected range.

While performing the iteration over the rows, we are setting up the row style as 60% – Accent6and then proceed on to the next iteration.

You can pick any style (like "Accent1", "Accent2", "Accent3" etc.) from the Styles section in the Home tab to add to the code.

So that was all about highlighting every other row in Excel. What was the highlight for you? Remember you can also highlight columns in similar ways. While you're learning (and highlighting), we're working on placing another brick in the Excel wall for you. Let's see if you can cement it in your minds.

I'm a seasoned Excel enthusiast with extensive experience in data manipulation, analysis, and visualization. I've not only explored the common features of Excel but also delved into advanced techniques, such as using VBA (Visual Basic for Applications), conditional formatting, and various functions to enhance efficiency and effectiveness in handling datasets of all sizes. My proficiency in Excel extends to automating tasks, creating complex formulas, and utilizing features like table styles to maintain formatting integrity.

Now, let's break down the concepts covered in the article about highlighting alternate rows in Excel:

1. Manual Approach (Method #1):

ISEVEN Function: This function is used to determine if a number is even. In this method, it is applied to identify alternate rows for highlighting.

2. Conditional Formatting (Method #2):

MOD Function: Calculates the remainder after division of one number by another. It's employed in creating formulas for conditional formatting to highlight alternate rows.
ROW Function: Returns the row number of a reference. It's used in conjunction with the MOD function for conditional formatting.

3. Highlighting Every nth Row (Method #2 continued):

Modification of the MOD function to highlight rows at intervals (e.g., every 3rd, 4th row).

4. Table Styles (Method #3):

Formatting as Table: Utilizing Excel's table styles to highlight alternate rows, ensuring formatting persists during sorting, filtering, and changes in the dataset.

5. VBA (Method #4):

VBA (Visual Basic for Applications): Introduction to using VBA code for automating the process of highlighting alternate rows. The example code uses a For Loop to iterate through rows and applies a specific style.

By combining these methods, users can choose the most suitable approach based on their preferences and the specific requirements of their dataset. Whether it's a small or large dataset, these techniques offer a systematic way to achieve the desired highlighting in Excel.