Top Excel Functions for Data Analysts & What NOT to Waste Time Learning 

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

Aw, thanks so much, Carol!

Wow, thanks so much!

So pleased to hear that 🥰

Cheers, Chris!

Glad you liked it 🙏

Glad you think so!

Glad you liked it! 🙏

You're very welcome!

Thanks so much, Ivan!

Cheers, Ivan! FREQUENCY is another good one to include 👍

Wow, thanks so much!

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.

Wow, thank you!

Thank you very much!

Glad you liked it!

Awesome to hear!

So glad you like them! 🙏😊

Thank you! Will do!

Thank you very much!

Great to hear you'll find it useful!

Well said 👏

Happy to hear that!

Our pleasure!

Glad it was helpful!

Glad you think so!

Great to hear!

Glad you enjoyed it!

Awesome to hear 🙏😊

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 🤜🏻🤛🏻👌🏻

My pleasure 😊

Glad you like them!

You are so welcome!

Glad to hear that!

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!

You're welcome 😊

Cheers, Darryl 🙏

Thanks so much!

I learned a new word today: pedagogical - "relating to teaching" :) Thanks

Thanks so much!

My pleasure 😊

Glad you liked it!

Glad you liked it!

Glad you liked it 😊

My pleasure 😊

Many thanks!

You're welcome!

TEXTJOIN is super handy too 👍

Indeed 👍

You're welcome 😊

My pleasure 😊

I use the FORMULATEXT function to display the formula.

Glad you like my tutorials 😊 I’ll keep your suggestion in mind.

Pleasure 😊

Great to hear 🙏😊

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

Thank you!

Wow, thank you!

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

You could create a file that demonstrates the use of these functions.

🙏 glad you liked it!

Thank you! I'll keep your suggestion in mind.

Thank you!

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]," "),{""})," ")

Glad it was helpful!

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.

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Du73CPqWGQw.html

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 the FORMULATEXT function 😉

Perhaps this video on embedding reports in web pages will help: www.myonlinetraininghub.com/embedding-excel-dashboards-in-a-web-page

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

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

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

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,

Glad you liked it!

Yes, absolutely for those users with Office 2021 or Microsoft 365.

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

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 😉

The first holiday in January is also a weekend day, so you only count this once.

@@MyOnlineTrainingHub Noticed. Wow. Thanks.

You can probably do this with the new formula evaluate tooltip: ru-vid.comIkKYPVAFCOg

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

😁 glad you enjoyed my video!

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.

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

My pleasure 😊

Welcome 😊