Advanced Excel Functions | BYROW vs MAP vs SCAN vs REDUCE | Excel Off The Grid

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

★ Get the example file ★
Sign up for our free Insiders program and get the example file:

★ About this video ★
In this video we compare 4 of Excel's LAMBDA help functions to find out what they do and when to use them.

INCLUDES:
0:00 Introduction
0:29 Data
1:11 BYROW
5:34 MAP
7:10 SCAN
9:50 REDUCE
12:10 Wrap up

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

★ Where to find Excel Off The Grid ★

#MsExcel

[IvanCortinas_ES]

Very didactic explanation, clearly showing the difference between these functions. Thank you, Mark.

[DataVisualisation]

Explained briefly and concisely but very clearly and comprehensibly. If anyone has not understood it yet ...

[Al-Ahdal]

The most comprehensive tutorial on a very important new functions.

[akulanarayana]

Wow what a presentation which is very important.🎉👍👏👌

[tibibara]

I think, this is the most comprehensive and clear comparative tutorial about these 4 LAMBDA helpers. Thanks!

[pamphlex]

This is the current best video about all those functions in excel.

[westleyempeigne]

Thanks Mark, that’s looks clearer now, what each one does and the differences between them!

[Rice]

Simply great lesson! Thank you, Mark!😊
Recently I've started to learn these questions, but your video just get in time!
Now I'll just apply it on my daily work!
THANKS A LOT!🤗

[ennykraft]

Great video. Thank you! I rarely use these functions so that was really helpful.
Btw this scenario is the reason why I'm head of heels with GROUPBY and PIVOTBY.
First example: =SUBSTITUTE(GROUPBY(Data[Item], Data[Value], ARRAYTOTEXT, 0, 0), ", ", "|")
For the cross tabulated array, I'd use =SUMIFS(Data[Value], Data[Item], I6#)
For PIVOTBY I'd first create my array in I5 with this function.=PIVOTBY(Data[Item], Data[Quarter], Data[Value], SUM, 0, 0, , 1)
And then =IFERROR(CHOOSECOLS(I5#, 6)*1.2, "") Of course you can replace I5# with the PIVOTBY
(PS I'm German and hopefully I didn't mess up the functions and the commas)

[DinoDelight]

Another great video, love how your exploring Lambda more

[stevereed]

Very nice Mark, thank you. I would like to see more videos on SCAN and REDUCE

[spen]

Mind blown... Love it ! 🤯🤯 Thanks for sharing

[viktorasgolubevas]

Perfectly summarized and structured, as always. Thanks a lot!
And a winner is... PIVOTBY :)

[christoslefkimiotis]

nice video, but very hard to follow maybe because I am an old school xl user, I recently discovered this world of xl. I believe that it would be better instead of you typing the areas to just select them as it might be confusing for some users, it was for me at least as I couldn't understand what you were trying to do and I was trying to locate it on the screen. But is a good video

[Excelambda]

Great video!!
TJ can ignore empty so we can exclude filter:
=MAP(F6#, LAMBDA(r, TEXTJOIN("|", , IF(Data[Item]=r, Data[Value], ""))))

groupby alternative that looks cool:
=GROUPBY(Data[Item], "|"&Data[Value]&"|", CONCAT, , 0)

Makes more sense and it's very easy to do scanning running totals "byrow" not cumulative. BYROW can not spill more than 1 val/row, REDUCE with VSTACK rows is the slowest method.
We can still use SCAN with an accumulator that sets to 0 at the beginning of each row
For an array "a" of x rows and y columns running tot for each row :
=SCAN(0, SEQUENCE(x, y), LAMBDA(v, i, LET(m, MOD(i-1, y), IF(m, v, 0)+INDEX(a, QUOTIENT(i-1, y)+1, m+1))))

for your data, clean running tot 1.2* byrow:
=SCAN(0, SEQUENCE(3, 4), LAMBDA(v, i, LET(m, MOD(i-1, 4), IF(m, v, 0)+1.2*INDEX(J6#, QUOTIENT(i-1, 4)+1, m+1))))
returns:
74.4 154.8 224.4 328.8
80.4 158.4 234 319.2
96 210 272.4 370.8

[jerrydellasala]

Great video! These are great functions that aren't covered enough!
It's amazing how much MS is doing to Excel! I'm on the Beta Insider edition (Version 2409 Build 16.0.18011.20000), and when I was following along in the BYROW section around 5:17, when I typed the comma in the "=BYROW(J6#, " functions popped up the way they do in the GROUPBY and PIVOTBY functions. I'm able to use the formula "=BYROW(J6#, SUM)*1.2" without having to use a LAMBDA! No such luck with the other functions, but something to look forward to!

[chilaw]

“Let’s go!”? What happened to “Let’s get started!”? New functions, new catchphrases - same great explanations from Mark.

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0220 BYROW MAP SCAN REDUCE Comparison

[RichardJones]

Made my head hurt, but at least I understood it lol

[AnshulMarele-gbaa]

Hi, you always post very informative videos. I have an Excel365 file which data from the odbc table which updates automatically in scheduled intervals, how can I sync that Excel data into Google Sheets with auto updates?