Md. Sourov Hossain Mithun
You may face many situations when you need to delete alternate rows in Excel worksheets. For example, you may want to keep data for odd rows and move all data of even rows. We can do it manually but that is not efficient for a large worksheet. So I’ll show 5 quick and suitable methods in this article to delete alternate rows in Excel with sharp steps and vivid illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
5 Ways to Delete Alternate Rows in Excel
Firstly, I’ll introduce you to my dataset which contains some salespersons’ sales in two regions for the same month. Now we’ll apply five efficient methods to remove the alternate rows which means we’ll remove the rows which contain the UK region.
Method 1: Use Excel Flash Fill and Filter to Delete Alternate Rows
In our very first method, I’ll use Excel Flash Fill and Filter option to remove the alternate rows. For that, I have added a helper column.
Steps:
- Type TRUE in the first column and FALSE in the second column in the data table.
- Then select those two cells and drag down the Flash Fill
Now all the cells are filled with that pattern.
- Later select any cell of the dataset and click as follows-
Home > Editing > Sort & Filter > Filter.
Now take a look that the filter option is added in the headers.
- Click the filter icon in the Helper column.
- Then mark only the FALSE option.
- Press OK.
Now it is showing the rows of the UK region only.
- Select the rows and hit the Delete button on your keyboard.
The rows are deleted now.
- Now to get back the other rows click the filter icon again.
- Then mark the TRUE option only.
- Finally, just press OK.
Here are our remained rows.
Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)
Method 2: Insert Cell Format And Filter to Erase Alternate Rows
We can use the cell Format and Filter options together to remove alternate rows. We’ll use the Excel table format here.
Steps:
- Select the whale dataset.
- Then click as follows- Home > Format as Table.
- Later, select a table format that has fill color and no fill alternatively between rows.
Then a dialog box will open up which will show the data range. Make sure that the My table has headers option is marked if you select the data range with headers.
- Press OK.
- After that click successively- Design > Convert to Range.
- Then again click as follows-
Home > Editing > Sort & Filter > Filter.
- Now press any of the headers’ filter icons and click- Filter by Color > No Fill.
Now you will observe that not filled cells are filtered which contain UK regions.
- At this moment just select the rows and press Delete on your keyboard.
- Now to get back the other rows just turn off the Filter option by clicking again-
Home > Editing > Sort & Filter > Filter.
We have got back the other rows now.
Read More: Delete an Excel Row If a Cell Contains Specific Values (3 Methods)
Method 3: Use MOD And ROW Functions to Delete Alternate Rows in Excel
Using functions is very user-friendly in Excel. We can perform the task by using the MOD and ROW functions. The MOD function is used to return the remainder of two numbers after division and the ROW function is used to return the row number of a cell. Here we’ll need a helper column again.
Steps:
- Type the following formula in Cell F5–
=MOD(ROW(),2)- Hit the Enter
- Drag down the Fill Handle icon to copy the formula.
The helper column is filled now.
- Then select any cell and click as follows to active filter option-
Home > Editing > Sort & Filter > Filter.
- Later, click the filter icon of the Helper column.
- Put a mark on the ‘0’ option.
- Then press OK.
- After that just select those filtered rows and hit the Delete button on your keyboard.
The rows are removed now.
- To get back the other rows just press the filter icon again and put a mark on the ‘1’ option.
- Finally, press OK.
Here’s our output.
⏬ Formula Breakdown:
➥ ROW()
The ROW function will return the row number of that cell which is-
{5}
➥ MOD(ROW(),2)
Then the MOD function will find the remainder after dividing the row number by 2 and that will return as-
{1}
Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)
Similar Readings:
- How to Delete Multiple Rows in Excel Using Formula (5 Methods)
- Delete Multiple Rows in Excel at Once (5 Methods)
- How to Delete Rows in Excel with Specific Text (3 Methods)
- Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)
- How to Delete Unfiltered Rows in Excel Using VBA (4 ways)
Method 4: Apply Excel ISEVEN And ROW Functions to Remove Alternate Rows
We can use another combination of functions to do the operation they are the ISEVEN and ROW functions. The ISEVEN function is used to check whether a number is even or not.
Steps:
- In Cell F5 write the following formula-
=ISEVEN(ROW())- Hit the Enter
- Then drag down the Fill Handle icon to copy the formula for the other cells.
- After that select any cell and click as follows to active filter option- Home > Editing > Sort & Filter > Filter.
- Then click the filter icon of the Helper column.
- Put a mark on the ‘TRUE’ option.
- Press OK.
- Later, just select those filtered rows and hit the Delete button on your keyboard to remove them.
- Now to get back the other rows just press the filter icon again and put a mark on the ‘FALSE’ option.
- Finally, just press OK.
The rows with the UK region are deleted now.
⏬ Formula Breakdown:
➥ ROW()
The ROW function will return the row number of that cell which is-
{5}
➥ ISEVEN(ROW())
Then the ISEVEN function will check whether the row number is even or not and it will return as-
{FALSE}
Read More: How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)
Method 5: Use VBA Macro to Remove Alternate Rows in Excel
In our last method, I’ll show how we can do the operation using VBA Macros. It has fewer steps and is a very fast method.
Steps:
- Right-click on the sheet title.
- Select View Code from the context menu.
A VBA window will appear.
- Write down the following codes in it-
Sub Delete_Alternate_Rows()
Dim x As Range
Set x = Application.InputBox("Select the Range Without Headers", "Range Selection", Type:=8)
For i = x.Rows.Count To 1 Step -2
If i Mod 2 = 0 Then
x.Rows(i).Delete
End If
Next i
End Sub- Then press the Play icon to run the codes.
A dialog box will appear to select the data range.
Now set the data range and press OK.
And we are now done with deleting alternate rows.
Read More: Macro to Delete Row in Excel If Cell is Blank
Conclusion
I hope all of the methods described above will be good enough to delete alternate rows in Excel. Feel free to ask any question in the comment section and please give me feedback.
