Data Cleaning in Excel - 10 Tricks (Beginner to PRO)

Рет қаралды 369,895

Күн бұрын

Dirty data on your mind?
Just spray the amazing "data cleaner" on it.
In this video, learn how you can use 5 Excel features to clean data with 10 examples.
You will learn about:
1) Flash Fill
2) Text to Columns
3) Remove Duplicates
4) Formulas
5) Power Query
Video Topics ⏱👇
===============
0:00 - Data Clean-up - the challenge
1:17 - Extracting the first name (Flash Fill)
2:10 - Get the city from location (Flash Fill)
3:12 - Get the city from location (Text to Columns)
4:20 - Splitting cost centre & ID number (Text to Columns)
5:35 - Duplicate Valvalues (Remove Duplicates)
6:33 - But there is a problem!!!
7:16 - Remove extra spaces from name (Formulas)
8:01 - Full time or Part time (Formulas)
8:49 - Power Query!!!
10:04 - Splitting Cost Centre & ID with Power Query
10:57 - Fixing inconsistent dates with Power Query
11:40 - Adding a Full / Part column with Power Query
12:30 - Bonus Example - Fixing or Removing incomplete data
13:50 - Loading Cleaned up data to Excel & Data Updates (Refresh)
Download the sample file 🟩
=======================
Here is a blank data file. Use it to practice these data clean-up tasks:
chandoo.org/wp/wp-content/upl...
Watch next 💻👍
==============
🧹10 Quick and Smart Data Cleaning Tricks - • Data cleaning in Excel...
👩‍💻 Detailed Video on Power Query - • Power Query Tutorial -...
🎶 Power Query Playlist - • Power Query Tutorial -...
🤷‍♂️ How to _____ in Excel - Playlist - • How to add a LINE to y...
~
#Excel #powerquery
May your data be clean and your days be serene 😀

Пікірлер: 252

@lynnajema4163 2 жыл бұрын

You are my plug when it comes to Excel....... Thanks💖

@chandoo_ 2 жыл бұрын

You're welcome 😊

@nobelmcr Жыл бұрын

hold on, imho, I believe some other words exist to replace you are my "plug" 🙃

@oseikwamejones9019 Жыл бұрын

Your Excel capabilities are out of this world. I'm glued to this channel.

@gauravchheda2005 Жыл бұрын

You are awesome! Complex concepts explained with simplicity and loads of examples. Kudos.

@Azhar_Khan383 2 жыл бұрын

Your videos are like a gem to me every time I look to get updated about my skills. Awesome & amazing techniques. Thanks

@chandoo_ 2 жыл бұрын

Thanks Azhar... 😀

@Manjumannavlog90075 2 ай бұрын

Most sensible and Experienced data analyst ever seen in KZfaq

@talkinghat88 2 жыл бұрын

Loving the insightful way you explain the data cleaning feature.

@tinajefferies5682 9 ай бұрын

Chandoo - you are such a lovely man. Your videos are so clear and your teaching style is perfect! I, (a 75 year old grandmother retiree from the Uk), am teaching myself Excel and your videos are just superb!

@gauranshbhutani592 2 жыл бұрын

Gem of a person..... so peaceful lectures...a big thank you

@wearyjake8531 2 жыл бұрын

Thank you Chandoo! I hope there are more people like you.

@gurnoorkaur3471 Жыл бұрын

Another amazing video! Explained so wonderfully well !!!! Thank you 🌸

@julsofthesand 9 ай бұрын

I am really thankful to all the tutorials you have. It saves me a ton of time and work. Keep it up🤟🤟🤟

@georgeaboh 2 жыл бұрын

You are amazing, Chandoo. Thanks for all your labour of love!

@priyaranjanbiswas4946 Жыл бұрын

The best and easy to understand video on Excel.

@yaswanthtirupathi1831 Жыл бұрын

Enjoying like a movie🤩🤩, impressed the way of your explanation. Thank you so much for sharing knowledge sir..🙏🙏

@KevinCastillousac 9 ай бұрын

You´re the best my friend, when i going to wacth a video with a powerfull tips you´re first option for ever.

@make-n-mend-Anglesey 9 ай бұрын

What a professional teaching style. Really useful tutorial. Subscribed.

@wayneedmondson1065 2 жыл бұрын

Great one Chandoo! Always entertaining and useful. Thumbs up!!

@prathmesh_jadhav8930 2 ай бұрын

It is always amazing to watch your videos… you give accurate, on point and no nonsense information… Thank you Chandoo sir

@nisakhan5043 Жыл бұрын

Explained Wonderfully! Thank you

@mahanaiminternationalminis5484 3 ай бұрын

Wow! what a wonderful way to teach the data cleaning in Excel. Thank U Sir.

@internationaladmissionkano2843 2 жыл бұрын

Thank you sir, you made my day with this video. I`m taking Google Data Analytics Professional certification

@anandk5536 2 жыл бұрын

Our journey of data analysis with Excel in your hand. We saw you really active from past six months. Thank you for your dedication and hard work by heart. Please don't let down us with basic to advance of Excel or any shortcut tips. It is your responsibility to teach us everything in practical way. And again thank you very much.

@chandoo_ 2 жыл бұрын

Hi Anand... I have been making Excel + data content for over 12 years. There is all sorts of content on the channel and my website. I am not sure what you mean by "let us down". But my duty is to only make content that I enjoy creating. It is your responsibility to take it or leave it.

@souleymanediallo1307 Жыл бұрын

@@chandoo_ when he says don’t let us down.. he meant you shouldn’t let us down in creating these amazing contents. Time in time, we will run to your Chanel to drink from your fountain of knowledge. Thank you sir.

@NarasimhaMurthy-pc6yo 2 жыл бұрын

This is Awesome !! Its like a new updates installed onto myself.

@chandoo_ 2 жыл бұрын

Great to hear!

@officetricks6303 2 жыл бұрын

This is very helpful. Those data cleaning techniques are extremely useful for our daily excel work.

@chandoo_ 2 жыл бұрын

Glad to hear that OT :)

@benjcheah3031 Жыл бұрын

Chandoo...Thank God I found you!!! This is the first time I actually understand Excel in the first 5 minutes and not tempted to watch Kdrama or Netflix. Anyone wanting to learn Excel quickly and not fall asleep..this guy your man ^^. Cheers mate! P.S, Do you have a video for those of us using Excel on Macs or just add in your vids short cuts for Macs if there are any. TQ

@shubhamkumarram9408 Жыл бұрын

You are providing an excellent content Please Keep it up. I started learning Excel and Found it as a most powerful software which we used to ignore in our school days

@questionresearch8721 Жыл бұрын

👏👏👏 That's awesome!!

@Giovi_ofressh 2 жыл бұрын

Hi Chandoo! Support in every videos you publish! Thanks for your tips! 🇮🇹

@chandoo_ 2 жыл бұрын

Thanks Giovanni...

@LearnChannelFox 2 жыл бұрын

You are the best 👏 thank you so much for all your help.

@ritikaranaa Жыл бұрын

Thank you so much for such a great content sir. Makes everything so simple.❣

@ShayanAsim 9 ай бұрын

This was amazing!

@jhonnafg 2 жыл бұрын

Hi! I am a new subscriber. Just found out you already have this data cleaning playlist. loving all your videos!!!!

@chandoo_ 2 жыл бұрын

Welcome!!

@WitchyGirl 2 жыл бұрын

Your videos are so helpful. Thank you for sharing. 😃

@BenDieselBasicAutomotive Жыл бұрын

New learning again..... Thanks..

@leerv. 2 жыл бұрын

Chandoo your videos are wonderful! Especially the ones dealing with Power Query! You convinced me to really try to learn it, so I started using it recently and after a decade of working with Excel I feel like a fool for not doing so sooner! I deal with repeated, periodic reports and often the original source is so messy. I really appreciate that I can use PQ, set it up once, and NEVER have to manually clean up the same report for the thousandth time (literally)! Just the other day I took a daily file that is over 500MBs (don't ask!) and cut it down to under 50, and I'm not even done yet! Spectacular tool!

@chandoo_ 2 жыл бұрын

Wow.. .what a transformation (excuse the pun). Power Query is one of the first things I teach in my classes now a days. That alone can save a lot of downstream headaches.

@leerv. 2 жыл бұрын

@@chandoo_ Yes! I love the idea of "you gave me this, but what is relevant is this, and what is useful to my recipients is actually THIS, presented in this way" :)

@MuhammadEldeeb 2 жыл бұрын

Very Awesome!🙏, keep up the good work!

@pro0392 2 жыл бұрын

Your video is very helpful bro. I gained plenty of knowledge in Excel by your videos.

@chandoo_ 2 жыл бұрын

Glad to hear that Pro.. :)

@kAUrikEnshin Жыл бұрын

You are so Awesome thanks Chandoo for imparting these tricks to us... More power to your channel...

@udm2048 Жыл бұрын

This is just Fantastic learning material and that is for free for everyone 👍👍👌👌

@sportshungers Жыл бұрын

Many many thanks to you for this nice video on data cleaning.

@jovan58770 4 ай бұрын

I love you man, you are amazing!

@user-th9uj9qn7t 7 ай бұрын

Thank you chief!

@chrism9037 2 жыл бұрын

This was another great video, thanks Chandoo!

@chandoo_ 2 жыл бұрын

Glad you enjoyed it Chris. PS: I also want to thank you for watching and commenting on most videos 😍

@chrism9037 2 жыл бұрын

@@chandoo_ sure, they’re great!

@azhariqbal1390 Жыл бұрын

Thanks, Chandooooo! You are brilliant

@questionresearch8721 Жыл бұрын

Thank you ❤️ this is helpful.

@peterfj6218 Жыл бұрын

Excellent tutorial.

@ihgamingfun6295 Жыл бұрын

Very helpful video 👍🏼👍🏼👈

@i_m_manjunath 9 ай бұрын

your way of teaching is awsome sir, i like this this video

@alanmendoza1134 Жыл бұрын

thanks you, i am poor and i had to practice with google sheets and i had to figure things out but it is equally fun to follow, thanks!

@apa4203 Жыл бұрын

Again, well done :)

@Name_VS Жыл бұрын

Thankyou for your useful videos, Sir.😊

@WithRose111 Жыл бұрын

Thank you so much Chando.

@charlidavid5592 Жыл бұрын

Awsome Chandoo..!

@mohammedshahidrahman8276 Жыл бұрын

Wonderful!!! Thank you so much.

@chandoo_ Жыл бұрын

You are so welcome!

@VinayKumarT1302 2 жыл бұрын

Woah!! Thank you for this amazing video Chandoo. Never thought we can do so much in power query 🤩

@OzduSoleilDATA 2 жыл бұрын

All of that ... and MORE

@chandoo_ 2 жыл бұрын

PQ is the bees knees.

@chandoo_ 2 жыл бұрын

AMEN to that brother...

@grendbelema 2 жыл бұрын

thanks...once again maestro

@OzduSoleilDATA 2 жыл бұрын

GOOOOOD stuff!

@chandoo_ 2 жыл бұрын

Wow Oz... Thank you 😎

@muhammadrashid3307 3 ай бұрын

nice demonstration.....Respect from Pakistan

@aashishmalhotra 2 жыл бұрын

wow man woderful job so smooth!!!!!!!!!!!

@chandoo_ 2 жыл бұрын

Thanks a lot!

@poornimachari9411 Жыл бұрын

Thank you so very much

@deepakjadhav4u Жыл бұрын

Thank u sir great video

@maxvision15 2 жыл бұрын

Love It!

@rajkumarpalle8217 Жыл бұрын

Thank you for sharing nice vedio

@hazemali382 2 жыл бұрын

Always Great Chandoo ♥

@sunnygawande5283 2 жыл бұрын

What a quote on data ✌️✌️✌️

@zulhilmibokhari4621 Жыл бұрын

Thanks Chandoo!

@natynt227 Жыл бұрын

very very helpful

@frankbradford9616 Жыл бұрын

This guy is really good! And his ability to create a video is getting better and better. I love it

@chandoo_ Жыл бұрын

Thank you Frank 😀

@mrthapaa3184 Жыл бұрын

Very useful 👍 vedio

@sumakudala688 2 жыл бұрын

Wow, this video is really helpfull to do cleaning process thank you so much 😊and I'm moving forward to remaining vedios....

@chandoo_ 2 жыл бұрын

You’re welcome 😊

@Lnair2010 2 жыл бұрын

Thanks Chandoo. Very Helpful videos especially me like people who started career in midlife

@chandoo_ 2 жыл бұрын

It's my pleasure Latha. More power to you :)

@sunilkumar-jl7uf 2 жыл бұрын

Lajawaab 👌👌

@arun.kumar.s 2 жыл бұрын

When ever i see excel tutorial videos, you always come to my mind. Did Chandoo taught this : oh yeah!!

@chandoo_ 2 жыл бұрын

Thanks Arun 😀

@nathanielaboagye2259 2 жыл бұрын

You guys are friends right?

@learn_ai_with_us Жыл бұрын

you are great in excel + your sense of humour amkes it more interesting....i call you BOSS of excel

@domxem5551 2 жыл бұрын

Thanks!

@cindycintiya6680 10 ай бұрын

I like your editing video 👍

@satyajitdora8169 2 жыл бұрын

Awesome , great video

@chandoo_ 2 жыл бұрын

You are welcome Satyajit...

@natynt227 Жыл бұрын

Thank you soo much

@Mars7822 Жыл бұрын

excellent.

@Ehte640 Жыл бұрын

Wow....just wow to your content...❤️

@chandoo_ Жыл бұрын

Thank you so much 😀

@sandipansarkar9211 Жыл бұрын

finished watching nd practicing

@sreeathethan5495 8 ай бұрын

The best.

@tejasrajpathak5034 2 жыл бұрын

excellent, thankyou!

@chandoo_ 2 жыл бұрын

You are welcome Tejas.

@acharya16 2 жыл бұрын

you are the best

@nbharat21 2 жыл бұрын

Thanq soo much Chandoo.. This video helps me a lot. Lots of love from INDIA 😍

@chandoo_ 2 жыл бұрын

You are welcome Bharat 😊

@satyajitchattopadhyayyt Жыл бұрын

Amazing

@sayidomarali7549 Жыл бұрын

Thank you...

@canaanjudahkibalama1958 2 жыл бұрын

My friend refeered me to you and i just subscribed without even watching a single video😱😀

@gareemadhingra 2 жыл бұрын

Awesome video

@chandoo_ 2 жыл бұрын

You are welcome Gareema.

@mohammedshabaaz9625 2 жыл бұрын

Your content learnings is like a movie to watch😍😍 Interesting and addictive... Thanks Chandoo Sir❤️

@chandoo_ 2 жыл бұрын

Glad to hear that

@sonalinaralkar5267 2 жыл бұрын

Sir, ur explanation very awesome its very is to understand. :):)

@chandoo_ 2 жыл бұрын

Thanks and welcome

@nitishsaharoy2917 Жыл бұрын

Thank you

@notesfromleisa-land Жыл бұрын

Power Query is a must have/know tool.

@Football-on5jw Жыл бұрын

Chandoo bahe is a blessing

@ramiabdelrahim9815 2 жыл бұрын

Great 👍 thanks

@chandoo_ 2 жыл бұрын

You are welcome Rami...

@searchbug Жыл бұрын

Keeping your customer database clean is a lot like maintaining your car: if you don't do it regularly or with the right tools, it's just going to get worse. Luckily, there are some easy things you can do that will help you keep your customer data clean and organized. Aside from what Chandoo shared, we also suggest data appending for those who are not really techy. It serves as data enrichment and data cleansing tool. 🙏

@questionresearch8721 Жыл бұрын

Facts 🙂👏

@octalogicsmith9028 Жыл бұрын

OMG! Thank you very much for this. You were clear concise and it was easy to follow and understand I’m a save this video because I’m just now learning how to do this and you seem to be the best teacher so far. Thank you very much.

@salehmuhammad6811 2 жыл бұрын

Excellent Chandoo

@chandoo_ 2 жыл бұрын

Thanks Saleh... 😎

@ahamedashik65 Жыл бұрын

Hi Chandoo, It was a great video as usual, thanks for teaching us in a simpler method where everyone can understand. I also found a mistake while cleaning the data by copy pasting the new values, so it shows the column gender where we changed null value to other but it shows zero in your video. Can you please look into the video again and explain why it is showing zero again instead of other. Thank you 😊

@zp5808 7 ай бұрын

Hi, I guess you are talking about the part around 14:15. From the tab named "Table1" he switched back to the half clean data set, specifically the tab named "PQ Data" which was not affected by Power Query. It was left there unmodified, Power Query ran some processes and then created a new tab with the clean data. He just went back in the video to look at and therefore to show us the original, dirty data on PQ Data tab, that's why we still see NULLs there.