You can use the following basic formula to select every other row in Excel:
=OFFSET($A$1:$B$1,(ROW()-1)*2,0)
This formula selects every other row (for columns A and B) in the current sheet starting from row 1.
The following examples show how to use this formula in practice.
Example: Select Every Other Row in Excel
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
We can use the following formula to select every other row in this dataset:
=OFFSET($A$1:$B$1,(ROW()-1)*2,0)
We will type this formula into cell D1 and then drag and fill it down to the remaining cells in column D:
Notice that every other row has been selected from the original dataset.
We can manually verify this by identifying ever other row in the original dataset:
We can see that every other row from the original dataset has indeed been selected.
Note: In this example we used $A$1:$B$1 within the OFFSET function to tell Excel that we’d like to select all cells in columns A and B, starting from the first row.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Sum Every Nth Row in Excel
How to Highlight Entire Row Based on Cell Value in Excel
How to Count Rows with Value in Excel
