Microsoft

Quickly delete every nth row in an Excel worksheet

Combine Excel's Mod() function and a simple filter to delete unwanted rows.

Most likely, you sometimes delete an empty row from a worksheet.  And if you have to delete several rows, you might use a filter to automate the task. First, you determine which column best represents the entire row. Next, you apply an AutoFilter that selects all the rows where that particular column is blank. Then, you delete the selected rows. You can expand this simple technique to delete every nth row in the sheet, but it takes a bit more work. You'll need two new columns. In one column, use AutoFill to sequentially number the rows, starting with the number 1. In the first row of the second column, enter the following formula =MOD(firstcellinsequence,n) where firstcellinsequence is a relative reference to the cell that contains the value 1 in the sequential number column, and n equals the nth value. For example, in the figure below, the sequential number list is in column F. Therefore, the formula in G2 references F2 and returns 0 for every third row: =MOD(F2,3) Now you're ready to implement a filter to delete every third row in the sheet (A2:E11). First, select the rows where the MOD() function returns 0, as follows:

  1. Select the column with the Mod() functions. In this case, that's G1:G11.
  2. Choose Filter from the Data menu.
  3. Select AutoFilter.
  4. Using the filter drop-down arrow, choose 0. Excel will display only the rows that contain a 0 in column G.

  5. Select the rows displayed by the filter; in this case, that's rows 4, 7, and 10.
  6. Choose Delete Row from the Edit menu to delete the selected rows.
  7. Remove the filter by selecting (All) from its drop-down list.
  8. Uncheck AutoFilter from the Filter menu (the Data menu).
  9. Delete the sequential number list in column F and the Mod() functions in column G. (Or leave them, if you need to repeat this task frequently.)
Deleting entire rows and columns has the potential to delete unseen data, so be careful. Before deleting a row or column, press [Ctrl]+[End] to find the last cell that contains a value or formatting in the current sheet.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox