Data cleaning in Excel - 10 tricks *PROs* use all the time

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

[@mrmusicloverable]

Here is a dead simple way...Lol. I love this guy's videos. Great job Chandoo.

[@7sevens]

Chandoo you’ve been the OG of excel teaching for years - great to find you on KZbin. And thanks for the humble way you’ve shared your spectacular skills all these years!

[@chandoo_]

Thank you Seven. 😀

[@planxlsm]

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

[@Jupiter_176]

Thank you for these time stamps

[@Miguel-zz1vv]

Thank u sir

[@kishorekumark5621]

@@Jupiter_176 sambh

[@ameeriqbal6090]

Thank you, Chandoo. I got the recommendation from one of my colleague in Manila. Very simple and effective presentation.

[@chandoo_]

Glad it was helpful!

[@johnroberts1734]

OMG - this is the very best Excel video ever. Need to watch like 5 times and takes note. Gold found here.

[@chandoo_]

Wow, thanks!

[@kelvinwambua9791]

Chandoo has become my lecturer in my journey of becoming an analyst, learning as I practice is the best experience. Thanks, Chandoo.

[@purplecrayon7281]

Hope you know that you saved a lot of lives with this video. Great content.

[@malchicken]

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.

[@chandoo_]

Good tip!

[@peterlindner3283]

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.

[@jimminrod1567]

Great video. I added it to several playlists just so I know I can find it quickly for reference. Thanks.

[@ExcelWithChris]

Number 6 the BEST one! Once again brilliant stuff from the master.

[@chandoo_]

Thanks Chris... :)

[@yveslitalien]

I just have one word for this video: wow! I guess I could add 'thank you' for all those great tips too!

[@chandoo_]

You are so welcome!

[@yushirievanhaaght4673]

Awesome video! Bonus trick #2 is just legendary.

[@cjimmer4877]

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.

[@chandoo_]

Great tip! The old Text Import wizard saves the day !!!

[@FTube2000]

Value() or Multiply by 1 also work. Great video thanks!

[@geniecy3117]

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 👏💪🤟

[@chandoo_]

That's awesome! Welcome and more power to you. 😀

[@JuanKis]

Fantastic! I have been using Excel for years and these tecnique will improve A LOT my skills for Data Cleaning. Thank you so much!

[@searchbug]

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. :)

[@WinPureDM]

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.

[@Rex-dr3jm]

Never skip ads when watching his videos. Dead simple way to thank Chandoo 😉.

[@JacobChoi92]

You're always awesome!!!.. Thanks..

[@aliubisiriyu8145]

This was awesome. Especially that of unstacking data

[@barttitulaerexcelbart9400]

Woahh! nr 6: stacked columns, what a great and easy way to fix this! well done Chandoo!

[@chandoo_]

I know, right?

[@MaMuNAcademY]

@@chandoo_ how to make it dynamic sir?

[@staypositive4358]

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.

[@vikashaldar982]

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.

[@ExcelWithChris]

@@vikashaldar982 agree, but it is going to take longer.

[@livefognik]

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!!!

[@olliwest7341]

Great video and the "Monika" with the "k"... Priceless!! 😄

[@medasbabu]

liked the way you got file name from full path, this is one thing we need on daily basis,, thanks, loved it

[@dsminhkhoa]

Amazing! Thanks, Chandoo!

[@shenry25]

Very useful tips, and clearly explained. Many thanks

[@jpemanahan8699]

Happy to find you in youtube. you ease my burden of extracting, cleaning and analyzing data for my report. Salamat po.

[@CMHCVG]

Flash fill, awesome! thank you!

[@felipesignorellireis7839]

Great trick (the last one) Chears from Brazil

[@Rice0987]

Thank you, Chando! So simple and so powerful! :)

[@chandoo_]

Glad it was helpful!

[@CircuitClash]

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.

[@isaid5098]

Great video, nice and easy explanation! Thanks you

[@grg1000]

Awesome! Thank you!

[@JJ_TheGreat]

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!

[@relax9286]

This was awesome! Thank you!!!

[@kuldar.]

One of the best Excel tutorial!

[@chandoo_]

Wow, thanks!

[@BryonGaskin]

Great work. Super simple.

[@_KING_SHADOW]

Most of them i know as financial guy but few are unique. Thank you Chandoo. GOD BLESS YOU.

[@chandoo_]

Thanks Nitin...

[@rogerlima8961]

Chandoo, great work! Thank you for the content... Keep it going!

[@TS-yy6jb]

Knew these features,but the way you combined these simple features were amazing

[@chandoo_]

Thanks High Priestess :)

[@ICIVALMI]

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 KZbin channel as well!!

[@chandoo_]

Happy to hear that!

[@swapnil988]

OH MY GODD... You have no idea how valuable the bonus trick #2 is. I'm literally crying after finding the solution to this probelm 😭

[@chandoo_]

Wow.. Awesome to hear that Swapnil.

[@09shirish]

OSM, thanks for valuable tutorial !

[@JmoneySra]

Midway through the video and this is already the best excel video I've seen

[@hemanthkumar-ll3fl]

Simply superb chandhu Anna❤🎉

[@nehamulik4461]

Finding your channel is literally blessing for me thanks lot!❤

[@mukultyagi3737]

Chandoo sir, you are my lifeline... I can't imagine using the excel without your videos. Marvelous job !!! Love from India.

[@davejkt1]

Thank you for your tips.

[@naarfviews9617]

Nice tutorial. Thanks so much.

[@ruthngmj]

Great tips!!!!!!!!

[@syedmohammedmustafa2475]

Thank You So much, I have always struggled with Numbers in Text Format, but after watching the video I have got the solution.

[@monishnagarajan5917]

Informative and worth watching ❤️

[@DrunkenKnight71]

Some great tips there, thank you for sharing!

[@chandoo_]

Thanks for watching!

[@satenderrandhawa2803]

Awesome. Loved it

[@jonurwin1]

Great video, thanks!

[@chandoo_]

Glad you liked it!

[@dhikeshmm620]

I really enjoy learning excel from your videos.... very simple and quick to understand..Thanks

[@vishwanathstorm]

Found your channel today. Your method of teaching is amazing.

[@svetievboris]

Thank you!

[@sowmyaraykar874]

Thank you. I learnt +go to,clear,flex,format operation devide,multiply,add

[@clausschafer8236]

Thank you so much. This is awesome and will be applied so often and will safe so much time!

[@chandoo_]

You're so welcome!

[@IvasensMathsAcademy-fg9dm]

You are absolutely amazing. Thank you for your videos

[@muhammedasifkhurshid1156]

Great explaination.. thanks and regard.

[@ZaiqaEMashriq]

Amazing. This guy is wizard.

[@shoppersdream]

Nice, thanks!

[@stephanweaver1960]

Some elegant built-in alternatives to using mid() and find(). The one to extract numbers is awesome

[@chandoo_]

Glad you found those useful.

[@madhurig3974]

Brilliant Video !!Thanks a lot for sharing such wonderful tricks.

[@sentikironde9543]

Dude - your tips are awesome! I tried each one, they work and you explanations are easy to follow. Thank you so much

[@hazemali382]

always Simple and great ♥

[@ksumar]

Nice one! Thank you so much.

[@mxrvxn08]

wow, I learnt alot from this. it's so cool

[@mmarkhughes]

Thank you very much for this amazing video.

[@harkeshsharma3264]

Nice ways, thanks for sharing

[@elainev670]

Seriously good!

[@sharadsrivastava1043]

Thanks for these really useful tips 👍

[@RalphSkyView]

Thank you so much for showing us these smart solutions in Excel.

[@abdulbasital-sufyani6828]

Thank you so much man Very useful and well presented

[@SanthoshKumar-xy3zm]

Thank you very much.

[@magdahassib694]

Thank you so much, you are distinguished and always presents new information

[@canirmalchoudhary8173]

Really cool tricks... Flash fill and finding color formatted cells.

[@chandoo_]

Thanks Nirmal...

[@mohamadhadid2403]

One of the best

[@maher-s5515]

Great Done...

[@Donkeys_Dad_Adam]

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

[@chandoo_]

Wow... that is a good tip Adam. Donut for you 🍩

[@Donkeys_Dad_Adam]

@@chandoo_ you are too kind Sir

[@pravinpathade3237]

It's very helpful,, thanks

[@truepenny2514]

Phenomenal tips, thanks!

[@chandoo_]

Glad it was helpful!

[@tomjun8945]

Very helpful video. Thank you for all your contents!

[@ryanyang2486]

Very good video. Very concise.

[@markmoreno2286]

This is excellent. Awesome job

[@chandoo_]

Thank you very much!

[@barbieblack1043]

this is awesome!!!👍👍👍

[@timmytesla9655]

Great video. I learnt a lot. Thank you.

[@aq2237]

Zabardast 👍

[@JunaidKhan-gq8nw]

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.

[@chandoo_]

You are most welcome

[@ahmedalsherif4451]

Excellent Excel tutorial! Keep it up 🦾🦾

[@rachelirene8819]

Thanks Chandoo... your vids helps me.

[@Tapzzzzz_007]

Thank you so much

[@smartphoneuser791]

Wow. You made it so simple. Thank you

[@chandoo_]

You’re welcome 😊

[@RajendraPatel.]

Smart excel tips .!

[@chandoo_]

Thank you Raj...

[@adaezeo1574]

These are really powerful tips. Thanks

[@chandoo_]

You're so welcome!

[@saching24]

Thanks for the upload

[@ganeshoptom]

Wow, looks like you have a lot of experience especially #10 thank you. So impressed will follow your videos. Earlier used to follow Leila

[@chandoo_]

Welcome aboard! Leila is a great instructor.