How to Delete Every Other Row in Excel

As an Excel professional, you should know how to delete every other row in Excel.

There are many situations where you may need to delete alternate rows in Excel sheets. For example, you might want to keep data for even weeks (rows 2, 4, 6, 8, etc.) and move all odd weeks (rows 3, 5, 7, etc.) to another sheet.

In general, deleting every other row in Excel boils down to selecting alternate rows. Once the rows are selected, a single swipe of the Delete button is all it takes.

This tutorial explains how to remove every second row in Excel using filtering or VBA code.

Further in this article, you will learn several techniques to quickly select and delete every second or every Nth row in Excel.

How to Delete Every Other Row in Excel by Filtering

The typical way to delete every other row in Excel is this:

First, you filter out the alternate rows, then select them and delete them all at once.

Below are the detailed steps:

Enter a sequence of zeros and ones in the empty column next to the original data.

You can quickly do this by typing 0 in the first cell and 1 in the second cell, then copying the first two cells and pasting them down the column to the last data cell.

Alternatively, you can use this formula: =MOD(LINE();2)

The formula’s logic is simple: the ROW function returns the current row number, and the MOD function divides it by two and returns the remainder rounded to an integer.

The result is that you have 0 in all even rows (since they are divided by two evenly with no remainder) and 1 in all odd rows:

Filter out ones or zeros, depending on whether you want to remove even or odd rows.

To do this, select any cell in the Helper column, go to the Data tab > Sort & Filter group, and click the Filter button.

Dropdown filter arrows appear in all header cells. Click the arrow button in the Help column and check one of the boxes:

0 to remove even rows
1 to delete odd rows

In this example, we remove the rows with “0” values, so we filter them:

Now that all the “1” rows are hidden select all the visible “0” rows, right-click the selection, and click Delete Row:

The above step left you with an empty table, but don’t worry; the “1” rows are still there.

To make them visible again, simply remove the automatic filter by clicking the Filter button again:

The formula is recalculated for the remaining rows, but you no longer need it. You can now safely remove the Help column.

As a result, only even weeks remain on our sheet; odd weeks are gone!

If you want to move every other row somewhere else rather than deleting them completely, copy the filtered rows and paste them to the new location, then delete the filtered rows.

How to Delete Every Other Row in Excel With VBA

If you don’t want to waste time on a trivial task like deleting every other row in Excel sheets, the following VBA macro can automate the process:

Insert the macro into the worksheet in the usual way using the Visual Basic editor

Press Alt + F11 to open the Visual Basic for Applications window.

On the top menu bar, click Insert > Module and paste the above macro into the module

Press F5 to run the macro.

A dialog box will appear, asking you to select a range. Select the table and click OK.

How to Delete Every Nth Row in Excel

For this task, we will extend the filtering technique we used to remove every other row. The difference is in the formula on which the filtering is based: MOD(ROW()-m, n)

Where:

m is the row number of the first cell with data minus 1

n is the Nth row you want to delete

Let’s say your data starts on the second row, and you want to delete every third row.

So in your formula, n equals 3 and m equals 1 (row 2 minus 1): =MOD(ROW() – 1; 3)

If our data starts at row 3, then m would be equal to 2 (row 3 minus 1), and so on. This fix is required for consecutive line numbering, starting with 1.

The formula divides the relative row number by three and returns the remainder after the division.

In our case, it gives a zero for every third row because every third number is divisible by three without a remainder (3,6,9, etc.):

And now, you perform the already familiar steps to filter the “0” rows:

Select any cell in the table and click the Filter on Data button

Filter the Help column to show only “0” values.

Select all visible “0” rows, right-click, and select Delete Row from the context menu.

Remove the filter and remove the Helper column.

Similarly, you can delete every 4th, 5th, or other Nth row in Excel.

Delete Every Second Column (Or Every Nth Column)

Clearing every alternate or third/fourth row is easy because you can use the filter option.

All you have to do is use a formula that identifies alternate rows (or every third/fourth row) and filters those rows.

Unfortunately, the same methodology won’t work with columns—because you can’t filter columns the way you filter rows.

So if you have to delete every other column (or every third/fourth/Nth column), you need to be a little more creative.

In this section, we’ll show you two methods you can use to delete every second column in Excel (and you can use the same method to delete every third/fourth/Nth column if you want).

Remove Alternate Columns Using Formulas and a Sort Method

Suppose you have a dataset, as shown below, and you want to remove every other column (except column header A)

The trick here would be to identify the alternate columns using a formula and then sort the columns based on that formula.

Once you have sorted columns, you can manually select and delete them.

Below are the steps to delete every second column in Excel:

Insert a row above the header row (right-click any cell in the header, click Insert, then click Whole Row)

Enter the following formula in the cell above the leftmost column in the dataset and copy it to all cells: =MOD(COLUMN(),2

Convert formula values to numbers. To do this, copy the cells, right-click, and go to Paste As –> Paste Values.

Select the entire dataset (except column headers). In our example, we selected B1:G14 (and NOT column A)

Click the Data tab

Click the Sort icon

In the Sort dialog box, make the following changes:

Click the “Options” button, and in the dialog box that opens, click “Sort from left to right” and then click the OK button.

In the Sort By drop-down list, select Row 1.

This is the row that contains the results of the formula

In the Order drop-down list, select Smallest to Largest.

Click the OK button

The above steps will sort all the columns and join all the alternate columns in one place (at the end).
Now you can select all those columns (for which the formula value is 1) and delete them.

While it’s not the best solution, it still does the job.

Conclusion

We’re glad you understood this guide about how to delete every other row in Excel.

Feel free to leave questions on the columns you have trouble deleting in Excel, and we will be glad to create a guide like this tailored to meet your Excel needs.