How to Select Every Other Row in Excel: 4 Quick Methods

Learning how to select every other row in Excel is a valuable skill for any spreadsheet user. Whether you need to remove duplicate entries, apply specific formatting to highlight data, or simply make large datasets easier to read, knowing this trick can save you significant time.

However, many users find that there is no obvious, single-click button to perform this action. If you have been struggling to manage non-contiguous data efficiently, this guide will walk you through four simple and effective ways to get the job done.

1. Manual Selection Using the Ctrl Key

If you are working with a small dataset, the most straightforward approach is to use your keyboard’s modifier keys.

Simply click on the row number to highlight an entire row. Then, hold down the Ctrl key on your keyboard while clicking the row numbers of every other row you wish to include. This technique creates what is known in Excel as a “non-contiguous range.”

While this method is easy to learn, it is not practical for large datasets, as it is time-consuming and prone to human error. For longer lists, the automated methods below are much more efficient.

2. Using Tables for Banded Formatting

If your primary goal is to improve the readability of your data by visually separating every other row, using an Excel Table is the best approach.

1. Click any cell within your data range.
2. Navigate to the Insert tab and select Table.
3. Excel will automatically apply a default style featuring “Banded Rows,” which automatically highlights every other row.

You can easily toggle this effect on or off by going to the Table Design tab and checking or unchecking the Banded Rows box. You can also customize the colors by choosing a different style from the Table Styles gallery.

3. The Helper Column and Go-To Special Method

When you need to perform an action on alternating rows—such as deleting them—the “Helper Column” method is highly reliable.

1. Create a new column next to your data.
2. Type “1” in the first row and “2” in the second row. Select both cells and drag the fill handle down to the bottom of your data range. If you hold the Ctrl key while dragging, Excel will automatically repeat the 1 and 2 sequence.
3. Highlight the entire helper column.
4. Go to the Home tab, click Find & Select, and choose Go-To Special.
5. Select Column Differences. This will highlight all cells that do not match the active cell.

Once the rows are selected, you can right-click any of them and choose Delete to remove the entire row from your dataset.

4. Using Filters to Select Alternating Rows

If you need to select the entire row based on an alternating pattern, Excel’s Filter function is a powerful tool.

1. Prepare a helper column with alternating values (1 and 2), as described in the previous method.
2. Select your entire data range, go to the Data tab, and click Filter.
3. Use the filter drop-down on your helper column to display only the rows marked “1.”
4. Select the visible range, then go to Go-To Special and select Visible cells only. This ensures you are not accidentally selecting hidden rows.
5. Clear the filter for that specific column to keep your selection active.

Bonus: Automate Selection with VBA

For power users who need to perform this task frequently, a simple VBA macro can automate the entire process.

1. Select the range of rows you want to work with.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module and paste the following code:

Sub AlternateRows() 
    Dim Rng As Range 
    r_start = Selection.Row 
    r_end = Selection.Rows.Count + r_start - 1 
    Set Rng = Rows(r_start) 
    r_start = r_start + 2 
    For r = r_start To r_end Step 2 
        Set Rng = Union(Rng, Rows(r)) 
    Next r 
    Rng.Select 
End Sub

Once the code is pasted, you can run the macro from the View > Macros menu. As with any VBA script, it is best practice to back up your file before running new macros.

By mastering these techniques, you can handle non-contiguous rows with confidence and speed. Choose the method that best fits your workflow—whether it is a simple format change or a complex data cleanup task—and optimize your productivity in Excel today.