Excel Insert Blank Row After Every Two Data Rows #Shorts

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 21, 2022 .
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.
maxresdefault.jpg


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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,596
Office Version
  1. 365
Platform
  1. Windows
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.
 
Joined
Feb 8, 2002
Messages
3,186
Office Version
  1. 365
Platform
  1. Windows
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.
 
Joined
Feb 8, 2002
Messages
3,186
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 22, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 22, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 22, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1dim: 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)
3aaaabbbbccccddddeeee
4aaaabbbbccccddddeeee
5
6aaaabbbbccccddddeeee
7aaaabbbbccccddddeeee
8
9aaaabbbbccccddddeeee
10aaaabbbbccccddddeeee
11
12aaaabbbbccccddddeeee
13aaaabbbbccccddddeeee
14
15aaaabbbbccccddddeeee
16aaaabbbbccccddddeeee
17
18aaaabbbbccccddddeeee
19aaaabbbbccccddddeeee
20
21aaaabbbbccccddddeeee
22aaaabbbbccccddddeeee
23
24aaaabbbbccccddddeeee
25aaaabbbbccccddddeeee
26
27aaaabbbbccccddddeeee
28aaaabbbbccccddddeeee
29
30aaaabbbbccccddddeeee
31aaaabbbbccccddddeeee
32
LAMBDA
Cell Formulas
RangeFormula
A1A1=IFERROR("dim: "&ROWS(A3#)&" x "&COLUMNS(A3#),"")
A2A2=FORMULATEXT(A3)
A3:E32A3=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
NameRefers ToCells
d='One Cell Solution 2'!$B$3:$F$22A3
'One Cell Solution 2'!data='One Cell Solution 2'!$B$3:$F$22A3
 

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
659
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 22, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
"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

Well-known Member
Joined
Mar 8, 2021
Messages
659
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 8, 2021
Messages
659
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Forum statistics

Threads
1,176,429
Messages
5,903,074
Members
435,005
Latest member
AXILLIAN

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