Top Excel Functions for Data Analysts & What NOT to Waste Time Learning
Vložit
- čas přidán 27. 07. 2024
- 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 - Věda a technologie
My favorite Excel instructor. She is succinct, to the point, and very clear in her instructions.
Aw, thanks so much, Carol!
By far the best excel channel on CZcams. Thank you so much for all your work. Please keep making more videos.
100%
Thanks for your kind words 😊
Agreed!
Agreed!! Well done Mynda & Team! Keep up the good work!!
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 CZcams!
Wow, thanks so much!
That's certainly quite a top video with the most common functions we need when making Excel spreadsheets. Thank you, Mynda!
You're very welcome!
This was your best video yet, Mynda. Great summary of what I would agree are the most useful Excel functions!
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.
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 CZcams ABOUT EXCEL FUNDAMENTALS. I DID LEARN A LOT AND HAVE ALL THE TIME TO GO BACK AND FORTH TO REALLY GRASP THE IDEA.
So pleased to hear that 🥰
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.
Glad you liked it! 🙏
WOW. I've been using spreadsheets for many years, and was so glad to find your channel. Loads of great info!
Glad you liked it 🙏
Excellent thanks Mynda. Agree completely on the functions that aren't really needed, I rarely use them
Cheers, Chris!
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
Cheers, Ivan! FREQUENCY is another good one to include 👍
It is important that professionals as good as you do these very complete tutorials that are highly profitable. Thank you very much Mynda.
Thanks so much, Ivan!
This is my first exposure to Excel (I come from a programming background), & I found your video incredibly clear & concise. Thank you!
Wow, thanks so much!
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!
Wow, that's awesome to hear! Congratulations on your hard work and success.
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 😅
@@paryonoono5019 Same! Good job 🤜🏻🤛🏻👌🏻
Beautifully done.
Thank you very much!
An amazing collection of valuable functions for data analysis. 👍👍👍
Glad you think so!
Love your videos, and this is one is great. Well done, keep up the great work.
Thank you very much!
Many thanks for an EXCELlent video!
Glad you enjoyed it!
The best & magnificent approach !
Gracijas!
Glad you liked it!
incredibly useful as ever!
I will check out the other videos as well. 🙂
Happy to hear that!
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. :)
Great to hear you'll find it useful!
GREAT !!! Thank you for sharing !
Our pleasure!
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
Awesome to hear!
I tend to agree that this channel can be the best I came across.
Thanks so much!
Dear Mynda, thank you very much!
You are so welcome!
Hi Mynda!Really Great Tutorial...Thank You :)
Cheers, Darryl 🙏
Thank you very much for your clear explanation.
Glad it was helpful!
This is the best Excel video I have ever seen. Thank you!
Wow, thanks so much! Please share it with your co-workers 🙏
@@MyOnlineTrainingHub I already shared it with our whole system admin team and have been getting positive comments back. Thank you again!
the last section is golden
Great to hear 🙏😊
Many thanks all your videos are awesome
Glad you like them!
Ma'am 👍, Thank You So Much For Sharing This one..
My pleasure 😊
Thank you very much!... I learned a lot....
Glad to hear that!
Thank you so much for sharing this ma'am 🙏👌
My pleasure 😊
Excellent. Thanks. really helpful
Great to hear!
I like so much your presentations. It’s s so well described in a clear and precise manner. Thanks a lot.
So glad you like them! 🙏😊
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.
Thank you! Will do!
you are truly amazing! I learned so many tricks that have made my work so much more efficient!
Wow, thank you!
This is the summary of what we need to learn.... Very interesting!... Thank you Madam...
You're welcome 😊
Thanks for sharing ☺️
My pleasure 😊
Fantastic. Thanks
Glad you liked it!
Your videos and tutorials are awesome, very pedagogical ! Great job and thank you 👍
Thanks so much!
I learned a new word today: pedagogical - "relating to teaching" :) Thanks
Well don!
Thank you, It was great.
Awesome to hear 🙏😊
Great video. LARGE is another great function in my opinion.
Indeed 👍
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.
Well said 👏
Excellent!!
Many thanks!
Thank you, Thank you, Thank you a lot for this great🎞
Glad you liked it!
Thanks for sharing
My pleasure 😊
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.
TEXTJOIN is super handy too 👍
Loved the way you've explained so many formulas and their functions please share a video on payroll
Glad you liked it 😊
Thank you
My pleasure 😊
thank you!!!!
You're welcome!
Thanks
Pleasure 😊
Nice work for exl
Thank you!
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
Glad you like my tutorials 😊 I’ll keep your suggestion in mind.
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?
I use the FORMULATEXT function to display the formula.
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.
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 👍
@@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
Waiting for the VBA lessons
Perfect
🙏 glad you liked it!
Wow Mam... 👍
Thank you!
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.
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]," "),{""})," ")
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 .
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
I use datedif rather than days as it gives me other options; months and years
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
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
You could create a file that demonstrates the use of these functions.
You're the best !!!! thank you. Can you tell us how to do a Powerbi dashboard and reports for trading with the stock market.
Thank you! I'll keep your suggestion in mind.
Thank you, Mynda, for always great value entertainingly but lucidly delivered. But this time you've managed to even top yourself!
Wow, thank you!
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 👏🏻👏🏻👏🏻
Glad it was helpful!
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.
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: czcams.com/video/Du73CPqWGQw/video.html
Great video and this function enough for data analyst work or more need to learn.pls must reply
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.
Hello. May I ask, do I need to use microsoft 365 to use this functions and tools??
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
👍
When you write a function,in the right cell we see the function itself in words form.Which function do you use for this?
I use the FORMULATEXT function 😉
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
Perhaps this video on embedding reports in web pages will help: www.myonlinetraininghub.com/embedding-excel-dashboards-in-a-web-page
👏
Glad you liked it!
Is it possible to utilise MS Project (Desktop) with power Query?
Yes: support.microsoft.com/en-au/office/use-power-bi-desktop-to-connect-with-your-project-data-df4ccca1-68e9-418c-9d0f-022ac05249a2
@@MyOnlineTrainingHub Ah, that's Project online, not desktop - i might try with a project xml output
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.
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.
Yes, the condition in the SWITCH function is equality, not a logical test. I missed that. Thanks, Paul,
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 !
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
well if you master xlookup then vlookup, match, and index function is not necessary anymore
Yes, absolutely for those users with Office 2021 or Microsoft 365.
At 6:45, why is Jeff Wiggle getting a bonus if his salary is above 100k?
It's OR criteria, therefore only one of the conditions must be met for the bonus to be given. Jeff Wiggle Popular = Yes.
@@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.
If you need a more flexible sort function, you can use SORTBY: www.myonlinetraininghub.com/excel-sortby-function
@@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.
"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.
@@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.
I guess it depends how well you know Excel vs other tools 😉
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?
The first holiday in January is also a weekend day, so you only count this once.
@@MyOnlineTrainingHub Noticed. Wow. Thanks.
Oh Mynda 🥰 You make my life so much easier and fun 🥳 Please, marry me ⚘ and let's do spreadsheet forever!
😁 glad you enjoyed my video!
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
You can probably do this with the new formula evaluate tooltip: czcams.com/users/shortsIkKYPVAFCOg
@@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)
Ok, not sure I understand, but try pressing CTRL+` to show formulas in the cells. Press it again to revert back.
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!
Tables will never support spilled formulas, SEQUENCE or any other. The can only support formulas that return a single value.
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", ""))))) ]?
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
Thank you
You're welcome 😊
Thanks
Welcome 😊