How to Select Every Other Row in Excel & Google Sheets

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

  1. Click on the row header to select the first row.

 

select-every other row select row

 

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

 

select every-other row select next row

 

  1. 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.

  1. 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

 

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

 

excel-copyrows convert to range

 

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

 

excel copyrows filter

 

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

 

excel copyrows filterbycolor

 

  1. 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.

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

 

select every other row conditional formatting

 

  1. 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.
  1. In the Fill tab, select the color to format each alternate row, and click OK.

 

select-every other row select color

 

  1. Click OK again to apply the formatting to your worksheet.

 

select every other formatted

 

  1. 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.

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

 

select every other gs conditional formatting

 

  1. 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

 

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

 

select every other gs create filter

 

  1. 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

 

See also: How to Display Data With Banded Rows