how to insert rows

aberfoyle

Board Regular
Joined
Apr 19, 2005
Messages
60
Hi, I am a basic excel user. I have a spreadsheet with 700 rows of data. I need to insert 11 rows between each row. Is there an easy way to do this without writing a macro or using VBA since I don't know how to do these.

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

yes, you can do this in 2 minutes

if you will need this often you can automatize this process

make copy of your sheet in case this goes wrong
insert a column
in first cell next to data type =ROW()
copydown (or doubleclick rectangular handle at bottomright of cell)
select all those numbers (normally selection is already made when copying)
copy + pastevalues (Ctrl+C, rightclick-choose pastespecial, select values)
copy data again
paste at the bottom for as many rows you want to insert
now you will have something like this (this sample to insert 3 rows)
   A  B       C       
 1 1  data A1 data B1 
 2 2  data A2 data B2 
 3 3  data A3 data B3 
 4 4  data A4 data B4 
 5 1                  
 6 2                  
 7 3                  
 8 4                  
 9 1                  
10 2                  
11 3                  
12 4                  
13 1                  
14 2                  
15 3                  
16 4                  

Blad1

[Table-It] version 06 by Erik Van Geit

select all data including new column + data where you want to insert rows
sort the data (menu data / sort)
delete inserted column

kind regards,
Erik
 

aberfoyle

Board Regular
Joined
Apr 19, 2005
Messages
60
Here is how I have the data: I would like to insert 12 rows between item 7100 and 7111 and then 12 rows between 7111 and 7122 and then 12 rows between 7122 and 8888 etc. In total there is 700 items in this spreadsheet.

column A
Item #
7100
7111
7122
8888
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Thanks but I am having trouble. How do you do the sort?
select all data including new column + data where you want to insert rows
sort the data (menu data / sort)
any problem with this ?
Here is how I have the data: I would like to insert 12 rows between item 7100 and 7111 and then 12 rows between 7111 and 7122 and then 12 rows between 7122 and 8888 etc. In total there is 700 items in this spreadsheet.

column A
Item #
7100
7111
7122
8888
yes, it was clear

EDIT: first try out with a small sample
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

sort on which column? column a b or c?
why not try out ?
you can always undo (control+z) immediately after the operation

make sure to select your entire table
the key will be column A of course: all numbers will "join", so creating empty rows between your data
BEFORE
   A  B       C       
 2 1  data A1 data B1 
 3 2  data A2 data B2 
 4 3  data A3 data B3 
 5 1                  
 6 2                  
 7 3                  
 8 1                  
 9 2                  
10 3                  

Blad1

[Table-It] version 06 by Erik Van Geit

after
   A  B       C       
 2 1  data A1 data B1 
 3 1                  
 4 1                  
 5 2  data A2 data B2 
 6 2                  
 7 2                  
 8 3  data A3 data B3 
 9 3                  
10 3                  

Blad1

[Table-It] version 06 by Erik Van Geit
 

aberfoyle

Board Regular
Joined
Apr 19, 2005
Messages
60
If you have more than 10 rows of data then you will be inserting more than 3 rows. I have 700 rows of data. I think I get the logic of what you are trying to do
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
If you have more than 10 rows of data then you will be inserting more than 3 rows. I have 700 rows of data. I think I get the logic of what you are trying to do
the number of inserted rows is independent of the number of datarows

I'll dig up some code now...
 

aberfoyle

Board Regular
Joined
Apr 19, 2005
Messages
60
I tried again and now I understand how to do it the key is copying all 700 numbers 12 times below the data I had. Thanks for the help.
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top