Shamima Sultana
Today, we are going to show how to select every other row in Excel. While performing functions or formulas in multiple rows we may require selecting every other row. This allows users to perform different task by selecting their desired alternate rows. Here, we will select alternate rows by using different methods like the picture below.
Download to Practice
6 Ways to Select Every Other Row in Excel
In this article, we will show 6 handy ways 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.
1. Using Conditional Formatting to Mark Selectable Cells
In this example, we will use the Conditional Formatting feature to highlight the alternate rows.
Steps:
- Firstly. choose the B4:E13 cell range.
- Secondly, open the Home tab >> Go to Conditional Formatting >> then select New Rule.
- As a result, a dialog box will pop up.
- From there, select Use a formula to determine which cells to format.
- Thereafter, write down the following formula in the Format values where this formula is true box,
Type the formula
=MOD(ROW(B4),2)=0- Then select the format of your choice.
- Finally, click OK.
- Consequently, the MOD(ROW(),2)=0 functions will Highlight every 2nd row starting from the first.
- Now, select the first Highlighted row then hold the CTRL key and select the rest of Highlighted rows.
Read More: Excel Alternating Row Color with Conditional Formatting [Video]
2. Using Highlight Feature
In this method, we will highlight alternate rows using the Conditional Formatting option. We will use the ISODD and ISEVEN functions to separately select the odd and even rows.
I. For ODD Rows
In this instance, we will highlight only the odd rows using the Conditional Formatting option.
Steps:
- To begin with, 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
- As a result, a dialog box will be on the screen.
- From the box, 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()) - Next, you can select the Format of your choice.
- Finally, click OK.
- As a result, it will Highlight the ODD number of 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.
II. For EVEN Rows
Like the odd number of rows, you also can Highlight the even number of rows.
Steps:
- 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.
- Here, we will choose the B4:E13Â cell range.
- Then, open the Home tab >> Go to Conditional Formatting >> now select New Rule.
- After selecting New Rule, it will pop up a dialog box.
- From there, choose Use a formula to determine which cells to format.
- After that, you can use the ISEVEN function. It will only Highlight the rows where the row number is even.
Write the formula=ISEVEN(ROW()) - Now, choose the Format of your choice.
- At last, click OK.
Thus, the EVEN rows will be Highlighted.
Thus, the EVEN rows will be Highlighted.
- Finally, 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.
Read More: How to Highlight Every Other Row in Excel
3. Using Keyboard and Mouse Shortcut
The easiest and shortest way to select every other row is by using the keyboard and mouse.
Steps:
- First, select the row number then double click on the row number by the right side of the mouse.
- Then, it will select the Entire Row.
- Thereafter, hold the CTRL key and select the rest of the rows of your choice using the right side of the mouse.
Similar Readings
- How to Increase Excel Row Limit (Using Data Model)
- How to Expand or Collapse Rows with Plus Sign in Excel (4 Easy Methods)
- Group Rows with Plus Sign on Top in Excel
- How to Create Collapsible Rows in Excel (4 Methods)
- How to Group Rows in Excel Pivot Table (3 Ways)
4. Utilizing Table FormatÂ
To select every other row in Excel you can use Table.
Steps:
- First, select a range of rows to insert Table.
- After that, open the Insert tab >> then select Table.
- It will pop up a dialog box showing the selected range.
- From there, select My table has headers.
- Finally, click OK.
- After that, the selected ranges will be converted into Table.
- Here every other row has different fill color to Highlight every other row.
- 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.
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.
Steps:
- Firstly, I selected the F4 cell and type the following formula,
=ISEVEN(ROW())
- Now, press Enter.
- As a result, it will show TRUE for row number 4 as it is an even number.
- Last but not least you can use the Fill Handle to AutoFill formula for the rest of the cells.
- Thereafter, select the range where you want to apply the Filter.
- I selected all the columns.
- After that, open the Data tab >> select Filter.
You also can use the CTRL+SHIFT+L keyboard shortcut.
- Consequently, the Filter will be applied to all columns.
- Then, select the Row Odd/Even column to use Filter options.
- From there, select the TRUE value to Filter.
- Finally, click OK.
- Consequently, all the column values will be Filtered where the value is TRUE.
- After that, 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
- As a result, a dialog box will be on the screen.
- From there select the Visible cells only.
- Finally, press OK.
- Here, the visible cells are selected.
- Again, open the Data tab >> select Filter.
- Next, it will show the selected values along with all values by removing Filter.
6. Applying VBA
In this final method, we will use a simple VBA code to select every other row in Excel.
Steps:
- First, open the Developer tab >> then select Visual Basic.
- Consequently, it will pop up a window for Microsoft Visual Basic for Applications.
- Then, click on Insert >> then select Module.
- As a result, a new Module will be opened.
- 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
- Meanwhile, Save the code and go back to the worksheet.
- After that, First, select the range where you want to apply the VBA.
- Then open the View tab >> From Macros >> select View Macro.
- It will pop up a dialog box. From there select the Macro name SelectEveryOtherRow.
- Finally, click Run.
- As a result, every other row will be selected from the first row.
Practice
I have been given a sheet to practice the mentioned and explained ways in the workbook.
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.
