How to Select Every Other Row in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on November 19, 2022

This tutorial demonstrates how to select every other row in Excel and Google Sheets.

select every other row intro

When you’re working with data in Excel, you have a need to select the data in every other row. There are a few ways to select every other row.

Select Every Other Row

Keyboard and Mouse

Click on the row header to select the first row.

select-every other row select row

Holding down the CTRL key on the keyboard, click in the row header of the second row.

select every-other row select next row

With the CTRL key still held down, click on every other row header until all the rows you need are selected.

select every other row selected

Cell Format and Filter

You can use Format as Table and filtering to select every other row.

Highlight the cells whose rows you wish to select. In the Ribbon, go to Home > Cells > Format as Table and select the formatting.

excel-copy alternate format table

Then, to remove the table, in the Ribbon, select Table > Convert to Range.

excel-copyrows convert to range

Next, in the Ribbon, select Home > Editing > Sort & Filter > Filter.

excel copyrows filter

Click the drop-down arrow on any of the column headings and select Filter by Color.

excel copyrows filterbycolor

Every second row is now filtered for. Select these rows.

select every other row filter

Conditional Formatting and Filter

You can use Conditional Formatting and Filtering to select every other row.

Highlight the cells whose rows you wish to select. In the Ribbon, go to Home > Cells >Conditional Formatting.

select every other row conditional formatting

Select (1) Use a formula to determine which cells to format and then (2) enter the formula:

=MOD(ROW(B3),2)=0

Then (3) click on Format…

select every other row enter formula

The ROW Function returns the row number of the cell.
The MOD Function, here, tests whether a number is even or odd by returning the remainder of the ROW formula divided by 2.

In the Fill tab, select the color to format each alternate row, and click OK.

select-every other row select color

Click OK again to apply the formatting to your worksheet.

select every other formatted

As with Format as a Table, you can now Filter by Color to select every alternate row.

select every other row filter by color

All the rows with the yellow cells are shown.

select every row filtered

Select Every Other Row in Google Sheets

As with Excel, you can select every other row in a Google Sheet by selecting the first row and then holding down the CTRL key and selecting each alternate row thereafter.

select every other row gs control key

You can also use conditional formatting and filtering to select every other row.

Highlight the cells whose rows you wish to select. In the Menu, select Format > Conditional Formatting.

select every other gs conditional formatting

Select (1) Custom formula is from the Format cells if… list and then (2) type in the following formula:

=MOD(ROW(B3),2)=0

Then (3) select the color you wish every other row in the selection to be formatted with.

select-every other gs format rules

Click Done to format the sheet.
Then create a filter to filter the rows by color.
In the Menu, select Data > Create a filter.

select every other gs create filter

In the filter drop-down list at the top of one of the columns, select Filter by Color > Fill Color and then select which color to filter on.

select every other gs filter by color

Every other row is now shown.

select every other gs filtered