When You Should Use the New Excel LET Function
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 2024
- The first 1000 people who click the link will get 2 free months of Skillshare Premium: skl.sh/leilagharani0820
Whether you're dealing with long formulas or seeking to enhance formula performance, this video is a game-changer for Excel users. Excel's new LET function can make your formulas easier to read and it can improve the performance of your formulas so they run faster. This applies to formulas that use the same expression (e.g. formulas, values) multiple times.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/let-functio...
✨ Key Highlights:
▪️ Introduction to LET Function: Discover the basics and the availability of the LET function in Excel.
▪️ Syntax and Examples: Learn the syntax and see practical examples, including IF conditions and dynamic arrays.
▪️ Performance Improvement: Explore how the LET function can speed up your Excel formulas, especially in large datasets.
▪️ Real-Life Application: Detailed walkthrough of using LET in different scenarios, enhancing readability and efficiency.
In this video you'll learn the basics of the LET function and also find out in which situation the LET function can REALLY make a difference for you. I measured the time for a full calculation on a large data set for different scenarios. You'll be surprised when you see the results!
Note: LET function is not available in Excel 2019 or Excel 2016. Only in Excel for Microsoft 365.
Link to Fast Excel from Charles Williams: www.decisionmodels.com/FastExc...
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
LINK to Excel New Features & Dynamic Arrays Playlist: • Excel for Office 365 &...
Advanced Excel Tips & Formulas: • Excel Advanced Formula...
00:00 Why use Excel's LET function
01:07 Availability and Syntax of Excel LET Function
03:12 Example of LET Function with IF Conditions
06:53 Example of LET Function with Multiple IF Conditions and Symbols
08:55 Does Excel LET Function REALLY Improve Performance?
11:03 LET Function with Excel Dynamic Arrays
14:46 Performance of Excel LET Function with Dynamic Arrays
➡️ 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
Many thanks to Skillshare for sponsoring this video!
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
Grab the file I used in the video from here 👉 pages.xelplus.com/let-function-file
Thanks Leila for this tutorial of LET function ! You are simply ahead of everyone! 💛💛💛🧡🧡🧡
Thank you so much for pointing out the LET (and IFS) functions. This has drastically simplified a very complex formula we have been using. Our existing formula was 1615 characters in length, and with just the LET function it reduced the formula length to 487 characters. With a combination of the LET and IFS functions, it reduced the length of the formula to 469 characters.
Here is the end result using LET & IFS:
=LET(x,INDEX(TableName,MATCH([@sale],[sale],0),MATCH(Adj1_Element,TableName[#Headers],0)),a,Adj1_a,b,Adj1_b,c,Adj1_c,d,Adj1_d,e,Adj1_e,
IFS(
Adj1_Graph_Type="Exponential",a*EXP(b*x),
Adj1_Graph_Type="Linear",a*x+b,
Adj1_Graph_Type="Logarithmic",a*LN(x)+b,
Adj1_Graph_Type="Polynomial (2 order)",a*x^2+b*x+c,
Adj1_Graph_Type="Polynomial (3 order)",a*x^3+b*x^2+c*x+d,
Adj1_Graph_Type="Polynomial (4 order)",a*x^4+b*x^3+c*x^2+d*x+e,
Adj1_Graph_Type="Power",a*x^b,
TRUE,""))
While I have not measured any performance improvement, since I have only run a limited test, I am sure it is there.
Leila, I'm a heavy excel user and I love your videos ! You are always clear and to the point. Greetings from Brazil !
I've been grumbling that Excel should have this feature for years. Now i'm super excited!
Great video... as always :) When you have tables with long column names and these get repeated in formulas it can make them really hard to read, so using LET will make these much easier to read and maintain - all we have to do is look at the variable at the beginning and we'll see which columns this refers to throughout.
Ma’am you are a great teacher... i found you just 2 days back and i have become crazy about your videos. Thank you so much🙏 from India
Started watching your videos for the view and then realized that I learned lot of short cuts in excel. Thanks great job
Thanks Leila! Your explanations and use cases are awesome, but by taking the time to measure it against larger datasets, you have encouraged me to use this function without fear of it slowing down my workbooks. These sorts of above-and-beyond insights are what make your channel so useful to me. Thanks again!
Glad it's appreciated, Walter!
That second example is crazy, I'm guessing because it can grab the complex function from the variable established, instead of reading it each time it is faster; however, when doing small calculations the time to set the variable initially ever so slightly outways the benefit in cost of time.
REALLY awesome stuff, can't wait for that official launch.
Indeed! LET function would be a one of the most with FILTER function to make the excel more powerful. Thank you for sharing the great knowledge
You're very welcome!
A very thorough treatment of this little useful function.
Speaking of performance, I remember how, about the time you were born, a 100 x 20 sheet was recalculated for several seconds (VisiCalc on Apple ][), and auto-recalc was usually turned off.
First video of yours I've watched and I hit that bell at the 5:00 mark. Your depth of knowledge, ease and speed of use of Excel, and clarity of communication combines into an informative video that surpasses all other Excel videos I've ever watched by everyone, ever. Your quality and professionalism is the gartner quadrant that has been missing in the excel video community, until now.
Wow, thank you Morgan! Glad to have you onboard :)
This is great.Many thanks for this tutorial quiet straightforward and impactful.
I see let function working more like the variable function in powerbi.
So brilliant. The LET function is so great. You're a an amazing teacher. Thanks for sharing
Thanks so much!
I appreciate living in the age free education and great teachers. Thanks Leila.
Let us all have more fun efficiency now that LET has arrived! Thanks, Teammate!
You bet Mike ;)
Would love to see a collaboration between you two. That’ll be like the Avengers version of excel.
Yes please. Would be like 2 DJs mixing side by side.
A function LET in programming mode, declaring variables. Thank you for this fantastic tutorial Leila!
Glad you enjoyed it, Iván!
Hey Leila, Thanks for this tutorial of new LET function. This is surely going to improve file performance and make long formulas easier to read.
You are making my job so easy..... Thank you Leila... You are a savior
Thanks for increasing my interest immensely in learning more
The addition of the LET function means that Excel formulas are basically becoming like a programming language where declaring variables is the norm.
EXACTLY
Still no loops tho
Python is making Excel increasingly archaic and I love it
@@ivanxdxd because it is functional programming where there is no loops. Instead, you use an call to the previous value. And repeat It through the desirable range. There is your loop.
you could use VB to code them pretty much the same. it's just inline instead of function now.
Understanding from one time from Mrs. Leila GHarani Videos
Thanks
Thank you for your vids! I apply some of your tutorials to the work I do. It’s been a major help!
[6:17 thaattttt (breaking up using alt + entere) is new to me and what i needed for so long. thanks for including that. and glad i clicked this vid.
7:53 conditional formatting
Hear hear!
Never mind all the new functions Leila is showing us, even experienced Excel users; it’s these usability tips that are of equal if not more importance.
ps, I only found Win+. for emojis this afternoon by accident, then an hour later Leila shows us!
I think through some of the crazy 10-line formulas I've written over the years and my mind is blown at how much easier this would/will be!! Thanks for the great work and video Leila!
It really can make a difference for these complex formulas.
@@LeilaGharani How to import live data excel to spreadsheet? Please help me. My e-mail adress is melikov_xalid@yahoo.com
Coming from a programming background and only now learning Excel, this is a huge deal.
Love your tutorials and the clear way you present them. I keep watching them over and over till it sinks in. Also love the Excel downloads so I can play with them..
Glad you like them, Mireille!
Finally got to use LET at work. Thanks for helping me understand it!
Thanks Leila! a great example of productivity.
Amazing and most important is your way of explanation is so simple and easy to understand any complicated formulas... Thanks
Thanks for giving such a great opportunity to learn on skillshare..I have no words except you are great and nice as always ...Thanks
Hi Leila.. great in-depth summary of the advantages of the LET function. The timing comparisons were particularly interesting. Thanks for sharing your insights. Thumbs up!!
I have found that most speed improvements are negligible unless dealing with large datasets. The kind of datasets not typically seen in small businesses. BUT, I have always been a fan of making formulas readable understandable at a glance by someone that did not wite the code. I am frustrated sometimes when analyzing someone else's code when they do not use common conventions or make use of named ranges. Most people can understand Assets=Capital - Liabilities easier to read than some combination of summed cells - other summed cells.
Great finding dear Gharani, Appreciate your dedication to us
Great to see speed tests on LET function!!! Think it's the first one out here. Many thanks for that!!. A great advantage of LET is it's kind of portability. We can write a complex formula in one workbook, copying it as a string, pasting it in other workbook , changing only the variables reference addresses , and good to go. No VBA😉
Thank you! That's right. It'll make managing workbooks easier.
A lot of the formulas i write for excel repeat sections of the formulas over and over so this will make them a lot shorter. thanks for the vid
definitely need this LET in my big data files and long repeated formula elements
Great video tutorial as always! I have been learning a lot from you, Leila! Keep up the great work.
Thank you! Will do!
Finally! Filemaker has has this for years and it’s extremely useful.
Thanks for this Leila! Please advise if this can be used to improve performance of volatile formulas such as Indirect or Offset.
It's really helpful and interesting, Leila. But, I am unused to Name Manager usages. So, felt little difficult on this.
Thanks for share.
Leila, congrats on excellent videos. I like your pace, and your ability to explain this to us. All the best.
Thank you very much, Alain!
I've been so frustrated that Excel didn't have such functionality before, principally for readability and maintenance. Wasn't aware of LET it until I saw this. (Interesting point about performance on the simpler functions too.) Thanks you
As always clear and direct. Thanks Leila
I did not see the point at the begining but it's really useful! thanks Leila!
I am regular watching TH-cam channel, big thanks for you make lot of videos especially on excel, I think I am becoming expert on excel because of you, thanks 🙏 please continue making videos on excel and also on MS Word
Thanks for sharing Leila! Awesome tuto!! 👍🏼
Thanks a lot for this helpful vdo Leila.... You are the best🥰
What an excellent teacher - clear explanation and meaningful yet still clear examples.
Glad you think so, Gary!
What I great way to show how we can use Let! Thanks!
Thank you for this. This is such a game-changer for me.
I didn't know about the WIN+. key! thanks for that tip :)
Hi, I have applied this to my report for payroll and the outcome is really very effective in terms of times and efficiently.. Really love the part whn you analysed and compared the time reduction between w/wo LET... Thank you again Leila... :)
Great to hear!
Thank you for sharing!! This is really useful!!
Thanks Leila for the knowledge.
I have learnt so much from this channel ❤❤❤ it has really helped me in my work.
That's awesome! Thank you.
Love that function I have created many stacked if statements with similar formulas....I will try this function out 😎
you are awesome, your explanation is out of this world, so easy to understand, nothing like this i have ever seen before. thank you Leila
Wow, thanks!
Since I watched this video a few months ago and learned about the "LET" function, I'm using it every day since. Thank you. 🙂
Excellent! Glad the video was helpful.
Hi@@LeilaGharani , I forgot to mention that using the LET function allowed me to add comments easier in the formula by adding a comment line at the start and then change line for the rest of the formula: = LET( COMMENT; N("......"); .....)
Thank you Leila. As always, great video! I would be interested in understanding the performance difference between defining variables in the LET() function versus defining them in named ranges. I typically refer to named ranges in complex formulas. Would LET still be faster?
Yooo this could be a game changer. I tend to use IF as a decision tree abs this would make it clearer
Very helpful (as usual; no surprise there!) Thanks a ton, LG!
Very good one. Thanks. This can help on mobile, as apps doesn't give access to Name manager menu.
That # for value lists is good to know.
...and then God said, "LET" there be "Leila" and our Excel problems are solved! :-) Thank you for another great video!
That is true !!!😊
😀
YESSSSS!!! Finally. I have benn LONGING for this feature! Awesome showcase/vid as ever!
Thanks Jan! 😁
Thank you for another great video! You inspire me
I can use this A LOT. Thanks!
That's one of the most useful functions I have learned! Your videos are just terrific.
Glad you like them!
Learned a lot here. Not just the LET function. Awesome video! Subbed!
Thanks for the sub, Mark!
Thanks! You have great tutorials. I'm fairly advanced, but I always find your videos helpful!
Great to hear, David!
Leila, can you do a similar performance comparison between the new FILTER function vs the classic way on creating a filtered table? My guess is that since dynamic array formulas reside only in one cell rather than every cell to create an array table, a spreadsheet is more responsive and smaller file size.
I have to admit i like the way you teach, very easy to follow. I am now going to start using LET!
Great to hear!
Thank you i leaned so much from you thanks isn't enough.
I wonder if defining names in a different cell once without repeating let function every cell will work ?!!?
Great topic, awesome video and beyond perfect tutor. Thank you.
Wow, this should simplify a lot of my formulas, particularly where I chain multiple IF and IFERROR, IFNA, etc. Thanks. Look like you have you have a good programming skills. Have you done any videos on Excel data analysis with Python Pandas? If not, it's another area you could focus on.
I used to create hidden columns with a formula that would provide the repeated calculation. This is so much more concise, and easier to maintain.
You are a true professional... You deserve all the best in life
Thank you!
Very helpful! Thank you so much!
Thanks Leila for your suggestions, it worked, I just added more columns to the formula
Glad it helped, Jean!
👏🏻👏🏻👏🏻👏🏻 this is going to be very useful for complex financial models. Thanks Leila
You're so welcome!
💖 ... just playing with the Win-dot shortcut 😊
I don't have time to watch all your vids but whenever I did watch, I am glad I did.
Great to hear, Amy.
Thank you a lot for share! the background of Leila's video is getting more gorgeous than before :D
I'm glad you like it
Mam you're fabulous Excel tutor I had ever seen.👌👌
Thanks a lot 😊
I think this will make typing formulas being less error prone.
Nice background setup.
It's very wonderful to watch your video
Glad you liked it :)
You should VLOOKUP(K4:L6)" for the range, out of range IF statement instead of typing IF B4
OMG, LET. Flashing back to my BASIC boyhood
quality of your videos are getting better and better. Congrats
I appreciate that!
LET function is very useful . The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula
Fantastic function I learned from you today! Thank you!
You're very welcome!
Excellent video; it had me using LET, with enthusiasm, in no time. I have one issue though. Not to do with LET but you have the VAT rate embedded in a formula; I consider this to be bad practice. VAT can, in theory, change at very short notice. Ideally it would be stored as a global somewhere but more realistically it should be in a cell where it's visible.
The same applies to values like income tax rates and thresholds: any constant that isn't constant.
Congratulations from Rio. Your videos help a lot and you explain the concepts very well.
Awesome, thank you!
Hi Leila!The New LET Function Looks Really Interesting..Thank You For The Great Tutorial :)
It sure does have its advantages 😊
Why you make my live easier
Ms Leila you rock😊
Me finally getting around to learning Lambda last week thinking it was more like this. I knew there was a way I could define my own variables without all the extra rigmarole of naming lambda functions.
thank you leila like let function its simple,
This is very useful! I'm wishing for something like this in Google sheets for s long time...
Great comparison of performance