Top Excel Functions for Data Analysts & What NOT to Waste Time Learning
Ғылым және технология
Top Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
Download the example Excel file here: www.myonlinetraininghub.com/t...
- IF Formula builder tool: www.myonlinetraininghub.com/i...
- SUMPRODUCT function tutorial: www.myonlinetraininghub.com/e...
- XLOOKUP function tutorial: www.myonlinetraininghub.com/e...
- INDEX function tutorial: www.myonlinetraininghub.com/e...
- FILTER function tutorial: www.myonlinetraininghub.com/e...
- GETPIVOTDATA function tutorial: www.myonlinetraininghub.com/e...
- Introduction to Power Query tutorial: www.myonlinetraininghub.com/i...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 Top Excel Functions for Data Analysts
0:44 IF Function
2:01 Nested IF Formulas
3:14 IFS Function
4:42 AND Function
5:43 OR Function
6:49 SUMIFS Function
8:42 XLOOKUP Function
10:07 INDEX & MATCH
12:17 FILTER Function
13:22 IFERROR Function
14:38 EOMONTH Function
15:59 EDATE Function
16:43 NETWORKDAYS.INTL Function
18:54 GETPIVOTDATA Function
20:24 UNIQUE Function
21:48 SORT Function
23:11 SEQUENCE Function
24:43 The functions you don't need to learn
Пікірлер: 196
My favorite Excel instructor. She is succinct, to the point, and very clear in her instructions.
@MyOnlineTrainingHub
5 ай бұрын
Aw, thanks so much, Carol!
By far the best excel channel on KZread. Thank you so much for all your work. Please keep making more videos.
@alexanderbaranov5418
Жыл бұрын
100%
@MyOnlineTrainingHub
Жыл бұрын
Thanks for your kind words 😊
@chrisg758
Жыл бұрын
Agreed!
@freezendog7
Жыл бұрын
Agreed!! Well done Mynda & Team! Keep up the good work!!
@accountantzone5581
Жыл бұрын
Great Channel. Outstanding explanations. If possible please make a video to identify fixed and variable cost using excel formulas or other functions. Thanks
I'm a regular user of all your "must know" formulae other than Sequence which I hadn't come across before. Even when your videos are covering familiar territory I invariably learn something new to add to the toolkit. The best Excel channel on KZread!
@MyOnlineTrainingHub
Жыл бұрын
Wow, thanks so much!
Mynda, this is really great. I've used most of these very frequently, and others rarely. This is a terrific overview of functions and I appreciate the links to drill down further.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it! 🙏
This is my first exposure to Excel (I come from a programming background), & I found your video incredibly clear & concise. Thank you!
@MyOnlineTrainingHub
Жыл бұрын
Wow, thanks so much!
This was your best video yet, Mynda. Great summary of what I would agree are the most useful Excel functions!
@MyOnlineTrainingHub
Жыл бұрын
Glad you think so!
I am using Excel for about more than 30 years, I first used "Multiplan" on an amber colored (bulb) screen of 15 inch. All these years I just used a few formulas which results are a bit comparable with this course. (mostly I combined several formulas to get the almost the same results) Seldom worked with financial data in Excel, I work mainly with data about employees (P&O data) and about objects like ships and their inspections. And most courses are based on financial data. And even after more than 30 years experience, I am still learning. Your small courses are the best I have ever seen in these years. And this collection of most valued functions for data analyzing is just a Pearl. And maybe it's my bad, but it looks like Excel is developing more and more new formulas lately?(to let you buy new versions 🙂?) I am a fan.
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much for your kind words! Yes, new functions and features are being added all the time if you have 365! It's worth it if you use Excel day to day.
ONE OF THE BEST CHANNELS ON KZread ABOUT EXCEL FUNDAMENTALS. I DID LEARN A LOT AND HAVE ALL THE TIME TO GO BACK AND FORTH TO REALLY GRASP THE IDEA.
@MyOnlineTrainingHub
4 ай бұрын
So pleased to hear that 🥰
WOW. I've been using spreadsheets for many years, and was so glad to find your channel. Loads of great info!
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it 🙏
That's certainly quite a top video with the most common functions we need when making Excel spreadsheets. Thank you, Mynda!
@MyOnlineTrainingHub
Жыл бұрын
You're very welcome!
Excellent thanks Mynda. Agree completely on the functions that aren't really needed, I rarely use them
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Chris!
It is important that professionals as good as you do these very complete tutorials that are highly profitable. Thank you very much Mynda.
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much, Ivan!
Following your tutorials have given me enough confidence and knowledge to use Excel that I am getting noticed at work by managers from other departments. I dream of being a point of contact about this amazing tool, and I feel I am headed in the right direction. Thanks a lot!
@MyOnlineTrainingHub
Жыл бұрын
Wow, that's awesome to hear! Congratulations on your hard work and success.
@paryonoono5019
Жыл бұрын
I have very much similar experience like yours since I've found this channel and learned from it. I never thought that using variety of index and match, Power query, Power pivot and making dynamic dashboard would make me to be noticed by some top management. Funny thing is when it comes to excel, they rather to ask me to help them instead of to IT department 😅
@geekatari4391
Жыл бұрын
@@paryonoono5019 Same! Good job 🤜🏻🤛🏻👌🏻
So true, about the old formulas but old habits die hard. The new ones you explained so well, but one I missed is the FREQUENCY formula
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Ivan! FREQUENCY is another good one to include 👍
Best video ever. It took me a long time to learn these on my own. Now I can share this video to my team In order to help them, versus them relying on my horrible excellent training skills. :)
@MyOnlineTrainingHub
Жыл бұрын
Great to hear you'll find it useful!
Can't believe myself that i knew & been using 90% of those formula. Big thanks to you cause i have learnt it since I've found your channel. Now, I'm like excel nerd in my work that many people come to me to help their work
@MyOnlineTrainingHub
Жыл бұрын
Awesome to hear!
I like so much your presentations. It’s s so well described in a clear and precise manner. Thanks a lot.
@MyOnlineTrainingHub
9 ай бұрын
So glad you like them! 🙏😊
Love your videos, and this is one is great. Well done, keep up the great work.
@MyOnlineTrainingHub
Жыл бұрын
Thank you very much!
The best & magnificent approach ! Gracijas!
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
you are truly amazing! I learned so many tricks that have made my work so much more efficient!
@MyOnlineTrainingHub
Жыл бұрын
Wow, thank you!
Wow Mynda - that was a great summary video for Data functions. I dont know how you do it! Keep them coming. I remember nightmares using CONCAT.
@MyOnlineTrainingHub
9 ай бұрын
Thank you! Will do!
I tend to agree that this channel can be the best I came across.
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much!
incredibly useful as ever! I will check out the other videos as well. 🙂
@MyOnlineTrainingHub
Жыл бұрын
Happy to hear that!
This is the best Excel video I have ever seen. Thank you!
@MyOnlineTrainingHub
Жыл бұрын
Wow, thanks so much! Please share it with your co-workers 🙏
@Jimboatwork
Жыл бұрын
@@MyOnlineTrainingHub I already shared it with our whole system admin team and have been getting positive comments back. Thank you again!
Your videos and tutorials are awesome, very pedagogical ! Great job and thank you 👍
@MyOnlineTrainingHub
11 ай бұрын
Thanks so much!
@txreal2
9 ай бұрын
I learned a new word today: pedagogical - "relating to teaching" :) Thanks
I use a combination of the functions you listed to create powerful excel tables and aggregate data using advanced logic. I work with arrays and multiple criterias a lot and i must say excel is indeed the bread and butter for all data analysts out there. It's good to know how to code on python and build complex machine learning models but we must not forget that real life data us rarely clean and 80% of the time is actually spent on excel making sense of the data before anything else can be done.
@MyOnlineTrainingHub
7 ай бұрын
Well said 👏
An amazing collection of valuable functions for data analysis. 👍👍👍
@MyOnlineTrainingHub
Жыл бұрын
Glad you think so!
Beautifully done.
@MyOnlineTrainingHub
Жыл бұрын
Thank you very much!
This is the summary of what we need to learn.... Very interesting!... Thank you Madam...
@MyOnlineTrainingHub
Жыл бұрын
You're welcome 😊
Great video and appreciate you sharing all your knowledge. How do you get the formula text to display in the column to the right of the formula your entering and could you out put that to a seperate tab?
@MyOnlineTrainingHub
Жыл бұрын
I use the FORMULATEXT function to display the formula.
Many thanks for an EXCELlent video!
@MyOnlineTrainingHub
Жыл бұрын
Glad you enjoyed it!
Hi Mynda!Really Great Tutorial...Thank You :)
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Darryl 🙏
GREAT !!! Thank you for sharing !
@MyOnlineTrainingHub
Жыл бұрын
Our pleasure!
Thank you very much for your clear explanation.
@MyOnlineTrainingHub
Жыл бұрын
Glad it was helpful!
Well don! Thank you, It was great.
@MyOnlineTrainingHub
6 ай бұрын
Awesome to hear 🙏😊
Great video. I can't remember ever using CONCAT. I might use TEXTJOIN rather than just the ampersand if I'm concatenating multiple strings and I want spaces or some other delimiter between them, as that function allows you to specify the delimiter once without entering it repeatedly. Makes the formula a little bit more intelligible.
@MyOnlineTrainingHub
Жыл бұрын
TEXTJOIN is super handy too 👍
Ma'am 👍, Thank You So Much For Sharing This one..
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
Many thanks all your videos are awesome
@MyOnlineTrainingHub
Жыл бұрын
Glad you like them!
Loved the way you've explained so many formulas and their functions please share a video on payroll
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it 😊
Dear Mynda, thank you very much!
@MyOnlineTrainingHub
Жыл бұрын
You are so welcome!
Thank you very much!... I learned a lot....
@MyOnlineTrainingHub
Жыл бұрын
Glad to hear that!
Excellent. Thanks. really helpful
@MyOnlineTrainingHub
Жыл бұрын
Great to hear!
Thank you so much for sharing this ma'am 🙏👌
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
Great video. LARGE is another great function in my opinion.
@MyOnlineTrainingHub
Жыл бұрын
Indeed 👍
Thank you for sharing, I'v learn so much. I'm struggled when typing IF(TODAY( ) this return error, the last parenthesis turn highlighted in red .
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it. For help with your IF(TODAY(... formula, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you, Thank you, Thank you a lot for this great🎞
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
Very good insight! I think CONCAT() can still be very useful for joining a larger RANGE of cells together (not just one or two cells which is simple using the & to join them). Keep up the great work.
@MyOnlineTrainingHub
7 ай бұрын
If I have a lot of cells to join, I typically use TEXTJOIN, but that's a newer function and not all versions of Excel have it, so CONCAT's a good alternative 👍
@Excel101
7 ай бұрын
@@MyOnlineTrainingHub 🙏 I thought concat() upgraded concatenate() around when textjoin() came out, but I could be mis-remembering. Regardless, the CONCATENATE() function has ALWAYS been useless!! :D
Hi i was following you for a while And you always introduce a new ideas 💡 thanks for your time. I hope if you can introduce some print technique for the records printing transcripts for multiple students
@MyOnlineTrainingHub
Жыл бұрын
Glad you like my tutorials 😊 I’ll keep your suggestion in mind.
There is a mistake in 25:06 regarding the trim function. The function removes spaces from the beginning and the end of a string but also removes extra spaces between words leaving only one space. In power query the respective function only removes from the beginning and the end, not spaces between words.
@MyOnlineTrainingHub
Жыл бұрын
Good catch that TRIM also removes extra spaces between words. You can do this with Power Query too, albeit with a custom function like Text.Combine(List.RemoveItems(Text.Split([Data]," "),{""})," ")
Fantastic. Thanks
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
the last section is golden
@MyOnlineTrainingHub
3 ай бұрын
Great to hear 🙏😊
Thanks for sharing ☺️
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
Thank you
@MyOnlineTrainingHub
9 ай бұрын
You're welcome 😊
thank you!!!!
@MyOnlineTrainingHub
Жыл бұрын
You're welcome!
Excellent!!
@MyOnlineTrainingHub
Жыл бұрын
Many thanks!
Can you please show us how to highlight that we have knowledge in these functions on a resume/cv? Thank you for the great content
@MyOnlineTrainingHub
Жыл бұрын
You could create a file that demonstrates the use of these functions.
Thanks
@MyOnlineTrainingHub
Жыл бұрын
Welcome 😊
Thanks for sharing
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
You're the best !!!! thank you. Can you tell us how to do a Powerbi dashboard and reports for trading with the stock market.
@MyOnlineTrainingHub
Жыл бұрын
Thank you! I'll keep your suggestion in mind.
Nice work for exl
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
Thanks for the video, but I want to ask about the 1:21 minute you chose the cell, and it gives you the Loan data but when I chose it, it gives me only the cell I clicked on, can you explain to me this point please.
@MyOnlineTrainingHub
10 ай бұрын
I have my data formatted in an Excel Table, therefore the cell references are 'Structured References' instead of regular cell references like C10. More on Excel Tables here: kzread.info/dash/bejne/dqlrlaWJobqtgdo.html
Wow Mam... 👍
@MyOnlineTrainingHub
10 ай бұрын
Thank you!
Great video and this function enough for data analyst work or more need to learn.pls must reply
@MyOnlineTrainingHub
Жыл бұрын
Thank you. These are the top functions, but function skills alone do not make you a data analyst. You also need PivotTables, Power Query, Power Pivot and more.
Thank you, Mynda, for always great value entertainingly but lucidly delivered. But this time you've managed to even top yourself!
@MyOnlineTrainingHub
Жыл бұрын
Wow, thank you!
When you write a function,in the right cell we see the function itself in words form.Which function do you use for this?
@MyOnlineTrainingHub
Жыл бұрын
I use the FORMULATEXT function 😉
Waiting for the VBA lessons
Perfect
@MyOnlineTrainingHub
Жыл бұрын
🙏 glad you liked it!
Hello. May I ask, do I need to use microsoft 365 to use this functions and tools??
@MyOnlineTrainingHub
Жыл бұрын
No, many of them work in all versions of Excel. This post documents which ones require 365: www.myonlinetraininghub.com/top-excel-functions-for-data-analysts
I use datedif rather than days as it gives me other options; months and years
@MyOnlineTrainingHub
4 ай бұрын
That sounds odd. Have you refreshed the query? If you're still stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hellllo :) Any chance you could make a video about transposing sheet data into HTML but without converting the entire sheet into HTML code or embedding the sheet onto a HTML page? More like ways to dynamically selecy individual cell data and have it show on a web page? Thanks if you can :D :D :D
@MyOnlineTrainingHub
Жыл бұрын
Perhaps this video on embedding reports in web pages will help: www.myonlinetraininghub.com/embedding-excel-dashboards-in-a-web-page
Nice video, I’m familiar with them all except Networkdays.Intl which looks handy. I need to use xlookup a bit more as I think it gets round a limitation I’d got with index/match which I always used instead of vlookup. Keep up the good content 👏🏻👏🏻👏🏻
@MyOnlineTrainingHub
Жыл бұрын
Glad it was helpful!
Is it possible to utilise MS Project (Desktop) with power Query?
@MyOnlineTrainingHub
Жыл бұрын
Yes: support.microsoft.com/en-au/office/use-power-bi-desktop-to-connect-with-your-project-data-df4ccca1-68e9-418c-9d0f-022ac05249a2
@netdiscs
Жыл бұрын
@@MyOnlineTrainingHub Ah, that's Project online, not desktop - i might try with a project xml output
Hi Mynda i want to change data in excel by power query (EX: CURENT DATA: COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 A 10 20 B 40 60 --> I WANT CHANGE DATA : COLUMN1 COLUMN2 COLUMN3 A 10 20 B 40 60 ). Please tell me How ? Thank you so much !
@MyOnlineTrainingHub
Жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
In the IFS section, for this particular case, wouldn't it be better to use SWITCH? Indeed, in this case, the expression, that is, the left part of the logical test, is the same, and it can be lengthy. With SWITCH, you state it as the first argument, then you have condition/value_if_true pairs and you can end with a default value, skipping the TRUE. The formula is shorter and easier to understand, it seems.
@MyOnlineTrainingHub
8 ай бұрын
Hi Paul, not in this case because each condition and value_if_true requires a different logical test. That is, there isn't one logical test that you can put in the SWITCH expression argument that will apply to every value/result pair. The first logical test is testing if it's > 90 and the second one is testing if it's =90.
@paulvanobberghen
8 ай бұрын
Yes, the condition in the SWITCH function is equality, not a logical test. I missed that. Thanks, Paul,
well if you master xlookup then vlookup, match, and index function is not necessary anymore
@MyOnlineTrainingHub
Жыл бұрын
Yes, absolutely for those users with Office 2021 or Microsoft 365.
👍
👏
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
I don't get it. 17:35 it says you have 20 network days including 3 holidays (with weekend type 1). Why the hell there is 20 working days? If we exclude holidays i got 22 working days. And when i got 3 holidays (in january i can see there are 3) i got 20 working days. Why there is not 19 working days instead of 20 working days? Why it doesnt calculate properly?
@MyOnlineTrainingHub
5 ай бұрын
The first holiday in January is also a weekend day, so you only count this once.
@7absinth
5 ай бұрын
@@MyOnlineTrainingHub Noticed. Wow. Thanks.
At 6:45, why is Jeff Wiggle getting a bonus if his salary is above 100k?
@MyOnlineTrainingHub
Жыл бұрын
It's OR criteria, therefore only one of the conditions must be met for the bonus to be given. Jeff Wiggle Popular = Yes.
@jcstrydom2505
Жыл бұрын
@@MyOnlineTrainingHub my bad, now it makes sense
sequence is no more than a sorted list. it would be more powerful by not having a sequence function and instead have a more flexible sort and a powerful list generation function. you could use them together or alone. a lot of string functions could be better done by regex, rather than clumsy individual functions.
@MyOnlineTrainingHub
Жыл бұрын
If you need a more flexible sort function, you can use SORTBY: www.myonlinetraininghub.com/excel-sortby-function
@Chris-op7yt
Жыл бұрын
@@MyOnlineTrainingHub : spreadsheets would be better if they were simpler and more generic. when i need to analyze volumes of data and have programming style flexibility, i do it with SQL or a programming language. it's sad that Excel keeps adding not much used features but the one thing it should have done right, it hasnt. namely, not silently dirtying data during import. i rarely use Excel for any real data analysis or transformations, because having certainty about the data you imported, and having access to checking results (inverting transformations etc) is just too hard in Excel, and even many other spreadsheet software, which tends to look up to Excel. You can manage some summary data dashboards in Excel but, it's an uphill battle always. Anyways, my point was that you can have more flexibility by keeping those two things as separate concerns/functions: generate a list and sorting. Having a combined "sequence" function is always going to be more limited and clumsier. A sequence is nothing more than an orderred list. Having a list generation rather than sequence (limited list and sort combined) is more powerful and less complicated to implement and use.
@MyOnlineTrainingHub
Жыл бұрын
"silently dirtying data", I presume you mean things like converting to scientific notation, removing leading zeros on numbers etc.? If so, you can now turn those 'features' off in the Advanced Options. I always use Power Query to import data so I can clean it as required.
@Chris-op7yt
Жыл бұрын
@@MyOnlineTrainingHub : yes. back in the good 'ol days (chuckle) used to have import templates, rather than auto-magical Excel guesses and silent conversions. In any case, data analysis in spreadsheets for a few hundred rows is ok but not much more. technically it can handle more rows however, analysis work is done better in non spreadsheet engines. Excel is ok for final output of summary data when end users require Excel.
@MyOnlineTrainingHub
Жыл бұрын
I guess it depends how well you know Excel vs other tools 😉
Hello where are my BTC.
What to do if I don't want to remove VLOOKUP formula but I need to read the long multi-line text in some cell? If i click that cell, instead of displaying the text in the formula bar, it shows the VLOOKUP formula. I want an option where I can temporarily view/read the results in the formula bar without hiding/protecting/copy pasting the worksheet/cells
@MyOnlineTrainingHub
Жыл бұрын
You can probably do this with the new formula evaluate tooltip: kzread.infoIkKYPVAFCOg
@shyamadasgupta9423
Жыл бұрын
@@MyOnlineTrainingHub Apologies but this is not what I am looking for. The only way out is to copy the cell/worksheet and paste as values, and then start editing the results in the cells. But that doesn't help me. I need to switch reading/editing between the formula and results in the formula bar. 1. I don't want to remove the formula from my worksheet 2. I don''t want to hide/protect either 3. i want to just switch from one mode to another (view & edit formula, and view & edit the result)
@MyOnlineTrainingHub
Жыл бұрын
Ok, not sure I understand, but try pressing CTRL+` to show formulas in the cells. Press it again to revert back.
Oh Mynda 🥰 You make my life so much easier and fun 🥳 Please, marry me ⚘ and let's do spreadsheet forever!
@MyOnlineTrainingHub
Жыл бұрын
😁 glad you enjoyed my video!
Sadly, until Sequence becomes compatible with Excel tables, it's a bit of a lemon. It really isn't worth sacrificing all the great advances Microsoft has made on the alter of Sequence. Sorry!
@MyOnlineTrainingHub
Жыл бұрын
Tables will never support spilled formulas, SEQUENCE or any other. The can only support formulas that return a single value.
@martins8587
Жыл бұрын
That's a big shame. It does mean that we still need "the old ways" to work in parallel with #spill functions then. Great video! Just sadly disagree with some of the last section.
I was trying to do this check a status of the cell based on 5 different statuses but the result is giving me a #value! error, do you know how to get this to work? [ =IF(A1="Status1", CHAR(128994) & " Status1", IF(A1="Status2", CHAR(128993) & " Status2", IF(A1="Status3", CHAR(128992) & " Status3", IF(A1="Status4", CHAR(128308) & " Status4", IF(A1="Status5", CHAR(128309) & " Status5", ""))))) ]?
@MyOnlineTrainingHub
Жыл бұрын
Not sure, but you should be using a lookup function for this, not an IF formula. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks
@MyOnlineTrainingHub
Ай бұрын
Pleasure 😊
Thank you
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊