10 Super Neat Ways to Clean Data in Excel

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

Просмотров 1,9 млн

50% 1

In this video, I will show you 10 simple ways to clean data in Excel.
Data forms the backbone of any analysis that you do in Excel. And when it comes to data, there are tons of things that can go wrong - be it the structure, placement, formatting, extra spaces, and so on.
Excel can be an amazing tool for data analysis. But we hardly get the data that can be used right away. And bad data leads to bad analysis.
Cleaning the raw data that you get in Excel will depend on what type and structure of data you get. There are a lot of things you can do before-hand to make sure your raw data get cleaned and ready for use.
Excel has a lot of formulas and functionalities that can help you clean your data. For example, you can use a formula such as TRIM to clean your data of leading, trailing, and double spaces. Or you can use the remove duplicate functionality to remove any occurrence of duplicate records.
The following data cleaning techniques are covered in this video:
0:00 Get Rid of Extra Spaces (leading, trailing, and extra spaces between words)
1:20 Select and remove All Blank Cells/rows
2:38 Convert Numbers Stored as Text into Numbers
5:00 Remove Duplicates cells/records
7:21 Highlight cells that contain Errors
9:24 Change Text to Lower/Upper/Proper Case to make it all consistent
10:54 Parse Data Using Text to Column
13:15 Spell Check (using a keyboard shortcut)
13:54 Delete all Formatting
14:33 Use Find and Replace to Clean Data in Excel
The data cleansing techniques covered in this video are some of the most common ones that you can use as soon as you get your raw data. Again, based on your dataset, you may need to add more to it.
Read the full tutorial here:
trumpexcel.com/clean-data-in-...
Also, I have made all of my Excel courses available for FREE. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
#Excel #ExcelTips #ExcelTutorial

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

12 июл 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@trumpexcel 3 года назад

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Here are some other Excel tips videos you may find useful: ✅ How to Delete Blank Rows in Excel - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2t6gEKjTqvI.html ✅ How to Remove Leading and Trailing Spaces in Excel - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-EGien2dwG9M.html Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course

@pa2happiness13 2 года назад

💞

@mahamohan1 6 лет назад

I have taken to learning excel on a full time after my retirement in May 2017. First it started as a hobby and now it has become an obsession since it gives me a lot of happiness. I almost spend 4 to 5 hours going through all the excel videos that come in RU-vid and practice each one of them. I can definitely say that the way you teach excel is simply fantastic. All the explanations are extremely crisp. Hats off to your work

@ashaydwivedi420 2 года назад

i was 8 years old when i watched this video. now im 15 and i wanna say that im the best at excel in the whole neighborhood, thank you sumit bansal

@Beluga747 4 года назад

0:08 Trim extra space 1:19 select blank cells. treat then all the same. 2:39 Convert numbers stored as text into numbers 4:59 Remove duplicates 7:21 Highlight errors 9:24 Change text to lower/UPPER/Proper case 10:54 break up concatenated text (e.g. street address) into separate columns 13:15 Spell check 14:33 Find and Replace 16:59 remove line breaks in text 17:47 Clean (out unwanted characters from) text data

@dimonovych 4 года назад

13:55 Deleting all formatting

@sophialawrence6880 4 года назад

For spell check when i pressed F7 , i am getting airplane mode on

@JohnDoe00Zero 4 года назад

@@sophialawrence6880 hit Fn + F7, Fn is your function key, located next to the 'alt' key.

@prakashsathyapriya 4 года назад

@@sophialawrence6880 press Fn+f7

@granand 3 года назад

Do you use some API function to get time stamps or painstakingly do it manually.

@muhammadhaider8561 4 года назад

There are many excel teachers in the RU-vid, but i must say the way you guys teach and summarise the functions are awesome. Well done and keep inspiring guys.

@keithau8159 3 года назад

Thanks for sharing,buddy.Learn a lot from your channels in easy and simple ways.Hindi:Kee sai ho?T-Koo.Can/t lai lee kai ho can/t lai nei he?Can/t lai nei he kai ah mei tao mei key lai dar hung.Au bag dai bar kar kar hei.Bo ho ma sai he hei.Tao lee tao lee,Hindi,Hindu art dee hei.Mei,Hindi,Hindo art dee hei?Mei India kam kee hei ,kar kee hei.Au bag dai bar kar kar hei,easy art dee hei.Bo ho ma sai he hei.Bar hei bo ho got me hei?Kam beer jar dar ban lee "B lai"Bar hei bo ho bar lish hei?Chut dar sure lure kee mut.Mei jar lai ha hung,Bo ho tek kee hung.Bar mei link it.Mei ban lee.So kar lee ah.Ug lai hip see dai mei link it.So kar lee ah.Doom ah char in shan who?Ji b must sai gar mut dee ho,doom Ma lee ma du jar kee ho.

@pa2happiness13 2 года назад

@@keithau8159 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--su8Z4j1p1A.html

@InsightfulJourney123 2 года назад

I am agreed!!👏

@CARLPERU 5 лет назад

Wow, this is excellent. Thank you for uploading this video, you have no idea how helpful this video is.

@simonafullinfaw9730 4 года назад

To the point and very precise. Didn't waste a single second. Thank you for providing this.

@keithau8159 3 года назад

@CH5092 6 лет назад

I've looked at about 15 excel videos and this one was the best and most clearly explained without excessive chit chat..This Professor rocks! thanks.

@thepeddler9226 5 лет назад

Wow. I'm speechless. There are a few I knew Excel could do (ex. caps/no caps) but couldn't find it anywhere since I couldn't explain it properly. Learned a lot. Keep up the good work. I like the Ctl-H thing too. Extra step with Ctrl-F... No fluff, no hesitation, right to the point. My kind of tutorial. Thanks for showing, Nicole from Canada

@sumayaasif62 2 года назад

This video is a jem on internet. God bless you brother.

@serdip 4 года назад

Excellent video! Thank you so much. When the cursor is inside a block of data, press Ctrl + A to select the block of data instead of dragging the cursor. When copying down a formula in a column adjacent to existing data, select first cell to be copied, hover mouse over that cell's lower right corner until it becomes a cross and then double click to automatically copy down the formula or content of the first cell into the cells beneath it down to the last row of data in the adjacent column. This technique is faster than dragging down the cursor over the target cells. Thank you kindly.

@ravisankar295 4 года назад

💐💐💐

@nengtech6914 4 года назад

Very nice tip

@parminderghai 4 года назад

Thank you. 🌻

@nurjayantianis6758 3 года назад

@@parminderghai c

@tinkerbrownie 4 года назад

I've been using excel for heavy analysis work for the past year. Even then, I have learned at LEAST 8 new things from your video. Wow. Thank you so much!

@lenac3587 5 лет назад

Simple everyday functions but never cease to learn something new to exploit different ways of using them. Really appreciate the versatility of Find/Replace feature that goes beyond finding and replacing text. Woah! 😲👏👏

@timbabitelor 3 года назад

I didn't know about using Ctrl + J in Find & Replace box.. thank you, that would be very very useful someday.

@ashokanselliah9571 4 года назад

Excellent. hugely helpful. Many thanks...

@eniitanoyesowo7159 Год назад

Thank you so much for this video. I have been struggling with data cleaning and most of the videos I have been seeing are not useful but this video solve it. thank you once again!

@qualityrenov 7 лет назад

I honestly cant thank you enough for this! Wonderful tutorial.

@zhangxinyi8997 4 года назад

I would say this is the most helpful video Ive watched recently although its already 2020! Thank you so much!

@st.charlesstreet9876 4 года назад

This really is one of the Best tips post for Excel functions!

@joestauffer 4 года назад

You have an excellent teaching style. Very complete instruction without the unnecessary blab you find on many RU-vid videos. Very knowledgeable, yet easy to follow and understand. Keep up the good work.

@mrinalbanik5159 5 лет назад

Fantastic video, those data cleaning techniques are extremely useful for our daily excel work...Great Mr. Bansal 👍

@flormurillo3661 4 года назад

I agree with you Mrinal, it's been an incredible class.

@shreya6115 Год назад

Hey, follow this guy. He has pivoted into data analyst and has really helpful videos ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-axTt_5Zr46M.html

@karimlike1 4 года назад

Excellent video !

@mohamadehtheshams2527 3 года назад

Bro you have EXCELLENT Knowledge on Excel , great

@Nabhishek 4 года назад

You are simply Amazing I am a Corporate Graphic designer and time time I receive Excel Documents where I require to do some tweaks and I always face problems, but now I know a place to go to where I get all the solutions with absolutely no nonsense. :)

@TG1181 4 года назад

Thank you for the excellent presentation. Everything is clear and concise.

@jasfradique 4 года назад

I don't usually comment on videos, but this time I have to say: great tips and amazing explanations! Thank you man, you rock!

@viveksharma2320 5 лет назад

Thanks bro, Its exactly what I was looking for Please do same for ms word Hindi language is always welcome Just read your story very nice Keep simple keep going

@ashwinantony7140 3 года назад

You have used "=TRIM" in your videos too. No nonsense no time waste. Thank you.

@jollygooddude 6 лет назад

Brilliantly presented, thanks

@riadsabbagh3970 5 лет назад

Thank you for such valuable tips

@umeshyeole123 2 года назад

Excellent......In very short time covered many important excel tools....Thank you

@puneetvij8433 5 лет назад

Sumit Bansal Sir, Really an impressive one! ; )

@nitinbhosale01 5 лет назад

Hello, I've not even reachedd half way but found your video to be crisp and to the point. Just enjoying this crash course. Pls keep making it.... Thank u

@meignanamurthy9795 3 года назад

One of the best videos on excel that I have ever watched

@cambellevans9945 3 года назад

If I could give this video another Like I would. I've saved it as a favourite and keep coming back to it. Awesome job; well done.

@trumpexcel 3 года назад

Glad you're finding the videos useful

@soe50060 6 лет назад

Thank you so much! Those non printing characters were proving a real headache.

@ZeeshanKhan-ld2ff 5 лет назад

Thank you, for sharing great tips! appreciate your help

@Hasanthaprasath 4 года назад

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

@rienthongw 5 лет назад

Thank you. It's very helpful.

@gunesbirinci1704 8 лет назад

The people who work with excel generallay, these hints and videos are very useful. Thanks.

@larifari7473 3 года назад

I just got your video and saw it only for one minute and I am exited about the first presented function! Excellent !!!

@Faithful_Tribe 2 года назад

Enjoyed that 'Parse data using text to column' method.

@aishwaryakk4369 2 года назад

Feels like found Treasure in RU-vid after many years , Great work

@Soulenergy31 8 лет назад

Thanks bro! Your are awakening my slept Excel power! Keep up with the outstanding work!!!

@deo700 5 лет назад

Wow! I don't know where you made this video but you helped me out here in Chicago just watching this.

@natavesta2010 5 лет назад

Thank you fir your clear, up to the point, video!

@amitgoyal9423 4 года назад

Thanks Sumit for this wonderful video, you have covered a lot real time scenarios in which I struggled and don't have answer how to easily fixed it

@ashokkhatri8577 4 года назад

wonderfull tricks.

@tahsin1st 8 лет назад

Thank You so Much for this amazing content.

@executiveengineerchitradur7542 2 года назад

Superb!!! Thanks Sumith. The way of teaching is excellent and with in a very short span of time.

@isldgrl2008 2 года назад

Straight to the point, covers each point thoroughly.

@kalangiprasad4073 4 года назад

one of the best RU-vid channel to learn Excel. Nice Explanation...

@TheRam425 4 года назад

Really helpful tips !!!! Thank you so much!

@mosadiq123 4 года назад

Wow, excellent. Thank you for uploading this video.

@MdIslam-zj6yt 5 лет назад

Thank you for your excellent techniques of teaching. Tis video is very useful.

@aaokuchhseekhe7458 3 года назад

Excellent video, very clear and precise

@srenyvas1 4 года назад

Simple and useful tips , Thank you very much

@rohitsingh9015 7 лет назад

I appreciate this.

@cdroze31 5 лет назад

I am so mad I didn't find this video sooner. Very helpful tips, thanks.

@isabellal.1727 4 года назад

Thank you so much for this video, very helpful!

@vipinamar8323 4 года назад

No nonsense! Some really great content in a relatively short amount of time.

@keithau8159 3 года назад

@subashchandrabowse2926 4 года назад

Wonderful and simple presentation! Good job Sumath!

@mehmeh9748 7 лет назад

Thank you! Very useful tricks and tips.

@nhtrungdaniel 4 года назад

VERY GOOD TIPS! I'm so happy to learn these new tricks. Thanks a lot and please keep it up!

@fconteEBdotcom 5 лет назад

Thank you for an informative tutorial.

@sachinrv1 5 лет назад

Very nice video. Many people are unaware of this Alt+G option which has got numerous useful things for the user. The another wonderful thing which I like; is Ctrl+Enter which fills data for the cells scattered in the data base. technically we call it RELATIVE CELL REFERENCE feature of excel; once again a very useful tool. Thanks for knowledge sharing :)

@PankajKumar-bd1hx 3 года назад

Very useful in day to day operations!

@sloggingdanish7945 4 года назад

You are a great and honest teacher - salute you -thanks once again.

@26k1ko8 4 года назад

Fantastic video, thank you! It will save me a lot of time

@rashidkhan4455 5 лет назад

Thanks so much for sharing (Super Helpful)

@natasha4851 7 лет назад

very useful video. You make Excel look so simple and helpful.. great work. thank you

@shreya6115 Год назад

Hey, follow this guy. He has pivoted into data analyst and has really helpful videos ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-axTt_5Zr46M.html

@jsoloughlin 4 года назад

Excellent short presentation of very helpful tips. Thanks you.

@richardmerritt5600 7 лет назад

Excellent, thanks

@wasimbader9170 8 лет назад

nicely done, easy to swallow and to the point, thank you for your hard work. thumbs up

@tinafosita3141 3 года назад

Just discovered this now, love it ! THANK YOU :D

@billpoon9017 4 года назад

Great tutorial..

@cambellevans9945 4 года назад

You are a star sir. So useful.

@narenderyadav1727 4 года назад

Very useful video boss.

@smitamodi9632 7 лет назад

Excellent video and explanation

@yuriykoretskiy6067 5 лет назад

Thank you for the video, good job!

@haiderabbasi5320 2 года назад

Excellent Presentation

@victorcox3227 4 года назад

Very neat useful tips, no faffing around straight into the "Functions"... very precise,very useful, very well done Sir!

@susansmart7211 4 года назад

This is excellent - thank you.

@MsAnupamsingh 6 лет назад

No words for the explanation. Thx

@santamills8638 4 года назад

Thank you so much. I think your lessons are very helpful

@Adi-jk2ef 4 года назад

simple and time saving tricks ! Thanks for sharing

@charmainebendanillo516 4 года назад

Thank you so much fir this very informative and helpful excel tips, Sir Bansal. :)

@MAVlogs505 4 года назад

Very helpful video thanks

@lindameredith1941 5 лет назад

I wish I'd found this video sooner! Great information.

@xanderdoegreat7110 5 лет назад

Wow... thanks Man... these tricks are so useful

@janechannel5588 2 года назад

Thank you.This really help me.More videos please.

@awfullawton4470 2 года назад

This guy is sharp and knows his stuff

@prakritimaheshwari4186 5 лет назад

Amazing video, very very helpful.

@guestoria 6 лет назад

Totally Awesome. I love this video!

@pthakker982 5 лет назад

Its super good, Even your voice is perfect pitched for this tutorial.

@stevenderrick2215 4 года назад

LMAO

@daowara85 2 года назад

I love your video and thank you very much for doing this!

@parikshitmohite 6 лет назад

Great job buddy!

@sabelozwane6917 5 лет назад

Great and clear explanation. Thanks!

@bluesage1528 4 года назад

Thank you! Great tips. Very useful.

@shdwctchr 3 года назад

Thank you for taking the time to create this video!

@merveilmeok2416 7 лет назад

Bravo! Bravo Sunil!