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.

 

[smozgur]

I thought the following was going to be faster but it takes about the same time.

With VBA in the Immediate window, it can be also accomplished by using the following one liner. The range is supposed to be selected for this implementation.

For i = Selection.Rows.Count + Int((Selection.Rows.Count \ 2) * 2 = Selection.Rows.Count) To 2 Step -2: Selection.Rows(i).Insert xlDown:Next i

 

[Peter_SSs]

A slight variation without the need for formulas (& a couple less clicks in the GoTo dialog)

1. Leave cell F5 blank
2. Put a 1 in F6
3. Select F6 and F7 and double-click the Fill Handle
4. F5 -> Special.. -> Blanks -> OK
5. Home, Insert, Insert Sheet Rows.
6. Clear column F

 

[MrExcel]

A slight variation without the need for formulas (& a couple less clicks in the GoTo dialog)

1. Leave cell F5 blank
2. Put a 1 in F6
3. Select F6 and F7 and double-click the Fill Handle
4. F5 -> Special.. -> Blanks -> OK
5. Home, Insert, Insert Sheet Rows.
6. Clear column F

Hi Peter - I love this improvement. I managed to get my version down to 17 seconds (see:

)
With your improvements, I can easily shave a few seconds off of that.

 

[Peter_SSs]

With your improvements, I can easily shave a few seconds off of that.

Cheers.

 

[MrExcel]

A slight variation without the need for formulas (& a couple less clicks in the GoTo dialog)

1. Leave cell F5 blank
2. Put a 1 in F6
3. Select F6 and F7 and double-click the Fill Handle
4. F5 -> Special.. -> Blanks -> OK
5. Home, Insert, Insert Sheet Rows.
6. Clear column F

Hi Peter - that makes you the champion right now.... 11.9 seconds. Thanks for these steps.

 

[MrExcel]

Diarmuid Early pulls it off in 12 seconds:

 

[MrExcel]

My friend Dan Mayoh sent in an interesting set of steps:
Give the original data the name D.
In G1 =SEQUENCE(6000)
In H1 enter this formula: =IF(MOD(G1#,3)=0,"",INDEX(DATA,ROUNDUP(G1# * 2/3,0),COLUMNS(D)))
copy/paste as values
delete the original data.

This method works great, but I am too slow typing the formula to beat Diarmuid's or Peter's method. However, for someone who wants a formula, this is a good way to go.

 

[MrExcel]

Here is a Power Query solution from Geert Delmulle:

let
Source = Excel.CurrentWorkbook(){[Name="fInput"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
RemoveOtherColumns = Table.SelectColumns(AddIndex,{"Index"}),
InsertModulo = Table.AddColumn(RemoveOtherColumns, "Modulo", each Number.Mod([Index], 2), Int64.Type),
    FilterRows = Table.SelectRows(InsertModulo, each ([Modulo] = 0)),
    AppendQuery = Table.Combine({AddIndex, FilterRows}),
SortRows = Table.Sort(AppendQuery,{{"Index", Order.Ascending}}),
RemoveColumns = Table.RemoveColumns(SortRows,{"Index", "Modulo"}),
PromoteHeaders = Table.PromoteHeaders(RemoveColumns, [PromoteAllScalars=true]),
    RemoveTopRow = Table.Skip(PromoteHeaders,1),
ChangeType = Table.TransformColumnTypes(RemoveTopRow,{{"Region", type text}, {"Product", type text}, {"Customer", type text}, {"Date", type date}, {"Revenue", type number}})
in
    ChangeType

 

[GeertD]

Here is a Power Query solution from Geert Delmulle:

let
Source = Excel.CurrentWorkbook(){[Name="fInput"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
RemoveOtherColumns = Table.SelectColumns(AddIndex,{"Index"}),
InsertModulo = Table.AddColumn(RemoveOtherColumns, "Modulo", each Number.Mod([Index], 2), Int64.Type),
    FilterRows = Table.SelectRows(InsertModulo, each ([Modulo] = 0)),
    AppendQuery = Table.Combine({AddIndex, FilterRows}),
SortRows = Table.Sort(AppendQuery,{{"Index", Order.Ascending}}),
RemoveColumns = Table.RemoveColumns(SortRows,{"Index", "Modulo"}),
PromoteHeaders = Table.PromoteHeaders(RemoveColumns, [PromoteAllScalars=true]),
    RemoveTopRow = Table.Skip(PromoteHeaders,1),
ChangeType = Table.TransformColumnTypes(RemoveTopRow,{{"Region", type text}, {"Product", type text}, {"Customer", type text}, {"Date", type date}, {"Revenue", type number}})
in
    ChangeType

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!