How to Select Every Other Row in Excel

Selecting adjacent rows is easy with keyboard shortcuts Shift + → or Shift + Ctrl + → but choosing alternate rows or rows at regular intervals (such as selecting odd or even rows or every nth row) can be more complex.

If your dataset is small, you can manually select the non-adjacent rows by pressing the Ctrl key while clicking the row number in the leftmost column.

But this isn’t feasible when dealing with large datasets. Also, there is a slight chance that you might miss some rows.

Fortunately, there are various workarounds to significantly speed up this process, which includes using table format and VBA code. 

Using VBA

This is perhaps the most efficient and quickest way to select rows. And, it works even if you have a large dataset with thousands of rows/records

By running the VBA code, you can automatically select every second, third, or any nth row. 

1. Select all the rows.
   
2. Right-click on the worksheet and select View code.
   
3. Paste the following VBA code in the new window.
   

Sub SelectEveryNthRow()
Dim RowRange As Range
Dim RowSelect As Range
Dim rowNumber As Integer

rowNumber = 3 'Replace 3 with every nth row you want to select

Set RowRange = Selection
Set RowSelect = RowRange.Rows(rowNumber)

For i = rowNumber To RowRange.Rows.Count Step rowNumber
Set RowSelect = Union(RowSelect, RowRange.Rows(i))
Next i

Application.Goto RowSelect

End Sub

4. Press F5 or click the Run icon.
   

Using Table Format

This method is particularly useful if you want to select every odd/even row. Here, we take the advantage of the fact that a table design applies two different colors on alternate banded rows.

1. Select all the cells containing the required data.
2. Press Ctrl + T to convert them into a table format. Enable the My table has headers if your data already contains a header.
   
3. Make sure you use a table design that has banded rows with alternate colors. Otherwise, choose the appropriate one in the Table Styles section under the Table Design tab.
4. Now, select the Table Design tab and click Convert to Range inside the Tools section and click Yes on the next prompt.
   
5. Then, under the Data tab, select Filter.
   
6. Click the dropdown arrow next to one of the headers.
7. Select Filter by color and choose a row color or No fill to filter out the alternate cells.
   
8. Now, choose the odd/even cells based on which color appears on banded rows of the table.

Using the SEQUENCE Function

The SEQUENCE function generates a list of sequential numbers like 1,2,3,4…., which we can use as a flag to filter out every alternating row at the nth position. Then, we just need to select them.

Syntax:
=SEQUENCE(rows, columns, start, step)

Where,

• Rows: Number of rows on which you want to generate a sequential list of numbers (1,2,3,…)
• Columns: Number of columns you want to include while generating the list (since we don’t need it for this particular case, we can skip it.)
• Start: starting number of the sequence list
• Step: number by which the next number is incremented

Alternatively, you can highlight alternate rows using conditional formatting. Then, you can filter and select them using Step 2.

Step 1: Generate the Numbers 

1. Select a cell that’s part of the dataset. Then, use the shortcut Ctrl + Shift + Space to select all the adjacent rows of the dataset.
2. Create a helper column next to your last column. Here, we are using the “Flag” column.
3. Under the new column, enter the following formula.
   =SEQUENCE(3,,1,1)
   

4. Select the generated values and drag down the fill handle until the last row.
   

Step 2: Use the Filter Option to Select Rows

1. Now, select the helper column header.
2. Then, click Filter under the Data tab. You can find it inside Sort & Filter section.
   
3. Click the dropdown icon next to the header and unselect the Select All checkbox.
   
4. Now, only enable the checkbox of the number you want to filter. To select every 3rd row, select only the checkbox next to 3 and click OK.
   
5. Now, select the filtered rows.
   

Using the Go to Tool

This method is specifically applicable if your dataset contains empty rows recurring at a specific position. 

1. Select all the cells (s) containing the dataset.
2. Press Ctrl + G.
3. On the next prompt, click the Special button.
   
4. Then, choose the Blanks option.
   
5. Once all the alternating blank rows are selected, right-click and select the Delete > Entire  Row option.
   

Related Questions

How to Select Every Other Column in Excel?

Selecting alternate columns is similar to selecting rows. 

1. First, select the rows in your dataset and copy (Ctrl + C) them. 
2. Select a cell where you want to paste them and press Ctrl + Alt + V and E. Alternatively, right-click and select Transpose under the Paste options.
   
3. Use one of the methods to select alternating rows and transpose them.