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.

 

[GeertD]

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

We've discussed efficiency vs. beauty/preference before, and yes: "the guy with the smartest algorithm (most efficient) always wins".
Halas, does not qualify, because you can't control the block-size (=1), only the gap-size (and the block-size needs to be 2).
So, this is efficient, but not effective in the current context.

 

[Xlambda]

No problem, I've tried. ✌. At 350 cpm (chars/min) typing speed, could have been completed in 10.8s With Tab key for functions, even less

 

[Xlambda]

For fun only, not to compete, Geert's is unbeatable, only as alternative technique for 2/block.
=IFERROR(INDEX(d,TEXTSPLIT(TEXTJOIN({",",",-1,"},,SEQUENCE(4000)),,","),{1,2,3,4,5}),"")

 

[GeertD]

For fun only, not to compete, Geert's is unbeatable, only as alternative technique for 2/block.
=IFERROR(INDEX(d,TEXTSPLIT(TEXTJOIN({",",",-1,"},,SEQUENCE(4000)),,","),{1,2,3,4,5}),"")

Hey Xlambda, of course you should compete and beat me! You always do and I respect that.
But in this case your formula is still longer in terms of all characters.
Besides, you use text functions (from view it is not immediately obvious to me how this can be generalized), whereas I have a more geometrical approach, strictly focusing on reshaping the data-array.
The generalization of that (arbitrary block size and arbitrary gap size) is pretty much straightforward as can be seen in my LAMBDA above.
BTW: as you know, those manual "use the Excel interface" solutions are even much more efficient than what we achieve in terms of required keystrokes.
(but it's a lot of fun and we like to create functions )

 

[Xlambda]

I only wanted to share the multi delimiter technique in textjoin to create patterns that can be useful in other scenarios., Off the record.
And yes, can be generalized, this lambda creates the index patterns of "n" elements for any block size "x", and any gap "g" as seen here INSRWS
ZINS(n,x,g) (Zeros Insert function
=LAMBDA(n,x,g,LET(d,IF(SEQUENCE(x)<x,",",","&REPT("0,",g)),--TEXTSPLIT(TEXTJOIN(d,,SEQUENCE( n),,",")))

 

[GeertD]

Very nice! Indeed while I chose the geometry (reshaping) route, you chose the pattern route. Good job (as always).
There are a few disbenefits of your method that I don't see how to avoid:

• you impose the text format,
• whatever character you use as a gap-pattern: it will interfere with the data if that pattern occurs there.

My geometry/reshaping route doesn't do this.
(My Power Query solution ends on a ChangeType step as well, but in hindsight I would drop that step because it is not needed, rendering the solution a lot more robust.)

 

[Xlambda]

Very nice! Indeed while I chose the geometry (reshaping) route, you chose the pattern route. Good job (as always).
There are a few disbenefits of your method that I don't see how to avoid:

• you impose the text format,
• whatever character you use as a gap-pattern: it will interfere with the data if that pattern occurs there.

My geometry/reshaping route doesn't do this.
(My Power Query solution ends on a ChangeType step as well, but in hindsight I would drop that step because it is not needed, rendering the solution a lot more robust.)

The formula creates only the patern of indexes, always sequence of numbers, so is no text pattern to interfere ever
And to a pattern of indexes always what is inserted are 0's because this is what formula does Zeros Insert.
So no worries, everything is consistent, and was only presented as an alternative. As you well know I have also embraced the geometry method. My posts stand for that.

 

[GeertD]

The formula creates only the patern of indexes, always sequence of numbers, so is no text pattern to interfere ever
And to a pattern of indexes always what is inserted are 0's because this is what formula does Zeros Insert.
So no worries, everything is consistent, and was only presented as an alternative. As you well know I have also embraced the geometry method. My posts stand for that.

You are absolutely right, my bad: on the fly I mistook ZINS for the function that does the insert of rows (INSRWS).
I'll need to take another look at your geometry solution (INSRWS)... Oh my! You really went to town on it yesterday (I looked at it last weekend): that's exactly why you are the prof!
Those generalizations are amazing!