❗Update: Microsoft changed the add-in. It's now called "Excel Labs". Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Leila, you seem to have influence at Microsoft. Can you tell the software engineers to have Lambdas able to be defined with other created Lambdas? If this were possible, I, and I am sure others could develop functions much more easily and elegantly. Right now you can not do this.
Quite useful, but needs to be updated. The Addin has different name - "Excel Labs" and "Advanced formula environment" is one of the features. Also the function editing interface is different. You have to input only your business logic functions, without using LAMBDA directly.
Considering LAMBDA was finally released on February 8th, this comes right on time. Thank you. I always chide you for showing things so early when business users can't use office insider features until they are released; however, this is great.
OH man, importing sounds very promising. I can now keep my repository of commonly used items out of the spreadsheet and import as needed. This is a game changer.
This, I think, is now produced as a gateway to Excel's DAX language. At a later date, I think they presented it to users as a test example of making Excel more useful in DAX language. While I was writing the formula, I saw the DAX formulas :)💥
Yes, I definitely enjoyed it as usual 🙂 I'm waiting a day in which I watch a video of yours that do not add anything to me!! My colleagues think that I'm an Excel expert, but whenever I watch your videos I feel like I'm at the beginner level 😔
This is amazing. And it beats having a "conventions" sheet for functions (which was my workaround for the horrible Name Editor). Thank you so much for showing and explaining this add-in.
I enjoy watching your videos and learning a lot about Excel from them. This, however, is one of the few times that I've been genuinely surprised by your content! I would never have learned about it had I not been subscribed to your channel. Thank You! I remember what a pain it was using VBA to create custom worksheet functions. Now, combined with LAMDA and the Advanced Formula Environment, my work is going to be so much easier. This is an awesome change. Now I just wish it were a standard part of Excel that doesn't require the use of an Add-in! 😷
Having taken over the running of our foodbank I have to make a weekly list of food parcels distributed and then from that data complie a monthly report of Meals Provided, Finances Used, Clients numbers(Adults and Children) etc. Already at the end of Feb and need to compile this months report and I am now looking forward to using Lamda functions to assist with this task ... I am sooo thrilled as always when you are bearing news that helps me -- I assume you are talking directly to me only ;)
Thanks for the great video. I have had the AFE for a few weeks now and have been playing with it. It is absolutely amazing and a long overdue addition to the Excel addins. Just as easy to use as the VBA editor if you only need to build a simple function. I also like that you can seperate out your lambdas into various namespaces. I really don't find the fact that you cannot direct reference cells to be a particular problem and would try at all costs to avoid hard-coding ranges into the function except where absolutely necessary (for instance a lookup range that you don't want other users to find and change lol).
Thanks Roy for the feedback. The namespaces are great! I didn't get a chance to cover them since the video was getting too long. I agree the cell referencing is not a problem for Lambdas, it's actually better not to have that option, but if this will be a replacement for name manager, then we need to have it. Many people use dynamic named ranges for charts, or have crazy offset and choose functions in there :)
@@LeilaGharani Yes I totally agree, it seems pretty clear that Excel named ranges are going to be replaced with the AFE and will need to be compatible with the previous version of named ranges; this could be good as they are stored in a hidden sheet, which will, hopefully, avoid other users hitting f3 and deciding to look at the formulas, unintentionally editing them by trying to move around with the arrow keys (This is something that happened on one of my workbooks 😞👿)
Thank you Leila. Hopefully one day I can make this Lambda function useful & integrated to my work. Btw this is a bit out of context, but could you pls make a video giving a function that replaces DATEDIF function in O365? Thanks!!
This looks much simpler than using the name manager to manage those LAMBDA functions. However, I wish there would be better support of keyboard (shortcut) support in this new configuration. Requiring users to move their hands between their keyboard and mouse back and forth is almost always a bad thing for productivity tools like Excel. In addition, if the Excel team really wants LAMBDA to be more user accessible, they should really consider making it possible to manage and edit all these LAMBDA formulae in a separate window, just like how VBA editor is in a separate window currently.
Awesome! Excellent video tutorial. Been waiting for this Lambda. Will definitely use this. As always useful videos and outstanding YT tutor. Thank you for sharing your knowledge. ❤
Could you make a video about making an Excel file digitally signed? For example, if I am going to give an Excel file with VBA to someone, to ensure that they can open it without going through so many steps. With and without active directory login please....Thank you...!
Whew ... 😵😵😵😵 Candidly, I do not fly in the Advanced Formula Environment. Maybe someday, but for now I only walk on Planet Earth. But if that day arrives in my lifetime, I will know where to go to learn more. So once again, the explanation and delivery was exceptional (no surprise) but as soon as I heard the word 'LAMBDA' my palms got sweaty, my brow beaded up in sweat ... and my heart rate shot to 187 ... 😲
Microsoft needs to realize that many enterprises block the use of Add-Ins due to security concerns. That renders solutions like this absolutely useless in the environments where they would otherwise be valued and used most.
WELLLLLL IT heads (managers, etc...) should actually open their eyes to those kind of tools and allow the use of at least some of these add-ins; or have a process where you can actually ask them to enable specific ones. This add-in for example, only import formulas at most... what security threat does that actually pose is something I cannot understand. Same goes for Teams. In many cases, for many of my customers, EVERYTHING is blocked; including add-ins OF TOOLS THEY ACTUALLY USE on a daily basis. When you talk to them about it they mostly DID NOT KNOW what an add-in and did not bother to look into it and blocked everything from the start. SMH
Thanks for the great video! I get workbooks from other people and then need to manually import my Lambdas and I want to automate how to bring them in. I created a gist but have to manually import that to every new workbook.
I have started using this feature since last week or two. I see two points for improvisation. First, It would have been great if exporting of lambda in some file format so that it can be shared across files to different people and second, sync names to name manager for selected lambdas only because I many not require some of them at the point of importing.
Hola Leila, I love your channel and how you Easily you explain everything in plain English! Thanks for your help. I would like to know what kind of computer you recommend (surface or iMac), I know I asked before what type of computer you use for work jejeje, but I'm buying a computer and is a very important investment for me. Can you do a video regarding this (what type of computer you recommend)? I appreciate your help and answer. Wish you the best!
I still struggle with recursion. Providing a step-through of some of the imported functions such as text reverse would help greatly with making more use of lambda. Thanks
Hi! Thank you for make this useful video. I learn a lot from you. I wanna ask, is your course have english subtitle? Cause I can't watch video without it
Thank you for all you did for me in Excel during the almost last 3 years MRS Gharani, But the great and trending question that comes to my mind is as an add-in won't cause problems in future to our excel performance??
Hi Leila, Thank you for your consistent delivery of high quality, well packaged and excellently delivered video tutorial. Please, I need a favour from you. My laptop was stolen just over a week ago and I lost a lot of files as a result. One of the files I lost is the excel file that contains all the list and links of the videos you have made. I really can't remember how to get the file again so I would like to ask if you can send the link to me so I can re-download it. Thank you in advance.
Another awesome lesson! Is the ability to store the Lambdas for use with another file coming? It appears that the Lambda created is gone when you open another file.
Thank you Leila for this guide. I wonder if the file will work when I share it with my colleagues. Or do they need to make extra steps before using the file?
Hi, I couldn't find the add-in by Advanced Formula Environment because now the add-in has a different name, "Excel Labs". Is the same add-in as Advanced Formula Environment?
LOL, great stuff but I just learned about the F2 key while using mouse keys in Named Ranges editor. God how annoyed have I been for all these years just to learn about this now. :)
Hi Leila, Thanks a lot for making amazing videos. I have one question, thought u definitely know this, how can one fill the websites of the companies automatically into the google sheets, if one has company names into the google sheets?
As soon as I saw LAMBDA functions for the first time I thought this was going to be its natural evolution. A repository so you can share LAMBDAs across users, a name manager since Excel's existing is terrible, a place to add comments to LAMBDAS as you build them since they can get complicated & long like VBA code, a way to import your favorite functions to other workbooks so you don't recreate them each time, etc. Only improvements to come from here, exciting! I'm sure some people will naturally develop top rated LAMBDAs, Excel might even make the functions added to your 'favorites' and become default added to any new Excel workbook, etc.
Great new feature and excellent explanation. BUT it must be: not an add-in; integrated fully into name manager; be integrated into the formula bar AND accept cell references to be optimally useful.
Hello Leila, first of all, great praise for your channel. I have also booked some courses with you / Xelplus which has helped me a lot. Is there anything to consider when I use a German version of Office / Excel 365? Because it does not work for me. It comes the error message: Sync names with Excel name manager. failed: The argument is invalid, is missing or has a wrong format. Any Idea from your side?
Problem at 2:25… When i click on that sync icon i get the error “Sync names with Excel name manager failed: Das Argument ist ungültig, fehlt oder hat ein falsches Format.”. I never used this advanced formula environment before, i did exactly the same as you in this video. Even if I delete the examples and try it with the Lambda you mentioned here later (noblank) it won’t sync. Do i have to set something special for getting this working?
@ingo: If you are using another delimiter than comma between arguments (in Denmark we're using the semicolon), you'll have to temporarily change it to a comma on your PC (I don’t remember the specific place, but it is somewhere under regional settings). Then you can sync it to the name manager and afterwards you can change the delimiter back to the original and use the “formulas” as usual.
I had a similar issue when I first downloaded the AFE, it went away when I re-downloaded the AFE and re-installed it to Excel, maybe you could try that.
@@hcandersen2007 but then it would conflict with the decimal separator, which is also a comma in many countries. You'd have to change all regional settings. This add-in should be locale-aware.
Thx, Leila. Unfortunately the AFE does not work for me. Export to Name Manager shows either analysis error or "OK", but nothing arrived at Name Manager. I'm using: - *German* Microsoft 365 - AFE plugin (working: show, edit) - Chris' github gist source of Lambdas, available in AFE. I guess this: 1. AFE cannot syntax check, if the source is *not English* (translated English source to German; put to AFE: red curled under.) 2. If you enter *German* Lambdas, you still cannot export to Name Manager. How to overcome?
In this first edition it doesn't seem to support settings where ";" is the argument separator (But I'm pretty sure they will add that in). I think you should be able to temporarily change your Excel separator to a comma to use the add-in and then back.
Thanks for great video. Leila need your help if you can help me, i have linked my shapes with other worksheet showing data as % and i want to show negative % in red and positive % in green either by VBA or any other method.
I went interview, got some data. Interviewer asked to give some useful information by analysis data. I did it but not good. So kindly make video about how to analysis data and take out some useful information from it. Thank you 🌹
Hi. I have a question for u plz, i use french version of office 365. I tried to upload the chris gross new lambda as you did in yr tuturial. But i failed and don’t know the reason. Tried many times without success. Can u help plz?
Looking forward to finally using LAMBDA. Should the AFE show items that you originally created in Name Manager? I created a new item in the AFE and clicked on the sync button - this shows in the Name manager but other items in Name Manager don't show up in the AFE? Am I missing something?
Here is an alternative to the LAMBDA function: TEXTREVERSE (suggested by Chris Gross) It is much shorter than the original /* FUNCTION NAME: REVERSESTR AUTHOR: Meni Porat DESCRIPTION: Reverses a string ARGUMENSTS: string: text string to be reversed EXAMPLE: =REVERSESTR("four score and seven years ago") */ REVERSESTR =LAMBDA(string, let(strlen,LEN(string), IF(strlen=0,string, CONCAT(MID(string,SEQUENCE(strlen,1,strlen,-1),1)))))
Hi, I'm from Brazil, I discovered this supplement a month ago, but I couldn't understand the explanation. how the editing of the lines works there in the Advanced Formula Environment editor so I created a lambda formula it calculates the BMI the body mass of a person =LAMBDA(HEIGHT; WEIGHT; WEIGHT / (HEIGHT^2)) how do i enter the lines in the Advanced Formula Environment editor?
So wie ich das sehe funktioniert es derzeit aber nur mit der englischen Version. Ich habe keine Möglichkeit gefunden, es auf deutsch umzustellen. Schreibe ich die Formel mit den englischen Bezeichnungen, habe Ichbewusstsein Problem bei der Synchronisierung, schreibe ich die Formeln mit den deutschen Bezeichnungen wird es vom AddIn nicht akzeptiert.
Hello. I have Microsoft 365 and yet when i try to use the add in i get the message that "This add-in won't run in your version of Office. Please upgrade to either one-time purchase Office 2021 or to a Microsoft 365 account". Anyone with a solution?
Is it possible to give feedback to Microsoft about the new formula editor or is it not Microsoft that develops it. Right now the LAMBDA function is available in my version of Excel but I cannot use the new formula editor because I am from Denmark and the function names are in Danish and the function separator are a semicolon instead of a comma. I get an error message every time I try to sync with the name manager. I would really like to give Microsoft this feedback and ask them when or if the new advanced formula editor will be localized in different languages.
@Leila Gharani I installed the Advanced Formula Editor, recreated both your example worksheets (including named formulas using the Name Manager). I opened the Advanced Formula Editor and clicked the 'Sync-to-Name-Manager' button but NO FORMULAS except what comes stock with AFE. I removed Advanced Formula Editor, updated my Office 365 installation, reinstalled the Advanced Formula Editor and clicked the 'Sync' button again with your example workbook opened. Nothing happened. What am I doing wrong?
Rather specific ByRow/Lambda/Aggregate question that has me stumped. Using those three formula works ONLY when the Aggregate function is one that requires a 'k' input (14-Large up to 19-Quartile.Exc). For the other arguments like a simple 1-Average the Aggregate seems to interpret the inputs as a Ref1, Ref2... rather than the Array, k version. This formula works: =BYROW(AU9:AU10,LAMBDA(Larray,AGGREGATE(16,4,FILTER(Z9#:AG9#,((X9#>=Larray)*(X9#
I miss the capability to add a help to know which variable to be selected in each step. I mean, in an excel formula you find this structure =IF([logical_test,if_tue,if_false]) in a Lambda function you don’t find this kind of help, so you have to remembet how you constructed your formula. Is that right? I hope I explained well
This component only works in English With the Italian version I can't synchronize the workbook name list. I get the error "Sync names with Excel name manager. failed: The argument is invalid, missing or has an incorrect format". Any other experience?
Tried it on my coorparate laptop - all add-ins blocked. Tried on my Window 11 personal laptop with the latest updates but their is no named manager on Excel 365
Out license only updates Bi Anually :( so i dont even have Lambda yet, and the Gist Github files are with English delimiters. so when i try to import them to name manager. i usually get Errors. it would be nice if Excel could just automatically identify location / language and update the codes accordingly
I believe the language adjustment is on the list of updates for Microsoft. For now, you could temporarily switch your settings in Excel and once you send the formulas to name manager, then you can switch back.
Leila - Is there a way to make the Lambda functions global; meaning that you create the functions once and they become available on any workbook that you open?
By default it uses a hidden metadata sheet in the same workbook to sync with the Name Manager. You can choose not to (in the add-in settings), but you lose the line breaks, comments etc. you add to the formula in the AFE.
Oh my god! The append row function its what i need to combine multiple tables data into 1 witouth writing in vba the same code everytime... I need lambda in my life, now 😂
As mentioned on the Mr.Excel / Access Analytic TH-cam channels: this functionality should be integrated in the Formula Bar & Name Box. And the Name Manager should be synchronised with that as well (or the latter could possibly be delayed by a checkbox). Imagine having this extended functionality over there… “what a wonderful world this would be” (like in the song). We can dream, right? :-)
And, indeed: at work all this means “zilch” (aka. nada, absolutely nothing) because we’re not allowed any add-ins. (Although I do hope “LAMBDA is coming to town” there as well - we want it !)
@@LeilaGharani No, all add-ins are prohibited for security reasons. By standard policy. I know of no organisation where this is not the case. Privately it is accessible.
❗Update: Microsoft changed the add-in. It's now called "Excel Labs".
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Leila, you seem to have influence at Microsoft. Can you tell the software engineers to have Lambdas able to be defined with other created Lambdas?
If this were possible, I, and I am sure others could develop functions much more easily and elegantly.
Right now you can not do this.
Quite useful, but needs to be updated. The Addin has different name - "Excel Labs" and "Advanced formula environment" is one of the features. Also the function editing interface is different. You have to input only your business logic functions, without using LAMBDA directly.
Thanks.
Considering LAMBDA was finally released on February 8th, this comes right on time. Thank you. I always chide you for showing things so early when business users can't use office insider features until they are released; however, this is great.
About video: easy to understand for all levels. As usual. Great.
This is definitely going to be my next favourite Excel function! Thanks Leila!
This feature is so awesome! Thanks for showing it to us in such an interesting and engaging way.
Wow, this functionality is just super powerful. I had no idea and would definitely start using this environment from now on.
Hi Leila. Great lesson! Thanks for the demo of how to use the new formula editor. Very helpful! Thumbs up!!
Never see anyone explaining ms excel in such a beautiful understandable way only Leila can explain 😘
This is quite fascinating. Looking forward to using this formulae editor. Thanks, Leila for this.
You're very welcome!
OH man, importing sounds very promising. I can now keep my repository of commonly used items out of the spreadsheet and import as needed. This is a game changer.
Once I have =LAMBDA() available I hope I also can get this Formula Editor. Unfortunately in the Office AddIns are managed by administrator 😥.
This, I think, is now produced as a gateway to Excel's DAX language.
At a later date, I think they presented it to users as a test example of making Excel more useful in DAX language.
While I was writing the formula, I saw the DAX formulas :)💥
Yes, I definitely enjoyed it as usual 🙂
I'm waiting a day in which I watch a video of yours that do not add anything to me!!
My colleagues think that I'm an Excel expert, but whenever I watch your videos I feel like I'm at the beginner level 😔
I love it when you teach new dance moves 😂
This is amazing. And it beats having a "conventions" sheet for functions (which was my workaround for the horrible Name Editor).
Thank you so much for showing and explaining this add-in.
I enjoy watching your videos and learning a lot about Excel from them. This, however, is one of the few times that I've been genuinely surprised by your content! I would never have learned about it had I not been subscribed to your channel. Thank You!
I remember what a pain it was using VBA to create custom worksheet functions. Now, combined with LAMDA and the Advanced Formula Environment, my work is going to be so much easier.
This is an awesome change. Now I just wish it were a standard part of Excel that doesn't require the use of an Add-in!
😷
Having taken over the running of our foodbank I have to make a weekly list of food parcels distributed and then from that data complie a monthly report of Meals Provided, Finances Used, Clients numbers(Adults and Children) etc. Already at the end of Feb and need to compile this months report and I am now looking forward to using Lamda functions to assist with this task ... I am sooo thrilled as always when you are bearing news that helps me -- I assume you are talking directly to me only ;)
Thanks for the great video. I have had the AFE for a few weeks now and have been playing with it. It is absolutely amazing and a long overdue addition to the Excel addins. Just as easy to use as the VBA editor if you only need to build a simple function. I also like that you can seperate out your lambdas into various namespaces. I really don't find the fact that you cannot direct reference cells to be a particular problem and would try at all costs to avoid hard-coding ranges into the function except where absolutely necessary (for instance a lookup range that you don't want other users to find and change lol).
Thanks Roy for the feedback. The namespaces are great! I didn't get a chance to cover them since the video was getting too long. I agree the cell referencing is not a problem for Lambdas, it's actually better not to have that option, but if this will be a replacement for name manager, then we need to have it. Many people use dynamic named ranges for charts, or have crazy offset and choose functions in there :)
@@LeilaGharani Yes I totally agree, it seems pretty clear that Excel named ranges are going to be replaced with the AFE and will need to be compatible with the previous version of named ranges; this could be good as they are stored in a hidden sheet, which will, hopefully, avoid other users hitting f3 and deciding to look at the formulas, unintentionally editing them by trying to move around with the arrow keys (This is something that happened on one of my workbooks 😞👿)
Thank you Leila. Hopefully one day I can make this Lambda function useful & integrated to my work.
Btw this is a bit out of context, but could you pls make a video giving a function that replaces DATEDIF function in O365? Thanks!!
This looks much simpler than using the name manager to manage those LAMBDA functions.
However, I wish there would be better support of keyboard (shortcut) support in this new configuration.
Requiring users to move their hands between their keyboard and mouse back and forth is almost always a bad thing for productivity tools like Excel.
In addition, if the Excel team really wants LAMBDA to be more user accessible, they should really consider making it possible to manage and edit all these LAMBDA formulae in a separate window, just like how VBA editor is in a separate window currently.
Wow!! I thought I was a champ in excel but Leila!! got me on my knees!!
There's always more to learn 😊
Leila is beauty with 🧠.... awesome explanation.....huge respect to u
Awesome! Excellent video tutorial. Been waiting for this Lambda. Will definitely use this. As always useful videos and outstanding YT tutor. Thank you for sharing your knowledge. ❤
love the appenrow, amazing. My students are going to love that one.
👍 It's really convenient.
Could you make a video about making an Excel file digitally signed? For example, if I am going to give an Excel file with VBA to someone, to ensure that they can open it without going through so many steps. With and without active directory login please....Thank you...!
Absolutely gonna use this in future
Leila this is great video from you as always.
Great explanation of the new tool. It looks great environment to write formulas. It could be in the ribbon by default instead in add ins.
Whew ... 😵😵😵😵
Candidly, I do not fly in the Advanced Formula Environment. Maybe someday, but for now I only walk on Planet Earth. But if that day arrives in my lifetime, I will know where to go to learn more.
So once again, the explanation and delivery was exceptional (no surprise) but as soon as I heard the word 'LAMBDA' my palms got sweaty, my brow beaded up in sweat ... and my heart rate shot to 187 ... 😲
🤣 I've seen some people call it Lambada - maybe that's a better name for it. It comes with happy vibes 💃
Microsoft needs to realize that many enterprises block the use of Add-Ins due to security concerns. That renders solutions like this absolutely useless in the environments where they would otherwise be valued and used most.
It's like getting the ice cream but being refused the sauce... :(:(:(
WELLLLLL IT heads (managers, etc...) should actually open their eyes to those kind of tools and allow the use of at least some of these add-ins; or have a process where you can actually ask them to enable specific ones.
This add-in for example, only import formulas at most... what security threat does that actually pose is something I cannot understand.
Same goes for Teams. In many cases, for many of my customers, EVERYTHING is blocked; including add-ins OF TOOLS THEY ACTUALLY USE on a daily basis. When you talk to them about it they mostly DID NOT KNOW what an add-in and did not bother to look into it and blocked everything from the start. SMH
Same is the case with macros
Oh I hope not. I'll have to it out if I can use in mine. I know I got scolded once for writing a simple VBA function
Thanks for the great video! I get workbooks from other people and then need to manually import my Lambdas and I want to automate how to bring them in. I created a gist but have to manually import that to every new workbook.
I have started using this feature since last week or two. I see two points for improvisation. First, It would have been great if exporting of lambda in some file format so that it can be shared across files to different people and second, sync names to name manager for selected lambdas only because I many not require some of them at the point of importing.
👍
Rajan ji, Chaupal me aapka darshan durlab ho gaya. Kab aapko chaupal me dekh payenge
Hola Leila, I love your channel and how you Easily you explain everything in plain English! Thanks for your help. I would like to know what kind of computer you recommend (surface or iMac), I know I asked before what type of computer you use for work jejeje, but I'm buying a computer and is a very important investment for me. Can you do a video regarding this (what type of computer you recommend)? I appreciate your help and answer. Wish you the best!
Oh that editor view is going to be a game changer
Thanks Leila 🙏🏻 this really is a great advance! Thanks as always for the detailed presentation 😃
Great explanation about lamda.. thanks
I still struggle with recursion. Providing a step-through of some of the imported functions such as text reverse would help greatly with making more use of lambda. Thanks
Thanks Leila, this is another game changer...
Hi! Thank you for make this useful video. I learn a lot from you. I wanna ask, is your course have english subtitle? Cause I can't watch video without it
You have great videos, thanks for that. My Excel do not have the Add-in tab under Insert. How do I insert the Add-ins tab?
I found Add-Ins on my ribbon's Home tab.
Thank you for all you did for me in Excel during the almost last 3 years MRS Gharani, But the great and trending question that comes to my mind is as an add-in won't cause problems in future to our excel performance??
Hi Leila,
Thank you for your consistent delivery of high quality, well packaged and excellently delivered video tutorial.
Please, I need a favour from you. My laptop was stolen just over a week ago and I lost a lot of files as a result. One of the files I lost is the excel file that contains all the list and links of the videos you have made. I really can't remember how to get the file again so I would like to ask if you can send the link to me so I can re-download it.
Thank you in advance.
Great video and what a great feature but how can I access previously created Lamda's in a new workbook?
AFE has changed a lot, and I was looking for how to save to Gist. Would love to see an update including that.
Another awesome lesson! Is the ability to store the Lambdas for use with another file coming? It appears that the Lambda created is gone when you open another file.
Thank you Leila for this guide. I wonder if the file will work when I share it with my colleagues. Or do they need to make extra steps before using the file?
Hi, I couldn't find the add-in by Advanced Formula Environment because now the add-in has a different name, "Excel Labs". Is the same add-in as Advanced Formula Environment?
Very usefull comment!
Another awesome video, thanks Leila!
Are you going to add it to your courses? It just seems very key when writing lambdas.
Thanks for all your videos, they made my job so much easier.
LOL, great stuff but I just learned about the F2 key while using mouse keys in Named Ranges editor. God how annoyed have I been for all these years just to learn about this now. :)
Been there too, David 😊 Glad you found something helpful.
Hi Leila, Thanks a lot for making amazing videos. I have one question, thought u definitely know this, how can one fill the websites of the companies automatically into the google sheets, if one has company names into the google sheets?
As soon as I saw LAMBDA functions for the first time I thought this was going to be its natural evolution. A repository so you can share LAMBDAs across users, a name manager since Excel's existing is terrible, a place to add comments to LAMBDAS as you build them since they can get complicated & long like VBA code, a way to import your favorite functions to other workbooks so you don't recreate them each time, etc.
Only improvements to come from here, exciting! I'm sure some people will naturally develop top rated LAMBDAs, Excel might even make the functions added to your 'favorites' and become default added to any new Excel workbook, etc.
Lambdas are great. And I'm sure there is going to be more development in this area.
لیلا دوست داریممم مرسیی که هستییییی❤❤
Nice, can we create one to sort a table automatically once the value changes in it.
Great content, but I just added it to Excel. In french version, arguments are separated by " ; " (if(a
I love this; however, when I add it the Enviroment is blank and it does not appear to be working.
Has something changed in the last year?
Great new feature and excellent explanation. BUT it must be: not an add-in; integrated fully into name manager; be integrated into the formula bar AND accept cell references to be optimally useful.
Exactly what I was thinking!
Agree - Power Query was an add-in too at first before it got integrated into Excel, so hopefully it's the same in this case....
Hello Leila,
first of all, great praise for your channel. I have also booked some courses with you / Xelplus which has helped me a lot.
Is there anything to consider when I use a German version of Office / Excel 365?
Because it does not work for me. It comes the error message:
Sync names with Excel name manager. failed: The argument is invalid, is missing or has a wrong format.
Any Idea from your side?
Problem at 2:25… When i click on that sync icon i get the error “Sync names with Excel name manager failed: Das Argument ist ungültig, fehlt oder hat ein falsches Format.”.
I never used this advanced formula environment before, i did exactly the same as you in this video.
Even if I delete the examples and try it with the Lambda you mentioned here later (noblank) it won’t sync.
Do i have to set something special for getting this working?
@ingo: If you are using another delimiter than comma between arguments (in Denmark we're using the semicolon), you'll have to temporarily change it to a comma on your PC (I don’t remember the specific place, but it is somewhere under regional settings). Then you can sync it to the name manager and afterwards you can change the delimiter back to the original and use the “formulas” as usual.
I had a similar issue when I first downloaded the AFE, it went away when I re-downloaded the AFE and re-installed it to Excel, maybe you could try that.
@@hcandersen2007 but then it would conflict with the decimal separator, which is also a comma in many countries. You'd have to change all regional settings. This add-in should be locale-aware.
After years of advanced Exceling ... I never knew F2 to use the arrow keys ... data validation, etc. will be easier now
This is an awesome new feature :)
Thx, Leila.
Unfortunately the AFE does not work for me. Export to Name Manager shows either analysis error or "OK", but nothing arrived at Name Manager.
I'm using:
- *German* Microsoft 365
- AFE plugin (working: show, edit)
- Chris' github gist source of Lambdas, available in AFE.
I guess this:
1. AFE cannot syntax check, if the source is *not English* (translated English source to German; put to AFE: red curled under.)
2. If you enter *German* Lambdas, you still cannot export to Name Manager.
How to overcome?
The addin looks pretty good: is it possible to use the semicolon as arguments separator instead of comma?
In this first edition it doesn't seem to support settings where ";" is the argument separator (But I'm pretty sure they will add that in). I think you should be able to temporarily change your Excel separator to a comma to use the add-in and then back.
Thanks for great video.
Leila need your help if you can help me, i have linked my shapes with other worksheet showing data as % and i want to show negative % in red and positive % in green either by VBA or any other method.
Perhaps conditional formatting or custom number formatting if you're using symbols?
@@LeilaGharani Custom formatting
@@LeilaGharani Is it your answer or are you asking me question? But is it possible to do custom formatting on test in shapes??
I went interview, got some data. Interviewer asked to give some useful information by analysis data. I did it but not good. So kindly make video about how to analysis data and take out some useful information from it.
Thank you 🌹
Hi. I have a question for u plz, i use french version of office 365. I tried to upload the chris gross new lambda as you did in yr tuturial. But i failed and don’t know the reason. Tried many times without success. Can u help plz?
Looking forward to finally using LAMBDA. Should the AFE show items that you originally created in Name Manager? I created a new item in the AFE and clicked on the sync button - this shows in the Name manager but other items in Name Manager don't show up in the AFE? Am I missing something?
Is this Advanced Formula Editor still there? I can't find it in the Store!
It is now "Excel Labs, a Microsoft Garage project" . It seems it is a little "use at your own risk"
Here is an alternative to the LAMBDA function: TEXTREVERSE (suggested by Chris Gross)
It is much shorter than the original
/*
FUNCTION NAME: REVERSESTR
AUTHOR: Meni Porat
DESCRIPTION: Reverses a string
ARGUMENSTS:
string: text string to be reversed
EXAMPLE:
=REVERSESTR("four score and seven years ago")
*/
REVERSESTR =LAMBDA(string,
let(strlen,LEN(string),
IF(strlen=0,string,
CONCAT(MID(string,SEQUENCE(strlen,1,strlen,-1),1)))))
I can't get this addin on my office laptop due to org policy,so waiting on Lamda function to get very soon.
Hi, I'm from Brazil, I discovered this supplement a month ago, but I couldn't understand the explanation.
how the editing of the lines works there in the Advanced Formula Environment editor so I created a lambda formula it calculates the BMI the body mass of a person
=LAMBDA(HEIGHT; WEIGHT; WEIGHT / (HEIGHT^2))
how do i enter the lines in the Advanced Formula Environment editor?
So wie ich das sehe funktioniert es derzeit aber nur mit der englischen Version. Ich habe keine Möglichkeit gefunden, es auf deutsch umzustellen. Schreibe ich die Formel mit den englischen Bezeichnungen, habe Ichbewusstsein Problem bei der Synchronisierung, schreibe ich die Formeln mit den deutschen Bezeichnungen wird es vom AddIn nicht akzeptiert.
Leila, after installing the AFE, I now am getting an error in my (admittedly huge) model. I see no way to uninstall the AFE. Do you know how?
Hello. I have Microsoft 365 and yet when i try to use the add in i get the message that "This add-in won't run in your version of Office. Please upgrade to either one-time purchase Office 2021 or to a Microsoft 365 account".
Anyone with a solution?
i have the same problem - im using office365 as well and get the same error
hi,
how can I use Lambda that made in one workbook, in all my other workbooks?
Can we import a web url (ends with.csv) into Lambda function??
Is it possible to give feedback to Microsoft about the new formula editor or is it not Microsoft that develops it.
Right now the LAMBDA function is available in my version of Excel but I cannot use the new formula editor because I am from Denmark and the function names are in Danish and the function separator are a semicolon instead of a comma.
I get an error message every time I try to sync with the name manager.
I would really like to give Microsoft this feedback and ask them when or if the new advanced formula editor will be localized in different languages.
@Leila Gharani I installed the Advanced Formula Editor, recreated both your example worksheets (including named formulas using the Name Manager). I opened the Advanced Formula Editor and clicked the 'Sync-to-Name-Manager' button but NO FORMULAS except what comes stock with AFE. I removed Advanced Formula Editor, updated my Office 365 installation, reinstalled the Advanced Formula Editor and clicked the 'Sync' button again with your example workbook opened. Nothing happened. What am I doing wrong?
Rather specific ByRow/Lambda/Aggregate question that has me stumped. Using those three formula works ONLY when the Aggregate function is one that requires a 'k' input (14-Large up to 19-Quartile.Exc). For the other arguments like a simple 1-Average the Aggregate seems to interpret the inputs as a Ref1, Ref2... rather than the Array, k version. This formula works: =BYROW(AU9:AU10,LAMBDA(Larray,AGGREGATE(16,4,FILTER(Z9#:AG9#,((X9#>=Larray)*(X9#
I cannot copy and paste from my desktop excel to my one drive excel...how to do this... please help me via video... 🙏
I miss the capability to add a help to know which variable to be selected in each step. I mean, in an excel formula you find this structure =IF([logical_test,if_tue,if_false]) in a Lambda function you don’t find this kind of help, so you have to remembet how you constructed your formula. Is that right? I hope I explained well
do you know how fantastic you are, i am devoted subscriber
This component only works in English
With the Italian version I can't synchronize the workbook name list.
I get the error "Sync names with Excel name manager. failed: The argument is invalid, missing or has an incorrect format".
Any other experience?
Tried it on my coorparate laptop - all add-ins blocked. Tried on my Window 11 personal laptop with the latest updates but their is no named manager on Excel 365
Out license only updates Bi Anually :( so i dont even have Lambda yet, and the Gist Github files are with English delimiters. so when i try to import them to name manager. i usually get Errors. it would be nice if Excel could just automatically identify location / language and update the codes accordingly
I believe the language adjustment is on the list of updates for Microsoft. For now, you could temporarily switch your settings in Excel and once you send the formulas to name manager, then you can switch back.
@@LeilaGharani in not sure exactly what language to use though
This AFE add-in does not work offline. Is that the case?
Leila - Is there a way to make the Lambda functions global; meaning that you create the functions once and they become available on any workbook that you open?
Not. But hopefully we'll be able to in the future. Also saving them to a OneDrive folder (like Office scripts) would be great.
External add ins are blocked in my company, frustrating.
Does this add-in work with excel 2019 and older versions?
Can not get this to work on my computer. Get error message regarding "Task Pane"???
I have a french version of excel, but it looks like this addin is not compatible with the french version
Awesome.. 😍😍
Those names sit in a specific file right?
When i put it into my Personal.xlsb, are they available in other files or do I need to copy them every time?
By default it uses a hidden metadata sheet in the same workbook to sync with the Name Manager. You can choose not to (in the add-in settings), but you lose the line breaks, comments etc. you add to the formula in the AFE.
@@fstorino okay, i will check it out. Thanks
Hi Leila... I can't find lambda in my office 365...any idea why?.. Can you help me?
Thanks in advance
It depends on your update frequency (monthly or semi-annual).
Is lambda now live in excel programs?
Yes. It's finally rolled out to Office 365 - those that get the first round of updates. I think it's the current channel.
Oh my god!
The append row function its what i need to combine multiple tables data into 1 witouth writing in vba the same code everytime...
I need lambda in my life, now 😂
Wow. Thank you
As mentioned on the Mr.Excel / Access Analytic TH-cam channels: this functionality should be integrated in the Formula Bar & Name Box.
And the Name Manager should be synchronised with that as well (or the latter could possibly be delayed by a checkbox).
Imagine having this extended functionality over there… “what a wonderful world this would be” (like in the song).
We can dream, right? :-)
And, indeed: at work all this means “zilch” (aka. nada, absolutely nothing) because we’re not allowed any add-ins.
(Although I do hope “LAMBDA is coming to town” there as well - we want it !)
Do you need admin rights to install these types of add-in? i.e, the ones done directly through the Ribbon?
@@LeilaGharani No, all add-ins are prohibited for security reasons. By standard policy. I know of no organisation where this is not the case.
Privately it is accessible.
Update for the video: It is now "Excel Labs, a Microsoft Garage project" . It seems it is a little "use at your own risk"
Thanks for sharing this!
Unfortunately link doesn't work. It says "The supplied URL is malformed or not supported." I followed as mentioned in video.