How to Clean, Analyze and Present Data with Excel (FREE Adv. Course)

Подписаться 610 тыс.

Просмотров 354 тыс.

50% 1

You asked for it. Here is my free course on advanced data analytics.
💥Want more... get my Excel School program 👉 chandoo.org/wp/excel-school-p...
In this comprehensive video, learn:
1) How to approach a data analysis project
2) How to systematically clean data
3) Doing EDA with Excel formulas & tables
4) How to use Power Query to combine two datasets
5) Statistical Analysis of data
6) Using formulas like COUNTIFS, SUMIFS, XLOOKUP
7) Making an information finder with your data
8) Male vs. Female Analysis with Pivot tables
9) Calculating Bonus based on business rules
10) Visual analytics of data with 4 topics
11) Analysing the salary spread (Histograms & Box plots)
12) Relationship between Salary & Rating
13) Staff growth over time - trend analysis
14) Regional Scorecard to compare NZ with India
You will also learn below Excel features:
1) Using Tables
2) Working with Power Query
3) Formulas
4) Pivot Tables
5) Conditional formatting
6) Charts
7) Data Validation
8) Keyboard Shortcuts & tricks
9) Dashboard Design
💥📁 Sample files:
================
Blank data file (perfect for following along) - chandoo.org/wp/wp-content/upl...
Completed workbook - chandoo.org/wp/wp-content/upl...
⏱ Video Timestamps:
==================
0:00 - Introduction & Topic Coverage
1:50 - Data Cleaning with Excel
6:17 - Ad-hoc Analysis of Data
10:23 - Using Power Query to combine & clean data in one go
21:25 - 5 Business Questions for Data Analysis
23:12 - Quick Analysis of Data - Summaries & Statistics
31:17 - Building an information finder using Lookup Formulas
36:59 - Information Finder v2.0
41:43 - Male vs. Female Comparison with Pivots
48:15 - Calculating Bonus based on Business Rules
51:03 - 4 Data Visualization Themes
52:46 - Understanding the salary spread with histograms & box plots
59:50 - Salary vs. Employee Rating Correlation
1:08:50 - Employee Trend over time
1:19:19 - Creating a Report Card to compare NZ vs. India
What to watch next?
=================
I recommend setting aside sometime to practice what you just learned. But if you are in the mood to watch more, I suggest these videos:
Beginner to PRO Data Analysis Excel Course - • Beginner to Pro FREE E...
5 key skills you need to be a GREAT data analyst - • 5 key skills you need ...
5 Excel Skills you need to focus on - • Top 5 Excel skills you...
15 Excel functions you should know - • These are the ONLY 15 ...
My playlist on data analysis - • 5 key skills you need ...
Want to learn more? Join my Excel School 👉
=====================================
If you want more step-by-step education & resources to be AWESOME at your work, consider joining my Excel School program. This online class will teach you everything you need for data analysis + reporting roles.
Visit chandoo.org/wp/excel-school-p... to sign up today.
My Recommended Excel Books 📚👌:
===============================
chandoo.org/wp/best-excel-pow...
😎 SAY HELLO 👋
===============
Apart from RU-vid, I frequently post on,
my blog - chandoo.org/wp/
twitter - / r1c1
Instagram - / chandoo.xlsx
Have a beautiful day 🌼🌞😀
#DataAnalysis #Excel

Наука

Опубликовано:

5 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 378

@alexrosen8762 11 месяцев назад

One of the best tutorials by far because you took several steps further from simple lookup formulas and other type av what I call "typical" tutorials that you can find in most channels. Extremely insightful and useful since it can be used in a lot of business situations when you work in this type of environments. A big thank you from Sweden!

@DaddyHexler 8 месяцев назад

Chandoo, bless you and your family. This is an incredible tutorial, I can't thank you enough. Aiming to transition from Carpentry into Data Analysis shortly and you've helped me immensely.

@far3582 11 месяцев назад

Thank you so much Chandoo - ppl may not realize the effort & time it goes into understanding the data then, making a script and then recording video. You're doing noble work for beginner DA!

@WithRose111 9 месяцев назад

Amen

@aqibhussain6578 4 месяца назад

In Pakistan Study we studied that Indians are our enemies but believe me you are the great teacher and i learn much more from Indian instead of Pakistan. Love from Dubai

@Hopspowered 7 месяцев назад

Thanks Chandoo - I've watched many of your shorter videos and find them very helpful and enjoy the entertaining style so I am trying to this longer version one!

@chandoo_ 7 месяцев назад

You are welcome @Hopspowered. Thanks for the super too 😍

@khangnguyen1306 11 месяцев назад

Count number of >90k$ staffs: =COUNTIFS(Employee[Salary],">90000"), and the ratio is number of >90k$ staffs/ count of staffs ~= 34%

@arunonmalwar4898 11 месяцев назад

Thanks I was struggling to find the right formula

@enisusilowati8549 Месяц назад

thanks for the formula, but may I ask. why do we put quotation marks on >90000, isn't quotation marks used to input strings with text type only?

@user-zz8zc7vi9i Месяц назад

@@enisusilowati8549 you mixing SQL with Excel

@jaded.9915 11 месяцев назад

It's the best video I've ever had in Excel so far. The best part is it went through the whole process. I learnt so much! Thanks a million!

@irfanriad7511 3 месяца назад

Thank you so much, brother. The thing I like about you is that you teach from the basics and share your different perspectives while analysing. May you get your reward in double for the amount of effort you put into this video.

@user-ix8jp1mr8b 7 месяцев назад

Awesome tutorial. Thank you so much for being clear and detailed in what you are doing (the steps) and telling us different ways we can analyze the data.

@souravmaity9732 11 месяцев назад

I loved this video. As i am learning to be in the field of data analyst i have already gone through all your videos but this is the best and most helpful as it gives the idea of working in a data set in real life rather than other videos which is more specific in certain topic Please make this kind of videos which is summary of all the topics❤

@PoojaGupta-bt4qb 8 месяцев назад

Hey, What a great video made by you... We actually need these kinds of videos to help us out. Thank you so much ❤

@sakeenasammi1768 11 месяцев назад

Thank you chandoo in this video the every part is explained so clearly. Please make such videos more whenever you are available. Once again thank you for your content. Keep it as always😃

@willy7968 11 месяцев назад

if you're watching this and finding it difficult to memorize the CTRL + SHIFT + 4 or CTRL + SHIFT + 3, he stated earlier.......... Just remember that the number key stands for what it does. 4 is USD when u press shift

@chandoo_ 11 месяцев назад

Great tip. That is how I remember it too.

@candicecwl8186 3 месяца назад

Thank you so much! I really appreciate that you went through the whole project and explained so clearly as it progressed. Your teaching method is great!

@swooz7746 2 месяца назад

Thank you so much, Chandoo! I really appreciated this more in-depth example of cleaning, analyzing and presenting.

@aleli1997 11 месяцев назад

"Awesome video! I am a financial analyst myself. Instead of data, I focus more on numbers…. However, I would say I would have done things about 85% similar to what you did, with a few exceptions in terms of formulas. I might not have used power query, although that's the purpose of the video. It's amazing how much we have learned, and you don't even realize your own knowledge until you watch a video and find yourself predicting the actions before they are even performed! Great video!"

@insightfulmind958 6 месяцев назад

Hello want to become a financial analyst myself , what courses + skillset will you recommend I learn ? Would be grateful for the information...

@aleli1997 6 месяцев назад

Hey Buddy! This is my transcript. A. Did google Analytics Course B. Did from Udemy “The Complete Financial Analyst course 2023” C. Studied Business Management/Accounting D. MBA in Finance Current Tittle: Financial Analyst for Central America & Caribbean at a S&P 500 company. If you have a degree plus good excel skills, and a good understanding of $$$ and/or ### you can have a great carrer! Yet without school degree, you will have to start from rock bottom and prove yourself up!

@kurtevangelista9332 11 месяцев назад

You're my idol chandoo, I have learned a lot from your videos. Thank you. Please keep uploading more videos like this.

@frankfriday6707 7 месяцев назад

This is one of the best data analysis tutorials I hv watched step by step so easy to understand

@user-cy1eo9jh3v 11 месяцев назад

You're always an expert on it. Just thank you for the knowledge share.

@abhilashn2993 8 месяцев назад

Awesome and clear explanation. Really enjoyed it . Thanks for making it.

@MuhammadAsif-sc8uo 11 месяцев назад

After introduction to the topics i realized that this is the most wanted video by me.

@travelsphere7402 11 месяцев назад

Kya jhoot bolta hai

@mirwansiregar 7 месяцев назад

You're really help many people to share this precious video Chandoo...many thanks. Wish you all the best

@karenwood8558 11 месяцев назад

Thanks for the video, it's very interesting. If you need to add more data to the tables that needs to be cleansed, what is the easiest way to do this? Thanks

@eroteam2 10 месяцев назад

As always a great content! Tks Chandoo!

@OlabodeThompsonJinadu 4 месяца назад

This is so insightful. I took my time to go through every step. Thank you so much

@ryanng328 10 месяцев назад

Thank you for being a very few amount of Indian RU-vidr that speak in English, you earned m subscribe.

@johnfritzelmacalisang8360 11 месяцев назад

Hopefully you have a lesson like this for SQL where we can follow what you do on videos, for us to understand regarding the topic.

@chandoo_ 11 месяцев назад

I do. here it is - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-l8DCPaHc5TQ.html

@subhashpareya4866 11 месяцев назад

I really enjoyed sir, Thank you for sharing such valuable content, I want learn complex formulas like the way you use in validation etc.,

@madhurig3974 3 месяца назад

Thank you Chandoo sir. This course is excellent.!! Very concise and organized!!

@Levince36 11 месяцев назад

Thank you very much, I consider Excel better than I thought.

@marthakibs1000 11 дней назад

You are heaven sent, great work indeed.

@hamzaosman8573 11 месяцев назад

Thanks for the knowledge Chandoo sir, May Allah blesses you and your family

@karunanagre4949 2 месяца назад

Hi Chandu! I'm very thankful for your videos. My excel knowledge was zero and now I'm getting much better through your videos. And I love your explanation :-)

@StephenHucker 11 месяцев назад

thank you, a good refresher, coming from the R world to Excel

@pradeeppadeliya 11 месяцев назад

Sir, it's most awaited video.... Please create more with different dataset... And also guide for interview...

$@frachiar4444$

@frachiar4444 11 месяцев назад

Very smart, thank you Chandoo!! I got some cool ideas for my sheets. 🙂

@deborahkris3746 7 месяцев назад

This video is well explained, take your medal!!!

@nykhalil21 7 месяцев назад

Very good explanation of real business scenarios. Good job.

@maxwelnoa6292 4 месяца назад

The best tutorial no doubt you are just amazing. Well explained, clear what steps we have to take in each next minute and how we have to move on each analysis. Just make more videos bro

@Auchley 11 месяцев назад

Amazing chandoo i really appreciate it!

@swarnadipbandyopadhyay1727 6 месяцев назад

Remarkable Video! Thank you so much. It teaches me a lot of things.

@sparkvibe1319 11 месяцев назад

Thank you for your channel it helps me a lot.... you impart your knowledge well.... God bless you more...

@VijayKumar69 11 месяцев назад

Fantastic Video. I love your Videos Chandoo!!

@saaneroa5024 2 месяца назад

Excellent tutorial Chandoo. Thank you.

@preetijain5337 3 месяца назад

Thank you Sir! You are the gem of a teacher 🙏

@rishabhgandhi4984 11 месяцев назад

Great course sir🎉 Just a request, also tell how can we put our dashboard on web for further showing on our resumes & LinkedIn profile.

@mskavoo 11 месяцев назад

Hello Mr Chandoo Thanks for your presentation & it's possible show Nz VS India (x /Y ) analysis based on Gender/Salary & ratings! 🙌

@TravisFX 5 месяцев назад

Excellent video. Thanks for your efforts. Am about to put together something similar where my client wants to consolidate a bunch of stuff from social media and more. How about some direction for a front end method of updating those tables with new weekly or monthly data

@ramvenkat9292 11 месяцев назад

Hello Chandoo - I am follower of your website from 10 years. Just discovered you are on YT too...Subscribed!!

@harshshah9006 11 месяцев назад

Thank u chandoo sir. Learnt new things. Plz bring more such videos

@jalaluddin8276 10 месяцев назад

Really this is an in-depth video and I enjoyed it and learned a lot. Thanks Chandoo

@chandoo_ 10 месяцев назад

Glad you liked it!

@aperson2020 8 месяцев назад

Wow. This my dream course. ❤❤❤❤ Thanks chandoo.

@tysonlason9923 11 месяцев назад

Thank you so much Chandoo.

@chrism9037 11 месяцев назад

Amazing Chandoo, thanks!!

@chandoo_ 11 месяцев назад

Glad you like it Chris :)

@INDYEAH 11 месяцев назад

This video is going to be the top viewed video on your channel !

@Is_haqq 8 месяцев назад

Great tutorial. Great tutor. Amazing

@mariogomes3029 Месяц назад

Chandoo, excellent course. Namaste

@arbazahmad7177 11 месяцев назад

Excellent tutorial.. Thanks 😊

@aafriyan 11 месяцев назад

Thanks as always Mr. Chandoo

@huda_excel 5 дней назад

I really enjoyed the video thank you so much

@harbanslal976 Месяц назад

Thank you for this video chandoo sir, I learned a lot from it

@maxmorris4562 11 месяцев назад

Analysis Adhoc Row bottom of columns 6:32 Total 6:54 Average salary Shortcuts formatting 7:21 Currency: Control Shift 4 8:21 Dates: Control Shift 3 Combining Two tables: 11:32 Into Power Query 13:00 Duplicate 13:28 With second duplicate selected, in formula replace name with name of other table. Now the second duplicate will show the other data. Re name it. 14:06 First data selected: Add column with country of that data. To identify that data is from that country for example. Repeat for second. 15:30 Combining tables; Append queries as new: Can then name it staff rather than each country Clean up: 16:26 Remove duplicates 17:33 Values missing: Right click: Replace values: Replace "Null" with "Other" Close and Load: 19:25 Close and Load to: New connection: Only create connection In Excel: New tab called all staff In all staff tab: Add in the "Staff" from queries: Right click: Load to: Table in existing worksheet

@autobase16 11 месяцев назад

🎉🎉🎉🎉 awesome 😎😎😎😎

@zoha3728 11 месяцев назад

Kindly tell me about power query topic when we apply from table / Range then show me large formula bar not like sir chandoo formula and not change the india _ staff formula

@Ennampol_vaazhkai 11 месяцев назад

@@zoha3728 if you couldnt able to see the formula bar, in the power query, go to "view" and enable "formula bar"

@fashmartins62 11 месяцев назад

I can view the formula bar in 13.28 but the contents are different, it was displaying the headers of the table and I am finding it difficult to edit as it does not show NZ_staff instead it displays all table headers.

@Fatima-gw7sm 11 месяцев назад

Can you do videos on how to perform Forcasting in excel

@bgrg 11 месяцев назад

@Chandoo Hi Chandoo. After completing this course, what kinda of projects would you recommend us to try? Thank you

@viralhaibhai04 11 месяцев назад

thanku sir for one of the most amazing tutorial .it revises many of steps that are always needed in data analysis love your videos thnaku for your efforts love from India😇

@chandoo_ 11 месяцев назад

You are most welcome

@VirtualVibist-ul2wd 6 дней назад

I love your knowledge Chandoo master😍

@ololadeelizabeth9549 26 дней назад

You are really a good teacher

@stanleyonyilimba7802 11 месяцев назад

This is really awesome. can you help with sites where one can apply for jobs as a junior data analyst, business analyst or data scientist hybrid. Thank you

@hassanmughal2059 10 месяцев назад

This is amazing.... Learned alot

@clementbaloi6401 11 месяцев назад

Very insightful tutorial Sir.

@a.achirou6547 7 месяцев назад

Excellent ! Thank you.

@preetshah3793 11 месяцев назад

Please make video on data analysis using SQL

@sharathgowdrusiddhartha6317 3 месяца назад

Thanks 🙏 for sharing the knowledge ☺️

@kishandosa7904 11 месяцев назад

how are you so good with excel? how long did it take you to become an expert? i wish i can be at this level one day...

@blueberrie1317 11 месяцев назад

whoa such a detailed tutorial :O

@myimran 6 месяцев назад

Dear Sir, without any question this video is simply great, I have a request, would you please have a video for us in which explain every chart by example avaiable now a days for visualization, i.e. scatter plot, histogram. temp, box................... please accept my Thank you in advance.

@kunalzshah 4 месяца назад

When I do Total Row in India Data, it gives me the total of the salaries! Total Row worked perfectly well in the NZ data sheet

@nareshkumarsenthil4816 7 месяцев назад

I think this video may charge my carrier.🎉❤

@annay1283 7 месяцев назад

Amazing tutorial ! thank you for your work

@chandoo_ 7 месяцев назад

Glad you liked it!

@emilynrequiron7082 12 дней назад

I'm using excel office 2021. is there any alternative to the filter function? Thanks in advance. This tutorial is superb. Thank you very much Chandoo..God speed

@amangoswami146 10 месяцев назад

Is anything can be more simpler than this? I mean love you 3000. 📙

@neongghalley6910 11 месяцев назад

Thank you very much for the information sir ❤

@VermaRajinder 10 месяцев назад

Chandoo --- You are good !!!

@oluwasogoabbey4269 11 месяцев назад

Thank you Ser!

@kunalzshah 4 месяца назад

Thanks mate!!

@abhishek7271 11 месяцев назад

Sir please One detailed video on chatGPT code interpreter or future AI tech will replace data analyst or not ? Replacement in the sense 1 Analyst required against 10 Please make this video for future of us, thanks in advance

@sohail3791 11 месяцев назад

Thank you chandu uu re sooo cooollll

@fatimaliban1877 2 месяца назад

thank you sir, would you plz tell me how did you added the green bar in power query.

@imranshethwala1492 3 месяца назад

Thank you very much Sir, May God bless you

@user-th9uj9qn7t 7 месяцев назад

Thank you chief!

@reneschindhelm4482 11 месяцев назад

Great video! Thank you very much!

@chandoo_ 11 месяцев назад

Glad you liked it!

@reneschindhelm4482 11 месяцев назад

@@chandoo_ I did and learned some new features/simplifications from it. Again, thank you for taking the time to create and share the video and sharing your knowledge for free. Much appreciated!

@sangethathad9524 11 месяцев назад

What a great video in excel. Thank you so much for this extraordinary video material.

@chandoo_ 11 месяцев назад

Glad it was helpful!

@ahisochieng8550 2 месяца назад

I love your sessions🙂

@shoibahmed9042 10 месяцев назад

Thank you so much Sir !

@ramnaslai2980 10 месяцев назад

Great video..many things are there to learn👌🤤

@ryuhayabusa3540 7 месяцев назад

Thank, I learned a lot

@najmahbasher2899 10 месяцев назад

@Thank you for this video Chandoo.

@priscillatayomi7942 2 месяца назад

Hello Chandon, thank you for this incredible tutorial, I find your videos very helpful. Quick one! I’m stuck, I’m trying to load my data into power query and I can’t seem to locate the ’From Table/Range’ icon on my Data ribbon. Please I need your help on how to locate it on my excel version. Thank you.