Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm working on an Excel spreadsheet in which I have to delete every alternate row, because it is messing up with my results. It's a set of 4000 randomly generated set of data. Hence, deleting 2000 would take about 30 minutes. Can it be done in 30 seconds using Excel alone and avoiding programming?

Is there any trick to do that in MS Excel?

PS: I want the deleted cell to be empty and not to be replaced with the following entry.

share|improve this question

You can use a macro to remove alternating rows, or you can cheat.

If it's sort of a one-off thing, I would cheat and add another column titled something like even. Then setup your data so it appears as the following:

value    item    even
10       foo     1
2        bar     2
11       baz     1
13       qux     2

You'd then filter based on the rows you wish to delete, select them and delete them. Filling out 1 and 2 is generally easy to just type the first four entries by hand, select the four values (1 2 1 2) and then double click the black box on the bottom right of the selection.

My hunch is that you might not even want to be doing that. In general whenever I've run across this particular need I've found a better way to solve it using Excel's built in tools, such as pivot tables.

share|improve this answer
That double clicking doesn't work for me, otherwise it would be a nice solution. I use the modulo function for the same effect. – TToni Nov 29 '10 at 19:32
How long does it take? – user57299 Nov 29 '10 at 19:37
between 10 seconds and a minute depending on how savvy you are with excel. – Sparr Nov 29 '10 at 22:21
@TToni MOD() is a good way to go. I tend to go the lazy way by either dragging to expand the selection or the double clicking just because I need it so rarely. – Tyler Dec 1 '10 at 1:07

A simple VBA script will do just fine. Save a Macro-Enabled copy of your Excel document. Create a macro, paste this code and click run.

Sub DeleteAlternateRow()
    For i = 1 To 4000
        Cells(i, 1).Select
    Next i
End Sub

Note: This macro deletes row 1,3,5,... if you want to start with deleting the second row then start the for loop with 2. Don't forget to make a copy of your document before running any macros

share|improve this answer
my VBA is rusty, shouldn't this be "For i = 1 to 4000 step 2"? – Sparr Nov 29 '10 at 22:22
Doing so would delete all the cells. Each time a row is deleted the next one automatically gets activated so the first step makes sure to skip one cell at a time. – Gani Simsek Nov 30 '10 at 7:46

In a new column (say "X") use


and expand the column over the whole set. That should give you an alternating sequence of 0 and 1. Now auto-filter and select only the 0s (or the 1s), then hit delete.

You may have to look up your local version of the functions, if you are not using English Excel. For example, in Germany it would be REST(ZEILE(X1;2)). MOD is the modulo function, and LINE should give you the current line number.

share|improve this answer
I like this one. much simpler than the MS macro i found – madmaze Nov 29 '10 at 19:50
Are you sure it is not the ROW function instead of LINE (the reference says it applies to Excel 2010) - =MOD(ROW(X1);2)? ROW also worked in OpenOffice 3.2.0 (released 2010-02-11) when I tried it. – Peter Mortensen Aug 16 '14 at 17:17
The corresponding Danish version is =REST(RÆKKE(X1);2). RÆKKE – Peter Mortensen Aug 16 '14 at 17:23

Say your data is in column A, you wish to get only data from odd number rows.

Step 1: Click on a different column, say B.

Step 2: In row 1, column B write "=A1".

Step 3: Click on that cell so it lights up blue.

Step 4: Also select the cell beneath it, you should now have two cells blue B1 and B2.

Step 5: Use the drag function found in the bottom right corner of cell B2 and drag down.

Note: Make sure to drag it all the way to the bottom on the first try.

share|improve this answer

Copying alternate rows of data into a column

Formula option

Starting in row 1, for the even positions:


For the odd:

share|improve this answer
This worked well for what I was needing to do – Chris Nevill Sep 1 '14 at 11:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .