Follow along with me as I show you how to delete every other row in Excel.

Deleting every other row in Excel can feel like sorting through stacks of paperwork by hand—tedious and time-consuming. If you’ve ever faced the frustration of manually removing rows one by one, you’re not alone.

This task can slow down productivity, especially when dealing with large datasets. But don’t worry—this tutorial simplifies the process so you can handle it in minutes. I’ll guide you through each step clearly, and by the end, you’ll have a faster, more efficient way to manage your data. Let’s dive in and make this easier for you!

Using a Formula and the Filter Tool

This is the basic method for removing alternative rows from a dataset in any Excel desktop app as well as Excel for the web. In this approach, I’ll show you how to create a helper column in your source dataset, populate alternative values, and filter out the alternating values using the Sort & Filter tool. Then, you can simply delete the filtered values.

Go to the source dataset and create a Helper column on the immediate right side of the data table as shown in the above screenshot.

Select the first cell of the Helper column, and type the following formula into it, and press Enter:

=MOD(ROW(),2)

The first cell will show 0.

Now, select the cell and drag it down along the column.

This will populate the alternative 0s and 1s.

Once you have generated these values in the Helper column, click on the column header in the dataset and press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Sort & Filter drop-down arrow in the column header.

In the Sort & Filter interface, uncheck the Select All option and only check 1s. Click OK to apply the filter.

Now, delete the remaining data rows after the filter you’ve applied.

Press Ctrl + Shift + L to deactivate the Sort & Filter tool.

Now, delete the Helper column.

That’s it! You’ve successfully deleted every other row in Excel.

Using the Sort Tool

This is a manual method where you’ll be using a helper column to sort out the alternative rows or blanks in your source dataset. Then, use the appropriate Sort function to sort out the ones you want to keep and the ones you want to delete. Then, you can easily remove the unwanted rows.

Therefore, go to your worksheet and create a Helper column.

Type Keep and Delete the way you see fit. Look at the above screenshot for reference. You can use the fill handle to easily popular the Keep and Delete values in the Helper column for a large dataset.

Now, press Ctrl + Shift + L to activate the Excel Sort feature.

Click on the Sort & Filter arrow and apply the Sort A to Z function.

Excel will group all the rows with the Delete text above those with the Keep text.

You can select all these rows, right-click, and delete the unwanted rows.

Delete the Helper column and press Ctrl + Shift + L to disengage the Sort & Filter feature.

Now you’ve got a clean dataset minus the alternative rows or alternative blank rows.

Using Power Query

Often you find a large dataset of unwanted values or characters in every alternate row of the entire data. Excel can handle up to 1,048,576 rows in a worksheet. Say, the source dataset contains twice the amount of these many rows. The alternate rows in this data contain junk data you don’t need, like asterisks. So, you can’t load this data to an Excel worksheet since it’s outside the tool’s limit.

Power Query steps in here to your rescue. You can load an unlimited number of rows in it, transform the input data set to remove the unwanted rows, and then load the clean data into an Excel worksheet.

The first step is to import the source data into Power Query. You can open the destination Excel workbook and go to the worksheet where you’d like to import the transformed dataset.

Click on the Get Data command inside the Get & Transform Data block of the Data tab.

Choose the primary data source, like, From Database, From Azure, etc.

Hover the mouse cursor over any of the chosen primary data sources to find the final data connectors, like the From SQL Server Database for the From Database option.

Click on From SQL Server Database and follow the onscreen instructions to import data into the Power Query Editor.

The other option is importing a dataset from an active Excel worksheet. To use this method, select the source data in your worksheet and click on the From Table/Range command.

Click OK in the Create Table dialog box to import your data to Power Query.

Here’s what the Power Query Editor interface will look like.

Now, click on the Remove Rows command in the Reduce Rows block of the Power Query Home tab.

Select the Remove Alternate Rows option.

The Remove Alternate Rows wizard will open. Find below how to configure:

• First row to remove: 2
• Number of rows to remove: 1
• Number of rows to keep: 1

Click OK to save and apply the Remove Alternate Rows command to the dataset.

Power Query will instantly apply your choice to the data and reduce the row counts by eliminating the alternate rows.

Click on the File tab and choose the Close & Load To option.

The Import Data dialog box will show up on the active worksheet.

Select the Existing worksheet option and highlight a cell as the destination for the transformed data.

Click OK to confirm importing data to your worksheet.

Congratulation! You’ve successfully deleted every other row in Excel using the Power Query Editor.

Using an Excel VBA Macro

Finally, if you wish to get the job done automatically by setting up a VBA macro you can use Excel VBA. It requires you to write the VBA script, use the code to set up a macro and execute the macro to delete every other row in Excel.

To start, you must learn how to create a macro in Excel using a VBA script. For that, go to the following Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

Now that you know the trick to set up a VBA macro, use the following script for your macro:

Sub DeleteAlternateRowsWithHeader()
    Dim rng As Range
    Dim cell As Range
    Dim deleteCount As Long
    Dim keepCount As Long
    Dim i As Long
    
    ' Show an input dialog to select the source data range
    On Error Resume Next
    Set rng = Application.InputBox("Select the source data range:", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "No range selected.", vbExclamation
        Exit Sub
    End If
    
    ' Delete alternate rows starting from the third row (header is the first row)
    For i = rng.Rows.Count To 3 Step -1
        If (i Mod 2) = 1 Then
            rng.Rows(i).EntireRow.Delete
            deleteCount = deleteCount + 1
        Else
            keepCount = keepCount + 1
        End If
    Next i
    
    ' Add kept row counts from header and the second row
    keepCount = keepCount + 2
    
    ' Show confirmation box
    MsgBox deleteCount & " rows deleted, " & keepCount & " rows kept.", vbInformation
End Sub

Make sure you save the VBA script on the Excel VBA Editor interface.

Now, press Alt + F8 on the active worksheet and select the DeleteAlternateRowsWithHeader macro.

Hit Run to execute the macro.

At first, the macro will ask you to select the input dataset. You can either use the mouse or enter the cell range address manually.

The next dialog box will show the results for how many rows were kept and how many of those were deleted.

⚠️ Warning: You can’t use the Excel undo feature when using Excel VBA. So, create a backup copy of the workbook before using this method.

Conclusions

So far, I’ve shown you four different ways to delete every other row in Excel. If you’re a beginner-level Excel user, use the methods that involve simple formulas, helper columns, and the Sort & Filter tool.

However, if you’re an experienced Excel user and often import raw data from external sources, you can use the Power Query-based method for efficiency, constancy, and automation.

Finally, if you wish to delete alternative rows with minimum effort, Excel VBA-based solution is the best option. I’ve created a VBA macro that’ll walk you through the process visually. Also, you’ll only need to select the source data and the script will do the rest, like deleting the alternative rows and adjusting the position of the rows.

If you liked this Excel tutorial or would like to share any feedback, use the comment box given below.