Adding an extra row after every existing row

[Josh W]

I have an excel worksheet with about 61 rows. Now i have to add another row
with the same text after EACH existing row - between row A and B, row B and C
etc. Is there any quick way to insert all these rows or do I simply have to
manually insert row by row.

 

[Gord Dibben]

If just for visual purposes, simply select all rows, which are the 1,2,3,4,5 and
not the columns which are A,B,C,D and double their height.

If you truly need a row inserted, in a helper column enter numbers 1 to 61.

Copy that range and paste to row 62.

Sort on that column ascending to get your blank rows.


Gord Dibben MS Excel MVP

 

[Josh W]

Sorry Gord, I know you are trying to help but I really couldn't follow you. I
tried to understand and do what you suggested several ways but all that
happens is that the existing data remain exactly as before and I only got an
extra 61 rows AFTER the existing rows and not each one in between.

 

[Gord Dibben]

Let's go over it again.

1. In row 1 of an an adjacent blank column....say H1... enter 1.

2. In H2 enter a 2.

3. Select both those cells and double-click fill handle of H2 to copy down to
H61.

4. Select H1:H61 and Copy.

5. Select H62 and Paste.

6. Select all your used columns including H and Data>Sort> "My data range has
no header row"

7. Sort Ascending on column H to get

1
1 blank
2
2 blank
3
3 blank

8. Delete Column H


Gord

 

[Ragdyer]

Just elaborating on Gord's explanation:

Say existing data in A1 to A61.
Insert a *new* (helper) column A by right clicking in the existing Column A
header and choosing "Insert".

In the NEW Column A, enter 1 in A1, and 2 in A2.
Select A1 and A2.
In the lower right corner of this 2 cell selection you'll see a small black
square, called the "fill" handle.

Hover the cursor over this square until it (the cursor) changes from a fat
white cross to a skinny black cross.
Then *double* click.

This will *automatically* fill the series down Column A, as far as there is
data in Column B.

This gives you the numbers 1 to 61 in the column next to your data in Column
B.

While these 61 numbers are *still* selected, right click in the selection
and choose "Copy".
Right click in the empty A62 and choose "Paste".

NOW, select A1 to B122, and from the Menu Bar:
<Data> <Sort>
Sort by Column A, ascending, <OK>.

And you're done.

Of course, if your data extends beyond Column B, say to Column J, then
select A1 to J122 before completing the sort.

 

[Ragdyer]

Sorry Gord, didn't see your reply b4 I posted!

 

[Gord Dibben]

Not a problem RD

Gord

 

[Josh W]

Thanks very much, it works.

Just elaborating on Gord's explanation:

Say existing data in A1 to A61.
Insert a *new* (helper) column A by right clicking in the existing Column A
header and choosing "Insert".

In the NEW Column A, enter 1 in A1, and 2 in A2.
Select A1 and A2.
In the lower right corner of this 2 cell selection you'll see a small black
square, called the "fill" handle.

Hover the cursor over this square until it (the cursor) changes from a fat
white cross to a skinny black cross.
Then *double* click.

This will *automatically* fill the series down Column A, as far as there is
data in Column B.

This gives you the numbers 1 to 61 in the column next to your data in Column
B.

While these 61 numbers are *still* selected, right click in the selection
and choose "Copy".
Right click in the empty A62 and choose "Paste".

NOW, select A1 to B122, and from the Menu Bar:
<Data> <Sort>
Sort by Column A, ascending, <OK>.

And you're done.

Of course, if your data extends beyond Column B, say to Column J, then
select A1 to J122 before completing the sort.

 

[Josh W]

Thanks very much Gord, now it works fantastic!! the "now header row" click
was a good tip.

 

[Gord Dibben]

Good to hear you're "sorted" out.

Feedback always appreciated and lets us know what works and what doesn't.


Gord

 

[RagDyer]

Appreciate the feed-back.