Pinterest Pixel

How to Delete Every Other Row in Excel – Top 3 Methods

Microsoft Excel is a powerful tool for data analysis and management, and sometimes you may find yourself... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Delete Every Other Row in Excel - Top 3 Methods | MyExcelOnline

Microsoft Excel is a powerful tool for data analysis and management, and sometimes you may find yourself the need to clean up your data. When you import data from different sources, you may want to delete every other row in Excel. This could be because you have unwanted or repeated rows after each relevant row.

While the manual method of selecting rows and deleting them is an option, it can be both time-consuming and prone to errors, especially with large datasets. In this article, we will cover the following 3 approaches that can be used to delete every other row in Excel –

Download the Excel Workbook below to follow along and understand How to Delete Every Other Row in Excel – download excel workbookDelete-Every-Other-Row-in-Excel.xlsm

 

Method 1 – Using Table Format

In this method, you need to format data to resemble a table with banded rows. This will automatically apply alternating colors to each row, making it visually distinct. After that, you can effortlessly apply a filter to identify and select the specific rows you want to delete.

See also  Insert Multiple Worksheets Using Macros In Excel

Follow the steps below to know how –

STEP 1: Press Ctrl + T to create a table.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 2: In the Create Table dialog box, double-check the range selected and check the My table has headers option.

How to Delete Every Other Row in Excel - Top 3 Methods

The data will be converted into a table with banded rows.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 3: Go to the Table Design tab > Convert to Range.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 4: In the dialog box, select Yes.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 5: Click on the Filter button, go to Filter by color, and select Light Blue.

How to Delete Every Other Row in Excel - Top 3 Methods

All the rows with no fill will be filtered.

STEP 5: Right-click and select Delete Row.

How to Delete Every Other Row in Excel - Top 3 Methods

The rows with light blue fill will be deleted. Only the rows with no fill will remain in the data table.

How to Delete Every Other Row in Excel - Top 3 Methods

 

Method 2 – Using MOD Format

In this method, you need to use the MOD function to determine if a row number is even or odd. If a row is even-numbered, Excel will display a result of 1 and it will display 0 when the row is odd-numbered.

See also  How to Highlight Unique Values in Excel

You will also have to use the ROW function to get the current row for each cell. Now let’s look at the step-by-step tutorial to know more.

STEP 1: Insert a helper column next to your data.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 2: Enter the MOD function.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 3: Enter the ROW function.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 4: Enter the argument for the row function i.e. reference. Here, you can select any cell in the same row where you are typing the formula.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 5: Enter the argument of the MOD function i.e. divider. Here, it is 2 as you will have to know whether the row is odd-numbered or even.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 6: Copy the formula down.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 7: Click on the Filter button and select 0.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 8: Right-click and select Delete Row.

How to Delete Every Other Row in Excel - Top 3 Methods

All the even-numbered rows will be deleted.

How to Delete Every Other Row in Excel - Top 3 Methods

 

Method 3 – Using VBA

In this method, you need to use a VBA code that can be employed to automate the process. Here are the steps to incorporate the same –

See also  How to Insert an Excel Table

STEP 1: Go to the Developer Tab and select Visual Basic to open the VBA editor.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 2: Go to Insert > Module.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 3: Copy and paste the following VBA code:

Sub DeleteEveryOtherRow()
Dim i As Long
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -2
ActiveSheet.Rows(i).Delete
Next i
End Sub

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 4: Press Ctrl + S to save the module.

STEP 5: In the dialog box, select No to save the file as a macro-enabled workbook.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 6: In the Save As dialog box, select Excel Macro-Enabled Workbook. Press Save.

STEP 7: Go to Developer Tab > Macro.

How to Delete Every Other Row in Excel - Top 3 Methods

STEP 8: In the Macro dialog box, select Delete Every Other Row, and press Run.

How to Delete Every Other Row in Excel - Top 3 Methods

Once you run this macro, all the odd-numbered rows will be deleted.

How to Delete Every Other Row in Excel - Top 3 Methods

 

Conclusion

In conclusion, whether you prefer a simple table format, a formula-based approach, or VBA automation, Excel offers multiple ways to delete every other row. Choose the method that best suits your workflow and data requirements.

Click here to learn more about How to delete every other row on an Excel worksheet!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  Remove Duplicates in an Excel Table
How to Delete Every Other Row in Excel - Top 3 Methods | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!