Have you ever needed to delete every other row in Excel? You are not alone.
There are a few ways to accomplish it. It’s a pretty good candidate for a VBA solution, but I’m going to show you how to quickly delete every other row without using VBA or a plugin. I won’t make you resort to all of your data either.
This method of Deleting every other row centers around two different Excel functions
- Flash Fill to create an Index.
- Filter to identify lines to delete
Here is our sample data set. Let’s begin removing every other row.
Step 1 – Create the Helper Cells
In a preceding column, Put a 1 in the first row and a 2 in the next row.
Right-click the bottom right corner of the cell and drag down.
When you reach the bottom, the dialog box will pop up with a bunch of options. Choose “Copy Cells.”
Great job! You just created an index or what my Excel friends like to call a “helper column.”
Step 2 – Filter on just the twos
Title the column “Index” or anything you want.
Then filter (Ctrl + Shift + L) on the index column to only display the twos. Now you only can see the rows you want to delete. Let’s get them out of here!
Step 3 – Delete the twos
Highlight all the visible rows (except the header!)
Shift + Space + Down Arrow. If you have a lot of rows, use Ctrl + Down Arrow. Then click Delete.
Step 4 – Unfilter
Every other row is now gone. I hope whats what you wanted! The undo command (Ctrl +Z) is there for you, just in case.
