Data cleaning in Excel - 10 tricks *PROs* use all the time
HTML-код
- Опубликовано: 6 июл 2024
- Good data is the cornerstone of great analysis. In this video, learn 10 powerful yet EASY ways for data cleaning in Excel that not many know. The tricks you will learn are,
0:00 - Introduction
00:18 - Extracting first name with Flash fill feature of Excel
1:47 - Converting numbers to percentages
3:18 - Remove blank cells or rows using Goto special
4:43 - Find common values between two lists
5:32 - Clean-up ugly formats with one click
6:15 - Bonus trick #1 - Remove extra spaces
7:30 - Unstack data easily with simple formulas
9:44 - Removing duplicate combinations in your data
10:37 - Deleting yellow or any colour cells
12:09 - Extract numbers from text values easily
13:35 - Get file names from full path with Find Replace in Excel
15:36 - Bonus trick #2 - Dealing with numbers formatted as text
Example file for practice:
====================
Please download example file with dirty data to practice here.
chandoo.org/wp/wp-content/upl...
More on Data Cleaning with Excel:
============================
10 ways to clean data in Excel (2022 techniques) - • Data Cleaning in Excel...
How to use Power Query to clean data - • Power Query Tutorial -...
Combine Excel Files Automatically - • Powerful trick to comb...
#ExcelTricks #DataCleanup 
Наука
Here is a dead simple way...Lol. I love this guy's videos. Great job Chandoo.
Chandoo you’ve been the OG of excel teaching for years - great to find you on RUclips. And thanks for the humble way you’ve shared your spectacular skills all these years!
Thank you Seven. 😀
Thank you, Chandoo. I got the recommendation from one of my colleague in Manila. Very simple and effective presentation.
Glad it was helpful!
0:00 - Introduction
00:18 - Extracting first name with Flash fill feature of Excel
1:47 - Converting numbers to percentages
3:18 - Remove blank cells or rows using Goto special
4:43 - Find common values between two lists
5:32 - Clean-up ugly formats with one click
6:15 - Bonus trick #1 - Remove extra spaces
7:30 - Unstack data easily with simple formulas
9:44 - Removing duplicate combinations in your data
10:37 - Deleting yellow or any colour cells
12:09 - Extract numbers from text values easily
13:35 - Get file names from full path with Find Replace in Excel
15:36 - Bonus trick #2 - Dealing with numbers formatted as text
Thank you for these time stamps
Thank u sir
@@Jupiter_176 sambh
Very nice ^^ For #8 I think it’s helpful to also mention that the Find, Format button, drop down allows you to select a specific cell to just pull the format from. It helps if you don’t know the exact color you want to duplicate.
Good tip!
Chandoo has become my lecturer in my journey of becoming an analyst, learning as I practice is the best experience. Thanks, Chandoo.
Never skip ads when watching his videos. Dead simple way to thank Chandoo 😉.
OMG - this is the very best Excel video ever. Need to watch like 5 times and takes note. Gold found here.
Wow, thanks!
Hope you know that you saved a lot of lives with this video. Great content.
I love those tips. For #10, converting text to number, highlight the range and use ALT+D+E+F. Much faster than a paste special.
Great tip! The old Text Import wizard saves the day !!!
Value() or Multiply by 1 also work. Great video thanks!
Great video. I added it to several playlists just so I know I can find it quickly for reference. Thanks.
Woahh! nr 6: stacked columns, what a great and easy way to fix this! well done Chandoo!
I know, right?
@@chandoo_ how to make it dynamic sir?
Learn to do this in Power Query or a Pivot table. Power Query in particular will blow your mind. In my opinion, #6 won't work in many cases.
Hi Chandoo,
For #6: What I can do is just copy the 3 cell and go to Street cell and do Alt e+s+e(transpose) then enter. Stacked will be done in few seconds.
@@vikashaldar982 agree, but it is going to take longer.
I just have one word for this video: wow! I guess I could add 'thank you' for all those great tips too!
You are so welcome!
Awesome video!
Bonus trick #2 is just legendary.
I do most of these practices but I didn't know that there's a flash fill function. I wasn't aware haha that's much faster and easier than doing delimit or formulas!!! Perfect! And very clear explanations. Yesterday was my first time watching your vid, but now I'm subscribing 👏💪🤟
That's awesome! Welcome and more power to you. 😀
Thanks for this instructive and interesting video.
I knew MOST of these tricks, but some were new to me. And some I wrote macros (years ago), and now it is a built-in feature.
Number 6 the BEST one! Once again brilliant stuff from the master.
Thanks Chris... :)
Excellent video Chandoo and we particular like the section at 9:44 - (Removing duplicate combinations in your data) as this is something we have been working on for many years in our tools, and although Excel has a very quick method of finding and removing duplicates we find that by combining advanced data matching algorithms with configurable lookup libraries can provide the most accurate results.
Fantastic! I have been using Excel for years and these tecnique will improve A LOT my skills for Data Cleaning. Thank you so much!
Some are so easy but will save you time. I needed to know number 3, which will save me about 60to 90 minutes of work tomorrow. Thank you so much!!!
300k views now! This is such an amazing video, Chandoo. Thanks for walking us through and giving your life hacks! For our data friends dealing with names, phone numbers, email addresses, and addresses, and want to fill in missing info, you may want to consider data appending to enrich your existing data sets. Whatever missing can be filled by simply appending your contacts. :)
Thank you for sharing these tricks. I've always been a big fan of your tips... glad to see that you now have a thriving RUclips channel as well!!
Happy to hear that!
Great video and the "Monika" with the "k"... Priceless!! 😄
Most of them i know as financial guy but few are unique. Thank you Chandoo. GOD BLESS YOU.
Thanks Nitin...
This was awesome! Thank you!!!
liked the way you got file name from full path, this is one thing we need on daily basis,, thanks, loved it
Flash fill, awesome! thank you!
Great video, thanks!
Glad you liked it!
You're always awesome!!!.. Thanks..
This was awesome. Especially that of unstacking data
Amazing! Thanks, Chandoo!
Thank you, Chando!
So simple and so powerful! :)
Glad it was helpful!
Some elegant built-in alternatives to using mid() and find(). The one to extract numbers is awesome
Glad you found those useful.
One of the best Excel tutorial!
Wow, thanks!
You rock as always. Re#8: Once you do the "Find All" you can also use Ctrl+A to select all of the found values without having to scroll down and select all of them (there may be hundreds).
Wow... that is a good tip Adam. Donut for you 🍩
@@chandoo_ you are too kind Sir
Great video! Thank you! I just started using the last Bonus trick recently, after I had learned about it. It comes in very handy, because that is a big issue!
Some great tips there, thank you for sharing!
Thanks for watching!
Knew these features,but the way you combined these simple features were amazing
Thanks High Priestess :)
Happy to find you in youtube. you ease my burden of extracting, cleaning and analyzing data for my report.
Salamat po.
you are a genius sir, your way of teaching, your example and your English is so simple. I don't have words to say thanks. your example no#6 is most useful for me.
You are most welcome
Chandoo sir, you are my lifeline... I can't imagine using the excel without your videos. Marvelous job !!!
Love from India.
Awesome. Loved it
Phenomenal tips, thanks!
Glad it was helpful!
Great tips!!!!!!!!
Great video, nice and easy explanation! Thanks you
Thank you. I learnt +go to,clear,flex,format operation devide,multiply,add
Finding your channel is literally blessing for me thanks lot!❤
Nice tutorial. Thanks so much.
Great trick (the last one)
Chears from Brazil
Love you so much Brother...For Your practice sheet and for the tutorial. It Saves lot of time. May Allah bless You all with success, health, happiness, patience and strength.
Awesome! Thank you!
Thank You So much, I have always struggled with Numbers in Text Format, but after watching the video I have got the solution.
Dude - your tips are awesome! I tried each one, they work and you explanations are easy to follow. Thank you so much
Found your channel today. Your method of teaching is amazing.
Thank you!
Wow. You made it so simple. Thank you
You’re welcome 😊
Really cool tricks... Flash fill and finding color formatted cells.
Thanks Nirmal...
Nice, thanks!
Very good video. Very concise.
Awesome Chandoo! Loved it.
This is excellent. Awesome job
Thank you very much!
Knew most of these but enjoyed the way you presented. Excellent presentation and voice. Thank you for sharing.
Thank you. I am glad you enjoyed this.
@@chandoo_ People like you have been making many of our career. Thank you is not enough for your work. I am addicted with Excel you can say as I have been using it for last few years. Still love to watch videos online and learn new tricks. Keep it up. I am following you videos.
Very helpful video. Thank you for all your contents!
Awesome, easy tricks to use!
Glad you think so!
Amazing! More Power to You!
Thank you so much!
Great explaination.. thanks and regard.
wow great video, thanks man
this is awesome!!!👍👍👍
Great work. Super simple.
Seriously good!
I really enjoy learning excel from your videos.... very simple and quick to understand..Thanks
Very useful tips, and clearly explained.
Many thanks
Thanks for this video
I think that unstacking method might be a game changer for me. THANKS!
Really good job with this video!
Thank you very much.
These are really powerful tips. Thanks
You're so welcome!
super cool tricks. I wish I knew about them during my heydays! thank you for sharing!
Brilliant Video !!Thanks a lot for sharing such wonderful tricks.
Excellent Excel tutorial!
Keep it up 🦾🦾
👏👏👏👏👏👏 thank you so much!!!
You are so welcome!
Thank you for your tips.
Thank you Chandoo, please keep going with your videos!!!!
OSM, thanks for valuable tutorial !
Wonderful, Chandoo! Thank you.
You are welcome Gill.. 😀
Excellent!
Many thanks!
Thank you very much for this amazing video.
Vaah Chandoo!
Chandoo, great work! Thank you for the content... Keep it going!
Great tips, thanks for sharing
Thanks for watching!
Nice one! Thank you so much.
Awesome techniques, much appreciated
Glad it was helpful!
Midway through the video and this is already the best excel video I've seen
Thank you so much. This is awesome and will be applied so often and will safe so much time!
You're so welcome!
Amazing. This guy is wizard.
Wonderful. Many thanks
You are welcome Erica... 😀
You are absolutely amazing. Thank you for your videos
This was great. Super useful tips. Thank you!
You are welcome Bryan...
Great video Chandoo! Loving your videos and learning excel.. Thanks!
wow, I learnt alot from this. it's so cool