Love them all, but LET is a game-changer. I've spent most of my day watching your videos and this has been the best use of my time in months. Thank you!
My favourite function is actually filter as it solves a lot of my issues with my accounts file. I am not a professional user of excel and my knowledge of it is only 3 years old that's why I come accross situations where it hits a wall and goes no further. as for my question, it's about one of my filters I uses to create a statement of account for my clients where I also use choosecols. the if_empty part doesn't work for me at all. here is the formula [=CHOOSECOLS(FILTER(ORDERS!$B$4:$Q$318,(ORDERS!$D$4:$D$318>=G14)*(ORDERS!$G$4:$G$318="Yes")*(ORDERS!$D$4:$D$318
Hi Mynda, LET me thank you for this UNIQUE tutorial. You have FILTERed Excel for the best latest formulas, and have put them in proper SEQUENCE. Your narration is smooth and professional without any TEXTSPLIT. IFS we need any excel tutorials be assured we will XLOOKUP your channel!!! 😂 Excellent and to the point video as always.
Back in the day, a chap called Peter Noneley released a comprehensive cheat sheet of all Excel functions, with working examples too. The secret of my success with Excel. Look how far we have come! Another great video Mynda!
Just have to say, despite being an advanced user of Excel, your videos are brilliant! I'm learning things about stuff I thought I understood that is advanced for others... Thank you!
Outstanding video, Mynda. Easy to follow, very helpful and thank you for producing it. Your explanations are spot-on and your narration is superb (in love w/ your accent). Great video!
I just wish Microsoft could roll out these much quicker. After all these months I still do not even have the tick box option. I appreciate all your efforts to show all the new features, but after a while I tend to skip the videos as I know we will only see them in 12 months time. It's like Mercedes Benz advertises a new car, but then it will only be available in a years time. By then i have already bought another car.....
I know what you mean, Chris. It's frustrating, but the check boxes have undergone a rework based on feedback received in beta testing, so I guess it's worthwhile.
Favourites: IFS - have been caught by stacked close brackets SO many times, FILTER, SORT and UNIQUE - awesome enablers, and SEQUENCE - hadn't thought of using it as the index column that I've always had to create by formula before, generally with OFFSET to avoid REF errors with row insert/delete. Thanks for the example with tables, I'll seek out more guidance about where using them makes more sense than not.
I keep forgetting about the Filter function. Very useful and I like the Idea of findining distinct words by using UNIQUE. That kind of hint, is extremely useful. Using Functions more out of the box. Thank you
Wow, great video Mynda. I know all formulas from earlier video's you've shared but I tend to use the old ways and work around them. Much more time consuming, I know. My challenge next month wil be to use at least 5 of these. My personal fav is [LET]!
The best parts are the examples where these functions would be useful. Thank you! I had a workbook with 15 or more nested IF statements that I changed to IFS which helps readability. The piece about adding TRUE at the end is a great tip. I also threw in some LET statements in other formulas to remove repeated table references. Unfortunately, these changes ballooned the size of the file from 10MB to 50MB! Is there a tradeoff in file size when using any of these functions?
Glad you found the examples helpful. If you're nesting 15 IFs, I'm thinking maybe you should be using a lookup formula. As for LET ballooning your file size, I'd be interested to see the file for this, because it should result in the opposite effect. You can reach out via email: website at MyOnlineTrainingHub.com
Love a LET! ❤I particularly like that there is no need to nest LETs - you can use name1 in the definition of name2, and so on. I think it really helps the reader understand a complex formula.
Thanks for the video Mynda, Good to see I have mastered all 10. I might be tempted to add MAP to that as I tend to use this now so I don't have to copy the formula down.
The versions for each function is shown at the beginning of each example under the function syntax. 2024 will be out in the second half of this year, but I’d get 365 because 2024 is already out of date and there are loads of great features coming out.
Okay, so I will be using text joint forever more. However the array functions I tend to avoid because they can’t be used in tables. Any work around for that?
Awesome to hear! No workaround for dynamic array formulas in tables other than not using tables. A lot of the DA functions aren’t something you’d use in a table layout anyway, so don’t discount them altogether.
Hi Mynda, is it possible to create a dynamic and filterable column total by using SUBTOTAL(109, ...) inside the VSTACK() function? Here an example: =VSTACK( C2#-D2#, SUBTOTAL(109, C2#-D2#)). I think this is an important feature to create dynamic tables (via formulas) but it always gives me an error message... any idea from your side? This might also be an interesting topic for a new video. Thanks in advance and greetings from Germany - I love your videos!
Well, if anything it should be SUBTOTAL(109, VSTACK(...)), but this wouldn't work because SUBTOTAL takes a reference, and VSTACK returns an array. So, the answer is no. But you could use FILTER to return the filtered array that you pass to SUM. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi ! I tried the "SORT" function you specified in this video, but didn't find this function in Excel 2019. Tell me the way, how I can use this formula in Excel 2019.❤
Thank you for these 10 formulas. My favorite is the IFS function. I write a lot of formulas using nested IF statements; this new function is going to save me a lot of time! I also love the FILTER and LET functions. I am going to use those as well.
For the SORT function, I just found out that you can enter arrays as parameters for the sort_index and sort_order. This way, you can sort on more than one column or row. Ex: =SORT(C20:G28,{1,2},{1,1},FALSE)
I like to combine filter and choosecols for pulling data from master tables. I like to have one big ole table with a ton of fields and pull from it to generate multiple other reports. Combined with Sort and I have the whole office thinking I hacked excel 😎 Thank you for the awesome content. You are my go to when new stuff pops up.
I also like the xlookup function, but the filter function is great too, especially with large datasets. The only problem i have with that is that it is harder use analytical function on that filtered data. And the LET function is a simplified version of the LAMBDA function
My favourite is definitely LET. The other new functions are great simplifications as compared to the overcomplicated formulas we had write before. But LET enables doing things that were impossible (or close to) write and above all maintain. When you have nested conditions with chunks of formulas, that include the results of other inner tests, repeated serveral timers within the formula, it rapidly gets a nightmare to write, and much worse to adapt six months later. Before LET you would simply use helper columns, no other choice. With LET you don't need that anymore and your formulas are thousands of times easier to write and before all to adapt.
Wow, lots of great information here that I'll be using but I've NEVER heard of that LET function until now and always wished there was something like it. Thank you!
Have a function that will find near matches, like maybe one or two or three characters different or even more sophisticated matches that are based on some figure of merit? Thanks
I would love if the UNIQUE function would have an 'ignore blanks' option for lists that may continue growing. I don't always want to convert data sets to tables, and for those times, it would be helpful.
@@MyOnlineTrainingHub right, but it would be a lot nicer/cleaner to just have an [ignore_empty] option. TEXTJOIN has it, and I would like to see functions that are useful for cleaning data, such as UNIQUE, include the option, rather than having to build in workarounds. I have one particular data set that simply has empty fields. There is no data for some entries. It's a data set I use to build a conversion table that feeds a sales report, and I use the UNIQUE function on this data set to build part of my conversion table as well as feed data validations. I've made it work, but it would have been faster/cleaner/nicer if I could have just had an option in the function to ignore empty cells.
Nice video, filter is so powerful for me and used in combination with unique, sort, sequence can definitely make the difference in a table!. Minda, are you based in Sydney or any other city ? Thanks.
Thanks, Mynda! For me, it's a toss-up between SEQUENCE() and LET()... SEQUENCE() tickles your creativity because it can be applied in so many scenarios. LET() used with Excel Lab (AFE) makes long and complex formulas so much more manageable. ...Speaking of Excel Lab, no mention of LAMBDA()? 😮
Thanks for sharing! LAMBDA is a bit on the advanced side for a video like this. It's a very small subset of users that will ever venture into a LAMBDA, whereas most Excel users can benefit from these other functions.
I have Excel 2019. Do you suggest an upgrade to 2021 or should I go for 365? If I go with 365, will it have all the functions available in 2021 and will it also include the upcoming 2024 functions?
I wouldn’t waste time with 2021 or 2024. Both are already out of date even though 2024 isn’t released yet. 365 is well worth getting if you want the latest features and there are plenty of cool things coming that 2024 won’t have.
Possibly a dumb question... Is it possible to combine dynamic functions such as filter and xlookup? Lets say we are using xlookup to return an array with 4 columns. With reference to the output of xlookup, filter the 3rd column with some criteria?
Well, Xlookup for sure is something i need to use more and replace old friends there. But speaking of favorites from this list.. Sequence = looks so interesting with potencial - especially when combining with dates. Im pretty sure i will soon find some good use of this function.
Thanks for another fantastic tutorial Mynda, I'm pleased to say that I knew and use all of these. My favourite is the LET formula. I'm still waiting for GROUPBY and PIVOTBY to become available to me. I'll then have a list of 12 must have formulas in my toolkit. I know you've covered these a few months back but my M365 doesn't have them yet 😭.
I never notice EOMonth function's last parameter is monthS. It is plural, as in you can input array of month. I always thought that the last parameter means increment by the number of months. May reconsider switching from columns to sequence for EOMonth
It's just summing the values in the PivotTable. The PivotTable only displays what's selected in the Slicer, so it's the PivotTable that's doing the filtering, not the SUM function. Hope I've understood your question.
I would vote for the LET function. I see it as a potential Excel "game changer", not to improve performance, because it may not always improve performance. The secondary usage that you noted is its true strength, ie to improve readability. We've all had the experience or reading some pinhead's incomprehensible formula, and struggling for a long time to work our way through it. This situation is even worse when we ourselves are the formula's Author!! The Let function allows us to break down the most important formulas in a workbook, ie when they are challenging to read, and leave them in a format that is relatively easy to comprehend, assuming that the temporary variables are named intuitively. Some of the other functions are interesting. I've been wanting a way to "copy" or reference one table from another table. It looks like VSTACK may do what I want. I'll have to give that a try. [Let me know if there's another better way.] I'm spending most of my time on improving my PQ skills, so I'm not paying much attention to these new Excel functions these days, but they are cool. Loved the video. Thanks.
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
❓Which one of these functions is your favorite?
Learn more Excel functions in my course: bit.ly/functions24course
Love them all, but LET is a game-changer. I've spent most of my day watching your videos and this has been the best use of my time in months. Thank you!
Game changer!
6.25 #4 Filter - love it
My favourite function is actually filter as it solves a lot of my issues with my accounts file. I am not a professional user of excel and my knowledge of it is only 3 years old that's why I come accross situations where it hits a wall and goes no further. as for my question, it's about one of my filters I uses to create a statement of account for my clients where I also use choosecols. the if_empty part doesn't work for me at all. here is the formula [=CHOOSECOLS(FILTER(ORDERS!$B$4:$Q$318,(ORDERS!$D$4:$D$318>=G14)*(ORDERS!$G$4:$G$318="Yes")*(ORDERS!$D$4:$D$318
My favorite is SEQUENCE function.
I use it to generate two matrix and multiple them using MMULT to solve the problem, such as running total and ...
Hi Mynda, LET me thank you for this UNIQUE tutorial. You have FILTERed Excel for the best latest formulas, and have put them in proper SEQUENCE. Your narration is smooth and professional without any TEXTSPLIT.
IFS we need any excel tutorials be assured we will XLOOKUP your channel!!! 😂
Excellent and to the point video as always.
😁👏🙏😍
#9 is my personal favorite. Thanks Mynda!
Cool 😎 have fun with it.
Back in the day, a chap called Peter Noneley released a comprehensive cheat sheet of all Excel functions, with working examples too. The secret of my success with Excel. Look how far we have come! Another great video Mynda!
Thanks so much!
I agree Peters excel dictionary was exceĺlent
LET is so powerful, it can be overwhelming. To create simple variables, I like to use Name Manager. It keeps my formula bar clean and easy to read.
Name manager is great because those names can be used in multiple formulas 👍
I agree. Name Manager is better. Especially for multi-nested formulas.
VSTACK and LET are new to me. Thanks.
Awesome! Have fun with them 😁
My top pick is LET function
I never heard about that, it's a great addition to simplify complex formulas...
Great to hear you have some ideas for using LET 👍
Just have to say, despite being an advanced user of Excel, your videos are brilliant!
I'm learning things about stuff I thought I understood that is advanced for others...
Thank you!
Thanks! Glad you're finding them useful.
Outstanding video, Mynda. Easy to follow, very helpful and thank you for producing it. Your explanations are spot-on and your narration is superb (in love w/ your accent). Great video!
Thanks so much for your kind words!
Very useful, thanks Mynda.
Great to hear 🙏😊
I just wish Microsoft could roll out these much quicker. After all these months I still do not even have the tick box option. I appreciate all your efforts to show all the new features, but after a while I tend to skip the videos as I know we will only see them in 12 months time. It's like Mercedes Benz advertises a new car, but then it will only be available in a years time. By then i have already bought another car.....
I know what you mean, Chris. It's frustrating, but the check boxes have undergone a rework based on feedback received in beta testing, so I guess it's worthwhile.
Favourites: IFS - have been caught by stacked close brackets SO many times, FILTER, SORT and UNIQUE - awesome enablers, and SEQUENCE - hadn't thought of using it as the index column that I've always had to create by formula before, generally with OFFSET to avoid REF errors with row insert/delete. Thanks for the example with tables, I'll seek out more guidance about where using them makes more sense than not.
Great to hear you found some new ideas you can use. Thanks for watching.
tremenda explicación, muy util.👍👍👍👍👍👍👍👍👍👍👍👍
Thanks so much 🙏🥰
Haven't used vstack and Let that much yet, but just practiced them a bit more now...will use more. Thank you, excellent video 🙏🏽
Great to hear!
I keep forgetting about the Filter function. Very useful and I like the Idea of findining distinct words by using UNIQUE. That kind of hint, is extremely useful. Using Functions more out of the box. Thank you
Great to hear! 🙏😊
Wow, great video Mynda. I know all formulas from earlier video's you've shared but I tend to use the old ways and work around them. Much more time consuming, I know. My challenge next month wil be to use at least 5 of these. My personal fav is [LET]!
Thanks so much! Glad to hear you're going to try putting them to use 😊
The best parts are the examples where these functions would be useful. Thank you!
I had a workbook with 15 or more nested IF statements that I changed to IFS which helps readability. The piece about adding TRUE at the end is a great tip. I also threw in some LET statements in other formulas to remove repeated table references. Unfortunately, these changes ballooned the size of the file from 10MB to 50MB! Is there a tradeoff in file size when using any of these functions?
Glad you found the examples helpful. If you're nesting 15 IFs, I'm thinking maybe you should be using a lookup formula.
As for LET ballooning your file size, I'd be interested to see the file for this, because it should result in the opposite effect. You can reach out via email: website at MyOnlineTrainingHub.com
Love a LET! ❤I particularly like that there is no need to nest LETs - you can use name1 in the definition of name2, and so on. I think it really helps the reader understand a complex formula.
Great to hear!
Thanks for the video Mynda, Good to see I have mastered all 10. I might be tempted to add MAP to that as I tend to use this now so I don't have to copy the formula down.
Nice suggestion. I'm surprised you don't use BYROW for that. See this video: ruclips.net/video/8-ANSpXcHr0/видео.html
Thank you. VSTAK and LET are completely new to me
Great to hear! They're game-changers.
Thank you for demonstrating how to use SEQUENCE to generate dates. Will be very useful for generating charts using a seed date.
Glad it was helpful!
Wonderful presentation and clearly communicated. So glad that I came upon your channel. Thank you for sharing your expertise🙂
🙏Glad it was helpful! 😊
Thanks so much for this video! Are all of these available in Excel 2021? And do you know when Excel 2024 will be available? Thanks again
The versions for each function is shown at the beginning of each example under the function syntax. 2024 will be out in the second half of this year, but I’d get 365 because 2024 is already out of date and there are loads of great features coming out.
Combination of VSTACK and FILTER to get the headers is a great idea! Thank you
Glad it was helpful!
#4 is my favorite function. Thank you for sharing this video, Mynda!
Great to hear 🙏
Okay, so I will be using text joint forever more. However the array functions I tend to avoid because they can’t be used in tables. Any work around for that?
Awesome to hear! No workaround for dynamic array formulas in tables other than not using tables. A lot of the DA functions aren’t something you’d use in a table layout anyway, so don’t discount them altogether.
Great video as always. Thank you, your videos are my go to for building Excel skills.
Great to hear! 🙏
You made my life easy!!! Thank you!
Happy to help!
This is the Video I never thought I needed. Thanks so much Mynda!
😁 glad you liked it!
Hi Mynda,
is it possible to create a dynamic and filterable column total by using SUBTOTAL(109, ...) inside the VSTACK() function?
Here an example: =VSTACK( C2#-D2#, SUBTOTAL(109, C2#-D2#)).
I think this is an important feature to create dynamic tables (via formulas) but it always gives me an error message... any idea from your side?
This might also be an interesting topic for a new video.
Thanks in advance and greetings from Germany - I love your videos!
Well, if anything it should be SUBTOTAL(109, VSTACK(...)), but this wouldn't work because SUBTOTAL takes a reference, and VSTACK returns an array. So, the answer is no.
But you could use FILTER to return the filtered array that you pass to SUM.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Mam, you are doing well. I learned a lot from you thanks
Wonderful to hear 🙏😊
Brilliant video. Fav list - 1, 4, 6
Love your top 3 👍
LET is just wonderful!
Indeed, it is 😁
#6 is my favorite, but the Filter function is a close 2nd. Great video by the way!! You are the best!
Great to hear there are some new ones for you to try out 😎
LET -I wasn't aware you could declare a formula... this will definitely make some of my formulas easier to read and understand. Thanks.
Glad it was helpful!
Hi ! I tried the "SORT" function you specified in this video, but didn't find this function in Excel 2019. Tell me the way, how I can use this formula in Excel 2019.❤
That formula is not Excel 2019. You'd need to upgrade to a newer version of Excel to get it, sorry.
Hi Mynda, I thank you for your well rounded presentation. I like the VSTACK and everything else. Thanks once again.
Glad you liked it! 🙏😊
Dear madam
It was great excel session,thank you very much. God bless you .
It's my pleasure 😇
you are simply excel guru....Thanks
Thanks for watching 🙏😊
I like LET and IFS equally. Thank you.
Great choices 😁
Thank you for these 10 formulas. My favorite is the IFS function. I write a lot of formulas using nested IF statements; this new function is going to save me a lot of time! I also love the FILTER and LET functions. I am going to use those as well.
Great to hear you can make use of those functions 👍
For the SORT function, I just found out that you can enter arrays as parameters for the sort_index and sort_order. This way, you can sort on more than one column or row. Ex: =SORT(C20:G28,{1,2},{1,1},FALSE)
Yes! And you can arrange them in a different order to the columns e.g. =SORT(C20:G28,{2,1},{1,1},FALSE)
Thanks, all formulas are explained in easy way, but I like the unique formula.
Glad it was helpful! I like UNIQUE too 😁
Amazing video...Many Thanks
Glad you liked it 🙏😊
I like to combine filter and choosecols for pulling data from master tables. I like to have one big ole table with a ton of fields and pull from it to generate multiple other reports. Combined with Sort and I have the whole office thinking I hacked excel 😎 Thank you for the awesome content. You are my go to when new stuff pops up.
That’s wonderful to hear 🙏 the new array shaping functions like CHOOSECOLS are game changers. 🤩
Hi Mynda, which function could I use to autofill a cell with a predetermined number if I enter a specific letter in another cell ? Thanks
You could use IF for this: ruclips.net/video/0zTeXujtOZI/видео.html
I also like the xlookup function, but the filter function is great too, especially with large datasets. The only problem i have with that is that it is harder use analytical function on that filtered data. And the LET function is a simplified version of the LAMBDA function
Great to hear you're embracing the new functions. 😊
My favourite is definitely LET. The other new functions are great simplifications as compared to the overcomplicated formulas we had write before. But LET enables doing things that were impossible (or close to) write and above all maintain. When you have nested conditions with chunks of formulas, that include the results of other inner tests, repeated serveral timers within the formula, it rapidly gets a nightmare to write, and much worse to adapt six months later. Before LET you would simply use helper columns, no other choice. With LET you don't need that anymore and your formulas are thousands of times easier to write and before all to adapt.
I agree, LET is a game changer. Glad you can make use of it.
Before you mentioned the filter function, I thought of it. It is a very useful function. Thank you Mynda.
Glad we agree 😁
#10 LET function is my favorite : "efficiency and clarity".
Great to hear 👍
Your explanation is so easy to follow. Thank you.
Glad you think so! 🙏
Very boring lecture
Love these tips, really helpful. One slight issue(which may just be me) the links to other videos (where you point up to your left) don’t all appear!
Thanks so much! I'll check the missing links. You can also find them in the video description.
Wow, lots of great information here that I'll be using but I've NEVER heard of that LET function until now and always wished there was something like it. Thank you!
Awesome to hear!
Another great video 🎉
Thank you 🙏
Glad you enjoyed it 🙏😊
I learned LET, can't wait to try it out.
SAME.
Great to hear!
Have a function that will find near matches, like maybe one or two or three characters different or even more sophisticated matches that are based on some figure of merit? Thanks
You could use fuzzy matching in Power Query: www.myonlinetraininghub.com/fuzzy-matching-in-power-query
"Let" is definitely up there. reducing calculations in a formula.
Sure is 😊
The functions that cought my eye are the unique and sort functions. Thankyou for the exercis and cheat sheet.
Great to hear you found some functions that will be of use to you 😊
Pro Tip: Never be the go-to person in the office 😂😂😂
😁
what? How did I not know this? There's an IFS? All of these are great for modeling! Thank you!
Glad you found something useful from my video 😁 thanks for watching.
Hi I wanted to ask you if you got videos on how to create a dashboard using the online version of Excel
I don't, sorry.
Excel goddess….Some gems there….awesome as always Mynda…thank you.
Thanks so much 🙏😊
You truly are a god-send, clear and simple instructions. Thank you so much!!
Glad it was helpful 🙏
Thanks!
Welcome!
You are awesome! Many thanks for the tutorial.
Glad it was helpful!
I would love if the UNIQUE function would have an 'ignore blanks' option for lists that may continue growing. I don't always want to convert data sets to tables, and for those times, it would be helpful.
You can handle blanks with FILTER e.g. =FILTER(UNIQUE(...), UNIQUE(...)"")
@@MyOnlineTrainingHub right, but it would be a lot nicer/cleaner to just have an [ignore_empty] option. TEXTJOIN has it, and I would like to see functions that are useful for cleaning data, such as UNIQUE, include the option, rather than having to build in workarounds.
I have one particular data set that simply has empty fields. There is no data for some entries. It's a data set I use to build a conversion table that feeds a sales report, and I use the UNIQUE function on this data set to build part of my conversion table as well as feed data validations. I've made it work, but it would have been faster/cleaner/nicer if I could have just had an option in the function to ignore empty cells.
Yes, good point. 👍
So helpful, and I love how you combined functions such as filter, vstack, unique and sort. I’m going to work on my filter skills.
Great to hear! 🙏😊
Wow! Just finding you now and I’m a new subscriber! I like UNIQUE and SEQUENCE, but LET is probably my favorite. Thank you!
Welcome aboard! I like LET too. Super easy and deceptively useful.
Thanks for the great video! Let is new to me. I am excited to make use of it!
Great to hear 😊
Nice video, filter is so powerful for me and used in combination with unique, sort, sequence can definitely make the difference in a table!. Minda, are you based in Sydney or any other city ? Thanks.
Glad you liked it! I'm north of Brisbane 😉
Great! ❤
Thanks! 😄
...LET me say thank you one more time! 💚
😂Glad you liked it!
thanks for sharing! Loved the TextSplit - it simplifies several Mid, Left, Right sequences!
Glad it was helpful!
LET & LAMBDA are my two most power function in excel
Thanks for sharing 😊
Nice video, thanks for sharing!
Thank you!
Thanks, Mynda! For me, it's a toss-up between SEQUENCE() and LET()...
SEQUENCE() tickles your creativity because it can be applied in so many scenarios.
LET() used with Excel Lab (AFE) makes long and complex formulas so much more manageable.
...Speaking of Excel Lab, no mention of LAMBDA()? 😮
Thanks for sharing! LAMBDA is a bit on the advanced side for a video like this. It's a very small subset of users that will ever venture into a LAMBDA, whereas most Excel users can benefit from these other functions.
Agreed! (...then again, Excel Lab's AFE makes It so easy to create LAMBDA() functions...)
I have Excel 2019. Do you suggest an upgrade to 2021 or should I go for 365?
If I go with 365, will it have all the functions available in 2021 and will it also include the upcoming 2024 functions?
I wouldn’t waste time with 2021 or 2024. Both are already out of date even though 2024 isn’t released yet. 365 is well worth getting if you want the latest features and there are plenty of cool things coming that 2024 won’t have.
Hello ma'am I have founded your channel by Chat GPT. You are amazing ma'am.😊❤
Thank you so much 😊
Definitely useful information that everyone should know
Great to hear 🙏😊
I agree with you on FILTER being my favorite. But wait, GROUPBY and PIVOTBY are noticeably missing from your list! (insert smiley face here).
Good point but PIVOTBY and GROUPBY are not generally available yet 😉
Not available for all 😅😊
@@MyOnlineTrainingHub when, oh when?
Possibly a dumb question...
Is it possible to combine dynamic functions such as filter and xlookup?
Lets say we are using xlookup to return an array with 4 columns. With reference to the output of xlookup, filter the 3rd column with some criteria?
Yep 👍
Well, Xlookup for sure is something i need to use more and replace old friends there. But speaking of favorites from this list.. Sequence = looks so interesting with potencial - especially when combining with dates. Im pretty sure i will soon find some good use of this function.
Great to hear! XLOOKUP is the way to go for sure. SEQUENCE is surprisingly useful too. Glad you have some ideas on how you can use these functions.
Excellent, thank you!
Glad you enjoyed it!
Thank you. I love your VDOs.🌹
Thank you!
Thanks for another fantastic tutorial Mynda, I'm pleased to say that I knew and use all of these. My favourite is the LET formula. I'm still waiting for GROUPBY and PIVOTBY to become available to me. I'll then have a list of 12 must have formulas in my toolkit. I know you've covered these a few months back but my M365 doesn't have them yet 😭.
Great to hear 👏
EOM & Sequence - where have you been the last 20+ years???
Off beam question - IFS, do you know if this would be available within Filemaker?
Not sure, sorry. I’m not familiar with Filemaker.
I never notice EOMonth function's last parameter is monthS. It is plural, as in you can input array of month. I always thought that the last parameter means increment by the number of months.
May reconsider switching from columns to sequence for EOMonth
Glad you can make use of this trick 😊
Mynda, is vstack carrying the formatting from each table? That seems new...
No, I pre-formatted the cells 😉
Awesome. Picked up the EOMONTH + SEQUENCE combo.
Glad you'll find it useful.
5:22 at this point the sum function worked like subtotal can you please explain a bit!!
It's just summing the values in the PivotTable. The PivotTable only displays what's selected in the Slicer, so it's the PivotTable that's doing the filtering, not the SUM function. Hope I've understood your question.
I would vote for the LET function. I see it as a potential Excel "game changer", not to improve performance, because it may not always improve performance. The secondary usage that you noted is its true strength, ie to improve readability.
We've all had the experience or reading some pinhead's incomprehensible formula, and struggling for a long time to work our way through it. This situation is even worse when we ourselves are the formula's Author!! The Let function allows us to break down the most important formulas in a workbook, ie when they are challenging to read, and leave them in a format that is relatively easy to comprehend, assuming that the temporary variables are named intuitively.
Some of the other functions are interesting. I've been wanting a way to "copy" or reference one table from another table. It looks like VSTACK may do what I want. I'll have to give that a try. [Let me know if there's another better way.]
I'm spending most of my time on improving my PQ skills, so I'm not paying much attention to these new Excel functions these days, but they are cool.
Loved the video. Thanks.
Yes, try VSTACK or FILTER for extracting data from a table, or Power Query of course 😉
Thanks for the video. Can I ask for help?
Sure. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Perfect, thanks for the video.
Glad you liked it! 🙏😊
you are amazing! Thanks
Thank you too!
Ifs and let function is really good alternative to the dreadful nestedifs!
Imagine if we can store more variables in excel formula, similar to DAX!
Yep 👍
I'm not at a PC to check for myself, but how does the IFS function differ to the SWITCH function?
SWITCH is very similar but maybe not as intuitive for the average Excel user to write.
Are these only for the online version of Excel?
No, they're also available in Excel for Microsoft 365 users and some are also available in Excel 2021.
This is superb :)
Thank you!
01:23 Unique. 2:33 Sort 4:10 Textjoin 06:27 Filter 10:33 Sequence
6:29 filter 7:41 Vstack. 9:22 Xlookup 12:37 Textsplit 13:32 Ifs. 17:04 Let
Learning from you has set me
Apart 😁
Awesome to hear 🥰
@@MyOnlineTrainingHub it has, I have implemented so many things you have taught here to my work 😎