Many times, I have a need to select every other row in Excel so that I can highlight or delete them.
This is commonly the case when I download my data from databases or the web, and it adds an extra row after every useful row that I need to remove.
Another useful situation can be when you want to select every other row and then copy and paste them into some other location in your worksheet.
While there is no inbuilt functionality in Excel that allows you to select every other row, in this tutorial, I am going to show you a couple of workarounds to do this.
Note: In many cases, the reason people want to select every other row in Excel is to highlight them. If you are in the same situation, you should know that you do not need to select the row to highlight it. You can use conditional formatting or the inbuilt formatting options in an Excel table to automatically highlight every other row in Excel. You can read my detailed tutorial on this topic that covers the exact steps you need to take to get this done.
When working with data in Excel, you may often need to select every other row. For example you might want to apply formatting or formulas to alternating rows. Or maybe you need to copy specific rows to another location.
Manually selecting each second row can be tedious, especially in large tables. Fortunately, there are faster ways to select every other row in Excel using keyboard shortcuts and formula-based techniques
In this article, I’ll explain three easy methods to select every other row in Excel
- Using the Ctrl key
- With the Go To Special dialog box
- Using a formula
I’ll also provide examples demonstrating each approach. Follow along to learn how to quickly select every second, third or nth row in Excel.
Method 1: Use the Ctrl Key to Select Every Other Row
The quickest way to select every other row in Excel is by using the Ctrl key shortcut. This takes just a few clicks:
-
Click on the first row you want to select, and then press and hold the Ctrl key on your keyboard.
-
While holding the Ctrl key, click on every other row or on every nth row.
-
Release the Ctrl key, and you will have selected the desired rows.
For example, to select every second row:
- Select row 1.
- Hold Ctrl and select rows 3, 5, 7, etc.
- Let go of Ctrl.
Rows 1, 3, 5, 7 will be highlighted.
This uses the fact that Ctrl lets you make multiple non-contiguous selections. You can use this to quickly select large ranges of every nth row.
Method 2: Use Go To Special to Select Every Other Row
Excel also provides a built-in tool for selecting every nth row, found in the Go To Special dialog box:
- Select any cell within your data set.
- On the Home tab, click Find & Select > Go To Special.
- In the Go To Special dialog, select Blanks under Rows/Columns.
- Change the Selection type to Every 2nd row, Every 3rd row, etc. depending on n.
- Click OK.
This will select all specified rows, skipping blanks. For example, choosing Every 2nd row selects all even or uneven rows (depending on starting cell).
The Go To Special approach is faster than manual selection for large datasets. You can instantly select every nth row, without having to Ctrl + click each one.
Method 3: Use a Formula to Select Every Other Row
You can also leverage Excel formulas to generate a selection. This is helpful if you need to repeatedly alternate row selections.
Here are two options:
-
Use the MOD function:
-
In column A, enter row numbers from 1 to the max rows needed.
-
In B1 enter: =MOD(A1,2)=0
-
Copy down column B.
-
Filter column B to “TRUE” and select visible rows.
-
-
Use INDEX formula matching:
-
Enter header in A1 as “Select”.
-
In A2 enter: =IF(INDEX(1:1,MOD(ROW(),2)+1)=””,TRUE,FALSE)
-
Copy down column A.
-
Filter for “TRUE” and select visible.
-
Both formulas essentially test if the row number is even or odd, returning TRUE when it meets the desired condition.
The benefit of these approaches is you can reuse the column whenever you need to re-select every nth row.
Summary
Being able to swiftly select every other row in Excel is useful for many tasks. Here are some examples:
-
Apply formatting: Select every nth row to format with alternate colors for readability.
-
Add formulas: Build summaries or logic checks on specific row sets.
-
Copy subsets of data: Quickly copy every other row to another location.
-
Analyze trends: Compare metrics or changes between alternating rows.
Hopefully you found these techniques helpful for selecting every second, third or nth row in Excel. Use the Ctrl key for one-time manual selection. Go To Special and formulas provide reusable solutions.
Using the Helper Column & Filter Method
One simple workaround to select every other row in Excel is by adding a helper column and then using this helper column to filter the data so that only those rows are visible that we need to select.
Let me show you how it works with an example.
Below I have a data set where I want to select every other row.
Here are the steps to do this using a helper column and the filter functionality:
- Enter the text ‘Helper’ in cell G1. This would become our helper column, and the text ‘Helper’ becomes the header for that column.
- In cell G2, enter the below formula
Copy this formula for all the cells in the column.
This formula uses the ISODD function along with the ROW function to give TRUE if the row number is odd and FALSE if the row number is even.
- Select any cell in the dataset and then click on the Data tab.
- Click on the Filter icon in the Sort & Filter group. This will add filter icons to all the headers in your data set. You can also use the keyboard shortcut CONTROL + SHIFT + L to apply a filter to the header row.
- Click on the filter icon in the helper row column header.
- Uncheck the FALSE option and click OK. This will filter your data set, and you will only see those rows where the value will helper column is TRUE (which is the case for every alternate row)
- Select the dataset that is visible after filtering.
When you select the cells after the data set has been filtered, it only selects the cells that are visible.
So anything you do to this data set would not impact the cells that are not visible.
For example, if you highlight all these visible cells and then you remove the filter, Only the alternate rows would have been highlighted.
Select Every Other Row Manually
If you have a small data set, the fastest method to select every other row in Excel would be to do it manually.
To do this, you need to use both your keyboard as well as your mouse/trackpad.
Below, I have a data set where I want to select every other row (i.e., alternate rows).
Below are the steps to do this:
- Press the Control key on your keyboard and keep holding it
- Click on the row header of the row that you want to select (in this example, it would be row number 3, which is the second record in our dataset)
- Continue to hold the Control key and then click the row header of all the rows that you want to select
- Once done with the selection, leave the control key
This method is suitable only when you have a small data set, and manually selecting every other row won’t be too time-consuming.
Personally, I prefer this method when I have a data set of up to 10 to 12 rows. For anything longer than that, you can use the other methods covered in this article.
3 Easy Ways to Select Every Other Row in Excel (Select Alternate Rows)
How to select every nth cell in Excel?
Note: If you want every third, fourth or nth cell selected, simply place a number in the first row and a letter in every n-1 rows below it. This will ensure that there is a number only every nth row. After that, repeat the same procedure as shown above. Also read: How to Select Every Other Row (Alternate Row) in Excel?
How to select a row in Excel?
Click on the first row you want to select, and then press and hold the Ctrl key on your keyboard. While holding the Ctrl key, click on every other row or on every nth row. Release the Ctrl key, and you will have selected the desired rows. This method works well for small tables, where the number of rows is manageable.
How to select alternating rows in Excel?
While holding the “Ctrl” key, click on the number of every other row you want to select. Holding down the “Ctrl” key allows you to select multiple rows that are not adjacent to each other. This step is crucial for selecting alternating rows. After selecting all the rows you need, you can release the “Ctrl” key.
How do I select more than one row in Excel?
To select every other row using a keyboard shortcut, first, highlight the first row you want to select. Then, hold down the ‘Shift’ key and press the ‘Arrow Down’ key to select every other row. 4. Can I select more than every other row? Yes.
