10 Excel Formulas That Will Set You Apart (Cheat Sheet)
ฝัง
- เผยแพร่เมื่อ 8 เม.ย. 2024
- Top 10 new essential Excel formulas & functions in 2024.
👩🏫 Learn more Excel functions with my course: bit.ly/functions24course
⬇️ Download the example file here and follow along: bit.ly/functions24file
These days, knowing basic Excel won't set you apart. You have to keep up with the new features that enable you to improve productivity, or you’ll be left behind. In this video I’m going to share 10 must know modern Excel functions that simplify common tasks. These functions will not only make your life easier, they’ll also make you the go-to expert in your office.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
#Excel #ExcelFormulas #ExcelFunctions - วิทยาศาสตร์และเทคโนโลยี
![ใจดำกับฉันก่อน - วงทัพห้า [Official MV]](http://i.ytimg.com/vi/I48wa4qurAU/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!
#9 is my personal favorite. Thanks Mynda!
Cool 😎 have fun with it.
Excel goddess….Some gems there….awesome as always Mynda…thank you.
Thanks so much 🙏😊
Teacher . Thank you very 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!
Thanks so much for your kind words!
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!
Before you mentioned the filter function, I thought of it. It is a very useful function. Thank you Mynda.
Glad we agree 😁
You truly are a god-send, clear and simple instructions. Thank you so much!!
Glad it was helpful 🙏
Love this.
Perfect, thanks for the video.
Glad you liked it! 🙏😊
you are simply excel guru....Thanks
Thanks for watching 🙏😊
#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 😎
thanks for sharing! Loved the TextSplit - it simplifies several Mid, Left, Right sequences!
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.
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
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 🙏🏽
Great to hear!
Thank you Mynda. Awesome videos
Glad you like them! 🙏
Very useful, thanks Mynda.
Great to hear 🙏😊
Another great video 🎉
Thank you 🙏
Glad you enjoyed it 🙏😊
VSTACK and LET are new to me. Thanks.
Awesome! Have fun with them 😁
Mam, you are doing well. I learned a lot from you thanks
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]!
Thanks so much! Glad to hear you're going to try putting them to use 😊
Awesome. Picked up the EOMONTH + SEQUENCE combo.
Glad you'll find it useful.
Thank you Teacher
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
Great to hear! 🙏😊
I didn't know about 'sequence'. That's definitely one I'll be using! Thanks!🤩
Great to hear!
Dear madam
It was great excel session,thank you very much. God bless you .
It's my pleasure 😇
Your explanation is so easy to follow. Thank you.
Glad you think so! 🙏
Very boring lecture
Amazing video...Many Thanks
Glad you liked it 🙏😊
tremenda explicación, muy util.👍👍👍👍👍👍👍👍👍👍👍👍
Thanks so much 🙏🥰
Brilliant video. Fav list - 1, 4, 6
Love your top 3 👍
Definitely useful information that everyone should know
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: th-cam.com/video/8-ANSpXcHr0/w-d-xo.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.
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.
Great to hear!
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 👍
I learned LET, can't wait to try it out.
SAME.
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.
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
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!
Thanks so much! I'll check the missing links. You can also find them in the video description.
So good you are
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?
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.....
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!
Glad you found something useful from my video 😁 thanks for watching.
Great 😊
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.
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.
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
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 😭.
Great to hear 👏
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 😎
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.
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 🙂
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
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
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
Great to hear you found IFS interesting. 👍😊
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
You could use IF for this: th-cam.com/video/0zTeXujtOZI/w-d-xo.html
Hello ma'am I have founded your channel by Chat GPT. You are amazing ma'am.😊❤
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!
Yep 👍
In love right now
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.
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.
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. 👍
"Let" is definitely up there. reducing calculations in a formula.
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?
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
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)
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?
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).
Good point but PIVOTBY and GROUPBY are not generally available yet 😉
Not available for all 😅😊
@@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()? 😮
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...)
Mem can't say a word. I'm so far
I have seen as many videos as you on TH-cam
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.
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?
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
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
Glad you can make use of this trick 😊
My favorite function îs LET
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...
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.
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
You could try adding a column with this formula: =IF(ISNUMBER(A2),A2,"") assuming column A contains the text and number.
thank you very much!
I've used IFS before but the complexity was often overpowering. now LET is going to keep me sane
Great to hear! '😁
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.
This is amazing but not for the beginners in Excel, too fast to understand everything but cool to watch.
Thank you! I just released this video for Excel beginners: th-cam.com/video/fzdn1vcHPsA/w-d-xo.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.
Great idea. It forces you to be organised.
I like let but still need Lot of practice
Great to hear.
Re TEXTSPLIT, any solution for the name without middle name?
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.
Keep practicing. Before you know it, it will be second nature and you'll ace those tests.
Filter is my favorite, too
😎 thanks for watching.
❤❤
Thanks for watching 🙏😊
@@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!!
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?
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"
)
)
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.
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!🤭
😁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.
Oops. Thanks for the correction 🙏
We need two layers slicer like ( department and sub department)
That would be nice. Currently, you have to use two separate slicers. I can't see that changing any time soon, unfortunately.
👍
🙏
migerta
For your favorite #3 TEXTJOIN: the second argument is TRUE by default so you can just skip it. 😉
TRUE 😁
@@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.
You can do this with a LAMBDA. See here: th-cam.com/video/XAGdSKMV4wA/w-d-xo.html
Let there be Let and Microsoft made it so.
😁
5+ year old stuff
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))
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
Great to hear 😊