How to Delete Every Other Row in Excel

On occasion, you might find yourself faced with a data set where you only require every other row, or for that matter every N^th row for your purposes. In these cases, especially if the data set is large you might find that it becomes time-consuming and laborious to sort the data manually. Luckily, Excel allows nifty options for working through data sets without having to flex your index finger thousands of times working through each data point.

We will show you the method for deleting every N^th row and how to make it semi-automatic. We will however not discuss how to complete the task using a Macro (a small program that performs a repetitive task) as the way we will show you is just as effective and equally fast.

Method for Deleting Every N^thRow

We need to be able to filter the data to delete subsets of it. To do this we take advantage of the Excel filter function by adding an extra column to the data containing information we control. We can also take advantage of the Sort function in a similar way which we will discuss below.

Let us take the example below, the dataset contains the world’s capitals in alphabetical order, we have also numbered the items in the list on the left, and the country associated with each capital is also listed. Let us presume that Professor Microsoft has given us a piece of homework; to delete every other capital. If you have worked with excel before you’ll know that if you can find a way of selecting every other row, then you’ve solved the puzzle.

This is where the use of the filter function and an additional column to the data really shines. We will also be using the MOD function (If you only want to work with every other row, then just copy the formula listed below, if you want to work with N^th number rows we will explain how to modify the function in the next section.

Step 1; creating a supporting column

On the right side of your data, label a new column as your support column, in the example below the column is termed Filtering Column.

Go to the first cell in this column that corresponds to your first data point, in our example this cell is D2. In the equation field copy the following: =MOD(ROW(),2) you can also type it directly into the cell. Press return to apply the formula, a number in the cell will appear, in our example, this is a zero.

Hover your mouse in the right corner of the cell, now click this right corner, and drag it down all the way through to the last row of your dataset. Your support column should now be populated with every other number being a one and a zero as shown in the picture.

Step Two – Use the filter function to only display the 1 or the 0 depending on which part of the data you want to keep

In the top ribbon select Data and then click Filter in the ribbon below it.

On your filtering column, sick the down arrow that appears and deselect all the data you want to keep. So if you want to keep the rows with 1, deselect the 1, and if you want to keep the zero rows, select the 1 and deselect the zero.

Step Three: delete data

Once you have pressed ok, all only the rows with a 0 should be visible (in our example), these are the rows we want to delete.

Select all rows, down to the end of the data set then right click and hit Delete Row. You can also press the delete button on your keyboard.

Your table will disappear, however, the rows you have hidden in the previous step remain unaffected, hit the filter button again to make them visible again.

Using the Mod Function to Select Every N^thRow

To understand how the function, we used above can be applied for selecting the N^th row, we need to understand what the function does.

=MOD(ROW(),2)

This is the same thing as inserting the row number into the box (the ROW() function), then dividing the number by two (the second argument), and then providing them from the division.

So for even rows the result is zero, as there is no reminder, whilst for odd rows there is a remainder of 1.

So now that you understand how the basic function works, we can now modify the row number we insert, as well as the divisor we choose in order to produce different sets of N. To do this we use this the generic formula:

=MOD(ROW()-a,n)

A is the row number of the first cell in the data -1

So, in our example a = 1 as the first row in the data is D2 (row number 2); 2-1=1

As the formula rolls, the value of a is also rolling, for row 8, a=7 etc.

The number n denotes the number of times we want to divide the data, so for every other row n=2, while for every 7^th row, use n=7.

The number that will appear in the supporting column (in our case named the Filtering Column will range [0, n-1].

This is very useful as it allows us to divide data in a variety of ways, for example, you can delete the second and fifth 7th parts of a dataset if you so wish using the above function and the built-in filter.

In the image below we show examples for n=3, and n=7

Using the Sort Function

It can be a little unnerving for some people to use the filter function as one might naturally worry that data may be lost. Although it is perfectly safe to use if done properly, we understand the anxiety of potential data loss. If once you’ve applied the mod function to the data want to be able to see the entire data set as you delete certain rows you can use the sort function instead.

Create a sorting column as described above. Once it appears correct for what you want to do, follow these steps:

1. Highlight the entire column by pressing the corresponding letter in the column header
2. Click Ctrl + C to copy it
3. Right click on the letter then choose “paste as values” from the context menu that appears (denoted by a file with the numbers “123” under it), this sequence changes the entries in each cell from a formula (our MOD() function) into the value that formula calculated.
4. Click Sort under the data ribbon (where we previously used filter). A pop up will appear, select the column you want to sort by (in our case Filtering Column) and select numerical order
5. Your data will move around such that you can see each part of it clustered as in the image below
6. You can now highlight the rows you want to delete and delete them immediately while still being able to see the rest of the data

Deleting Every N^th Column

Hey ho, what about deleting every other column you ask? Impossible say we.

Just kidding, not at all, the trouble is the operations we were relying on such as filter only work for rows, so if you want to select every N^th Column, we advise inverting or transposing your table. This is really easy to do, and you can invert it back once you’ve completed what you need to do.

Transposing a table means that columns become rows, and rows become columns, which then means we can use our method from above and apply it to columns.

To transpose the data follow these steps:

1. Select all your data
2. Hit Ctrl + C  to copy
3. (our suggestion open a new sheet) and right-click where you want to paste the data -> select Transpose under the paste options
4. The transposed data should appear in the new sheet and you can work with it

Once you are done follow the same steps again to transpose it back.