how to insert rows

[aberfoyle]

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

 

[erik.van.geit]

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]

Thanks but I am having trouble. How do you do the sort?

 

[aberfoyle]

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]

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

 

[aberfoyle]

sort on which column? column a b or c?

 

[erik.van.geit]

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]

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]

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]

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.