6 Ways to Select Every Other Row in Excel

In Excel, we can easily select consecutive rows or columns. However, selecting every other row only follows a variety of ways. So, we will demonstrate six simple methods to select every other row in excel.

📁 Download Excel File

Download the Excel file used for the demonstration from the link below.

Learn to Select Every Other Row in Excel with These 6 Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have a dataset containing Salesperson’s name, Region, Product Name, and Quantity. We will show you how to select every other row using this dataset using these six simple methods in Excel.

This method will show how to select every other row utilizing Keyboard and Mouse. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select an entire row by double-clicking on the row number on the left side of the sheet. We are selecting row number 6.

• Next, press the Ctrl button and, holding on to it, click on the row number you want to select using the mouse. Similarly, you can select every other row according to your need by clicking on row numbers while pressing Ctrl.

This method will demonstrate selecting every other row by applying Conditional Formatting. To apply the condition, we used ISEVEN and  ROW functions since ISEVEN function returns TRUE if the number is even and FALSE if odd, and ROW function returns the row number of the cell. Now, let us have a look at the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range B6:E11. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

• Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true”. Choose the format according to your desire and press OK.

• Now, we will have the rows formatted according to our needs. So, select the first row manually, and then by pressing Ctrl, click on the row and have the rows selected.

Now, we will highlight the rows for selecting every other row. We can use this for odd and even numbered rows.

This method will select every other row by highlighting the even rows in Excel. Let us go through the steps. To highlight the rows, we used ISEVEN and ROW functions since ISEVEN function returns TRUE if the number is even and FALSE if odd and ROW function returns the row number of the cell. Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the range B6:E11. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

• Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true”. Choose the format according to your desire and press OK.

• Now, we will have the even rows highlighted. Thus, manually select the first row before selecting the subsequent even rows by clicking on the row while holding down the Ctrl.

This method will select every other row by highlighting the odd rows in Excel. Let us go through the steps. To apply the condition, we used ISODD and ROW functions since ISODD function returns TRUE if the number is odd and FALSE if even, and ROW function returns the row number of the cell. Now, let us have a look at the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the range B6:E11. Then, select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

• Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type and enter the formula in “Format values where this formula is true.” Choose the format according to your desire and press OK.

• After that, we will have the odd rows highlighted. Thus, manually select the first row before selecting the subsequent odd rows by clicking on the row while holding down the Ctrl.

This method will format the data as a Table to select every other row. Let us have a look at the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range B5:E11. Then, select Table from the Tables option of the Insert tab in the Ribbon.

• Next, tick on My table has headers and press OK.

• Now, select the first row manually, and then by pressing Ctrl, click on the row and have the rows selected.

This method will employ Filter and Go To Special to select every other row. We will filter the even rows using the Filter option. For filtering, we used ISEVEN and ROW functions since ISEVEN function returns TRUE if the number is even and FALSE if odd and ROW function returns the row number of the cell. Thus, we added another row into our dataset named Even, where the Filter option will return whether the row is even or odd. Let us have a look at the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell F6 and insert the formula.

• Then, drag or double-click on the Fill Handle.

• After that, select the range B5:E11. Then, select the Filter option from the Data tab in the Ribbon.

• Next, on the column, Even click on the drop-down sign and select TRUE from the options.

• Now, only even rows will be visible. Select the range visible and go to the Home from the Editing group, click on Find & Select and select Go To Special.

• After that, a Go To special named box will pop up. Select Visible cells only and press OK.

• Now, our desired cells are already selected. Thus, click on the drop-down sign of the Even column and click on (Select All).

• Finally, we will have every other row selected.

This method will demonstrate selecting every other row in Excel using Microsoft Visual Basic Application (VBA).

Also, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select Visual Basic from the code group by going to the Developer tab.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

• Enter the code and press F5.

Sub SelectingEveryOtherRow()

Dim aRange As Range

Dim Row As Range

Dim rowCount, i As Long

Set aRange = Selection

rowCount = aRange.Rows.Count

With aRange

Set Row = .Rows(1)

For i = 3 To rowCount Step 2

Set Row = Union(Row, .Rows(i))

Next i

End With

Row.Select

End Sub

• Now, select the range B6:E11, and press Alt+F8. Then, select SelectingEveryOtherRow and then click on Run.

• Finally, we will have every other row selected.

How to Delete Every Other Row in Excel

This method will demonstrate how to delete every other row in Excel using Microsoft Visual Basic Application (VBA).

Also, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, go to the Developer tab and select Visual Basic from the code group.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

• Enter the code and press F5.

Sub Deleting_Every_Other_Row()

Dim bRange As Range
Set bRange = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)

For RowCount = bRange.Rows.Count To 1 Step -2
If RowCount Mod 2 = 0 Then
bRange.Rows(RowCount).Delete
End If
Next RowCount

End Sub

• Now, select Deleting_Every_Other_Row and then click on Run.

• Select the range B6:E11 and press OK.

• Finally, we will have every other row deleted.

📝 Takeaways from This Article

📌  This article demonstrated 6 methods to select every other row in Excel.

📌  In the first approach, we used Keyboard and Mouse for selecting every other row in Excel.

📌  Then, we used Conditional Formatting to select every other row in Excel.

📌  Next, we utilized the Highlight option for selecting every even or odd row in Excel.

📌  After that, we formatted the dataset as a Table for selecting every even or odd row in Excel.

📌  Then, we applied Filter and Go to Special for selecting every other row in Excel.

📌  Finally, we embedded VBA code to select every other row in Excel.

Conclusion

This article has demonstrated 6 ways to select every other row in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.