How to☝️ Delete Every Other Row in Excel (or Every Nth Row)

At times while working in Excel, you may find the need to delete every other row, or every nth row (3rd, 4th, 5th, etc.). The good news is that Excel provides the means to do just that. This feature can help you get rid of unnecessary rows in your sheet, which can be especially helpful when performing certain calculations.

Read on to find out how to get rid of certain rows so you can work with ease in Excel.

How to Delete Every Other Row

To delete every second row, start by creating an additional column so you can sort the rows into categories of even and odd numbers.

This step-by-step guide will show you how to do that:

1. Click on a cell (in the top row) to create the additional column (B1) that will be used to sort the rows.

2. Type a name for the column in that top cell (“Additional section”).

3. Select the next cell down (B2) and go to the Formula Bar to input the =MOD(ROW(),2) formula.

4. Drag the cursor from the corner of that cell to copy the formula into the cells below it, applying it to the entire original list.

=MOD(ROW(),2) formula

As easy as pie! Your rows have been alternately labeled as a 0 or a 1. Let’s keep the ball rolling.

5. Select all rows.

6. Navigate to the Home tab.

7. Click Sort & Filter.

8. Choose the Filter option.

Filter in Excel

9. Сlick on the small arrow next to the Additional section cell to sort by that column.

10. Tick the box next to the data you want to delete (either that which is marked with a 0 or that which is marked with a 1).

11. Click OK.

Additional section

Now you can see the rows you intend to delete.

12. Select these rows in your sheet.

13. Right-click the mouse button on these selected rows.

14. Choose Delete Row.

Delete Row option

15. Navigate to the Home tab.

16. Go to Sort & Filter.

17. Select the Filter option to remove the filter.

The removing the filter

Just like that, you have successfully deleted the rows you do not need.

Excel indicators

Related Article: How to Stop Excel from Rounding Numbers

How to Delete Every Nth Row

You already know how to delete even or odd rows in Excel. Let’s now take a look at how to delete every nth row. The process is very similar.

1. Click on the first cell of the additional column (C1) that will be used to sort the rows.

2. Type a name for the column.

3. Select the next cell down (C2) and go to the Formula Bar to input the =MOD(ROW()-m, n) formula, where…

  • “m” is the row number with the “minus 1” indicator, which you want to delete, and
  • “n” is the nth row.

4. Drag the cursor from the corner of the first cell down to the last cell in the list to copy the formula into each cell.

=MOD(ROW()-m, n) formula

There you go! Let’s continue.

5. Select the data.

6. Choose Home.

7. Pick the Sort & Filter option.

8. Select Filter.

Sort & Filter option

9. Сlick on the small arrow next to the “Additional section” cell to filter according to that column.

10. Tick the box next to the data you want to delete.

11. Click OK.

Additional section in Excel

12. Now that the data has been filtered to show those particular rows, select the data.

13. Right-click the mouse button on the selected data.

14. Choose Delete Row.

Delete Row

15. Go to the Home tab.

16. Navigate to Sort & Filter.

17. Click on the Filter button again to cancel the function.

Filter option in Excel

That’s all. Easy as ABC! Now you can easily sort and remove rows from your Excel sheet as needed.

Indicators

If you’re having a hard time following the steps in this tutorial, chances are, it has to do with Excel filter not working correctly. We created a separate guide to help you troubleshoot the issue.

More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X