How to Delete Every Other Row or Every Nth Row in Excel?
In this article, we will see how to delete every other row or every nth row in excel. Generally, deleting every other row in Excel boils down to selecting alternate rows. Once the rows are selected, a single stroke on the Delete button is all it takes. But there are simpler ways to achieve so which we will be discussing in this article.
We can delete every nth column in excel by following steps:
- Create an extra column say ‘Helper’.
- Write modulus formula that divides the row number by n(nth row you want to delete) number in helper column.
- Filter out data whose helper column value is 1. (considering column header has row value 1 and entries starts from row number 2)
- Delete the filtered data.
- View the original data, delete the helper column created earlier.
- you can see that nth row data is deleted now.
We have the following data in excel:
For demonstration purposes follow the below steps:
Step 1: Create a new column with the name ‘Helper’.
Step 2: Write the formula.
- For demonstration delete every third column of the data.
Formula used: Mod(row(),3)
Step 3: Filter data
- We want to delete every third row, which means whose helper column value is 1.
- To filter data, go to Data>>Filter>> Autofilter.
- Select ‘1’ from the dropdown displayed with the Helper column.
Step 4: Delete filter data
- To delete the data: select data>> right click>> delete>> delete entire row.
- Press ‘OK’.
Step 5: View original data
- To go back to the original data, go to Data>>Filter>>Autofilter
- We can see that every third row is deleted as compared to the original data presented earlier.
Step 6: Delete the Helper column.
- Select helper column>> right click>> delete>> delete entire column>> select ok.