See all How-To Articles

How to Copy Every Other Row in Excel & Google Sheets

This tutorial will demonstrate 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 Row With 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 of cells to be copied.

 

excel copy enter formula

 

2. Drag the fill handle across the columns until all the information form the required row is displayed.

 

excel copyrows drag

 

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

 

excel-copyrows highlight down

 

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

 

excel copyrows drag down

 

As you had selected a blank row below the cells you wanted copied down, every second row will be copied rather than every row.

 

excel copyrows release drag

 

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

Copy Every Other Row With 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 required.

 

excel-copy alternate format table

 

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

 

excel-copyrows convert to range

 

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

 

excel copyrows filter

 

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

 

excel copyrows filterbycolor

 

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

 

excel copyrows copy

 

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

 

Copy Every Other Row With a 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

 

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

 

excel copyrows truefalse

 

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

 

excel-copyrows truefalse copydown

 

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

 

excel copyrows 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.

 

excel copyrows truefalse filter

 

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

 

excel-copyrows truefalse copydata

 

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

How to Copy Every Other Row in Google Sheets

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