❓Which one of these functions is your favorite? Learn more Excel functions in my course: bit.ly/functions24course

@melissaallisonwood

8 күн бұрын

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!

@NerdsRunTheWorld

5 күн бұрын

Game changer!

#9 is my personal favorite. Thanks Mynda!

@MyOnlineTrainingHub

Ай бұрын

Cool 😎 have fun with it.

Excel goddess….Some gems there….awesome as always Mynda…thank you.

@MyOnlineTrainingHub

Ай бұрын

Thanks so much 🙏😊

Teacher . Thank you very much.

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!

@MyOnlineTrainingHub

24 күн бұрын

Thanks so much!

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!

@MyOnlineTrainingHub

24 күн бұрын

Thanks so much for your kind words!

Before you mentioned the filter function, I thought of it. It is a very useful function. Thank you Mynda.

@MyOnlineTrainingHub

Ай бұрын

Glad we agree 😁

You truly are a god-send, clear and simple instructions. Thank you so much!!

@MyOnlineTrainingHub

10 күн бұрын

Glad it was helpful 🙏

Love this.

Perfect, thanks for the video.

@MyOnlineTrainingHub

Ай бұрын

Glad you liked it! 🙏😊

you are simply excel guru....Thanks

@MyOnlineTrainingHub

Ай бұрын

Thanks for watching 🙏😊

#6 is my favorite, but the Filter function is a close 2nd. Great video by the way!! You are the best!

@MyOnlineTrainingHub

Ай бұрын

Great to hear there are some new ones for you to try out 😎

thanks for sharing! Loved the TextSplit - it simplifies several Mid, Left, Right sequences!

@MyOnlineTrainingHub

8 күн бұрын

Glad it was helpful!

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.

@MyOnlineTrainingHub

20 күн бұрын

Great to hear you can make use of those functions 👍

Combination of VSTACK and FILTER to get the headers is a great idea! Thank you

@MyOnlineTrainingHub

16 күн бұрын

Glad it was helpful!

Haven't used vstack and Let that much yet, but just practiced them a bit more now...will use more. Thank you, excellent video 🙏🏽

@MyOnlineTrainingHub

Ай бұрын

Great to hear!

Thank you Mynda. Awesome videos

@MyOnlineTrainingHub

Ай бұрын

Glad you like them! 🙏

Very useful, thanks Mynda.

@MyOnlineTrainingHub

Ай бұрын

Great to hear 🙏😊

Another great video 🎉 Thank you 🙏

@MyOnlineTrainingHub

Ай бұрын

Glad you enjoyed it 🙏😊

VSTACK and LET are new to me. Thanks.

@MyOnlineTrainingHub

Ай бұрын

Awesome! Have fun with them 😁

Mam, you are doing well. I learned a lot from you thanks

@MyOnlineTrainingHub

Ай бұрын

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

@MyOnlineTrainingHub

Ай бұрын

Thanks so much! Glad to hear you're going to try putting them to use 😊

Awesome. Picked up the EOMONTH + SEQUENCE combo.

@MyOnlineTrainingHub

19 күн бұрын

Glad you'll find it useful.

Thank you Teacher

@MyOnlineTrainingHub

25 күн бұрын

Welcome!!

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

@MyOnlineTrainingHub

Ай бұрын

Great to hear! 🙏😊

I didn't know about 'sequence'. That's definitely one I'll be using! Thanks!🤩

@MyOnlineTrainingHub

16 күн бұрын

Great to hear!

Dear madam It was great excel session,thank you very much. God bless you .

@MyOnlineTrainingHub

4 күн бұрын

It's my pleasure 😇

Your explanation is so easy to follow. Thank you.

@MyOnlineTrainingHub

9 күн бұрын

Glad you think so! 🙏

@Panu-qi7tm

7 күн бұрын

Very boring lecture

Amazing video...Many Thanks

@MyOnlineTrainingHub

Ай бұрын

Glad you liked it 🙏😊

tremenda explicación, muy util.👍👍👍👍👍👍👍👍👍👍👍👍

@MyOnlineTrainingHub

Ай бұрын

Thanks so much 🙏🥰

Brilliant video. Fav list - 1, 4, 6

@MyOnlineTrainingHub

Ай бұрын

Love your top 3 👍

Definitely useful information that everyone should know

@MyOnlineTrainingHub

Ай бұрын

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.

@MyOnlineTrainingHub

Ай бұрын

Nice suggestion. I'm surprised you don't use BYROW for that. See this video: kzread.info/dash/bejne/amF1sLWpiMauopM.html

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.

@MyOnlineTrainingHub

Ай бұрын

Glad you liked it! I'm north of Brisbane 😉

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.

@MyOnlineTrainingHub

22 күн бұрын

Great to hear!

My top pick is LET function I never heard about that, it's a great addition to simplify complex formulas...

@MyOnlineTrainingHub

Ай бұрын

Great to hear you have some ideas for using LET 👍

I learned LET, can't wait to try it out.

@ktmansfield1016

19 күн бұрын

SAME.

@MyOnlineTrainingHub

19 күн бұрын

Great to hear!

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.

@MyOnlineTrainingHub

20 күн бұрын

Great to hear you found some new ideas you can use. Thanks for watching.

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

@MyOnlineTrainingHub

Ай бұрын

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.

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!

@MyOnlineTrainingHub

7 күн бұрын

Thanks so much! I'll check the missing links. You can also find them in the video description.

So good you are

@MyOnlineTrainingHub

19 күн бұрын

Thanks for your kind words!

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?

@MyOnlineTrainingHub

Ай бұрын

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

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

@MyOnlineTrainingHub

22 күн бұрын

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.

what? How did I not know this? There's an IFS? All of these are great for modeling! Thank you!

@MyOnlineTrainingHub

24 күн бұрын

Glad you found something useful from my video 😁 thanks for watching.

Great 😊

@MyOnlineTrainingHub

Ай бұрын

Thanks 😊

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.

@MyOnlineTrainingHub

28 күн бұрын

That’s wonderful to hear 🙏 the new array shaping functions like CHOOSECOLS are game changers. 🤩

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.

@MyOnlineTrainingHub

Ай бұрын

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

@MyOnlineTrainingHub

Ай бұрын

Thanks so much 🙏😊

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

@MyOnlineTrainingHub

Ай бұрын

Great to hear 👏

Learning from you has set me Apart 😁

@MyOnlineTrainingHub

Ай бұрын

Awesome to hear 🥰

@IamTheReaper911

Ай бұрын

@@MyOnlineTrainingHub it has, I have implemented so many things you have taught here to my work 😎

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.

@MyOnlineTrainingHub

Ай бұрын

Name manager is great because those names can be used in multiple formulas 👍

@SFStacker

Ай бұрын

I agree. Name Manager is better. Especially for multi-nested formulas.

I love your videos but I've only got IFS and TEXTJOIN in my 2019 version of Excel. I'm waiting for MS to release their new stand alone Excel with these new functions (LET looks like a life saver) since I just plain refuse to use software subscriptions. Until then, I use lots of helper columns 🙂

@MyOnlineTrainingHub

28 күн бұрын

The preview of 2024 is now available: techcommunity.microsoft.com/t5/microsoft-365-blog/commercial-preview-of-microsoft-office-ltsc-2024-is-now/ba-p/4102538

LET & LAMBDA are my two most power function in excel

@MyOnlineTrainingHub

Ай бұрын

Thanks for sharing 😊

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

@MyOnlineTrainingHub

Ай бұрын

Great to hear you're embracing the new functions. 😊

I am still an offset fanboy... My fav function.. Haven't tried IFS but it looks usefull

@MyOnlineTrainingHub

Ай бұрын

Great to hear you found IFS interesting. 👍😊

@matttownsend7119

20 күн бұрын

I was also a great fan of OFFSET for making formulae resistant to the relocation of source cells. But, OFFSET is a volatile function which means is is recalcualted any time any cell has a changed value, which means that if you use it in large data sets you can be waiting minutes for a recalc.

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

@MyOnlineTrainingHub

13 күн бұрын

You could use IF for this: kzread.info/dash/bejne/Yq6Ix7qumte1iqw.html

Hello ma'am I have founded your channel by Chat GPT. You are amazing ma'am.😊❤

@MyOnlineTrainingHub

6 күн бұрын

Thank you so much 😊

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!

@MyOnlineTrainingHub

Ай бұрын

Yep 👍

In love right now

@MyOnlineTrainingHub

Ай бұрын

Glad you enjoyed it 🙏

Ms. Treacy , this is one of my go to Excel channels and check it regularly for new posts. Your videos are consistently clear, succinct, and I love that they're almost always accompanied by a sample workbook. Your charting examples always amaze me - you're so good at it! That said, just wanted to mention that the button links in this sample are all broken. Easy to fix, and an unsurprising oversight. Hope that helps.

@MyOnlineTrainingHub

Ай бұрын

Thanks for your support! Oops! I forgot to fix the hyperlinks after changing the sheet names 🤦‍♀️ all fixed now. Thankfully you could still navigate using the sheet tabs.

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

7 күн бұрын

You can handle blanks with FILTER e.g. =FILTER(UNIQUE(...), UNIQUE(...)"")

@sweater0115

7 күн бұрын

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

@MyOnlineTrainingHub

7 күн бұрын

Yes, good point. 👍

"Let" is definitely up there. reducing calculations in a formula.

@MyOnlineTrainingHub

Ай бұрын

Sure is 😊

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?

@MyOnlineTrainingHub

Ай бұрын

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 I wanted to ask you if you got videos on how to create a dashboard using the online version of Excel

@MyOnlineTrainingHub

Ай бұрын

I don't, sorry.

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)

@MyOnlineTrainingHub

Ай бұрын

Yes! And you can arrange them in a different order to the columns e.g. =SORT(C20:G28,{2,1},{1,1},FALSE)

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?

@MyOnlineTrainingHub

Ай бұрын

Yep 👍

I agree with you on FILTER being my favorite. But wait, GROUPBY and PIVOTBY are noticeably missing from your list! (insert smiley face here).

@MyOnlineTrainingHub

Ай бұрын

Good point but PIVOTBY and GROUPBY are not generally available yet 😉

@Samu-gz3qj

Ай бұрын

Not available for all 😅😊

@dougmphilly

27 күн бұрын

@@MyOnlineTrainingHub when, oh when?

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()? 😮

@MyOnlineTrainingHub

Ай бұрын

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.

@HachiAdachi

Ай бұрын

Agreed! (...then again, Excel Lab's AFE makes It so easy to create LAMBDA() functions...)

Mem can't say a word. I'm so far I have seen as many videos as you on KZread so beautiful I have not seen the video before. But mem I am not a good English speaker. But mam,, after watching your video, I am downloading the files and packaging them. I am downloading and packaging, now I want the mind A lot to learn.

@MyOnlineTrainingHub

2 күн бұрын

All the best on your Excel learning journey. Glad I can help you along the way 😊

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?

@MyOnlineTrainingHub

Ай бұрын

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.

nice

@MyOnlineTrainingHub

Ай бұрын

Thank you 😊

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

@MyOnlineTrainingHub

Ай бұрын

Glad you can make use of this trick 😊

My favorite function îs LET

@MyOnlineTrainingHub

Ай бұрын

Great to hear!

Putting formula in one cell affects values in different cells (aka spills). Not sure if I like that idea. That is why we use macros to have better control of what is happening...

@MyOnlineTrainingHub

9 күн бұрын

Dynamic arrays certainly require forward planning, but if structured correctly they are great and don't come with the VBA security issues.

Textjoin eh? I like it.

@MyOnlineTrainingHub

Ай бұрын

Great to hear!

sorry could you help me this. I have a data that include type: text and type: number in the same coulumn but in different row, i want to split the number into another coulumn but its doesn't change the order. I tried but i could not find the way to done that. Thank you so much

@MyOnlineTrainingHub

20 күн бұрын

You could try adding a column with this formula: =IF(ISNUMBER(A2),A2,"") assuming column A contains the text and number.

@nguyenthong2409

18 күн бұрын

thank you very much!

I've used IFS before but the complexity was often overpowering. now LET is going to keep me sane

@MyOnlineTrainingHub

Ай бұрын

Great to hear! '😁

I'm not at a PC to check for myself, but how does the IFS function differ to the SWITCH function?

@MyOnlineTrainingHub

Ай бұрын

SWITCH is very similar but maybe not as intuitive for the average Excel user to write.

This is amazing but not for the beginners in Excel, too fast to understand everything but cool to watch.

@MyOnlineTrainingHub

9 күн бұрын

Thank you! I just released this video for Excel beginners: kzread.info/dash/bejne/mK6Y0JOvk6u2o6Q.html Hope you find it useful.

Even when I have not fully figured out what I want to do, I'll still start with =LET You cannot imagine what this function along with others can do.

@MyOnlineTrainingHub

Ай бұрын

Great idea. It forces you to be organised.

I like let but still need Lot of practice

@MyOnlineTrainingHub

25 күн бұрын

Great to hear.

Re TEXTSPLIT, any solution for the name without middle name?

@MyOnlineTrainingHub

Ай бұрын

I guess you could factor in an IF to check how many spaces in the name and then allow for only 1.

I had an Excel job interview that gave me formulas and a spreadsheet and asked me a multiple choice question about what would happen. The pop-up guides for functions have ruined me. I bombed the test.

@MyOnlineTrainingHub

22 күн бұрын

Keep practicing. Before you know it, it will be second nature and you'll ace those tests.

Filter is my favorite, too

@MyOnlineTrainingHub

Ай бұрын

😎 thanks for watching.

❤❤

@MyOnlineTrainingHub

Ай бұрын

Thanks for watching 🙏😊

@SothearithKONGMrMuyKhmer

Ай бұрын

@@MyOnlineTrainingHub The filter function is one my favorites, too. 🥰

5:22 at this point the sum function worked like subtotal can you please explain a bit!!

@MyOnlineTrainingHub

28 күн бұрын

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 am having an issue using more than one named variable with LET. I entered the following but the formula is returning a "Name?" error that appears to be tied to the "period" variable. :=LET(age,"TODAY()-[@[Loan Date]]", period,90, IFS(age>period, "Overdue",age=period, "Due", TRUE,"Not Due")) Anyone know what I am missing?

@benwoline

Ай бұрын

You don't need quotes around the first formula. It should read: =LET(age, TODAY() - [@[Loan Date]], period, 90, IFS(age > period, "Overdue", age = period, "Due", TRUE, "Not Due" ) )

@MyOnlineTrainingHub

Ай бұрын

Your variable calculation shouldn’t be in double quotes. Only text goes in double quotes in Excel and the variable is a calculation. It can’t calculate when it’s wrapped in double quotes.

@benwoline

Ай бұрын

In complex formulas, I also like a generous use of ALT+ENTER and spaces to align the code. With that I can quickly tell that this example LET function has two defined formulas and the IFS function has three conditions.

13:26 I always forget about IFS!🤭

@MyOnlineTrainingHub

Ай бұрын

😁some habits die hard. Glad I could remind you of it.

7:43 There is written false information below formula. VSTACK is not available on Excel 2021 tho. 12:36 Same. Not available on Excel 2021.

@MyOnlineTrainingHub

28 күн бұрын

Oops. Thanks for the correction 🙏

We need two layers slicer like ( department and sub department)

@MyOnlineTrainingHub

Ай бұрын

That would be nice. Currently, you have to use two separate slicers. I can't see that changing any time soon, unfortunately.

👍

@MyOnlineTrainingHub

28 күн бұрын

🙏

migerta

For your favorite #3 TEXTJOIN: the second argument is TRUE by default so you can just skip it. 😉

@MyOnlineTrainingHub

Ай бұрын

TRUE 😁

@rusektor

Ай бұрын

@@MyOnlineTrainingHub By the way, I like this function, too. I often have to concatenate cells in order to generate casual identifiers. 🙂

I don't get why they don't add a for loop formula. That'd be a game changer.

@MyOnlineTrainingHub

Ай бұрын

You can do this with a LAMBDA. See here: kzread.info/dash/bejne/inV7xrWEfbmap6Q.html

Let there be Let and Microsoft made it so.

@MyOnlineTrainingHub

28 күн бұрын

😁

5+ year old stuff

@MyOnlineTrainingHub

22 күн бұрын

You'd be surprised how many people this is new to.

Please define this formula for me it is working perfectly but i wanna know how its working Its calculating like If i sold 348 packs then incentive will be like 100×15+100×25+100×35+48×50 =SUMPRODUCT(LOOKUP(ROW(INDIRECT("1:"&I27)),$H$33:$H$36,$I$33:$I$36))

@MyOnlineTrainingHub

Ай бұрын

Sure, please post your question and example Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum My guess is there's also a much more efficient way to write this formula.

LET & LAMBDA are my two most power function in excel

@MyOnlineTrainingHub

Ай бұрын

Great to hear 😊