How to Copy Every Other Row in Excel & Google Sheets

This tutorial demonstrates how to copy every other row from a data range in Excel and Google Sheets.

 

 

When you’re working with data in Excel, you might have repeated or unwanted rows after each relevant row. There are a few ways to copy every other row to different range, duplicating only the relevant data.

Copy Every Other

The Fill Handle

One way to copy every other row is to use Excel’s AutoFill feature.

1. In a blank cell to the right of the rows you wish to copy, type in a formula that refers to the first cell in the range to be copied.

 

 

2. Drag the fill handle across the columns until all the information from the first row is displayed.

 

 

3. Highlight the cells as well as the row directly under the cells.

 

 

4. Using the fill handle, drag down with the mouse to autofill the range.

 

 

Since you selected a blank row below the cells you wanted copied down, every second row is copied rather than every row.

 

 

5. With the rows still highlighted, in the Ribbon, select Home > Editing > Sort & Filter to remove the blank rows in the selection.

 

 

Cell Format and Filter

Alternatively, you can use filtering to copy every other row.

1. Select the rows to copy. In the Ribbon, go to Home > Cells > Format as Table, and select the formatting you want.

 

 

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

 

 

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

 

 

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

 

 

5. Highlight the filtered data and in the Ribbon, select Home > Clipboard > Copy.

 

 

6. Move to the location where the data is required, and in the Ribbon, select Home > Clipboard > Paste to paste the rows selected.

 

 

Helper Column

A third way to copy every alternate row in Excel is to use a helper column. You can also use this to copy every n^th (3^rd, 4^th, etc.) row.

1. In a blank column adjacent to the rows to be copied, type TRUE in the first row and FALSE in the row below it.

 

 

2. Highlight the TRUE and FALSE cells, then drag down to the last row in the range.

 

 

3. Release the mouse to copy TRUE and FALSE down alternatively for each row.

 

 

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

 

 

5. Click the arrow next to the header of the new column. Then select either FALSE or TRUE. Click OK to filter the data.

 

 

6. Select the filtered data excluding the added “copy” column. Then in the Ribbon, select Home > Clipboard > Copy.

 

 

7. Move to the location where the data is required, and in the Ribbon, select Home > Clipboard > Paste to paste the rows selected.

 

 

Copying every other row in Excel can also be achieved by creating a macro using VBA.

Copy Every Other Row in Google Sheets

You can copy every other row in Google Sheets using the same three methods above.

For example, you can filter by color to show every other row.

 

 

Then copy and paste the filtered rows to a different location. When you clear the filter, you can see you’ve copied every other row.