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.

 

excel copy alternate intro

 

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.

 

excel copy enter formula

 

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

 

excel copyrows drag

 

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

 

excel-copyrows highlight down

 

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

 

excel copyrows drag down

 

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

 

excel copyrows release drag

 

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

 

excel copy rows sort

 

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.

 

excel-copy alternate format table

 

  1. Then 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. Highlight the filtered data and in the Ribbon, select Home > Clipboard > Copy.

 

excel copyrows copy

 

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

 

excel copyrows pasterows

 

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 nth (3rd, 4th, 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.

 

excel copyrows truefalse insert

 

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

 

excel copyrows truefalse

 

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

 

excel-copyrows truefalse copydown

 

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

 

excel copyrows filter

 

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

 

excel copyrows truefalse filter

 

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

 

excel-copyrows truefalse copydata

 

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

 

excel copyrows truefalse pasterows

 

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.

 

copy every other row filter

 

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.

 

copy every other row show