How to Select Every Other Row in Excel: 6 Easy Methods

The following dataset is about the sales information of a certain tech shop. It has four columns: Sales Rep, Region, Product, and Sales. These columns show the total sales information for a particular product by a sales representative.

Method 1 – Using Conditional Formatting to Mark Selectable Cells

Steps:

Choose the B4:E13 cell range.
Open the Home tab.
Go to Conditional Formatting and select New Rule.

A dialog box will pop up.
Select Use a formula to determine which cells to format.
Use the following formula in the Format values where this formula is true box:

Type the formula

=MOD(ROW(B4),2)=0

Select the format of your choice.
Click OK.

The MOD(ROW(),2)=0 function will highlight every 2nd row starting from the first.

Select the first highlighted row then hold the CTRL key and select the other highlighted rows.

Method 2 – Using the Highlight Feature

Case 1 – EVEN Rows

Steps:

Select the cell range.
Open the Home tab.
Go to Conditional Formatting and select New Rule

A dialog box will be on the screen.
Select Use a formula to determine which cells to format.
Use the ISODD function:

=ISODD(ROW())

You can select the Format of your choice.
Click OK.

It will highlight odd rows.

Hold the CTRL key and select the highlighted rows.

Case 2 – EVEN Rows

Steps:

Select the cell range that you want to highlight.
Open the Home tab.
Go to Conditional Formatting and select New Rule.

A dialog box will pop up.
Choose Use a formula to determine which cells to format.
Insert the following formula in the box.

=ISEVEN(ROW())

Choose the Format of your choice.
Click OK.
The EVEN rows will be highlighted.

Hold the CTRL key and select the highlighted rows.

Method 3 – Manually Selecting Rows

Steps:

Select the row number.

It will select the Entire Row.

Hold the CTRL key and select the rest of the rows of your choice.

Method 4 – Utilizing the Table Format

Steps:

Select a range of rows to insert a Table.
Open the Insert tab and select Table.

A dialog box showing the selected range will pop up.
Select My table has headers.
Click OK.

The selected ranges will be converted into a Table.
Every other row has a different fill color to highlight every other row.

Manually select the rows while holding Ctrl.

Method 5 – Using a Filter with Go To Special

We added a new column in the dataset name Row Even/Odd. This column will show TRUE for even rows and FALSE for odd rows.

Steps:

Select the F4 cell and insert the following formula:

=ISEVEN(ROW())

Press Enter.
This will show TRUE for row number 4 as it is an even number.

Use the Fill Handle to AutoFill formula for the rest of the cells.

Select the range where you want to apply the Filter.
Select all the columns.
Open the Data tab and select Filter.

You also can use the CTRL+SHIFT+L keyboard shortcut.

The Filter will be applied to all columns.

Select the drop-down for the helper column header.
Select the TRUE value to Filter.
Click OK.

All the column values will be Filtered where the value is TRUE.

Select the range where you want to apply Go To Special.
Open the Home tab and go to Find & Select, then select Go To Special

A dialog box will be on the screen.
Select the Visible cells only.
Press OK.

The visible cells are selected.
Open the Data tab and select Filter.

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

Method 6 – Applying VBA

Steps:

Open the Developer tab and select Visual Basic.

A window for Microsoft Visual Basic for Applications will pop up.
Click on Insert and select Module.

A new Module will be opened.

Insert this 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