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 - Наука та технологія
❓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!
#4 is my favorite function. Thank you for sharing this video, Mynda!
Great to hear 🙏
Teacher . Thank you very much.
Love this.
#9 is my personal favorite. Thanks Mynda!
Cool 😎 have fun with it.
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.
Excel goddess….Some gems there….awesome as always Mynda…thank you.
Thanks so much 🙏😊
Very useful, thanks Mynda.
Great to hear 🙏😊
Perfect, thanks for the video.
Glad you liked it! 🙏😊
Thank you Mynda. Awesome videos
Glad you like them! 🙏
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!
You truly are a god-send, clear and simple instructions. Thank you so much!!
Glad it was helpful 🙏
Before you mentioned the filter function, I thought of it. It is a very useful function. Thank you Mynda.
Glad we agree 😁
Another great video 🎉
Thank you 🙏
Glad you enjoyed it 🙏😊
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! 🙏😊
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 👍
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 😊
tremenda explicación, muy util.👍👍👍👍👍👍👍👍👍👍👍👍
Thanks so much 🙏🥰
Thank you. I love your VDOs.🌹
Thank you!
Amazing video...Many Thanks
Glad you liked it 🙏😊
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.
Wonderful presentation and clearly communicated. So glad that I came upon your channel. Thank you for sharing your expertise🙂
🙏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!
you are simply excel guru....Thanks
Thanks for watching 🙏😊
Thank you Teacher
Welcome!!
Definitely useful information that everyone should know
Great to hear 🙏😊
Combination of VSTACK and FILTER to get the headers is a great idea! Thank you
Glad it was helpful!
Nice video, thanks for sharing!
Thank you!
Awesome. Picked up the EOMONTH + SEQUENCE combo.
Glad you'll find it useful.
#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 😎
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 😊
VSTACK and LET are new to me. Thanks.
Awesome! Have fun with them 😁
Brilliant video. Fav list - 1, 4, 6
Love your top 3 👍
Mam, you are doing well. I learned a lot from you thanks
Wonderful to hear 🙏😊
Your explanation is so easy to follow. Thank you.
Glad you think so! 🙏
Very boring lecture
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! 🙏😊
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
Dear madam
It was great excel session,thank you very much. God bless you .
It's my pleasure 😇
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: ua-cam.com/video/8-ANSpXcHr0/v-deo.html
So good you are
Thanks for your kind words!
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 learned LET, can't wait to try it out.
SAME.
Great to hear!
Great 😊
Thanks 😊
excellent
Thanks so much 🙏😊
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 👍
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 😉
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.
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.
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. 🤩
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.
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.
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.
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.
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.
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
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 😎
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)
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. 😊
nice
Thank you 😊
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
In love right now
Glad you enjoyed it 🙏
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. 👍
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.
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: ua-cam.com/video/0zTeXujtOZI/v-deo.html
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?
LET & LAMBDA are my two most power function in excel
Thanks for sharing 😊
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.
Hello ma'am I have founded your channel by Chat GPT. You are amazing ma'am.😊❤
Thank you so much 😊
"Let" is definitely up there. reducing calculations in a formula.
Sure is 😊
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 👍
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 👍
Mem can't say a word. I'm so far
I have seen as many videos as you on UA-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 😊
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.
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 😊
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!
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...)
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 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.
Textjoin eh? I like it.
Great to hear!
My favorite function îs LET
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.
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.
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 🙏
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.
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: ua-cam.com/video/fzdn1vcHPsA/v-deo.html Hope you find it useful.
I like let but still need Lot of practice
Great to hear.
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've used IFS before but the complexity was often overpowering. now LET is going to keep me sane
Great to hear! '😁
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.
👍
🙏
Filter is my favorite, too
😎 thanks for watching.
❤❤
Thanks for watching 🙏😊
@@MyOnlineTrainingHub The filter function is one my favorites, too. 🥰
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.
13:26 I always forget about IFS!🤭
😁some habits die hard. Glad I could remind you of it.
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.
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. 🙂
migerta