Excel: How to Delete Every Other Row

You can do so much with Excel: the list is endless, from writing complicated formulas to Excel divisions. However, processes like deleting rows are part and parcel of pretty much every Excel project. This guide will show you how to delete every other Excel row, making your spreadsheet more digestible and discarding unnecessary values.

Manually Delete Every Other Row in Excel

Excel doesn’t have a built-in function that lets you delete every other row. Thankfully, there are many alternatives, one of which is manually selecting the fields. However, this solution is cumbersome and works only for small datasets.

1. Open the Excel spreadsheet you wish to modify.
2. Select the first row that you want to delete.
   
3. Press and hold the Ctrl button.
4. Use your mouse to select every other row without releasing Ctrl.
   
5. After highlighting the last item, release the Ctrl key and press Delete on your keyboard. Alternatively, right-click one of the cells and select Delete in the dialog box.
   

You can easily insert multiple rows in Excel, so even if you accidentally delete a few rows, you can reinsert them. 

Delete Every Other Row in Excel with the Helper Column

If you have to delete a lot of alternate rows or columns in Excel, you can use the Filter function to remove unnecessary items. Here, most of the work comes down to creating a helper column.

1. Open the Excel spreadsheet.
2. Go to a blank column next to the columns you wish to modify.
   
3. Type in alternating ones and zeros.
4. Enter the digits in the first two fields to speed up the process.

From there, copy/paste the information to the remaining rows until the column has the same number of rows as the original data.

Now that the helper column is locked and loaded, it’s time to move on to stage two. That’s where the Filter function comes in. Follow these steps to weed out the redundant rows:

1. Choose a field in the helper column.
   
2. Navigate to Data and press the Sort and Filter feature.
   
3. Choose Filter, and you should now see a drop-down menu in each cell of your helper column.
   
4. Click the drop-down menu at the top of the column and select 1 or 0, depending on which rows you want to delete. Excel will now hide the rows you want to hold onto, leaving the unnecessary items visible.
   
5. Select the visible rows with your mouse.
6. Right-click the highlighted rows and choose Delete or Delete Row. The wording may vary, depending on your Excel version.
   
7. Press the Filter key to disable the filter and return the rows you wish to retain in your table. The unnecessary fields will be gone for good, leaving you with a cleaner spreadsheet.
   
8. Remove the helper column to wrap things up.

Delete Every Other Row in Excel with a Function

No Excel tutorial would be complete without at least one function. The ISEVEN function is specifically designed to help you streamline data. When executed successfully, it describes even rows as true and odd rows as false.

It’s an effective formula, but you must combine it with a helper column and the filter feature, two of your most trusted companions.

1. Set up a new column next to the last one in your spreadsheet. Name it Helper.
   
2. Enter this line in the first cell under the header of your column: =ISEVEN(ROW()).
   
3. Copy the formula in every cell of the helper column.
4. Highlight all the data in your spreadsheet. This includes both the original columns and the helper column.
   
5. Press Data followed by the funnel-shaped Filter symbol. A drop-down menu should appear next to every header, indicating that your data is ready for filtering.
   
6. Uncheck the box next to True, leaving only False rows visible. The system will now use the ISEVEN tool to show you the fields where the value matches the False metric.
   
7. Choose every cell in your helper column, but leave the header.
   
8. Press the Delete button on your keyboard, select any highlighted cell, and choose Delete.
9. Tap the OK button to remove the visible information.
10. Head back to Data and press the filter symbol. The unwanted rows will be gone from your spreadsheet.
    

You don’t need to follow the above method to remove duplicates in Excel, as there is a better way of doing that.

Delete Every Other Row in Excel with a Macro

If there were ever an almighty Excel tool, it would be the Macros. These handy functions can be executed as many times as necessary. Furthermore, they allow you to initiate almost any actions that come to mind, making them incredibly versatile.

One of the most useful functions of macros is their ability to delete every other row. This is also the most advanced method, but once you get it right the first time, it’ll save you a lot of time in the future. You can copy the macro and remove the rows in a split second.

1. Launch a spreadsheet.
2. Use the Alt + F11 key combination.
3. In the upper part of your Visual Basic for Applications menu, press the Insert button.
   
4. Select Module.
   
5. Paste this macro into the Module window:
   Sub Delete_Alternate_Rows_Excel()
Dim SourceRange As Range
Set SourceRange = Application.Selection
Set SourceRange = Application.InputBox("Range:",
"Select the range", SourceRange.Address, Type:=8)
If SourceRange.Rows.Count >= 2 Then
Dim FirstCell As Range
Dim RowIndex As Integer
Application.ScreenUpdating = False
For RowIndex = SourceRange.Rows.Count - (SourceRange.Rows.Count Mod 2) To 1 Step -2
Set FirstCell = SourceRange.Cells(RowIndex, 1) FirstCell.EntireRow.Delete
Next
Application.ScreenUpdating = True
End If
End Sub
   
6. Strike F5 to initiate your macro.
7. Choose a range in the pop-up window and pick a corresponding table.
   
8. Press OK, and enjoy a revamped spreadsheet.
   

How Do You Remove Every Nth Row in Excel?

Deleting every other row is a versatile feature, but it’s not an all-cure to your data management struggles. What if the unnecessary information isn’t in every other row but elsewhere? For example, you might need to eliminate every third row. Besides the manual method, there’s another way to proceed:

1. Set up a helper column next to the header of the last column in your dataset. Name it however you want.
   
2. Type in the function in the first cell under the header of the helper column: =MOD(ROW(),3)=1. This formula marks all third rows as True and other fields as False.
   
3. Highlight the entire spreadsheet (all the columns) and navigate to Data in the upper part of the screen.
4. Hit the Filter key to filter your headers.
5. Head to the filter in your helper column and uncheck the box next to True to leave only those with a False selected.
   
6. Tap the OK button and highlight every cell in your helper column. Delete the cells with your keyboard or mouse.
7. Return to Data and choose Filter. Excel will deactivate your filter and purge the data by eliminating every third row. If you wish to delete every fourth or fifth row, you can change the first number in the MOD function.
   

Some Rows Must Be Banished

Functions, shortcuts, highlights, and macros are the backbone of Excel. One of the most effective things you can do with them is to remove every other row (or any other row, for that matter). Use your favorite method to deal with unnecessary fields whenever you encounter them.

Also, note that if you have made changes you do not want, you can always revert to a previous Excel file version.

FAQ

How do I quickly select all rows?

If you need to select all rows in Excel to apply a formula or to delete, you may use the keyboard hotkey: Ctrl + Shift + Space.

Is there a built-in function in Excel to delete every other row automatically?

Sadly, there is no built-in function for automatically deleting every other row. However, you may try any solutions we have covered in this guide.