Excel RECURSIVE Lambda - Create loops with ZERO coding!

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
The new Excel LAMBDA Function allows you to create your own CUSTOM FUNCTION without any coding. In this tutorial we'll take it to the next level and I'll show you how you can create a RECURSIVE Lambda in Excel. This is a function that's continuously looping through your data until it reaches a point of exit. What's the best way to write a recursive lambda?
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/lambda-file
Writing Recursive Lambdas can be quite challenging because they are not easy to test or debug. Can you test it in the formula bar or do you have to type it directly in name manager? In this tutorial I'll teach you my approach on writing recursive lambdas from scratch.
Important: Excel Lambda function is available only in Excel for Microsoft 365.
Watch the first Excel Lambda video here: • Excel LAMBDA - HOW & W...
00:00 What a Recursive Lambda Function in Excel Can Do
02:02 How to Create a Recursive Lambda Function in Excel
09:12 Excel Lambda in Name Manager
10:32 Practical Example of Recursive Lambda in Excel
LINKS to more videos about Excel's new functions: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #lambda

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/lambda-file

[@SLouadah]

You know you are lost to humanity when you get excited for a new function on Excel 😂😂😂 You rock Leila !

[@benterrell9139]

The most powerful statement you made was right at the end. The next generation of excel users will be intuitively learning functional programing techniques. I suspect that this was perhaps the deeper reason behind Simon Peyton Jones decision to join Microsoft. Personally I am delighted as I know from experience that it takes time to become fluent with these techniques.

[@cmukesh19]

Absolutely agree. I was in the camp that VBA aged, and we should provide support for language such as Python/ R natively in Excel. But after seeing these recent paradigm shift in Excel programmability, I'll agree that I was short sighted. Any general-purpose programming language is a distraction for the population who uses Excel. I am a programmer, and still love Excel as it allows you to focus on the activity rather than how to do it. Lambda (and all the recent functions to support it) feel so tightly integrated. This is probably the best evolution in Excel, ever.

[@butterplum2053]

That’s interesting-SPJ has worked a lot on the same kinds of things-perhaps with more emphasis on formal methods intersecting with domain languages. He seemed to weigh in stuff like formal methods in secure operating systems, while still being hyper focused on thinking about language ecosystems like his comparisons about Java. Having just stopped running a company I am back in a job in finance, yet I still have a heavy addiction to programming. This is giving me exactly what I need to stay interested in what I’m doing. I’m seeing that there’s actually good structure here-and it is textbook ML style FP. However I still get bugged by the functions not being first class polymorphic functions where you can pass in labels with values dependent on higher order functions though. Example…I’m typing in a structured reference this morning like to a table called [APR] which is also a name of a table column like [@[APR]], and yet there are no operads for me to touch the concept of the string-it’s still objects. It’s still something that is not well defined in the sense of Grothendieck Universes, Agda, or even Haskell’s system F. Another example-I’m writing a Sumif with structured references and embedded conditional criteria-but it’s dax sometimes and it’s something else other times. I can’t know how the value will be represented in memory-will an embedded conditional be passed up to a Calling function, hit a type error I never see, or is it simply an undefined parameterization because the designers didn’t intend for users to make criteria into multi-function call chains? I’ve been in the lisp world a bit more than usual these past several months entertaining myself with nix and I can never imagine doing series fast paced corporate work with those-only building systems. And yet these don’t have the barriers that make it harder to express deep relationships about data and the many people oriented issues around that data. You’ve gotta write most stuff yourself, and outside of excel it’s not gonna be in some nice text editing space where the rules are standard. For example, no two emacs configs are alike. Just check out the many inconsistencies in packages between scala, OCaml, python, R, Rust, or Clojure, all kinda functional interfaces but not completely pure or well founded, and yet they still don’t really have great workspaces like an excel sheet or a power query editor. Kinda amazing.

[@setha9818]

She's a genius. Someone give her the Nobel prize right now.

[@awesh1986]

Agree

[@awesh1986]

Do I know Excel? Not at all. People call me an Excel trainer but I think every time I watch an amazing video of yours it just makes me feel, super awesome. Thanks a lot for all the wonderful video content you keep posting.

[@OzduSoleilDATA]

WOW! I don't know what to say. I'm a bit overwhelmed but fascinated.

[@at-excel]

Welcome in my world ;-)

[@madelaine0601]

This is just so mind-blowing that I don't mind feeling a little nerd watching an Excel video on a weekend! 😂🤯

[@eagersnap]

Great explanation of this concept. Note that the name you give your parameters will appear in the function screen tips (the little pop-up that comes under your function when you are inside it). So if you name them "Text", "Before" and "After" instead of t, b and a, then it will be much easier for the user to figure out what to input in each parameter.

[@DingusBatus]

You make it look so easy, yet I’m sitting here thinking you’re using some form of magic. 🧐 What? How? 🤪 I love watching an artist at work. The ability to start with nothing and end up with something beautiful.🤩

[@LeilaGharani]

😊 thank you. It's definitely not the easiest topic 😁

[@excelismagical-shakeel4793]

Leila, watching your videos is like the quote: The MORE I know, the more I know How LESS I know.... Thank you for your efforts to make us understand this powerful function Lambda... Super Exited to start applying it for the tasks which were incomplete in past without looping functionality in Excel built-in functions

[@mustafamalik4211]

I come from a Python background and always wished there was a way to use loops in Excel without having to learn VBA. This video was my answer. Thanks!

[@willm7994]

This the best explanation of the recursive lambda I have seen thanks,

[@trs80model14]

Indeed it is, she chose a good example and explained it clearly, without going too fast. Well done Leila!

[@derrickadang9816]

This is just so great with your excellent explanation, been waiting for this second part of Lambda.

[@pwells2389]

Finally the ability to add coding, looping logic without VBA. Very nice and well explained . As always.

[@shaunwthompson]

You explained this PERFECTLY. You are the BEST!

[@Excelambda]

Great!! Wrote a recursive lambda that can handle the whole string column array RPLAB(ar,a,b,i)=LAMBDA(ar,a,b,i,LET(r,ROWS(a),IF(i=r+1,ar,RPLAB(SUBSTITUTE(ar,INDEX(a,i),INDEX(b,i)),a,b,i+1)))) --replace a with b for array ar and i=1 If you don't like extra variable i we write another one REPLACEAB(ar,a,b)=LAMBDA(ar,a,b,RPLAB(ar,a,b,1)) Anyhow "i" can be very handy in case you want to start the search from a different index in the replace list.

[@johnprevette7344]

I love your teaching style. Great job explaining such a complex concept!

[@IssueBoyStefan]

The universe is expanding! What's next - introducing for and while loops as functions in Excel maybe?! That will be awesome!

[@4verse79]

Or one could simply use VBA ...

[@ernahubbard2062]

@@4verse79 VBA gets complicated when a file needs to be stored on cloud or shared with different people.

[@shanelabuff7457]

@@4verse79 VBA functions are notoriously slower than inbuilt excel functions. LAMBDA essentially allows you create custom inbuilt functions at the speed of the inbuilt functions.

[@shanelabuff7457]

It exists in this video :) if(t="",t, MegaReplace(... AKA while t "" DO (function) t +=1 (or t = t + 1)

[@pmlover1810]

@@4verse79 a bit salty that the time it took to master vba will one day be rendered inefficient with these inbuilt functions

[@daseaton]

Excellent data hygiene tool! Can't wait to watch your next tutorial on this function.

[@Pityons]

It's really amazing! Thank you, Leyla, for sharing these super interesting news!

[@davidmanhire5532]

This would be the most exciting video you've done! Crystal clear explanation of recursive use of the Lambda function. Brilliant, thank you.

[@LeilaGharani]

Wow, thank you!

[@user-fd2dl2sy6j]

Just great. Perfect explanation, no useless info, to the point!

[@mariostudio7]

I have been waiting for this! Very exciting!

[@lnlefteris4744]

Happy new year! and with your amazing videos the year in the office will be more than wonderful!

[@at-excel]

Oh, Leila - this is an eye-opener! Thanks for sharing your knowledge. Actually I've got so many ideas to realize with LAMBDA, but recursive functions double the impact of this function. Can't wait to get the function at my insider-installation. Und ich hoffe auf 2021! Frohe Weihnachten und bleib gesund, Andreas.

[@LeilaGharani]

It really is a great development. Auch dir Frohe Weihnachten und ein gutes Neues Jahr!

[@BinsonPaulMenachery]

Brilliant. Couldn't stop saying this. I am really in love with these videos

[@Unbox747]

Amazing -- you explain this so clearly :) I am excited to try this out

[@enderkazakli9557]

Thanks a lot for this very helpful presentation. It's clear that LAMBDA is an other great improvement for an advanced Excel user. 💪

[@borgiedude]

What a fantastic feature, and what a fantastic video. Thank you Leila!

[@LeilaGharani]

Many thanks, Adrian!

[@joannadaanton2483]

I love this! This is super interesting. It's like programming visual basic inside a cell without the actual vb language. Thanks for this video! :)

[@LeilaGharani]

True 😁

[@davidmaes3253]

My thoughts excactly.

[@Rice0987]

History have been repeated again today! 😂 Yesterday i was watching this video, and today again i've helped to my colleague with this video. 🤗 Thanks a lot Leila!💃

[@LeilaGharani]

Amazing! Great job!

[@thinusgrobbelaar4788]

Amazing tutorial. Well done

[@curlywurly2021]

I remember this example from the Power Query List.Accumulate lecture! Great to see there is a new way to achieve this coming.

[@chrism9037]

Amazing, thanks Leila!

[@vijayarjunwadkar]

So finally the Thursday Video on LAMBDA is here! Thank you Leila for the nice example and simple explanation! Now waiting for this to become common to all users from Microsoft, for using it practically in some of my data processing challenges! 😊👍

[@LeilaGharani]

Thanks Vijay. Can't wait for this to become generally available.

[@s.y.daniel2137]

so well explained! thank you!

[@omotossso]

Great thinking from you, nice development. I feel like going back to my programming classes right now 💐

[@kvlpnd]

I think Excel is reaching its new heights in applications. Everyday it is providing something new.

[@LeilaGharani]

True. Lots of great developments recently.

[@georgespaulin3115]

impressive Many thanks Leila

[@TiborCES]

Very exciting! 10Q Leila!

[@TEKNISHA]

Excellent use case .. opens so many possibilities!!

[@yulinliu850]

A new level of Ms Excel! Thanks Leila!

[@LeilaGharani]

You're very welcome!

[@roberthenrydavis8304]

It will take a while to get my head around this one but with your help I've started. You have become my goto for anything Excel...Thanks!!

[@LeilaGharani]

Thank you, Robert!

[@kebincui]

Super. Thanks Leila ❤👍🌹

[@udusegbe]

Yep - it was definitely worth the wait since watching part 1! Thanks and liked the simple yet effective explanation on the term 'recursive', especially with the visual flow aid

[@LeilaGharani]

Great to hear!

[@humorousdave]

You are a brilliant tutor. Thanks

[@wayneedmondson1065]

Hi Leila. Awesome lesson and example! I love making VBA UDFs and it is kind of sad that VBA is at a dead end in terms of attention from Microsoft. But, these LAMBDAs look like fun and super powerful. Looking forward to testing them soon. Thanks for sharing :)) Thumbs up!!

[@ManojAg]

Colours of 2021... Grey and yellow...you are updated and so are we...thanks

[@trueindian2108]

You are such a great teacher!!!

[@AdolfoLeonSepulveda]

Hello Leila, you are very intelligent, a very good teacher and the best: very beautiful!

[@danielbejarano2734]

Love it! Love to problem solve so already imagining how to utilize it.

[@muneesraja6565]

I like your all Excel course

[@tuyoexcelypowerbi]

Incredible example!!!!

[@ribri01]

Thanks for teach and share with us !

[@nadermounir8228]

Very well explained Thank u Leila

[@chh8860]

Another Thursday 'Must-Watch' date with Leila … but today's OMG moments required a pot of black coffee to watch both Lamdda videos three times … and I still have much work to do. For me, many of these functions are like getting a drink of water with a firehose … but the ideas … ideas … ideas are numerous!!! I leave hard copy post-it notes and digital post-it notes all over my financial models to remind myself to review again and incorporate Leila's lessons. Overwhelming … but so good … so well explained. As always … your are a treasure … thank you … thank you … thank you …

[@LeilaGharani]

Trust me, it took me a long time and a lot of post-its to get the hang of it. But when it finally clicks it offers so many amazing possibilities. As always, thanks for your support!

[@MrManish261000]

This is going to help in many more ways which is hard to perceive at this moment as the function is new and limited in examples. Look forward to more examples in its application.

[@mrpage221]

Excellent as usual and very well explained. Thank you Leila.

[@LeilaGharani]

You're very welcome!

[@bryan6aero]

Thank you! I have wanted a way to do this on many occasions.

[@charlesngan8041]

Great videos, thank you

[@wmfexcel]

This is extraordinary! Thanks for the detailed explanation. bravo!

[@LeilaGharani]

Glad you enjoyed it!

[@mr.jaycasey4676]

Wow, Leila! This example is exceptionally cool in that it would allow you to add to your list of errors(b) and corrections(a) that it can fix without any change to your formula.

[@LeilaGharani]

Thank you! Yes. It's great that it's dynamic and no need for refresh or updating formulas.

[@dakshbhatnagar]

Hey Leila, Big fan and have been watching you for years. I recently came across the magic of Map and Lambda in Google sheets / Excel and I would definitely would love to take it for a spin. I haven’t had the chance to use recursive lambda but one day, for sure.

[@LeilaGharani]

Sounds great!

[@iawwad]

Wow so now we are having not only a custom functions in Excel but also a custom recursive ones... Thanks a million Laila

[@LeilaGharani]

You are welcome, Ibrahim 😊

[@waynethurman2144]

Thank you Leila!

[@AndreBeliveau]

Wow. It's a Christimas gift before Christmas. I just activated the beta channel so I be able to use Lambda. It will save me a lot of time and make my workbook easier to understand.

[@LeilaGharani]

Christmas came early for me too this year :)

[@FreakAzoiyd]

With BYROW and BYCOL this example can be done a lot easier, with the difference, that you will have to select the whole range of replacement words and not just the first cell. Thanks for the F2 tip in name manager that was super anoying until now.

[@x-el.s]

you are right! REDUCE might also help this to be leaner. (great video though :))

[@bazoo513]

Extremely useful - thanks. As a sidenote, you had to use recursion to actually implement iteration - this goes to illustrate that every recursive algorithm has an equivalent iterative one, and vice versa.

[@HowtoPowerBI]

amazing to see that it is possible now to create lambda functions in excel and that no vba is required 😀. Big game changer! Great explanation, thanks!

[@LeilaGharani]

Glad it was helpful!

[@JKPARRA]

Leila: Beautiful example, thanks

[@vida1719]

Wow. Great function and great explanation of new features

[@LeilaGharani]

That's really a great development in Excel. Thanks for watching!

[@WayanAdiana]

Very exciting function

[@iancasey1486]

I knew that Excel is very powerful but that powerful is shocker! Thanks Leila for teaching that other dimension of Excel so advanced and yet practical!

[@LeilaGharani]

Glad you enjoyed it!

[@VijayKumar-kh9qe]

Thanks for giving such wonderful knowledge

[@LeilaGharani]

It's my pleasure

[@TYoung023]

I’m going to be re-writing a lot of functions with this! I would be really helpful to have the pop up for the parameters when writing the function. Maybe, in addition to a comments section in name manager, there could be an option to add the parameters. Enjoying your videos!

[@maemartins]

Thanks for this video.

[@IncludeAkademia]

Thanks for this video. Many people do not even realize what a huge analytical tool Excel is.

[@LeilaGharani]

You're very welcome! Absolutely.

[@vivekphadke16]

This opens up Pandora's box, Now we have a tool to work on - try and try. Relate to our previous work, test where you can use it and create future intelligence. Thanks a Lot Lela

[@edge5817]

Awesome... its the vaccine of very long (helper columns) formula in excel! thank you Leila

[@VivekGupta1994]

The Father of all Functions is out here.. Thanks Leila for such wonderful explanation!! 😊

[@LeilaGharani]

You are so welcome!

[@denconstutube]

You have an excellent brain! This is very complicated, but you make it appear reasonable when you are explaining it. After you were done I realized that it is beyond me. However, it was wonderful and interesting just to listen to you.

[@LeilaGharani]

It definitely takes some time to get the hang of it.

[@1sav110]

Used Python for most of my data work but at my new job was forced to use Excel. Your tutorials have been a blessing. Really appreciate the work you put on these!

[@LeilaGharani]

Our pleasure, Daniel!

[@HusseinKorish]

Thanks Leila ...the way you explained Lambda function ... cleared alot of confusion for me

[@LeilaGharani]

Happy to hear that, Hussein!

[@eddieamorgan]

Excellent presentation!

[@LeilaGharani]

Thank you kindly, Eddie!

[@ericxu9350]

That's really awesome!

[@cdyard1]

Another great video and wonderful explanations. I still don't have this LAMBDA function but, thanks to you, I'll be ready for it. At last, I hope so. ;-) Oh, and I love it when you say "…here is how things can get complicated…". With Excel and people like you, life is never boring, there's always something to learn. So, a big thank you.

[@LeilaGharani]

Thank you, Claire!

[@abdelkrimmesaiahmed662]

OMG, that's great I need to see it again and again, thank you Leila for teaching me a lot of things about Excel

[@LeilaGharani]

You're very welcome!

[@salahderdar5148]

Awesome, super Thank you

[@rajanghadi1982]

Good one. I also solved tried solving this substitute and offset but was not able to complete because I couldn't make out exit strategy. I couldn't thought of it. Well done.

[@kingsleydyson4841]

Hi Leila. Awesome! I developed recursive functions when programming in LISP years ago, with a twist in that the function was able to modify iteslf on the fly during recursions and do things that I couldn't figure out how to implement any other way. I have missed that power since moving on to other languages. Great to see this functionality in Excel!

[@DaveSlavens]

Now there's a programing language I haven't touched in a VERY long time!! LISP = Lost in Stupid Parenesis

[@adnanabbasi9482]

hi Leila, You've explained the Recursive Lambda amazingly! Please make a separate video on MACHINE and explain what is call a machine.

[@ryderhook7068]

Thanks Leila really great explanation!

[@LeilaGharani]

Glad you think so!

[@Karthickchidambaram]

Mind Blowing

[@haashini10031985]

@leila Amazing lecture... Thanks

[@easysimplesmart]

wow. this is very cool... I have so many ideas....Thanks for exploring this.

[@LeilaGharani]

Have fun!

[@FredericLEGUEN-Excel]

Very well explained Leila. It's never easy to find a recursive example but this one rocks 👍

[@LeilaGharani]

Thank you! 😃

[@mostafa4321]

Wow, i am blown by the power of this function. Excell is slowly stepping into coding territory my head is still spinning but I will get it. thank you Leila :)

[@LeilaGharani]

My pleasure :)

[@dolphinloh7018]

It is better than using find and replace functions. Hopefully everyone can get to use it soon. Thanks for the long detail explaining of the use for this Lambda functions.

[@LeilaGharani]

You are welcome!

[@saviodcunha]

Thanks, Leila

[@habeebullakhan6690]

You r Super Awesome in Xcel Leila.... You have taken my Excel knowledge to an entirely new level... Thanks for ur great work.. Keep going....

[@LeilaGharani]

Glad to hear that