How to select every second row in an Excel spreadsheet

The problem of selecting every second row is not that rare. There are many situations where this might be needed. For example, when creating a second table based on the data of the first, when every second row is copied from the main table and inserted into the newly created table. There is no special function in Excel that does this. But still, there are several simple ways to highlight every second row in a table.
Select second lines manually
With filters
With VBA
With add-ons

Select second lines manually

Sometimes the manual way is the fastest. If the table is small, you can quickly select every second row in it manually.

1. Press and hold the Ctrl key until the row selection process is complete.
2. Click with the left mouse button on the heading of the topmost selected line.
3. Similarly, select all the second rows of the table without releasing the Ctrl key
4. Release the Ctrl key.

This is the simplest way and is effective for small tables. In other cases, you will have to use more complicated methods.

With filters

In order to use this method, an empty auxiliary column is added to the table, and then, using the data in this column, rows are filtered. To illustrate this method, it is enough to analyze an example of its use.

1. Add an extra empty column at the end of the table.
2. In the first cell of the column, enter the formula = ISODD (ROW())
3. Copy the formula to the rest of the cells in the column by dragging the lower right corner of the cell with the formula.
4. As you can see from the figure, the ISODD function in combination with the ROW function returns TRUE for odd rows and FALSE for even rows.
5. Click on any of the cells in the column and go to the “Data” tab in the Excel main menu
6. Click on the “Filter” button. This will display the filter icon in all column headings.
7. Click on the filter icon in the auxiliary line header.
8. In the menu that opens, uncheck the FALSE option.
9. Click OK. As a result, only the second rows will be visible in the table.
10. Select the remaining cells.
11. Remove the filter by clicking on the “Filter” button in the Excel menu of the “Data” tab. Only the second cells will be selected.

The filtered data can be copied to another table if necessary. After processing the data, the auxiliary column can be deleted.

With VBA

If you often need to select every second row in tables, it is best to automate this process using a macro. How to make a macro and use it?

1. Open the table.
2. Go to Developer using the Excel menu or using the keyboard shortcut Alt+F11. A VBA window will open
3. Press the “Insert” button and select the “Module” item in the menu that opens.
4. Paste the code:
   Sub SelectEveryOtherRow()
   Dim MyRange As Range
   Dim RowSelect As Range
   Dim i As Integer
   Set MyRange = Selection
   Set RowSelect = MyRange.Rows(3)
   For i = 3 To MyRange.Rows.Count Step 2
   Set RowSelect = Union(RowSelect, MyRange.Rows(i))
   Next i
   Application.Goto RowSelect
   End Sub
   
5. Close the Developer window.
6. Select a table.
7. From the Excel main menu, go to the “View” tab.
8. Click the Macros button and select Macros.
9. In the window that opens, select the macro you just created and click the “Run” button.

As a result of these actions, all second rows in the table will be selected. For convenience, you can add this macro to the Quick Access Toolbar.

With add-ons

This method can be used if for some reason it is not possible to apply the macros from the previous paragraph. On the Internet, you can find many programs and add-ons that highlight given strings. However, they are often paid.

When working with important Excel documents, do not forget to encrypt them so that no one except you has access to them. Do not be afraid to lose the password to such files. There are many programs that will help you Excel password recovery.