Delete Every Other Row in your Excel Worksheet

excel logo

There are lots of instances where you would like to delete every second, third, fifth, etc. rows in your worksheet. One instance that readily comes to mind is when you use data from another source.

Luckily this is not a difficult task!

Follow the steps below to learn how:

  1. Open your worksheet in which you need to delete rows.
  2. Insert two columns on the far left-hand side of your worksheet (A and B).
  3. In your new column A, use AutoFill to sequentially number your rows from 1 to the end.
  4. In B1, enter the formula =MODA1,2).
  5. Copy that formula down to all cells in the column.
  6. Click on the Data tab of your Ribbon
  7. In the Sort & Filter group, select the Filter tool.
  8. A small drop-down list will appear at the top of each column in your worksheet.
  9. Using the drop-down arrow in column B, select the zero. Now, only those rows with a zero in the column B appear.
  10. Select all the displayed rows and delete them.
  11. Turn off AutoFilter and delete columns A and B.

The formula in step 4 can be changed substituting another number than 2, depending on whatever multiple of rows you would like to delete.

Versions 2007, 2010

About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.

One Comment

  1. I tried this tip but the drop down list appeared in row 2 and there was no zero that I could see. I am using Office 2007