Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

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! 😷

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.

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. ❤

This is definitely going to be my next favourite Excel function! Thanks Leila!

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.

@retamapark

2 ай бұрын

Thanks.

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.

Hi Leila. Great lesson! Thanks for the demo of how to use the new formula editor. Very helpful! Thumbs up!!

This feature is so awesome! Thanks for showing it to us in such an interesting and engaging way.

Thanks Leila 🙏🏻 this really is a great advance! Thanks as always for the detailed presentation 😃

Wow, this functionality is just super powerful. I had no idea and would definitely start using this environment from now on.

Absolutely gonna use this in future Leila this is great video from you as always.

About video: easy to understand for all levels. As usual. Great.

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 😔

Another awesome video, thanks Leila!

This is quite fascinating. Looking forward to using this formulae editor. Thanks, Leila for this.

@LeilaGharani

2 жыл бұрын

You're very welcome!

Great explanation about lamda.. thanks

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.

Never see anyone explaining ms excel in such a beautiful understandable way only Leila can explain 😘

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 ;)

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 :)💥

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).

@LeilaGharani

2 жыл бұрын

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 :)

@roywilson9580

2 жыл бұрын

@@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 😞👿)

Thanks Leila, this is another 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 is an awesome new feature :)

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.

Leila is beauty with 🧠.... awesome explanation.....huge respect to u

Wow. Thank you

love the appenrow, amazing. My students are going to love that one.

@LeilaGharani

2 жыл бұрын

👍 It's really convenient.

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 love it when you teach new dance moves 😂

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!!

Thank you.

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.

Wow!! I thought I was a champ in excel but Leila!! got me on my knees!!

@LeilaGharani

2 жыл бұрын

There's always more to learn 😊

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!

لیلا دوست داریممم مرسیی که هستییییی❤❤

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 ... 😲

@LeilaGharani

2 жыл бұрын

🤣 I've seen some people call it Lambada - maybe that's a better name for it. It comes with happy vibes 💃

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??

Awesome.. 😍😍

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.

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

Oh that editor view is going to be a game changer

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?

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.

@jmds6660

2 жыл бұрын

It's like getting the ice cream but being refused the sauce... :(:(:(

@TheMozerfok

2 жыл бұрын

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

@irfanabbassi9202

2 жыл бұрын

Same is the case with macros

@whimpypatrol5503

Жыл бұрын

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

Superb

Εισαι θεα!

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?

do you know how fantastic you are, i am devoted subscriber

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...!

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.

Great video and what a great feature but how can I access previously created Lamda's in a 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.

@LeilaGharani

2 жыл бұрын

👍

@atpugsad

2 жыл бұрын

Rajan ji, Chaupal me aapka darshan durlab ho gaya. Kab aapko chaupal me dekh payenge

Nice, can we create one to sort a table automatically once the value changes in it.

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?

AFE has changed a lot, and I was looking for how to save to Gist. Would love to see an update including that.

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

Wow!

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?

@davidorchard4738

8 ай бұрын

I found Add-Ins on my ribbon's Home tab.

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.

@LeilaGharani

Жыл бұрын

Lambdas are great. And I'm sure there is going to be more development in this area.

The addin looks pretty good: is it possible to use the semicolon as arguments separator instead of comma?

@LeilaGharani

2 жыл бұрын

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.

After years of advanced Exceling ... I never knew F2 to use the arrow keys ... data validation, etc. will be easier now

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?

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 🌹

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 😂

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. :)

@LeilaGharani

Жыл бұрын

Been there too, David 😊 Glad you found something helpful.

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?

Does this add-in work with excel 2019 and older versions?

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?

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.

@samuelanane-kyei4822

2 жыл бұрын

Exactly what I was thinking!

@LeilaGharani

2 жыл бұрын

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....

I can't get this addin on my office laptop due to org policy,so waiting on Lamda function to get very soon.

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?

Can we evaluate the formula via F9?

Can we able to evaluate formula via F9 key?

Can we import a web url (ends with.csv) into Lambda function??

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.

@LeilaGharani

2 жыл бұрын

Perhaps conditional formatting or custom number formatting if you're using symbols?

@idhukaful

2 жыл бұрын

@@LeilaGharani Custom formatting

@idhukaful

2 жыл бұрын

@@LeilaGharani Is it your answer or are you asking me question? But is it possible to do custom formatting on test in shapes??

@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?

hi, how can I use Lambda that made in one workbook, in all my other workbooks?

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?

@LeilaGharani

2 жыл бұрын

Not. But hopefully we'll be able to in the future. Also saving them to a OneDrive folder (like Office scripts) would be great.

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?

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?

@juanfox2657

3 ай бұрын

Very usefull comment!

How to draw p&id diagram in excel please share videos

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.

This AFE add-in does not work offline. Is that the case?

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)))))

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?

As mentioned on the Mr.Excel / Access Analytic KZread 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? :-)

@GeertDelmulle

2 жыл бұрын

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

2 жыл бұрын

Do you need admin rights to install these types of add-in? i.e, the ones done directly through the Ribbon?

@GeertDelmulle

2 жыл бұрын

@@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.

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?

@thinkhelpservice

2 жыл бұрын

i have the same problem - im using office365 as well and get the same error

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

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

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?

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?

@hcandersen2007

2 жыл бұрын

@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.

@roywilson9580

2 жыл бұрын

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.

@fstorino

2 жыл бұрын

​@@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.

External add ins are blocked in my company, frustrating.

I cannot copy and paste from my desktop excel to my one drive excel...how to do this... please help me via video... 🙏

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?

@fstorino

2 жыл бұрын

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.

@jensmarkgraf8437

2 жыл бұрын

@@fstorino okay, i will check it out. Thanks

I have a french version of excel, but it looks like this addin is not compatible with the french version

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#

When I try to open the Formula Editor, I just get this message, “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.” even though I'm using a 2021 MS Office Professional Plus License. Does anyone have an idea as to why the Editor is not working on the desktop?