Excel Insert Blank Row After Every Two Data Rows #Shorts

LC has 4000 rows of Excel data. After every two rows, he wants to insert a blank row. How can he quickly insert 2000 blank rows in the data? My solution today involves a formula to create alternating cells with text and numbers. Then Home, Find & Select, Go To Special. Select all Formula cells that result in Text. This selects every other row. You can then Insert Sheet Rows and you are done in less than 60 seconds.

Transcript of the video:

L.C. has 4000 rows of data.
He needs insert one blank row after every two data rows.
This is going to use Go To Special. Type some text here, a number here.
Equal sign and point to the cell two cells above. Double click the fill handle to shoot that down.
That puts text on every other row. Control G for Go To.
Click Special. Choose Formulas. Unselect Numbers, Logicals, and Errors.
That leaves only the text selected, which gives us every other row.
Home, Insert, Insert Sheet Rows. Let the spinning circle spin for a good 20 - 30 seconds. Don't worry, it's inserting 2000 rows.
And you're done. Clear Column F, press the Delete key.
Great question from LC! Thanks for watching.
Click Subscribe.

 

[Peter_SSs]

Here's a fully dynamic array formula:

If you can type really fast, maybe you can beat the record time.

Timing would have to include defining 'data' (or getting the data address directly into the formula, copy/paste or re-type the headings, reformat the Date column, adjust columns widths etc.

In any case, though clearly this is not my thread/challenge, I don't see this sort of answer as a solution to "Insert Blank Row After Every Two Data Rows". Instead, it inserts blank cells after every two copies of the data. Further, any formulas or individual formatting in the data would be lost in the copy.

 

[MrExcel]

Here is a solution from Meni on LinkedIn:
1. Click in cell F5 to select it
2. Enter this formula in F5: =IF(MOD(ROW(),3),INDIRECT(CHAR(65+COLUMN()-6)&INT(ROUNDUP(ROW()+1/3,0)-ROW()/3)),"")
3. Select F5:J5, press Ctrl+R to fill right
4. Ctrl+G for Go to
5. Type J6005. Hold down Shift while you click OK
6. Wait for cells to calculate
7. Ctrl+C, Alt+esv to convert to values
8. Delete columns A:E
9. Delete Row 4
I am slow at typing formulas. Even by limiting the typing to =I⇥MO⇥ROW⇥),3),INDI⇥CH⇥65+COL⇥)-6)&INT⇥ROUNDU⇥ROW⇥)+1/3,0)-ROW⇥)/3)),"") It still took me 59 seconds.

 

[MrExcel]

That Power Query solution –although fully dynamic– is way too slow! Who wrote that?... Oh wait, that was me!
Here's a fully dynamic array formula:
call the data –er– "data".
Then try this formula in an adjacent cell (don't bother throwing away the original data):
=LET(expanded,WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(data),10),SEQUENCE(ROWS(data)/2,5,0,0))),5),IF(expanded=0,"",expanded))

If you can type really fast, maybe you can beat the record time.

Hey ExcelLambda, surely you can do better. Give it a shot!

Alright Geert - I like this formula. I changed a couple of things. The named range is "d" instead of "data" because who has type to type "ata" three times?
After selecting A5, Ctrl+Shift+Down+Right, I used Alt+F3, d, Enter to name the range. This is a trick I learned from Diarmuid and wrote up in Strategic Finance magazine.
I shortened the formula typing by using Tab in a lot of places. This also adds the opening parenthesis. I still had to type a lot of closing parentheses.
=LET⇥e,WR↓⇥toc⇥hs⇥wr↓⇥toc⇥d),10),seq⇥rows⇥d)/2,5,0,0))),5),if⇥e=0,"",e))

I was able to get it down to 35 seconds. Someone familiar with the formula could do it faster.

 

[GeertD]

Alright Geert - I like this formula. I changed a couple of things. The named range is "d" instead of "data" because who has type to type "ata" three times?
After selecting A5, Ctrl+Shift+Down+Right, I used Alt+F3, d, Enter to name the range. This is a trick I learned from Diarmuid and wrote up in Strategic Finance magazine.
I shortened the formula typing by using Tab in a lot of places. This also adds the opening parenthesis. I still had to type a lot of closing parentheses.
=LET⇥e,WR↓⇥toc⇥hs⇥wr↓⇥toc⇥d),10),seq⇥rows⇥d)/2,5,0,0))),5),if⇥e=0,"",e))

I was able to get it down to 35 seconds. Someone familiar with the formula could do it faster.

Here's a shorter version of the formula:

• =WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),10),IF(SEQUENCE(ROWS(d)/2,5,0,0)=0,"",""))),5)

Or in Excel editor Intellisense speak:

• =wr↓⇥to⇥hs⇥wr↓⇥to⇥d),10),i⇥seq⇥rows⇥d)/2,5,0,0)=0,"",""))),5)

Does exactly the same thing. A trained pro who learned to type properly on a keyboard should be able to do this in 20", or even slightly less.
(I'm neither )

 

[GeertD]

Thanks to professor Xlambda, I can shave off a few more characters of my formula:

• =WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),10),IF(SEQUENCE(ROWS(d)/2,5),""))),5)

in Excel Editor speak:

• =wr↓⇥to⇥hs⇥wr↓⇥to⇥d),10),i⇥seq⇥rows⇥d)/2,5),""))),5)

 

[GeertD]

Thanks to professor Xlambda, I can shave off a few more characters of my formula:

• =WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),10),IF(SEQUENCE(ROWS(d)/2,5),""))),5)

in Excel Editor speak:

• =wr↓⇥to⇥hs⇥wr↓⇥to⇥d),10),i⇥seq⇥rows⇥d)/2,5),""))),5)

Here's my function in LAMBDA-form (d is a defined name for any data array):

AddBlankRow_Better Still.xlsx
	A	B	C	D	E	F	G
1	dim: 30 x 5
2	=LAMBDA(d,[b],[g],LET( cols,COLUMNS(d),rows,ROWS(d),ga,ABS(g), gap,IF(SEQUENCE(rows/b,ga*cols),""), e,WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),b*cols),gap)),cols), IF(g>0,e,VSTACK(IF(SEQUENCE(b,cols),""),e)) ))(d,2,1)
3	aaaa	bbbb	cccc	dddd	eeee
4	aaaa	bbbb	cccc	dddd	eeee
5
6	aaaa	bbbb	cccc	dddd	eeee
7	aaaa	bbbb	cccc	dddd	eeee
8
9	aaaa	bbbb	cccc	dddd	eeee
10	aaaa	bbbb	cccc	dddd	eeee
11
12	aaaa	bbbb	cccc	dddd	eeee
13	aaaa	bbbb	cccc	dddd	eeee
14
15	aaaa	bbbb	cccc	dddd	eeee
16	aaaa	bbbb	cccc	dddd	eeee
17
18	aaaa	bbbb	cccc	dddd	eeee
19	aaaa	bbbb	cccc	dddd	eeee
20
21	aaaa	bbbb	cccc	dddd	eeee
22	aaaa	bbbb	cccc	dddd	eeee
23
24	aaaa	bbbb	cccc	dddd	eeee
25	aaaa	bbbb	cccc	dddd	eeee
26
27	aaaa	bbbb	cccc	dddd	eeee
28	aaaa	bbbb	cccc	dddd	eeee
29
30	aaaa	bbbb	cccc	dddd	eeee
31	aaaa	bbbb	cccc	dddd	eeee
32
LAMBDA

Cell Formulas
Range	Formula
A1	A1	=IFERROR("dim: "&ROWS(A3#)&" x "&COLUMNS(A3#),"")
A2	A2	=FORMULATEXT(A3)
A3:E32	A3	=LAMBDA(d,[b],[g],LET( cols,COLUMNS(d),rows,ROWS(d),ga,ABS(g), gap,IF(SEQUENCE(rows/b,ga*cols),""), e,WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),b*cols),gap)),cols), IF(g>0,e,VSTACK(IF(SEQUENCE(b,cols),""),e)) ))(d,2,1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
d	='One Cell Solution 2'!$B$3:$F$22	A3
'One Cell Solution 2'!data	='One Cell Solution 2'!$B$3:$F$22	A3

 

[Xlambda]

Thanks to professor Xlambda, I can shave off a few more characters of my formula:

• =WRAPROWS(TOCOL(HSTACK(WRAPROWS(TOCOL(d),10),IF(SEQUENCE(ROWS(d)/2,5),""))),5)

in Excel Editor speak:

• =wr↓⇥to⇥hs⇥wr↓⇥to⇥d),10),i⇥seq⇥rows⇥d)/2,5),""))),5)

"professor Xlambda". Look who's speaking, Professor Geert

 

[GeertD]

"professor Xlambda". Look who's speaking, Professor Geert

Ha ha, now make no mistake about it: I know when I'm outclassed.
BTW: this formula is even better (less characters):

• =WRAPROWS(TOCOL(EXPAND(WRAPROWS(TOCOL(d),10),ROWS(d)/2,15,"")),5)

 

[Xlambda]

Ha ha, now make no mistake about it: I know when I'm outclassed.
BTW: this formula is even better (less characters):

• =WRAPROWS(TOCOL(EXPAND(WRAPROWS(TOCOL(d),10),ROWS(d)/2,15,"")),5)

Glad you used EXPAND after all.
Formulas will always lose for sigle tasks against manual methods, but for let's say 3 or 4 data sets, different spreadsheets, different arrays, and variable gap size, will win.
There could be 3 scenarios,
Manual methods to compete for execution time.
Formulas to compete for large data sets like 1M rows.
And between them, for how many data sets formulas will take over manual methods.

 

[Xlambda]

And in terms of formula lengths, the shortest is not always the best. Efficiency should come first, then elegance. There are numerous examples, tasks solved by iterations with a formula half formula bar row long, that takes minutes to solve, and other formula, "analogic", 3 formula bar rows long that returns an answer in a split second.
Cut to the chase. Does this qualify?
=IFNA(INDEX(d,TOCOL(EXPAND(SEQUENCE(4000),,2)),{1,2,3,4,5}),"")
d, defined name array 4000x5, 1 row gap, for 2 rows gap, EXPAND(SEQUENCE(4000),,3)) ; no other presetting