Dynamically insert blank rows between items in Excel | Make your analysis easier to read.

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0254 Blank row between items

★ Want to automate Excel? Check out our training academy ★

★ About this video ★
In this video, we explore a technique to insert blank rows in any array or range.

When faced with a sea of values, it can be difficult to see what's going on. This method helps to add some spacing and make everything easier to read.

0:00 Introduction to Inserting Blank Rows
0:28 Shaping the data
1:17 Building the formula
5:58 Apply to different scenarios
6:59 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[mouhammadwasseemshaabani]

The way you are using Excel is exactly what it was designed for, your videos is like a gift, Thank you so much!

[Geeko]

I don't comment very often on YouTube. But in this case I am very astonished by the way Mark is showing on the fly how this formula works. Without this it would be very difficult for beginners to follow your thoughts. Thanks a lot for these kind of videos. Very valuable!🎉

[nckyr]

Wow, I thought I was okay at Excel but this went mostly over my head 😁 time for me to study a little to be able to pick this apart and understand it 😊

[stefankirst]

This is totally insane 😂 Once again: Mind blown 🤯 - Very well explained as always but I still can't wrap my ahead around the DROP<>DROP line. I guess I'll have to try it to understand it.

[reanalytics]

This is a great technique
Though it will be easier if we see the results of each step for better understanding

[ernstborgener]

Thanks Mark, a nice video. Above all, a good and understandable explanation.
I would like to point out another way in which this can be achieved.

=IF(ISERROR(INDEX(SORT(VSTACK(Data, UNIQUE(Data[Product])&" ")), , 2)), "", SORT(VSTACK(Data, UNIQUE(Data[Product])&" ")))

Salü
Ernst

[Ratnakumarwrites]

Great explanation as ever... Thanks Mark..

[muhammadtambawala]

Thank you Mark for another fantastic video on dynamic array formulas. My formula below is a humble attempt, but yours is truly impressive!

=LET(
data, GROUPBY(Data[[Product]:[Region]], Data[Value], SUM),
UniqueProduct, UNIQUE(CHOOSECOLS(data, 1)),
UniqueProductIndex, HSTACK(SEQUENCE(COUNTA(UniqueProduct)), UniqueProduct),
datawithIndex, HSTACK(XLOOKUP(CHOOSECOLS(data, 1), CHOOSECOLS(UniqueProductIndex, 2), CHOOSECOLS(UniqueProductIndex, 1)), data),
result, VSTACK(Data[#Headers], DROP(SORT(IFNA(VSTACK(datawithIndex, CHOOSECOLS(UniqueProductIndex, 1)), "")), -1, 1)),
result)

[joshuayaco]

This is superb! I just want to ask if we can do this returning month from a table? I want to return month horizontally with 2-3 columns in between month. Thank you!

[grahamc]

I use the EXPAND function to add blank rows in combination with REDUCE to stack results

=LET(
uniqueProducts, UNIQUE( SORT( Data[Product] ) ),
calc, REDUCE( "", uniqueProducts,
LAMBDA( accumulator, current,
VSTACK( accumulator,
VSTACK( EXPAND( "", 1, COLUMNS( Data ), "" ), FILTER( Data, Data[Product] = current ) ) ) ) ),
result, VSTACK( Data[#Headers], DROP( calc, 2 ) ),
result
)

[josh_excel]

Here is how I've done this before:
1. Add an index column using Match so each unique word get's it's own number
2. Make a list of those unique numbers and add .5 to it and append as column with table of blank rows
3. Combine 2 tables
4. Sort combined table by numbers so blank rows will fall after each applicable number
5. Remove Index column as needed

[tinhoyhu]

@5:40 is it assumed that SORTBY just chooses the blank rows to go first when the index is the same, because it is encountered first in the array? For example, Blank 3 comes before Bravo 3 after the SORTBY because the Blank rows got VSTACKed before the Calc rows.

[Excelambda]

Great video !
LAMBDA(a,
LET(
a, SORT(a),
x, TAKE(a, , 1),
s, SEQUENCE(, ROWS(x)),
i, s * (UNIQUE(x) = TOROW(x)),
j, IF(s = BYROW(i, MAX) + 1, -1, i),
IFERROR(INDEX(a, TOCOL(IFS(j, j), 2), SEQUENCE(, COLUMNS(a))), "")
)
)

[willmartin]

Thank you for the videos, I am slowly learning a lot. I am just wondering if you have any videos/courses that would help me solve my power query hurdle - it's driving me mad! I have made a query that cleans a daily bank statement ready for data input, I want to use this query for each daily statement that comes in.
The only way I can see to do this is to copy the script and paste it in the advanced editor. As I say, if you could point me in the right direction or give any help I would be super grateful.

[excelenthusiasts]

Hi,

This works as well

=WRAPROWS(TOCOL(HSTACK(I2:J14, IFS(I2:I14<>DROP(I2:I14, 1), EXPAND({"", ""}, , 2))), 3, FALSE), 2)

One will have to additionally sort.

[williamarthur]

Just to say, I you wnat to insert at a gvine interval, using let you cna crat a virtual column,
so to insert every 5 ; QUOTIENT(SEQUENCE(20, 1, 0, 1), 5) and this can just be referred to vial index, it neendn't
be an actual part of the table to which you are inserting rows.

[ruben_rositto]

Thanks, Mark, very flexible. What do you think of this one?
LET(


WithBlanc)

[KimzCraftz]

Stunning formula, Mark. Could we also do this in PowerQuery? perhaps you could show us, if it is possible. Also, I find the pivot table add blank row option doesn't always do it where I want it to. Perhaps I'm missing a step with that?

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0254 Blank row between items

[workplace-yw]

=LAMBDA(array, col_num,
LET(
for, array,
seq, SEQUENCE(ROWS(for), 1, 1, 1),
check, (CHOOSECOLS(for, col_num)<>INDEX(for, seq-1, col_num))*1,
sum, seq+check,
if, IF(check, seq, 1/0),
toc, TOCOL(VSTACK(sum, if), 3, TRUE),
final, DROP(SORT(IFNA(HSTACK(toc, seq, check, sum, if, for), ""), 1, 1), , 5),
final))

array - formula or array
col_num - column number of array on which you want to do grouping and put blank row thereafter.