Yes I will be using this I just opened up a kitchen and have many many different types of of spreadsheets keeping track of different types of data thank you very much
I have used lambda in a spreadsheet with 16 sheets for different states and product lists. The key formulae were identical except for a couple of parameters. Putting those formulae into lambdas with parameters, removed all the complexity from the worksheet itself. To me, a lambda is the equivalent of what we do when programming. If we find ourself repeating code, we write a separate function for that code and call it when we need it, which is both simpler and safer.
Your content is brilliant Leila, I hope to share my work with you, obviously and it is very clear that I would learn much more from you, but my experience is that you always learn when you share knowledge. Thank you for such quality!!!!!
Thanks Leila. With all of the "newer" dynamic array functions as well as Lambda functions, it really triggers me to evaluate my work flow and spreadsheet design. The largest obstacle preventing a shift is the inability to share files with non MS365 users. Thanks for sharing...
@@LeilaGharani … not forgetting just ‘plain availability’ of those. So far even lambda isn’t generally available, even if you are in a ‘office insider’ channel.
@@stuckcamping Or learn both. Each has pretty distinct use cases. The dynamic UI of Excel is hard work to reproduce in other environments, while the maintainability and portability of SQL are often the most important factor. Use each where they are the natural choice, and even use both together sometimes. And add some Python too for a very solid analytics tripod.
Is this wonderful or what😱? I never knew what Lambda was until seeing this video! And these new functions really do simplify the process by preventing copy and pasting of the function. With just one function you can get the output from multiple rows and columns 😎
What I missing is recommendation or examples of what other options there are in Excel, and opinions which is the most practical. But as some one comment - you always up -to-date and professional!
Dear Leila, I watch all your videos and have learnt a lot of tricks. I doubt I would use these. For example, to get the sum of each column, I would sum the first and drag the result across
Thank u for your informative step by step tips & tricks - (1) how to share screen to do presentation over MS TEAMS and other tips How to print presentations slides within A4 size Look forward to it
My respect for you has always been very deep and I have benefited immensely from your videos for many years now. Thank you Leila for doing what you do. God bless you.
This is awesome… the thing about excel is it has so much functionality that often times you don’t need it, don’t use and forget it even exists! However, no doubt, is super awesome!
I am watching your channel from Cebu City, Philippines. You are a wonderful teacher. Your lessons are precise, concise, and easily understood. I hope you continue as long as you can. Thank you very much!
Omg.Your presentation makes easy to learn the complex functions.The thirst to learn Office skills from you keeps bringing me here anytime.Thank you so much.
Another commenter spoke of using these functions along the many other new Excel features to rethink how sheets are designed. I agree and will slowly add these functions to my repertory.
I've only started watching your videos a few months ago and already you're one of my favorite youtubers who's taught me so much. This video in particular is exactly what I needed today to solve a complicated problem. Keep it up :)
Hi, this was very intuitive and am able to process a huge amount data comprising 12,583 rows and 144 columns just in seconds. Great job and thank you so much. My MS Access application is also confirming the same results output.
You are really an inspiration ... I have learnt alot new from your video... Thank you for sharing these kind of videos on regular basis... Great work 👍
Hi Leila. Interesting functions. Like so many things in EXCEL, the first impression might be "what?", but then you begin to think of ways to use them that simplify what is required without them. Thanks for sharing these examples :)) Thumbs up!!
Wish my version of Excel would keep up with all the new stuff but alas I sit and wait like a kid waiting for the candy shop to open. As I watched this video I am thinking to myself how would I approach what you did otherwise and I think I've seen you show multiple ways of getting the same result in other videos so it would have been nice to see that in this video. Just food for thought as I think often times some of us need to be able to translate or identify best approach practices. As always, awesome videos. Thanks again!
4:13, "just keep it simple", you are great:D thank you for all the updates you made for all of us. All we knew about Excel in the past period needs to be revised.
I can see myself using these to create filterable ranges for graphs. I know there are other ways to make this but this seems easy to make (and more importantly) to maintain.
I can't wait to try and promote the lambda functions in my company. Been watching these in your videos for quite a long time, but the lambda functions are still missing in my Excel (currently on 2021-08). Is that normal? Any idea when they will "show up"? Thanks!
Thank you Leila - well explained with a good example. I had seen byRow and ByCol and suspected they would answer several of my calculation problems in older sheets, and this confirms it.
I can definitely see some use for them, I don't think I can see them allowing us to do anything we couldn't before but it definitely makes formulas more concise and it's often useful to get an array output rather than how I'd usually do this which is autofill the formula across then filter the information from it separately.
Leila you are just amazing with your tricks on Excel. This function is surely going to be the game changer for many of the data analysts who struggle so much with different questions and aspects of their data.
Thanks Leila, i was not much present for excel things last months (since lambda function release, i guess) and I see lot of new things well explained and very interesting to me in practice. Thank you for that.
Something that I found interesting was that BYCOL returns a range reference and not just an array as I originally expected (which means that COUNTIFS works). Nice presentation.
You can use index function if you use byrow, or just use map or reduce and put in each column as a single parameter. The new tools brought a lot of possibilities and it might take some getting used to because the whole concept of lambda is huge.
Hi Leila, thanks for the video. I love this new Excel formulas, and having Lambda is very appreciated, considering we have it in Python too. Excel is becoming less of a spreadsheet and more of a visual programming language. Unfortunately this also means that the way we input the formulas is becoming problematic to manage this new coding-esque way of programming the sheet. We would need a real editor with beautify functions to split these long formulas in different lines and indent the inner parts for better readability. And also many of the new formulas in my opinion should mimic more the counterpart of the real programming language that inspired them. For example, trying to make an SQL SELECT choosing the desired columns using the FILTER function is a pain... and I can't understand why, as it is the way it should work normally. The VBE also needs a total refresh...
Love your videos. I'm having trouble using data from one tab and pulling it into another tab in tab drop down menus that populate an area when selected.
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
Looks interesting, I think I may use it in the future. Could you add next to the persons name the number of times they exceeded the target, so, for example, it would return "Doug (2)"?
Thank you. Your videos are brief and to the point hence I find them very useful. I got a question on a different topic. How can I plot more than 255 lines on same chart? Grateful if you could help here please. Thank you.
Nice video as usual, but I am sceptical about the !spill, the λ and the 365 only formulas. I am an advanced Excel user (not compared to you but for my job) and the !spill feature is rather confusing as excel was build as a '1 to 1" relationship between cell and formulas and now not All these "new formulas" especially the 365 try to help those that don't know/understand formula arrays (sumproduct -->CSE) but if someone is not a 365 user this is a big problem. Now the "λ function" is even more difficult to use as it is "your own function" and not a "universal function" with proper help, syntax etc (make work sharing difficult even within a company) So for me λ is a NO NO, also the 365 functions (xlookup, xmatch etc) are a NO NO as I don't know what my audience is using outside of my company. Thanks for the video.
Thank you for great explanation. Let’s array has positive and negative numbers. What combination of functions shall I use to know max no. of consecutive positive months ?
Great video!! Great Functions!!✌ Also these : =TRANSPOSE(FILTER(B4:M4,BYCOL(B5:M11,LAMBDA(a,OR(a>1500))))) =FILTER(A5:A11,BYROW(B5:M11,LAMBDA(a,OR(a>1500))))
I love your video, they are very well explained. I'm wondering though, in the sum example, why wouldn't I just have the 'sum' in each cell and drag it along the table? Is it faster or uses less memories?
Most wonderful explanation. Lelia is back in her original, exceptional and excellent containt (after long time ⌛) Big thank for enhancing the knowledge. Keep it up
My head was spinning like this 🙄 haha. I get the idea behind this but the technical stuff went over my head as you can imagine. I wish you had more courses on LinkedIn learning.
This could be quite useful. I work in a manufacturing facility where we have instruments that return large amounts of instrument-generated data in either CSV or XLS format. One of my jobs is to take the data and extract useful performance and/or analytical information from it. I could see using these functions in some of my reports.
Thank you, Leila! I love your content. Also a Udemy student. This is brand new info for me! I can definitely see using this with my dataset of what employees used my alert system, what students received a certain number of alerts, etc. Question: What's the benefit of using bycol/brow/lambda functions over Power Query to return tables based on the same criteria?
Grab the file I used in the video from here 👉 pages.xelplus.com/byrow-bycol-file
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
Yes I will be using this I just opened up a kitchen and have many many different types of of spreadsheets keeping track of different types of data thank you very much
I have used lambda in a spreadsheet with 16 sheets for different states and product lists. The key formulae were identical except for a couple of parameters. Putting those formulae into lambdas with parameters, removed all the complexity from the worksheet itself. To me, a lambda is the equivalent of what we do when programming. If we find ourself repeating code, we write a separate function for that code and call it when we need it, which is both simpler and safer.
Which version of MS Office are you using?
You are gifted in the way you explain subjects. Keep soaring...
Your content is brilliant Leila, I hope to share my work with you, obviously and it is very clear that I would learn much more from you, but my experience is that you always learn when you share knowledge. Thank you for such quality!!!!!
You are a magnificent teacher, who believes in updated technology and shares selflessly.....great stuff
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
Thanks Leila. With all of the "newer" dynamic array functions as well as Lambda functions, it really triggers me to evaluate my work flow and spreadsheet design. The largest obstacle preventing a shift is the inability to share files with non MS365 users. Thanks for sharing...
True, that will remain an issue.
@@LeilaGharani … not forgetting just ‘plain availability’ of those. So far even lambda isn’t generally available, even if you are in a ‘office insider’ channel.
Learn SQL, stop using Excel as your data manipulator....
@@stuckcamping I want to, but the biggest stumbling block is configuring SQL and permissions to db, cant over come this
@@stuckcamping Or learn both. Each has pretty distinct use cases. The dynamic UI of Excel is hard work to reproduce in other environments, while the maintainability and portability of SQL are often the most important factor. Use each where they are the natural choice, and even use both together sometimes. And add some Python too for a very solid analytics tripod.
Thank you so much. It solved a very tough task for me🎉🎉
Is this wonderful or what😱? I never knew what Lambda was until seeing this video! And these new functions really do simplify the process by preventing copy and pasting of the function. With just one function you can get the output from multiple rows and columns 😎
Leila, very clear explanation of the function,but most importantly a practical application.
Well presented Maam.. My Fundas in these two functions abs clear now
Great 👍
What I missing is recommendation or examples of what other options there are in Excel, and opinions which is the most practical. But as some one comment - you always up -to-date and professional!
Dear Leila, I watch all your videos and have learnt a lot of tricks. I doubt I would use these. For example, to get the sum of each column, I would sum the first and drag the result across
Thank u for your informative step by step tips & tricks -
(1) how to share screen to do presentation over MS TEAMS and other tips
How to print presentations slides within A4 size
Look forward to it
I don't have this in my Excel yet. Looks promising. Will definitely use it when it becomes widely available. Thanks for sharing.
My respect for you has always been very deep and I have benefited immensely from your videos for many years now.
Thank you Leila for doing what you do.
God bless you.
This is awesome… the thing about excel is it has so much functionality that often times you don’t need it, don’t use and forget it even exists! However, no doubt, is super awesome!
👍
I am watching your channel from Cebu City, Philippines. You are a wonderful teacher. Your lessons are precise, concise, and easily understood. I hope you continue as long as you can. Thank you very much!
Thank you! 😃
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
I don't work with excel that much, but every time I see a new video I get so excited.
Thanks Leila, for your all effort to share knowledge. 👍👍👍
I got inspired from you and started to take excel classes for school students in my small village. Thank you so much 🥰🥰🥰
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
Omg.Your presentation makes easy to learn the complex functions.The thirst to learn Office skills from you keeps bringing me here anytime.Thank you so much.
Another commenter spoke of using these functions along the many other new Excel features to rethink how sheets are designed. I agree and will slowly add these functions to my repertory.
👍
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
I've only started watching your videos a few months ago and already you're one of my favorite youtubers who's taught me so much. This video in particular is exactly what I needed today to solve a complicated problem. Keep it up :)
Wow, thank you Tomislav!
Hi, this was very intuitive and am able to process a huge amount data comprising 12,583 rows and 144 columns just in seconds. Great job and thank you so much. My MS Access application is also confirming the same results output.
Thanks. Just when I think I know everything about Excel, I can learn 10 more things by watching one of your videos.
Thanks a lot, always your videos are useful to my work. greetings from Chile.
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
You are really an inspiration ... I have learnt alot new from your video... Thank you for sharing these kind of videos on regular basis... Great work 👍
Hi Leila. Interesting functions. Like so many things in EXCEL, the first impression might be "what?", but then you begin to think of ways to use them that simplify what is required without them. Thanks for sharing these examples :)) Thumbs up!!
My pleasure, Wayne!
Wish my version of Excel would keep up with all the new stuff but alas I sit and wait like a kid waiting for the candy shop to open. As I watched this video I am thinking to myself how would I approach what you did otherwise and I think I've seen you show multiple ways of getting the same result in other videos so it would have been nice to see that in this video. Just food for thought as I think often times some of us need to be able to translate or identify best approach practices.
As always, awesome videos. Thanks again!
I have 365 apps for business. I hope this version will get the lambda function.
Great video.
Wow! Something I'll use regularly. I've shared this video with my team. This will save us a ton of time. Thank you SO much.
4:13, "just keep it simple", you are great:D
thank you for all the updates you made for all of us. All we knew about Excel in the past period needs to be revised.
😁
LET is more. 👌
Thanks leila, Lambda is a great revolutionary Function and it saves a lot of Time to me
Interesting!! I don't have a need for this now but I will keep it in the back of my mind as you never know....
Hope all well at your end. Thanks for the video. Request you keep creating such informative video for our knowledge 🙏
I will try my best
@@LeilaGharani Thank you Ma'am
I can see myself using these to create filterable ranges for graphs. I know there are other ways to make this but this seems easy to make (and more importantly) to maintain.
I can't wait to try and promote the lambda functions in my company. Been watching these in your videos for quite a long time, but the lambda functions are still missing in my Excel (currently on 2021-08). Is that normal? Any idea when they will "show up"? Thanks!
Unfortunately yes. They’re still not generally available. Hopefully soon though.
Thank you Leila - well explained with a good example. I had seen byRow and ByCol and suspected they would answer several of my calculation problems in older sheets, and this confirms it.
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
Very good explanation leila. Thank you for sharing this with us
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
I can definitely see some use for them, I don't think I can see them allowing us to do anything we couldn't before but it definitely makes formulas more concise and it's often useful to get an array output rather than how I'd usually do this which is autofill the formula across then filter the information from it separately.
We always learn new things with you... Thank you
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
U R teaching new lesson to me always Thank you very much & god bless you ma.
It's my pleasure.
Leila you are just amazing with your tricks on Excel. This function is surely going to be the game changer for many of the data analysts who struggle so much with different questions and aspects of their data.
Thanks Leila, i was not much present for excel things last months (since lambda function release, i guess) and I see lot of new things well explained and very interesting to me in practice. Thank you for that.
Excellent Leila. Thanks for these tips and practical explanation,, Regards !! You are a 'superb' teacher !!
Thanks Leila... Good to know info as always.... But ur voice is too sweet and the best
Nice to have you your highness
Sorry for being away that long
You're back! Happy to see your comment again!
That's Amazing Liela... it's a great solution we were waiting for to sum columns and rows for any array... it's absolutely usefull.
pplpppp
lpp
ppppp
pppp
ppppppppppppppppppppppppp
ppppp
lp
plpppp
ppl
l
ppl
pppppppppppp
plpp
It's great learning for me, thank you so much
Something that I found interesting was that BYCOL returns a range reference and not just an array as I originally expected (which means that COUNTIFS works). Nice presentation.
You can use index function if you use byrow, or just use map or reduce and put in each column as a single parameter.
The new tools brought a lot of possibilities and it might take some getting used to because the whole concept of lambda is huge.
I really liked the explanation of the aggregation, transpose however has a different meaning for sound engineering. super vid
Excellent, clear and looking forward to using.
Really useful. Will apply wherever necessary
Really clever useful technique, thanks for sharing the new functionality 👍
Super interesting, need to go over this a few more times to fully understand. Think this will be very useful for me. Thank you for sharing.
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
Great videos, you should do a short on copying totals of the range selected by using on the bottom bar. Not well known.
I can totally see myself using these functions. Thanks for making this video about them!
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
The formula bar definitely needs an upgrade now we're writing long complex programming code!
You can manually add lines and indentation, but you're right. I can see it moving to the side soon.
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
Excel is evolving at lightning pace ❤
Hi Leila, thanks for the video. I love this new Excel formulas, and having Lambda is very appreciated, considering we have it in Python too. Excel is becoming less of a spreadsheet and more of a visual programming language. Unfortunately this also means that the way we input the formulas is becoming problematic to manage this new coding-esque way of programming the sheet. We would need a real editor with beautify functions to split these long formulas in different lines and indent the inner parts for better readability. And also many of the new formulas in my opinion should mimic more the counterpart of the real programming language that inspired them. For example, trying to make an SQL SELECT choosing the desired columns using the FILTER function is a pain... and I can't understand why, as it is the way it should work normally. The VBE also needs a total refresh...
Many thanks for your feedback!
Thanks Leila. It is amazing. I don’t find Bycol in excel. Is this normal ? What should I do ? Can you advise please ?
Thx Leila ! I improve my english and my excel so that perfect for me !!!!
Wonderful, thanks for sharing your knowledge...
Love your videos. I'm having trouble using data from one tab and pulling it into another tab in tab drop down menus that populate an area when selected.
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
Thanks, Great tutorial. I have many applications for these functions
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
This is tremendously helpful!! I was literally just trying to figure out a way to create a spreadsheet just like this!
Wonderful. Seems very useful to my jobs. You are the best!
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
Looks interesting, I think I may use it in the future.
Could you add next to the persons name the number of times they exceeded the target, so, for example, it would return "Doug (2)"?
Thank you. Your videos are brief and to the point hence I find them very useful.
I got a question on a different topic. How can I plot more than 255 lines on same chart? Grateful if you could help here please. Thank you.
Thank you for your great explanation
❤❤🙏🙏
very good tip LEYLA thanks, greetings from Mexico
I use tables in excel extensively. (Thanks to you 😀) so when you discuss ranges it would help to describe how to reference the range from a table.
Great! Thank you Leila for yet another helpful video! Lucky to have you as a teacher, stay blessed! 😊👍
Thank you! You too!
Is that a Grundig radio behind you? My grandfather had one of those…. Awesome!
Nice video as usual, but I am sceptical about the !spill, the λ and the 365 only formulas. I am an advanced Excel user (not compared to you but for my job) and the !spill feature is rather confusing as excel was build as a '1 to 1" relationship between cell and formulas and now not All these "new formulas" especially the 365 try to help those that don't know/understand formula arrays (sumproduct -->CSE) but if someone is not a 365 user this is a big problem. Now the "λ function" is even more difficult to use as it is "your own function" and not a "universal function" with proper help, syntax etc (make work sharing difficult even within a company) So for me λ is a NO NO, also the 365 functions (xlookup, xmatch etc) are a NO NO as I don't know what my audience is using outside of my company. Thanks for the video.
Thank you for great explanation. Let’s array has positive and negative numbers. What combination of functions shall I use to know max no. of consecutive positive months ?
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
Great video!! Great Functions!!✌ Also these :
=TRANSPOSE(FILTER(B4:M4,BYCOL(B5:M11,LAMBDA(a,OR(a>1500)))))
=FILTER(A5:A11,BYROW(B5:M11,LAMBDA(a,OR(a>1500))))
LOVE IT! Thanks 👍
Amazing presentation as always!
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI
nice!
excellent, clear demonstration.
This is great....I see it changing the way i work
Definitely can see myself using these in the future.
I love your video, they are very well explained. I'm wondering though, in the sum example, why wouldn't I just have the 'sum' in each cell and drag it along the table? Is it faster or uses less memories?
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=QmCZIYf9APabszKC
New content always.
Thank you for the upload.
We are watching keep up more...
Most wonderful explanation.
Lelia is back in her original, exceptional and excellent containt (after long time ⌛)
Big thank for enhancing the knowledge.
Keep it up
Surely using it
My head was spinning like this 🙄 haha. I get the idea behind this but the technical stuff went over my head as you can imagine. I wish you had more courses on LinkedIn learning.
Thanks Leila. It's really helpful function
Great video. Very helpful. Will definitely use it
This could be quite useful. I work in a manufacturing facility where we have instruments that return large amounts of instrument-generated data in either CSV or XLS format. One of my jobs is to take the data and extract useful performance and/or analytical information from it. I could see using these functions in some of my reports.
Nice, You are my teacher ❤
Thank you! 😃
15 M wow congratulations
I am following you when 2k followers you have
I am a big fan
And you are our Superwoman
Great...thank you as always. Function is surely going to be useful.
You are madly genius. Than you so much. Quick doubt are these lambda functions available in excel 2016?
No, only in Excel 365.
I am going to update various spreadsheets thanks to you!
Thanks Leila your videos are great; but you know how can I paste formula only in visible cells?? A lot of thanks for your comment!!
Thank you, Leila! I love your content. Also a Udemy student. This is brand new info for me! I can definitely see using this with my dataset of what employees used my alert system, what students received a certain number of alerts, etc.
Question: What's the benefit of using bycol/brow/lambda functions over Power Query to return tables based on the same criteria?
I can certainly see a variety of use cases to support me in my role.
Thank you very much for the video tutorials.
Wow... Nice to know this Information on first hand... 👌
th-cam.com/video/Rp1BkCIb3o0/w-d-xo.htmlsi=vArhiR0iShJK6oxI