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
3 жыл бұрын
True, that will remain an issue.
@forgetittube5882
3 жыл бұрын
@@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
3 жыл бұрын
Learn SQL, stop using Excel as your data manipulator....
@medasbabu
3 жыл бұрын
@@stuckcamping I want to, but the biggest stumbling block is configuring SQL and permissions to db, cant over come this
@LeoLopesLL
3 жыл бұрын
@@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.
@dermotbalson
3 жыл бұрын
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.
@jscomputergenius3834
2 жыл бұрын
Which version of MS Office are you using?
@cenevspeed_CARS
3 жыл бұрын
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 😎
@caswarupghosh
3 жыл бұрын
You are a magnificent teacher, who believes in updated technology and shares selflessly.....great stuff
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.
@cenevspeed_CARS
3 жыл бұрын
How do you find out whenever something new is released or added? You are always up-to-date, how? Atleast I have subscribed to you and up-to-date, but still wonder where you get the information from🤔
@tuyetnhungnguyenthi8871
2 жыл бұрын
Video with full video duration ranges (18-5,400 seconds) should be available for Sara Dietschy. Now Leila Gharani has very short video duration ranges that cannot maintain good call quality on internet service provider.
@indzara
3 жыл бұрын
I don't have this in my Excel yet. Looks promising. Will definitely use it when it becomes widely available. Thanks for sharing.
@tuyetnhungnguyenthi8871
2 жыл бұрын
We're experiencing short video duration ranges. It may cause automatic shutdown to occur over long range.
@chh8860
3 жыл бұрын
WAAAY over my head ... but definitely not a criticism ... topics like these are one of the reasons I happily 'follow' Leila's Channel and Courses. But candidly, I cannot even relate the dataset to my work ... which is usually the prerequisite step for attraction. I still feel I have miles to go before I am fluent in many of the existing Excel functions ... so the prospect of using one, or both, of these functions in the near term is not too likely. Excel functions are like spices in the pantry ... lots and lots of choices ... which can provide many opportunities for creative applications ... but just the name 'Lambda' makes me slightly nauseous ... like finding a recipe for adding smoked thyme to peanut butter ... whoa! ... where did that come from? ... and who thought that was a good idea? But maybe someday ... you just never know. Thank you again Leila ... a brief window to a world I never knew existed.
@LeilaGharani
3 жыл бұрын
It's always good to know what's out there. Might come in handy one day :)
@chh8860
3 жыл бұрын
@@LeilaGharani Agree ... TOTALLY agree.
@vagga2u14
3 жыл бұрын
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.
@napoleontorres8379
3 жыл бұрын
it looks easy but difficult to use because of combination of different formulas
@Ronnet
3 жыл бұрын
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.
@bruxy70
3 жыл бұрын
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!
@LeilaGharani
3 жыл бұрын
Unfortunately yes. They’re still not generally available. Hopefully soon though.
@Krazycat2012
3 жыл бұрын
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
@Dev_Bartwal
2 жыл бұрын
15 M wow congratulations I am following you when 2k followers you have I am a big fan And you are our Superwoman
@Jonathanschwa
3 жыл бұрын
The first thing I think about regarding new function is compatability with older versions of excel and excel for Mac.
@Hundo_Mo
3 жыл бұрын
This is tremendously helpful!! I was literally just trying to figure out a way to create a spreadsheet just like this!
@thefamousdjx
3 жыл бұрын
im on office 365 and have never seen the lambda function or any of this. How do you get these features in yours?
@CASanjivBansal
3 жыл бұрын
Hi Leila, I've a specific query. I've a table with positive and negative numbers in many columns. I need to use subtotal function because I'd be applying filter AND need separate subtotals of positive and negative numbers below such columns even after applying filters. Say this is a ageing analysis of receivable ageing report.
@christoslefkimiotis9889
3 жыл бұрын
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.
@MaydayAggro
Жыл бұрын
I wonder why they created these to require LAMBDA. why not just =BYROW (SUM (B2#)? Why create the necessity of using a LAMBDA when the purpose is to break the array into parts? Then you could create a LAMBDA if you required a special use formula.
@sidhantgoyal4327
3 жыл бұрын
What if the LAMBDA function has two variables, then how to use these functions?
@nickdimuzio4629
Жыл бұрын
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?
@jtmh31
2 жыл бұрын
Like LAMBA and SCAN, is there any idea of when these functions are actually going to be AVAILABLE? For Enterprise subscribers, it's a difficult thing to tell your boss, "Hey, if we were insiders, we'd have access to these cool 'new' functions." Until then, videos like this are about pure vaporware.
@markippo
2 жыл бұрын
Ok. I'm a data analyst and programmer. I do use lambda functions a lot, because they're rather spread among programming languages. And for god sake... Exel these days simply sucks for analytics. Pivots are outdated, charts are inaccurate, limited and simply broken, vba is a joke, and one function that I usually use - vlookup is so broken when used with sql based data (data types conflict even from sql server ). And finally one bloody nail to the coffin - large excel file is an unstable excel file... Exel is good only for highly aggregated data with all flags created on sql level. Put in pivot, show, forget. You want proper, accurate report, use Python, R, whatever you want, excel today is good for showing that Dave hit the sales target...
@MalBackTalkingTesla
2 жыл бұрын
Leila can you help I need a button on my data sheet that when I hit it it goes to row 258 and another button to go to the top of the sheet my data base is alphabetical
@pegmolter3542
3 жыл бұрын
I'm somewhat of an Excel nurd, and this is wayyyy too confusing. In addition, I've been hearing about LAMDA (also confusing) for almost a year and still don't have it available in my 365 install. No, I won't use this, but will spend time learning something I can actually understand and apply.
@LEO_rumano
3 жыл бұрын
Nice functions . With this we can say goodbye to pivot tables
@Milaniriojas
3 жыл бұрын
How to get excel to show Monday - Friday in date format for that week? Ex. Cell c2 to show: Sep-9/27/21-Oct-10-1-21 Thanks!
@kanagarajkraj-vh1fo
3 жыл бұрын
In google sheet I have a range that refer to an external data. This external data is refreshed every one minute . So the data is changing every one minute. I need to copy the content of one fix cell in that range into another cell every one minute, each time copy to a different cell. Example: cell A1 has the content that refer to an external data. Cell A1 is updated every one minute. At first A1= 100, I need it to be copied to cell B1 (so B1=100); one minute later, A1=101, I need it to be copied to B2 (so B2=101) and so on how to make this in google sheet??
@techspertrichard1198
3 жыл бұрын
Your last example is a bit mis-leading in the output. Those results would best be servied, if both questions were being answered, with the names meeting/exceeding the amount per month, yes? Feb would list Doug, Darcy, Cat. It also helps if the colhdgs are centered. 😛
@McIlravyInc
3 жыл бұрын
Would this help with summing columns dynamically based on the header (like all columns that start with 47)? I want to do in PQ but can't figure out how to do it dynamically so wondering if I have to load the table to a worksheet and apply formulas there instead? Thx.
@puldanhaudanhabpem6720
3 жыл бұрын
Assalamualaikum Wr wb, good morning, I'm just asking you sis, is there an excel formula to find the sum between 2 columns, the first column contains 5 rows with numbers consisting of 5,4,3,2,1 and the second column contains 1 row, namely 10. is there an excel formula for numbers whichever of the first column has the same amount as the 2nd column (10) marked with a color or otherwise in the first column which indicates that from the first 5 rows of the first column is the sum of the 2nd column, which is 10 color mark in the row in the first column or other marks in the row in the first column indicating that the number marked with the number is the same as the second column kolom 1 kolom 2 5 10 4 3 2 1
@346saadkhan
2 жыл бұрын
DEAR LEILA, you didnt mention in this video that which office version in comparable of lembda function, I am using excel 365 but i didn't find in it
@leerichardson4629
3 жыл бұрын
Could I pick someones brain? Can anyone recommend the best formula to match two tables? Table 1 - purchase ledger, table 2 - sales ledger (each having 5 columns of data). I need to match the individual unit sold to the relevant purchase, this will allow me to calculate the margin per unit. Or is it possible to set up a 3rd table which pulls the purchase and the corresponding sale together?
@cleanthisconstantinou7013
3 жыл бұрын
LAMBDA function still not available in Office365... Any ideas when to expect for it??
@yamiyeo5352
3 жыл бұрын
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
@humorous2me
3 жыл бұрын
I think about little mammals in the countryside (lambs). Then I think, thank goodness I subscribe to your channel. :-)
@patrickkinbonso1809
3 жыл бұрын
Thanks Leila ... I don't seem to have the Bycol or Byrow function on my Microsoft 365 Excel?
@byteseq
3 жыл бұрын
It disgusts me that MS leaves earlier versions behind like this. They should years ago have made the calculation-module upgradable to make new formulas available to more users. Having support users on older platforms means these functions are wasted for many people.
@HASSAAN2306
3 жыл бұрын
By lambda I thought its something related to wavelength of a frequency.
@samlimam2740
3 жыл бұрын
Thanks Leila. It is amazing. I don’t find Bycol in excel. Is this normal ? What should I do ? Can you advise please ?
@luisburgois1727
3 жыл бұрын
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!!
@Swisscow6
3 жыл бұрын
sry Huang...but you have to get a new job...i'm sad aswell ^^ Great functions! Good job
@Rishigoyal78
3 жыл бұрын
Could you please help me actualy the attachments are not send with my Gmail , I don't know what happen but I tried everything after read all the instructions on internet. I m still facing the same problem and I don't know how to fix it. ?
@learnwithcookies398
2 жыл бұрын
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...
@LeilaGharani
2 жыл бұрын
Many thanks for your feedback!
@timwhite8952
3 жыл бұрын
I have 365 apps for business. I hope this version will get the lambda function. Great video.
@selinawang6532
3 жыл бұрын
Wow...this is quite complicated...I will try to use it if I come across something that needs the function. But, I cant think of any example that I may use it
@sameerh3111
3 жыл бұрын
It's great learning for me, thank you so much
@LebZeppelin
3 жыл бұрын
Me watching this vid and trying it on Excel 2013 😂 Is there a pack to install to get these functions working on my excel?
@jun369young
Жыл бұрын
Is there a way to make the table source dynamic as well? For example if I want to add 12 more months to the right, the only way for this to work is to manually expand the table formula reference?
@raviranjan2447
3 жыл бұрын
Thanks Leila... Good to know info as always.... But ur voice is too sweet and the best
@ShadowViewsOnly
2 жыл бұрын
I still don't even have LAMBDA in my Excel 365... Sooo... XD
@chaiyya345
3 жыл бұрын
Hi LG, im still with Excel 2016, do u mind to share an alternative for those with no dynamic arrays? Thank-You very much
@arunkumar-kk4ov
3 жыл бұрын
Hi leila, I have one quarry that i want to share with you. actually i have to make one report on Daily basis and that report is base of every other report that my management requires. For now i just want to know how i can share my quarry and report with you. Hope i will receive a reply Take care
@Freeflier1978
2 жыл бұрын
Lambdas aren't really new to me, since I write most of my excel sheets in python, but it's nice that MS brought them into Excel.
@DraconaiMac
2 жыл бұрын
Lambda does sound a little mysterious and vague. That makes me curious but, at first blush, it sounds like a deep-end math or trig function.
@racerx1326
Жыл бұрын
Can you name lambda functions that are passed to ByCol or ByRow? It's not working for me.
@maroofzada2756
3 жыл бұрын
Hi, i am using ms excel for students result card, in this card i alsi inserted school mono,there are two sheets that r linked with each other, now problem is that, sheets working is slow down, it hangs for 3 to 4 seconds. What should I do?
@HobbyFinn2
2 жыл бұрын
Formula bar needs upgrade. These should be written multi line with comments. The basic excel rule is no long formulas.
@TheNabilshow2
3 жыл бұрын
Can someone explains to me why so many companies still are reluctant to update to newer versions of Excel? It is so frustrating when some still are stuck in older versions.
@mishellekochel4510
2 жыл бұрын
I really need to start some beginner training and catch back up. I've lost some of my excel skills indeed. 😒
@tellnewstv
3 жыл бұрын
New content always. Thank you for the upload. We are watching keep up more...
@botsonyananyareli1073
3 жыл бұрын
I can definatetly use these however what are the options if my package is not a business package and thus I do not have Lambda supported?
@mrCetus
2 жыл бұрын
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?
Mind blown, came back to this vid today as opportunity to use this came up. So ggod
@philachinzah7543
3 жыл бұрын
Plzzz,can u explain again cash flow statement in more detail i have watched the one u posted but still im doubt plz plzz misss
@laffiny
2 жыл бұрын
I love your videos and i'm a big fan, but this doesnt really look like a simplification :)
@ivanmamchych5802
3 жыл бұрын
Thanks for the video, Leila However I would rather send the table to Power Query, unpivot it there, add the column with criteria, filter results, remove duplicate s and transpose the result. These steps seem to me far more logical and simpler
@koborkutya7338
2 жыл бұрын
a great evolution would be to run unpivot with a spill formula without invoking PQ. I work a lot with PQ but writing a single formula is IMHO not more complicated than building a query within the same workbook.
@terrywhite7190
2 жыл бұрын
I think, "until someone tells me what this Lamda stuff is about, I can't even begin to have a opinion."
@kevinchan6271
2 жыл бұрын
Hi Leila, i have a question, can I use Lambda in hopes to return multiple values if the first value was return and return the second?
@cantorgauss
3 жыл бұрын
Why not directly write SQL commands instead of these obscure microsoft functions
@iduncanw
3 жыл бұрын
Pretty cool. Can't immediately think of a use case though. Maybe one day 😎
@khorajia
Жыл бұрын
Why was 'more than sign' put in inverted commas in lambda function?
@guadalupeaguilera7818
2 жыл бұрын
very good tip LEYLA thanks, greetings from Mexico
@carbonbiker
2 жыл бұрын
Is that a Grundig radio behind you? My grandfather had one of those…. Awesome!
@YahuudGaming
3 жыл бұрын
help, the lambda function in my excel (latest) is missing
@brearegans8683
3 жыл бұрын
Jesus is your free gift to heaven when you believe in his death, burial and resurrection you are immediately saved.
@AbhishekSharma-gn4iu
2 жыл бұрын
CAN U GIVE AN EXAMPLE OF GOOGLE FINANCE TO GET ARRAY OF HISTORIC PRICES OF STOCKS FOR LIKE 1 MONTH USING MAP OR BYROW
@laffiny
2 жыл бұрын
Lambda Helper Functions - sounds bad, doesnt really tell me what is it for
@tclum
11 ай бұрын
Dear Laila, Is that a way way to combine byrow or bycol with sumifs function?
@sekhargreen
3 жыл бұрын
Hi even I am office 365 user, I am not getting many of these functions. Any way to get a hands on?
@piotrekolczyk9003
3 жыл бұрын
I would like to use them :D I think they might be useful planning a work schedule
@lagman7
3 жыл бұрын
Nice one but what if more than one person reached the target in a single month. You would just get the names but not the month these persons achieved this in.
@slavimo
3 жыл бұрын
What if I want to see the employees that were above the target and the list of the months where the employee performed per each employee in the next column? :)
@dragonshadow1902
3 жыл бұрын
Always enjoy learning these functions that I could never figure out. Though I never needed the Lambda function sincerity I never needed to take revenge on the nerds. 😁
@Roger-mg8zh
3 жыл бұрын
Na ja, ehrlich gesagt übersteigt hier der Aufwand den Nutzen aus meiner Sicht. Mit immer wechselnden Daten zu aufwendig. Aber mal was neues wer auch immer da ein Vorteil sieht. Thanks anyway :-)
@olliehopnoodle4628
Жыл бұрын
With dynamic arrays there are some functions that don't return 'arrays'. Min and max are a couple of examples. You can use BYROW/BYCOL to make it work with IF and other functions. I have also found that using dynamic arrays the AND function doesn't work as we might expect and clever use of BYROW/BYCOL can work around this as well.
@mrfriendly9956
3 жыл бұрын
For the most part we can all the standard functions available 15 years ago
@albert4491
3 жыл бұрын
This is great! i have a query, how can i get the names and the list of months next to them where they have surpassed the target, any help?
@kashifmasood6735
3 жыл бұрын
Pls present again 2nd function not correct answer asper desire cells. sales man name not correct place asper monthly performaer
@siddharthranawat1814
3 жыл бұрын
I always forget how to use the function you teach us. what should I do to make them permanent in my memory
@imbly
3 жыл бұрын
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 :)
@LeilaGharani
3 жыл бұрын
Wow, thank you Tomislav!
@awesh1986
3 жыл бұрын
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.
@manissery1956
Ай бұрын
Thank you so much. It solved a very tough task for me🎉🎉
@larrypenner83
3 жыл бұрын
Not going to be using Lambda anything. Too involved for an old dog like me..
@deluxelex9056
2 жыл бұрын
I don't think watching this while high is a good idea. I will need to watch 8 more times to grasp 🧐
Пікірлер: 460