how to delete every other row in excel

This article will demonstrate how to delete every other row from a data range in Excel and Google Sheets.

delete alternate rows 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 delete every other row, keeping only the relevant data.

Delete Every Other Row With Cell Format and Filter

Filtering is one way to delete 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 press DELETE on the keyboard.

delete-alternate rows select rows

6. Remove the filter to show the entire table and then highlight the remaining data.

delete-alternate rows remove filter

7. In the Ribbon, select Home > Editing > Sort and Filter > Smallest to Largest.

delete alternate rows sort

The blank rows will then be removed from in between the remaining data.

delete alternate rows sorted

Delete Every Other Row With a Helper Column

Another 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 data, type TRUE in the first row and FALSE in the row below it.

delete alternate rows truefalse

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

delete-alternate rows copydown

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

delete alternate rows copied down

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.

delete alternate rows filter

6. Highlight the filtered data and press DELETE on the keyboard.

delete alternate rows hightlight

7. Remove the filter to show the entire table. Then remove the helper column and highlight the remaining data.

delete alternate rows select data

8. In the Ribbon, select Home > Editing > Sort and Filter > Smallest to Largest.

delete alternate rows smallest to largest

The blank rows will then be removed from in between the remaining data.

delete alternate rows data-sorted

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

How to Delete Every Other Row in Google Sheets

You can delete every other row in Google Sheets using either of the methods above in exactly the same way.