Selecting every other row in Excel could be useful when you want to manipulate alternate rows in your data set.
For example, you may want to highlight every other row, or you may want to delete or copy every other in your data set.
In this tutorial, we are going to go through three different ways of selecting every other row in Excel. Once selected, you can work with these selected rows accordingly.
Method #1: Select Every Other Row Manually
The easiest way to accomplish this task is first to select the row of interest, and then while holding down the CTRL key, manually select the other rows using your mouse.
We have the example dataset, as shown below, that lists some products and their corresponding prices.
You can see that this dataset is quite simple and only has a few rows.
The manual method works best with these kinds of datasets since it can be quite time-consuming if you have many rows of data.
Follow the steps below.
- To select the entire second row, click on the row label, which in this case is 2, using your mouse and the left-click button (the one highlighted in the image below).
- Now while holding the CTRL key on your keyboard, click on row 4 with your mouse to select this entire row in addition to the current selection.
- While still holding the CTRL key, use your mouse to click on row 6, to select the entire row in addition to the current selection.
And there you have it, this is the simplest way to select every other row. Once you have these rows selected, you can copy them, hide them, delete them, highlight them, or do whatever else you want.
In case you end up selecting any row by mistake and want to undo the selection, keep holding the Control key and then click on the selected row header again. This will unselect the row.
Note: You can use this method to select any non-contiguous range in Excel.
While this method is quite straightforward, it does have a huge limitation – it is time-consuming and can only work when you have a small data set. Personally, I wouldn’t recommend this method if you have more than 20 rows of data. Instead, use the other methods covered in this article.
Also read: How to Select Every Other Cell in Excel (Or Every Nth Cell)
Method #2: Select Every Other Row Using Helper Column, Filter, and Go To Special
While there is no inbuilt method to quickly select every other row in Excel, a good workaround would be to use the method covered in this section.
We can select every other row by using what’s known as a helper column, the Filter feature, and then Go To Special.
A helper column is an additional column that you can use to assist with adding insight into formulas or, in this case, facilitating the process of selection.
In our example below, we have a column showing student names in one column and whether the student intends to attend the school camp recorded in another column.
Follow the steps below.
- In cell C1, enter the text Helper Column.
- We will create our column by typing TRUE in cell C2 and FALSE in cell C3 as shown below.
- Select range C2:C3 and then drag the values down the column to see the two alternate values populating the column.
- Select the entire dataset.
- With the dataset selected, go to the Data Tab in the ribbon, and click on the Filter icon.
- You should see the following filters applied to the range.
- Select the filter on the Helper Column and choose the value TRUE and then click Ok.
- You should see the following.
- Select all the rows in the dataset except the headings, as shown below.
- With the rows selected, go to the Home Tab, and in the Editing Group, choose Find & Select. Select the Go To Special…option.
- In the Go to Special dialog Box, check the Visible cells only option and then click Ok.
- You should see the following (where only the visible cells are selected).
- Now check Select All on the Helper Column filter, so that both TRUE and FALSE are checked. Click Ok to see every other row selected.
This method is useful when you have many rows of data.
Once you are done with whatever you want to do with the alternate rows, you can delete the helper column or hide it.
In case you want to select every Nth row (say every third row or every fourth row), you can modify the content of the helper column. For example, to select every third row, enter FALSE, FALSE, and TRUE in the first three cells and then copy it down. This way, when you filter the cells that contain only TRUE, every third row will be filtered and made visible.
Note: Before making any changes to your original data set, it is always a good idea to keep a backup copy of the data. You can do that by creating a copy of your worksheet or your entire workbook.
Also read: How to Select Visible Cells Only in Excel?
Method #3: Select Every Other Row Using VBA
And the final way that I want to show you to select every other row in Excel is by using a simple VBA code.
While this method does have a few steps you need to take to set up the code, it could be useful if this is something you need to do on a regular basis.
Let me show you how this works using the dataset below.
In our dataset shown, we are measuring the performance of Sales reps, and I want to select every other row in this dataset.
Here are the steps to do this using a VBA code.
- Select range A2:D17 which is the entire dataset without the headers.
- Go to the Developer Tab, and in the Code Group, click on the Visual Basic option.
Alternatively, you can also use the keyboard shortcut ALT + F11
- Go to the Insert option and then click on Module. This will insert a new module for our workbook where we will put the VBA code
- Copy and paste the following VBA macro code into the module.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Select_Every_Other_Row()
Dim rng As Range
Dim theSelection As Range
Dim i As Integer
Set rng = Selection
Set theSelection = rng.Rows(2)
For i = 2 To rng.Rows.Count Step 2
Set theSelection = Union(theSelection, rng.Rows(i))
Next i
Application.Goto theSelection
End Sub- Press F5 to run the code and return to your Excel workbook. You should see the following.
As soon as you run the VBA code, it is going to go through your entire selected data set and select every other row.
The above VBA code uses a simple For Next loop that starts from the second row in the selected dataset and then selects every second row.
You can modify the above VBA code to select every third or every fourth, or every nth row in your data set.
Note: If you want to save this VBA code in your workbook so that you can reuse it in the future, you need to save your file as a macro-enabled file (with a .xlsm extension)
For example, if you want to select every third row in Excel, you can use the VBA code below:
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Select_Every_Other_Row()
Dim rng As Range
Dim theSelection As Range
Dim i As Integer
Set rng = Selection
Set theSelection = rng.Rows(3)
For i = 3 To rng.Rows.Count Step 3
Set theSelection = Union(theSelection, rng.Rows(i))
Next i
Application.Goto theSelection
End SubIn this tutorial, I’ve covered three methods you can use to quickly select alternate rows in Excel.
If you have a small data set, it is best that you do this manually. but in case you have large data sets, you can use the filter technique it uses a helper column or the VBA method.
I hope you found this Excel tutorial useful. Let me know your thoughts in the comments section.
Other Excel articles you may also like:
- How to Select Non-adjacent Cells in Excel?
- How to Select Rows with Specific Text in Excel
- Select Row (or Rows) in Excel (Shortcut)
- How to Select Multiple Items from a Drop Down in Excel?
- How to Select Multiple Rows in Excel?
- How to Select Alternate Columns in Excel (or every Nth Column)
- How to Set a Row to Print on Every Page in Excel
