3 Essential Excel skills for the data analyst

[@AccessAnalytic]

📖📍Use discount code KZbin15 for 15% off my book "Power BI for the Excel Analyst"? I wrote it for those who love Excel and want to learn Power BI, there's even a whole chapter dedicated to Power Pivot in Excel. pbi.guide/book/ 📍Use KZbin15 for 15% off the book

[@StopWhining491]

Calm, controlled presentation with no cursors flying around the screen. He also tells you "why."

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@Potencyfunction]

WHAT ANALYTICAL SKILLS, HOW TO CONTROL A TABEL FOR BOARD THAT DOES NOT FIT IN THE REGION AS MY KEYBOARD IS IN DANISH. MAYBE I SHOULD REALIZE THAT I AM NOT ABLE TO HANDLE A TABLE AND MOVE OUT TO A BEGINNER LEVEL.

[@SilvestreECamposano]

When I am confused about the basic of Power Query, I usually go back to this lecture. Thank you Sir, for simplifying the very complicated topics.

[@AccessAnalytic]

You’re welcome. I recommend Master Your Data as a book to get a good understanding of Power Query. Https://pbi.guide/resources/

[@tomalcolm]

Your voice is music to my ears. Every other Excel tutorial I've come across is like nails on a chalkboard. Subscribed!

[@AccessAnalytic]

Thanks for subscribing 😀

[@deangawler9727]

Learnt more about advanced Excel use in 18minutes than I have in the previous year - brilliant!

[@AccessAnalytic]

Glad to help Dean 🙂

[@jcmorgan3305]

lol l

[@misskaykayRSA]

After years of using Excel, how am I only learning about Power Query now 😭😭😭😭😭!!! Cleaning data takes up most of my time! This video was brilliant. Thank you 🙏🏾

[@AccessAnalytic]

Thanks! Power Query will change your life! Make sure you check out my playlist here kzbin.info/aero/PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3 and my friend Oz Du Soleil's channel which is where I learned about Power Query... kzbin.info/aero/PLo4_ndJE07FRhKi5QexHta3jAbTSU1Jiy

[@Tearstank]

Now I feel like an idiot. God knows how many months I wasted manually cleaning up data, combining data, sorting data, making graphs etc.... Thanks for this, after all these years I will learn excel properly lol

[@AccessAnalytic]

You’re not alone, most people still aren’t aware of Power Query. I’ve a playlist that might help your learning journey kzbin.info/aero/PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3

[@brandonp2530]

This has transformed my life as a data analyst. Saves so much of time

[@AccessAnalytic]

That’s great to know Brandon!

[@Potencyfunction]

yes indeed especially the empty sheet pivot table.

[@verbeeck36]

The possibility to unpivot other columns is adressing a major obstacle for pivot table application. I was looking for a solution through traditional excel formulae but was completely unaware of this possibility. Thank you so much!

[@AccessAnalytic]

You’re welcome. Unpivot is a joy. Thanks for taking the time to leave a kind comment and letting me know you found it useful

[@Justjames89]

Power query is the one! I’m not an analyst but helping pull different information in quickly has been a game changer

[@AccessAnalytic]

Absolutely James !

[@uzairrehman6193]

wow, this is the first time I know about Power Query and Data Model in excel and their uses. The way you explain is called true teaching.

[@AccessAnalytic]

Great, thanks for leaving a comment Uzair

[@ds5030]

Wow! Power Query! Mind blown! Brushing up on my rusty-ish excel skills for my job interview. I've added several of your videos to my watch list! Thank you for providing such engaging content!

[@AccessAnalytic]

You’re welcome, glad to help, thanks for taking the time to leave a kind comment

[@Machende100]

Love your teaching mythology, clear and very easy to follow. I'll be reviewing a lot more of your lessons moving forward!

[@AccessAnalytic]

Great. I hope you enjoy the channel.

[@abdulwahabfakunle6513]

Great content. You not only have the knowledge of the right skills required for Data Analytics using Excel but you are also an excellent teacher. I'm a fan.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment , greatly appreciated

[@Shakesbeard89]

I just wanted to say THANK YOU. This is the first video I've seen that has actually sunk in for me.

[@AccessAnalytic]

Awesome to hear Moogoats! Thanks for leaving a kind comment.

[@im2yz4u17]

All this functionality is exactly what I would teach a new data analyst. Great Job.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@akashgautam1909]

​@@AccessAnalytic Could you please help me know what skills are required to become a data analyst?

[@AccessAnalytic]

Logical reasoning, breaking elements down into smaller problems, ability to communicate results simply to decision makers. Good Excel skills ( or some other data analysis tool ) It really depends on what type of data analyst role you want / have. A good understanding of SQL may also be required for certain roles. For me I’ve been fine with Power Query for my data extraction and prep

[@annapoundstone2443]

I just found your video and learned more in the 15 minutes I spent here than I have in a long time! Thank you so much and I look forward to using this information and skills in my next position.

[@AccessAnalytic]

Great to hear. Thanks for taking the time to leave a kind comment

[@Old52Guy]

I never could do pivot tables very well without a lot of correcting afterwards. This is the best tutorial I've seen about Excel for data analysis.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@PaulJenniferZigwai]

Please, what keys do I press on the keyboard to select two columns at a time in power query editor?

[@trangvo219]

Thanks so much, it is one of the most useful 18 minutes of my time for so long already. Much appreciated, Wyn.

[@AccessAnalytic]

You’re welcome. Thanks for taking the time to leave a kind comment

[@Theausomecaleb]

Control space. Control space 🤯 I've been highlighting and scrolling my entire life, ooof glad I found this video lol.

[@AccessAnalytic]

😀.. we’ve all been there

[@CeliaAlvesSolveExcel]

I agree with you about these essential skills, Wyn. About Trim, I would suggest applying that step before merging the columns because it seems that in Power Query Trim does not remove multiple spaces in the middle of the string as TRIM does in Excel.

[@AccessAnalytic]

Excellent point Celia, thanks for pointing that out

[@CeliaAlvesSolveExcel]

@@AccessAnalytic it's one of those annoyances of "why is Power Query not working the same way as its corresponding function in Excel?".

[@iankr]

It's the same with VBA's own Trim() function - it doesn't remove multiple internal spaces. You need to call Excel's worksheet TRIM() function via WorksheetFunction.Trim. Which is weird and annoying! I wonder if the two issues are connected?

[@running4fun863]

Super thanks , I have to prepare Monthly Dashboard in my new job asked by my CFO ,never did it before . this is so so helpful ,thanks and love the way you teach.

[@AccessAnalytic]

Thanks for the kind comment, glad to help

[@willykitheka7618]

I have often wondered how a channel like this doesn't have a million subscribers and millions of views! This stuff that you have shared here is very useful! Thanks for sharing tthough!

[@AccessAnalytic]

Thanks! Tell others and we'll get there eventually! Coming up to 2 million views in a few weeks!

[@KrypticPatriot]

Good information but your energy is off the charts and really helps keep my attention, you are very good at teaching. Thank you!

[@AccessAnalytic]

Thanks for taking the time to let me know. I love this stuff 😁

[@flipperiflop]

Thanks for the really useful info. Some of these features are going to help with so many pain points I've had with my excel skills - just the "insert table" is going to make a big difference

[@AccessAnalytic]

Glad to help. Thanks for leaving a comment to say it was helpful.

[@DaddyBear3000]

I found unpivot other columns last week. Saved me so much time!

[@AccessAnalytic]

Absolutely K

[@09tgfl81]

so practical that anybody can apply what they learn from this lecture right away.

[@AccessAnalytic]

Thank you

[@jeffmoden4332]

I'm an old DBA and I'm not easily impressed. With that understanding, I'll say that I'm seriously impressed, not only as a DBA but also as a teacher and presenter. Very, very, well done, good Sir! And those people that only watched the first 18 seconds before they bailed? Even if they already know this, I'm thinking they missed how to present with the proper order of revelation and more.

[@AccessAnalytic]

Thank you Jeff. Greatly appreciate you taking the time to leave some thoughtful feedback.

[@carstoryteller5855]

Under 20 minutes and you save me many hours of work. Thank you, sir!

[@AccessAnalytic]

You are most welcome CarStoryTeller

[@stingaling]

Excellent tutorial. Refreshing lack of 'fluff', straight to the point. Thank you!

[@AccessAnalytic]

Thanks, I appreciate you taking the time to leave the feedback.

[@donharrold1375]

There's a lot of negativity around Excel, mostly driven by concerns on data integrity. However, the fact remains that the world runs on Excel. Great videos - many thanks for posting.

[@AccessAnalytic]

Generally the concern results from a lack of training and awareness. Excel is too easy for its own good sometimes.

[@kimwoods1]

This guy is awsome!!!!!! Just got a crash course & have learned and retained so much!

[@AccessAnalytic]

😊 glad you found it useful

[@elisabethschlarb3059]

I am also incredibly excited about "unpivot" - what a dream come true! 😀

[@AccessAnalytic]

It's awesome!

[@dougmphilly]

Unpivot is awesome. I took 16 column by 50 row table into flat table in seconds. I can’t call it a coincidence but once I understood the power of PQ, my corporate profile increased dramatically.

[@AccessAnalytic]

@dougmphilly - it’s a super power

[@uchiha_sasuke_48]

Why did I missed this channel until these days😢 Excellent and pleasant teaching step by step clearly. 😊😊

[@AccessAnalytic]

Thank you for the kind comment and welcome to the channel 😀

[@JhongskPass]

this is the most crystal clear presentation tutorial stands out! Amazing

[@AccessAnalytic]

Thank you 😀

[@natashataylor9877]

I just came across this video, haven't worked with PQs yet. Genius!!!

[@AccessAnalytic]

Power Query is awesome!

[@iankr]

Great video, Wyn. I'm slowly building my experience with Power Query. As you say, a lot of people haven't heard of it. In my organisation so many people rely on fiddly manual processes to gather and report on data, which is so prone to error.

[@AccessAnalytic]

Thanks for commenting Ian. Start spreading the word 😀

[@davidpearce1583]

fiddly!

[@AccessAnalytic]

No library functionality currently

[@regme4life191]

What an excellent way of explaining. Many thanks for this video.

[@roitadmor]

What a time savior! Thank you.

[@AccessAnalytic]

You’re welcome.

[@michealkinney6205]

I knew some of this, but in 20 minutes I learned so much more, lol. Thanks.

[@AccessAnalytic]

Glad to help Micheal

[@suki9860]

Absolutely brilliant packaging and showcasing of all our favorites! Your reminder is timely and absolutely spot on, Wyn! Thanks!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment. Much appreciated

[@gastonescudero-t3l]

Hello, greetings from Argentina, your video helped me understand more about the concepts since I am just starting out and like everyone else at the beginning, I am just starting out with MS EXcel.

[@AccessAnalytic]

I’m glad it helps. It’s a never ending learning journey. I’m still learning after 27 years!

[@thereshego5550]

Well, I ran across this video I was given a project to analyze our deliveries and create a KPI I have no idea what I’m doing lol. I thought I knew the basic of Excel but viewing this video. I have learned many things. Thank you so much.

[@AccessAnalytic]

You're welcome. Thanks for letting me know it helped you learn some new things

[@rfdave3980]

Wow I am blown away. I need to get into this more. Thank you.

[@AccessAnalytic]

😀. It’s core to the corporate reporting our company develops and is the foundation to how Power BI works

[@cpsbhati]

beautiful ❤❤❤❤ THIS IS HOW AN EXCEL VIDEO SHOULD BE SHORT, STRUCTURED, TO THE POINT AND CRISP ❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

[@AccessAnalytic]

Thank you so much, greatly appreciated 😁

[@TheJoshtheboss]

I love how you explain. Very concise, yet detailed. Excellent!!

[@AccessAnalytic]

Glad it was helpful!

[@deepakgautam8958]

I am an Analyst I love Excel nowadays along with Tableau, Power BI, Sql or any other tools. Its amazing to learn and enhance skill everyday. I am passionate to learn excel advanced features along with Power BI ☺️☺️. Thank you for explaining in detail in this video.

[@AccessAnalytic]

You're welcome Deepak, good luck with the learning journey

[@harshitraj8479]

Deepak can u share your FB account.I am getting prepared for data analyst job.I will share my doubts there

[@radha_radha1]

Please mujhe bhi data analytic ke liye prepare karna hai to mujhe kuch pata nahi hai. Kuch bata sakte hain kaha se kare plz help me .

[@elisabethschlarb3059]

Lol I love your charisma! I relate to the excitement you have for auto updating formulas in tables so much 🤣

[@AccessAnalytic]

Thanks for the kind comment Elisabeth.

[@aimaikhile4006]

I subscribed 1 minute into the video. Thank you! This was super helpful and you made it so simple. Thanks a lot!

[@AccessAnalytic]

Awesome thanks Aima! 😀

[@jenna_eli]

This was such a helpful video! I'm excited to watch more. Thanks!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment. Hope you find more useful content here

[@kelliecraine3312]

Thank you so much! This is the best presentation I've seen. Very much appreciated.

[@AccessAnalytic]

Thanks very much, I’m grateful you took the time to let me know

[@ninas.2724]

Very good explanation. I’m learning excel now and after watching how you clean the data in power query I was thinking all the formula that I have to use to clean the data, way easier power query

[@AccessAnalytic]

Absolutely Nina. It completely changed how I use Excel

[@sjones7333]

Thanks so much for this video! It was extremely helpful! I feel like my Excel skills has grew profoundly. Appreciate the great work.

[@AccessAnalytic]

You’re welcome, thanks for taking the time to leave a kind comment

[@vasudevanjayaraman]

Simple and quik explanation.Easy to understand.

[@AccessAnalytic]

Thank you

[@pameladelorbe3478]

Love how enthusiastic you teach and speak about Data! It motivates me to keep learning! Thanks so much! 🥰

[@AccessAnalytic]

That’s fantastic to hear, thanks Pamela

[@vernoneatwell5449]

I wish my teachers taught me this work in school. This is so valuable, not only for data analytics but also for any industry or career. Thank you, sir, for teaching new Excel skills to advance in

[@AccessAnalytic]

You are welcome. Thanks for taking the time to leave a kind comment

[@craigjohnson2422]

Absolutely well presented, to the point and sooo much valuable info. Well done and thank you

[@AccessAnalytic]

Thank you. Thanks for taking the time to leave a kind comment

[@KB-rv9yc]

Hello to all I am a newby yo Excel I have always known about it but it was not until recently that I truly appreciate its value and I absolutely love it ❤️❤️❤️

[@AccessAnalytic]

Welcome K B

[@Chef-1707]

Wow then more wow. That is super helpfull and is going to save a pile if work Been using excel for years but never knew this was possible. Brilliant video, really well presented in an easy to understand way. Really grateful for you sharing this.

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment Chef 1707. 😀

[@BMBlogHost]

Excellent! Very simple explanation. I will revisit this as I practice power query and data models.

[@AccessAnalytic]

Thanks, cheers for taking the time to let me know you liked it

[@gjones0316]

Love the video! I've used PQ for a few years now and am amazed at how few people realize that it's there. I'm putting together some recommended PQ training for analysts at my company and will definitely include this video! FYI I love your pronunciation of POWA QWEWY :)

[@AccessAnalytic]

Share the knowledge Gordon!

[@benaycock1646]

Thanks so much! Power Query & PowerPivot are going to be so useful; I wish I knew about them years ago!

[@AccessAnalytic]

Yep it’ll change your working life for the better

[@imnotu00]

Nicest most concise summary of the differences between the Excel analytical layers. I wonder is the any reason whatever to continue to use ranges?

[@AccessAnalytic]

Thank you 😊. I’ll still use named cells occasionally or often ranges that reference table columns ( good for validation lists and a few other niche scenarios ), but Tables definitely replace a lot of need for Ranges.

[@timpanister73]

Agreed totally. Always work with tables and bring them backstage with Power Query for transformation, should be the default way to use Excel.

[@AccessAnalytic]

Definitely Jenny

[@rs2wtf]

7 Years working with excel and I have never seen anyone use power query - can't wait to try it out! :)

[@AccessAnalytic]

There seems to be a rough 10 - 15% of regular Excel users that have heard of Power Query... it's a crime the number is so low!

[@MrPrescamacho]

I wish I saw this years ago. Thank you. Legend.

[@AccessAnalytic]

You’re welcome. Cheers.

[@3zlatnalava]

You are great! This is also for beginners and not only for pro. Thank you !

[@AccessAnalytic]

Thank you 😊

[@richardf6932]

So glad to have found your channel. Excellent. Thank you...

[@AccessAnalytic]

You're welcome Richard (tell all your friends!)

[@AdachukwuVJ]

Clear, Concise, well delivered.

[@AccessAnalytic]

Thank you

[@VinnieFarsheds]

Thanks! I have discovered Power Query already and use it everyday. But I still learned some new tricks in your video such as the Unpivot Other Columns and when to use the Data Model. And yes, not enough people use PQ and the Tables function yet. Will show them this video next time 😀

[@AccessAnalytic]

That's great Vincent, thanks for leaving a kind comment.

[@shrirajdeshpande8125]

Awesome. Loved the power query unpivot columns concept. I am beginner in the field of big data analytics. This video helped me a lot. Thanks a Ton !!

[@AccessAnalytic]

Glad to help, I appreciate you letting me know

[@chrisgoes9610]

How did I miss power query? amazing. Lots and lots of exploring to be done in this new found process/tool

[@AccessAnalytic]

Life changing for me

[@mendezapoccalypto7534]

Waou...feeling so happy to meet yr video Sir. Thiz are kind of chanels where u dont need to request subscribers, yes! Great content...absolutely ill try to practice trough all yr videos 😊 thank you so much!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@alex626ification]

I remember discovering power query by accident a year and half ago. I was trying to combine multiple source workbook to make a pivot table. My life has never been the same.

[@AccessAnalytic]

Absolutely Alex!

[@bernicesmith8793]

This was awesome, I thought I was reasonably competent in Excel but I didn't know any of these features existed 😅! Thanks for sharing these, I'll be keeping an eye out for your upcoming courses!

[@chrisgoes9610]

Ditto

[@AccessAnalytic]

Great! It’s truly transformative technology

[@AccessAnalytic]

😃

[@ozansen_ozibaba]

I like your videos ,and the way you present it, Sir. They are highly educative and remind you of things that are easily forgotten.

[@AccessAnalytic]

Thank you Ozan, I’m glad to help

[@republikadugave420]

Never seen so many icons on quick acess toolbar :)...btw amazing video ..new to power querr and that unpivot trick made me smile..

[@AccessAnalytic]

Thanks 😀

[@Silentstrike46_]

Just discovered your channel - very well presented, and incredibly useful tools. Subscribed!

[@AccessAnalytic]

Excellent - welcome!

[@fourdotss918]

Brother you are amazing! I'm new to the excel world and I ready enjoyed your video. Awesome work.

[@AccessAnalytic]

Too kind, thanks ☺️

[@ROBROB301]

Excellent video with clear concise explanations. On;y just came across this channel and finding it super useful for learning the basics of power query👍

[@AccessAnalytic]

Thanks for letting me know you're finding it useful Robert. My goal is to tell as many people as possible about Power Query - it's awesome!

[@crispisauce]

Amazing, I did not know what powerquery was. So powerful!

[@AccessAnalytic]

Absolutely

[@soxpuff]

The Query Transformation was what I thought I always needed but didn't know it was available.

[@AccessAnalytic]

Glad to shed some light on the situation

[@rotimioladunni7761]

Thank you so much for this. You made so simple. I was referred to your video and I am impressed. Thank you

[@AccessAnalytic]

Great to hear Rotimi, please thank the person that referred you for me 😁

[@OzduSoleilDATA]

Good list. Pivot Tables are so essential. I agree that more people should use Power Query, but I've made too much money creating tools that do exactly what Pivot Tables are designed to do. I would have rather empowered those folks. Overall, excellent summary of the features. You even got a little into the data models. 👏🏼👏🏼👏🏼👏🏼👏🏼

[@AccessAnalytic]

Cheers Oz

[@rickeyjones729]

Excel on fire if you feel confident in your excel skills where should one apply. If they don't have the work experience.

[@Honking_Goose]

@@rickeyjones729 To a data analyst job? They are in demand so even with no experience you will probably get some responses, you will almost certainly be required to do a excel test or two for them though

[@MarkSmith-xf9cy]

Finally makes sense. Can't thank you enough.

[@AccessAnalytic]

No worries, thanks for taking the time to leave a kind comment

[@versai121]

Incredible tutorial. Thank you!

[@AccessAnalytic]

You’re welcome. Thanks for taking the time to leave a kind comment

[@alvinestrada1655]

Just what I needed right now. Thank you for sharing and count me as your new subscriber.

[@AccessAnalytic]

Great to hear Alvin. Welcome!

[@mohammedsalah790]

Amazing video with crystal clear explanation.

[@AccessAnalytic]

Thank you 😀

[@newbird5944]

Excellent video Wyn, you mention all key points from different function of Excel, I learned and understand instantly, Thank you. Had subscribe to your video, will continue learn from you.

[@AccessAnalytic]

Thanks newbird 😁

[@Blue-Kayak]

Great video and presentation. I think I learned the most in the shortest time of any video presentation.

[@AccessAnalytic]

Thanks for taking the time to let me know it was useful. Much appreciated Jerry

[@maxmillan8207]

Thanks alot, I learning on my own to advance my excel skills

[@AccessAnalytic]

You’re welcome, thanks for taking the time to let me know

[@controlsgirl]

Such a great overview. Explains it all so well

[@AccessAnalytic]

Thank you 😁

[@egroegknimmij3169]

Are the power qry and data model structures stored in the xlxs file as well? Probably, seeing 16:28 “workbook’s data model”. Usually there is already an application dtb in place, shouldn’t the sql or query handle the extraction and transformation (ucase, trim etc) already (@ 11:50 for power qry it is said that direct sql is possible)? But then there is no need for power qry, are different sources for data models possible? Again probably it is, at 15:30 in the home menu tab, “From dtb, from other sources, existing connections”. Still a manager wouldn’t do all this, he immediately would like to see the pivot, running it from the application menu, in my case Crystal Reports. Or Power BI if the company has it in place. Still it is great to have this in xls, for testing, fiddling etc. Another thing/trouble: you can’t access the local dtb sources anymore, it is all SaaS, a web layer is involved. Greetings from NL. 3:48 ctrl-space used it primarly for selecting a full column, but a table column is also nice!

[@AccessAnalytic]

Yes the Power Query and data model are contained in the workbook. Multiple data sources are possible. The same technology is built into Power BI

[@kankiritharan3418]

I like table concepts. Simple explanation. Thankyou Sir 🙏

[@AccessAnalytic]

Cheers. I appreciate you taking the time to let me know you found it useful

[@stephenl5693]

Superb video tutorial. Just when I thought I was an Ace at Excel its amazing to learn there are more productive time efficient ways to clean and prepare data. I'm looking forward to implementing these new ways! 👍

[@AccessAnalytic]

Thank you and best of luck

[@JeffSauer-g8j]

Such a great video, thank you! I've subscribed - you have an excellent teaching style, easy to understand, relevant material. Great stuff!

[@AccessAnalytic]

Awesome, thank you!

[@sidrahbilal3877]

I was very anxious for my business analyst interview 18:01 minutes ago. But I feel less overwhelmed now. Your pace is great, your delivery is excellent and your methods are really insightful. I look forward to watching all your videos on this topic! Thank you, Sir! Keep updating us.

[@AccessAnalytic]

Glad to help 😀. I appreciate you taking the time to let me know you found it useful

[@imohsin87]

Ohhh too good and very high level of teaching, great work

[@AccessAnalytic]

Thank you

[@thiernodiallo2992]

Thank you. Used the same thing and my boss loves it

[@AccessAnalytic]

Great

[@iiiprogressive]

Perfect presentation and guide, thank you for this!

[@AccessAnalytic]

You’re welcome GS

[@smcreation3567]

Sir, it's realy such a tremendous video to learn, very well explained everything in few minutes ❤❤❤

[@AccessAnalytic]

You’re welcome, thanks for taking the time to leave a kind comment