How to Select Every Other Row in Excel(6 Ways)

Featured Image to Select Every Other Row

Select is one of the most needed tasks of Excel. While performing functions or formulas in multiple rows we may require to select every other row. In this article, I’m going to explain how to select every other row in Excel.
To make the explanation visible and clearer I’m going to use a dataset. The dataset is about the sales information of a certain tech shop. There are 4 columns in the dataset which are Sales Rep, Region, Product, and Sales. These columns are the total sales information for a particular product by a sales representative.

Sample Dataset

Download to Practice

6 Ways to Select Every Other Row in Excel

1. Using Conditional Formatting

To use Conditional Formatting, first, select the cell range where you want to apply this format.
Now, open the Home tab >> Go to Conditional Formatting >> then select New Rule 

Using Conditional Formatting

It will pop up a dialog box. From there select Use a formula to determine which cells to format.

Here you can use the MOD function.
Type the formula

=MOD(ROW(B4),2)=0
Then select the format of your choice.
Finally, click OK.

Using Conditional Formatting

As I used the MOD(ROW(),2)=0 functions it will Highlight every 2nd row starting from the first.

Using Conditional Formatting
Now, select the first Highlighted row then hold the CTRL key and select the rest of Highlighted rows.

Selected Every other row

2. Using Highlight 

I. For ODD Rows

To Highlight the odd number of rows and then to select every other odd row first select the cell range that you want to Highlight and want to select.
Then, open the Home tab >> Go to Conditional Formatting >> now select New Rule

Using Highlight to select odd rows

It is going to pop up a dialog box. Then select Use a formula to determine which cells to format.
Here you can use the ISODD function. It will only Highlight the rows where the row number is odd.
Type the formula

=ISODD(ROW())

Now you can select the Format of your choice.
Finally, click OK.

Using Highlight to select odd rows

It will Highlight the ODD number of rows.

Using Highlight to select odd rows

Here to select every other odd row you can select the first Highlighted row then hold the CTRL key and select the rest of Highlighted rows.

Selected Every other odd row

II. For EVEN Rows

Like the odd number of rows, you also can Highlight the even number of rows.
To Highlight the even number of rows and then to select every other even row first select the cell range that you want to Highlight and select later.
First of open the Home tab >> Go to Conditional Formatting >> now select New Rule

Using Highlight to select odd rows

After selecting New Rule it will pop up a dialog box. From there select Use a formula to determine which cells to format.
Now you can use the ISEVEN function. It will only Highlight the rows where the row number is odd.
Write the formula

=ISEVEN(ROW())

After that select the Format of your choice.
At last, click OK.

Using Highlight to select even rows
Thus the EVEN rows will be Highlighted.

Using Highlight to select even rows

Hence to select every other even row, you can select the first Highlighted row then hold the CTRL key and select the rest of Highlighted rows.

Selected Every other even row

3. Using Keyboard and Mouse

The easiest and shortest way to select every other row is by using the keyboard and mouse.
First, select the row number then double click on the row number by the right side of the mouse.

Using Keyboard and Mouse

Then, it will select the Entire Row.

Using Keyboard and Mouse

Now, hold the CTRL key and select the rest of the rows of your choice using the right side of the mouse.

Using Keyboard and Mouse

4. Using Table Format

To select every other row in Excel you can use Table.
First, select a range of rows to insert Table.
After that, open the Insert tab >> then select Table.

Using Table Format

It will pop up a dialog box showing the selected range. From there, select My table has headers. Finally, click OK.

Using Table Format

Now the selected ranges will be converted into Table. Here every other row has different fill color to Highlight every other row.

Using Table Format

Thereupon to select every other row of your choice, you can select any Highlighted row then hold the CTRL key and select the rest of Highlighted rows you want to select.

Selected Every other row

5. Using Filter with Go To Special

To select every other row using Filter with Go To Special I added a new column in the dataset name Row Even/Odd. This column will show TRUE for even rows and False for odd rows.

Sample dataset with extra columnHere, you can use the ISEVEN function.
I selected the F4 cell to apply the formula.
The formula is

=ISEVEN(ROW())

Type the formula in the selected cell or in the formula bar.

Using Filter with Go To Special

Now, press ENTER.
It will show TRUE for row number 4 as it is an even number.

Using Filter with Go To Special

Last but not least you can use the Fill Handle to Autofit formula for the rest of the cells.

Using ISEVEN function

Now select the range where you want to apply the Filter.
I selected all the columns.
After that, open the Data tab >> select Filter

Using Filter with Go To Special

You also can use the CTRL+SHIFT+L keyboard shortcut.
Now the Filter will be applied to all columns.

Using Filter with Go To Special

Select the Row Odd/Even column to use Filter options. From there select the TRUE value to Filter then click OK.

Using Filter with Go To Special

Above all, all the column values will be Filtered where the value is TRUE.

Filtered Rows

Then, select the range where you want to apply Go To Special.
Here, open the Home tab >> from Editing group >> Go to Find & Select >> Finally, select Go To Special

Using Filter with Go To Special

Then, a dialog box will pop up. From there select the Visible cells only. Finally, click OK.

Using Filter with Go To Special

Here the visible cells are selected.
Again, open the Data tab >> select Filter.

Removed Filtered from rows

Now it will show the selected values along with all values by removing Filter.

Using Filter with Go To Special

6. Using VBA

First, open the Developer tab >> then select Visual Basic

Using VBA

Now, it will pop up a window for Microsoft Visual Basic for Applications.
Then, click on Insert >> then select Module.

Inserting Module in VBA

Now, a new Module will open.

New Module in VBA

After that, write the code to select every other row in the Module.

Sub SelectEveryOtherRow()
Dim userRange As Range
Dim OtherRowRange As Range
Dim rowCount, i As Long
Set userRange = Selection
rowCount = userRange.Rows.Count
With userRange
Set OtherRowRange = .Rows(1)
For i = 3 To rowCount Step 2
Set OtherRowRange = Union(OtherRowRange, .Rows(i))
Next i
End With
OtherRowRange.Select
End Sub

VBA code

Meanwhile, Save the code and go back to the worksheet.
First, select the range where you want to apply the VBA.
Then open the View tab >> From Macros >> select View Macro.

Using Macros to run VBA

It will pop up a dialog box. From there select the Macro name SelectEveryOtherRow.
Finally, click Run.

Using Macros to run VBA

Here every other row is selected from the first row.

Using Macros to run VBA

Practice

I have been given a sheet to practice the mentioned and explained ways in the workbook.

Practice Sheet

Conclusion

In this article, I’ve explained 6 ways to select every other row in Excel. These different approaches will help you to select every other row. Feel free to comment down below to give any kind of suggestions, ideas, and feedback.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo