Function in Excel to separate rows in equal intervals?

**n_ant** · 04-30-2013, 06:10 PM

Hi,

Is there any function or a smart way to separate rows in Excel in equal intervals? Say I have column data and I want 10 empty rows between each observation. Thank you!

**Oppressed1** · 05-01-2013, 01:36 AM

One manual approach that could fit if this is a one time deal is to pick an unused column and type FOO in one cell, then right below that enter
=IF(MOD(ROW(),11)=0,1,0)
and copy down. Highlight (select) from FOO to the last value. Then go alt-d/f/f to create an autofilter. Pop open the triangle and click on 1. Now only every 11th row appears. You could now type in your observations easily in every 11th row.

Another way would be to forget all that and type or paste in the observations in A2:A99. Then
B1=IF(MOD(ROW(),11)=0,OFFSET($A$1,INT(ROW()/11),0),"")
and copy down. Then highlight column B and copy, and paste special as values. Clear out column A and you're done.

**n_ant** · 05-01-2013, 05:04 AM

Thank you for your reply! I've tried to do it both ways you suggested but without success. In the first case, I create the column FOO and everything is ok, but when it comes to the filter I cannot make every 11th row to appear. When I open the triangle, the following options appear:

Sort smallest to largest
Sort largest to smallest
Sort by color

Number filters
and then there is the rectangle with
Select all
0
1

and all these are tick-ed.

However, I cannot make only the 11th row to appear.