Remove Alternate Rows In Excel

Let’s see how we can remove every alternate rows in excel.

We could use the MOD and ROW function in excel to remove every alternate rows or every nth row in excel.

We will see a simple example to understand that how we could do it with the data below.


Step 1

MOD function gives the remainder after a number is divided by a divisor and ROW function gives the row no of the cell.

Enter the formula =MOD(ROW(A2),2) in the cell B2 ,the first argument to MOD is the row number and second argument is 2 because we want to remove every alternate rows, if we wanted to remove every third row, we would have provided 3 as second argument to MOD function i.e. =MOD(ROW(A2),3).


Step 2

Drag the formula till where the data is available in column A.


Step 3

Now filter for 0 in column B as it signifies the alternate rows (every second row) and press Shift+Space bar to select the entire row and delete them.


Similar to this, you could change the second argument to n to delete any nth row, the row which has 0 is your target rows to delete.

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply