Excel Crash Course - Data Cleaning in Excel

Excel Crash Course - Data Cleaning in Excel - Microsoft Excel Tutorial

Рет қаралды 233,449

5 жыл бұрын

✅ Download your free course exercise file here and follow along ➡️ www.simonsezit.com/article/ex...
In this Excel crash course on data cleaning in Excel tutorial, MS Office expert Deb Ashby shows you:
- Why it's so important to work with a clean dataset
- Some amazing tips and tricks to help you clean data in Excel including using Excel to trim text with the TRIM, PROPER and CLEAN functions.
- Resizing and formatting data to make it visually attractive
- Paste Values in Paste Special
- How to remove blank rows or populate blank cells across an entire workbook
- How to make the most of the Find and Replace and SUBSTITUTE formulas.
- How to fix date problems and format dates properly
- How to import data from an external source and how import using a delimiter.
✅ Get a free Microsoft Excel 2016 course here: ➡️ simonsezit.lpages.co/free-exc...
🌍 Get unlimited training with Simon Sez IT’s 150+ courses ⏩ www.simonsezit.com/courses/
🚀 Level up your skills with a free 30-day trial to our complete training library of 8,500+ videos ⏩ www.simonsezit.com/ssit-first...
💻 Watch more free popular training tutorials from Simon Sez IT:
🔥Check out our FREE 300+ hour training course playlist here ➡️
• Simon Sez IT Long Trai...
✅ Check out our free video playlist for Excel beginners 👉 • Microsoft Excel 2019 T...
✅ Pivot Table Tutorials for Beginners here 👉 • How to Use Microsoft E...
✅ Microsoft Excel Tips and Tricks 👉 • Microsoft Excel Tips a...
✅ Microsoft Office 2021 and Office 365 Training here 👉 • Microsoft Office 2021 ...
🔥Check out our FREE 300+ hour training course playlist here ➡️
• Simon Sez IT Long Trai...
💬Stay in touch!
SimonSezIT.com: www.SimonSezIT.com/
LinkedIn: / simon-sez-it
FaceBook: / simonsezit
🔔 Subscribe to our channel:
kzfaq.info...
If you enjoyed the video, please give a thumbs up 👍🏽 ;-)

Пікірлер: 219

@SimonSezIT 5 ай бұрын

✅ Download your free course exercise file here and follow along ➡ www.simonsezit.com/article/excel-crash-course-data-cleaning-in-excel-in-1-hour/

@rmhamilton0914 4 ай бұрын

Your description say fee exercise file. When I click on the link, I have to register for an account and pay for a subscription. I'm I missing something?

@bencipherx 3 ай бұрын

@@rmhamilton0914same here

@brajaybr 3 жыл бұрын

Clear, crisp and short. And the perfect pacing of the presenter makes it engaging! Thank you everybody behind this awesome video for the effort and for sharing it free for all!

@SimonSezIT 3 жыл бұрын

You are welcome Ajay. Thanks for watching

@anietiebrownson1916 2 жыл бұрын

Thank you for this tutorial. At first, I thought I was going to be overwhelmed but you delivered it in a clear, simple, and understandable way.

@SimonSezIT 2 жыл бұрын

We are so happy to hear that!

@ekanshirastogi7387 2 жыл бұрын

This is perfect, short, and to the point lecture.

@crazydrifter13 2 жыл бұрын

What an effective instructor. Very happy to see this tutorial. I was half asleep and still got it all.

@SimonSezIT 2 жыл бұрын

I’m glad you liked it!

@jugubitrus7295 2 жыл бұрын

You treated topics i have long searched for... Thank you!

@gunnar1032 2 жыл бұрын

Thank you so much for all the wonderful videos you all produce. This one in particular is quite informative and helpful. Cheers.

@sonyst7286 5 жыл бұрын

This is an amazing explanation for Data Cleaning step by step. Thank you.

@SimonSezIT 5 жыл бұрын

Glad you found it useful!

@gabrielayuel7897 2 жыл бұрын

This is exactly what I have been looking for, thank you so much.

@DivineLightFitness 2 жыл бұрын

As always, fantastic work on showcasing what can be done with Microsoft Excel! As a Data Analytics student, I really appreciate this video and the many other videos that help us to scrub our data more efficiently.

@SimonSezIT 2 жыл бұрын

Great to hear!

@robertocarimo5128 2 жыл бұрын

interested in doing the same course, could you suggest me a university that gives the same distance learning? Here Roberto from Mozambique

@hpourmamih 3 жыл бұрын

Thank you very much for this wonderful crash course. you are AMAZING!

@SimonSezIT 3 жыл бұрын

Thanks Hassan! I appreciate you watching and commenting!

@georgeaboh 2 жыл бұрын

I am grateful for this session. Very educative.

@grahamhilldrup1256 3 жыл бұрын

Thank you so much for providing this content and, for also providing the data downloads so that we can work through the examples.

@SimonSezIT 3 жыл бұрын

You are very welcome Graham! Enjoy!

@adenikeomogenaija Жыл бұрын

This was so good! I even used "flash fill" to separate the entire table. It took less than 1 minute. THANK YOU!

@SimonSezIT Жыл бұрын

You are welcome! Lots of additional free Excel tips here 👉 kzfaq.info/sun/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI & Pivot Table training here 👉 kzfaq.info/sun/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u

@0shawhat 3 жыл бұрын

This helped a lot in my internship, thank you so much!

@SimonSezIT 3 жыл бұрын

Glad it helped!

@riddhiaggarwal7477 2 жыл бұрын

Where

@Amrdys 4 жыл бұрын

Very helpful and straight forward tutorial, kudos to Deborah Ashby 👍

@SimonSezIT 4 жыл бұрын

Thank you very much! very pleased to hear you've enjoyed the tutorial!

@iwona2335 3 жыл бұрын

wonderful, well done, have been waiting for this video for ages....

@SimonSezIT 3 жыл бұрын

Thank you and Glad you liked it!

@olawuyikhadijat5838 2 жыл бұрын

This was very helpful. Thank you!

@CARLPERU 3 жыл бұрын

This indeed is an excellent video. I cannot thank you enough.

@SimonSezIT 3 жыл бұрын

You're very welcome!

@darrylmorgan 4 жыл бұрын

Really Useful Well Explained Tutorial Thank You Deb :):):)

@SimonSezIT 4 жыл бұрын

Glad you found it useful Darryl

@Hurairahgiri 3 жыл бұрын

Thank you! A good refresher for me as a Data Analyst.

@SimonSezIT 2 жыл бұрын

Delighted to hear it Hurairah!

@Godflakes 2 жыл бұрын

this was incredibly helpful and easy to follow along, thank you

@SimonSezIT 2 жыл бұрын

Happy to learn it was helpful, Saminu Hanni :-)

@TVSCDN 3 ай бұрын

Thanks for your helpful data corrections🎉🎉🎉❤❤❤😊

@yousefshokir6481 2 жыл бұрын

Incredibly useful video. Please do another on the same topic. Thank you.

@jamesdep8128 2 жыл бұрын

This review is incredible. Thank you so much.

@SimonSezIT 2 жыл бұрын

Glad it was helpful!

@joyboy1536 3 жыл бұрын

Very well explained! Loved it!! Earned a Subscribe!

@SimonSezIT 3 жыл бұрын

Much appreciated. Thanks for watching!

@frankbradford9616 2 жыл бұрын

WTF!!! "Flash Fill" wow. mind blown. now THAT'S how you end a video with a grand finale!

@Exceltech810 Жыл бұрын

This video is very clear and at a decent pace, better than most of the excel tutorial videos.

@SimonSezIT Жыл бұрын

Glad you enjoyed! Thanks 👍

@conradmusonda1258 2 жыл бұрын

So interesting, You make excel look easy. Thanks

@SimonSezIT 2 жыл бұрын

Thank you, Conrad! Cheers!

@qww760 3 жыл бұрын

You are a fantastic trainer!

@SimonSezIT 3 жыл бұрын

thanks, much appreciated!

@TunjungUtomo Жыл бұрын

Beside being a very solid and concise material, I like the sound setting that give impression that you're in the same room with me talking directly

@SimonSezIT Жыл бұрын

Glad you liked it!

@charlesmangezi9564 3 жыл бұрын

Thank you so much Debora

@SimonSezIT 3 жыл бұрын

Happy to help Charles!

@udarasamarathunga3458 3 жыл бұрын

Thank you for this course

@SimonSezIT 3 жыл бұрын

You're welcome 😊 Thank you for watching and commenting!

@hannakuo5418 3 жыл бұрын

Thank you Deborah!

@SimonSezIT 3 жыл бұрын

You are so welcome!

@duztv5370 4 жыл бұрын

Thanks for this, I am grateful

@SimonSezIT 4 жыл бұрын

You're very welcome

@kavitakamble3669 5 жыл бұрын

Very nicely done video!

@SimonSezIT 5 жыл бұрын

Thanks, glad you like the video!

@mridulsharma7201 2 жыл бұрын

fantastic explanation of the basics of data cleaning in excel without charging any fee..thats the sign of a true tutor(guru)

@SimonSezIT 2 жыл бұрын

Glad you liked it!

@ogunmiluyiomowumi8639 2 жыл бұрын

Thanks alot for this.. Its really helpful

@abdullahnaim10 4 жыл бұрын

Thank you so much!

@SimonSezIT 4 жыл бұрын

No problem

@lindad1418 2 жыл бұрын

There should be more likes...you did a good job on explaining this!

@SimonSezIT 2 жыл бұрын

Glad you liked it!

@odishaeducationalandtech8500 4 жыл бұрын

Thank you. Like it. Love it heartily.

@SimonSezIT 4 жыл бұрын

Thanks for listening

@sandipanmajumder9419 11 ай бұрын

again you rocks ma'am !!!

@getinettafesetucho7999 Жыл бұрын

Many thanks Simon.

@SimonSezIT Жыл бұрын

You are very welcome! 😃

@nehasabharwal15 3 жыл бұрын

thank you for the great content!!!

@SimonSezIT 3 жыл бұрын

Glad you enjoy it!

@originunknown4273 3 жыл бұрын

Great tutorial, thanks

@SimonSezIT 3 жыл бұрын

Glad you liked it

@KeebasLife 3 жыл бұрын

Awesome. Thank you so much.

@SimonSezIT 3 жыл бұрын

Any time!

@mikewera7533 Жыл бұрын

Thank you so much

@alaminh.3397 4 жыл бұрын

wow Amazing teaching techniques 😍

@SimonSezIT 4 жыл бұрын

Thanks a lot 😊

@quynhpham898 2 жыл бұрын

Very useful, thank you so much

@SimonSezIT 2 жыл бұрын

I’m pleased this was helpful to you.

@kajalkumari-cc7ve 2 жыл бұрын

Thankyou . This was helpful.

@SimonSezIT 2 жыл бұрын

Thank you for watching Kajal!

@amenaislam4573 3 жыл бұрын

very helpful , thank you.

@SimonSezIT 3 жыл бұрын

You’re most welcome, glad you’ve enjoyed the tutorial!

@gogrowgrace6468 2 жыл бұрын

This is very helpful. Thank you very much!

@SimonSezIT 2 жыл бұрын

Glad it was helpful!

@paulakujuobi1932 Жыл бұрын

Thanks a lot.🙂

@kingswilling7517 2 жыл бұрын

Excellent Explanation

@SimonSezIT 2 жыл бұрын

Glad you liked it!

@gmsubu 3 жыл бұрын

Excellent presentation.

@SimonSezIT 3 жыл бұрын

Glad you liked it! Thanks for watching !

@mkgeidam 3 жыл бұрын

Thanks so much for the video

@SimonSezIT 3 жыл бұрын

No worries!

@charles4463 Жыл бұрын

A very wonderful tutorial

@SimonSezIT Жыл бұрын

Thank you! Cheers!

@melanieachdjian6035 4 жыл бұрын

Debbie, Thank you for the great overview, what a great step-by-step. I have a question about deleting blank rows (around 09:00). I am using 365. I can find and highlight the blank cells using your method, but it highlights not only the rows in the table, but also all of the cells in the columns to the right of the table. In addition, the delete option within the cells box is grayed out. Any idea why I am not seeing what you are seeing?

@RK-zs5j6 Жыл бұрын

THANK YOU!

@SimonSezIT Жыл бұрын

Welcome!

@Idrizly Жыл бұрын

Thank you

@RohitKumar-xv4lq 2 жыл бұрын

Thanks a lot this platform is great for learning

@SimonSezIT 2 жыл бұрын

Glad you like it

@albertobulletti3419 3 жыл бұрын

wow!! thank you so much

@SimonSezIT 3 жыл бұрын

No worries!

@Pablo-pd2gr 2 жыл бұрын

Thank you so much 👌

@SimonSezIT 2 жыл бұрын

Most welcome 😊

@sreelekshmia7332 2 жыл бұрын

I'm in love with your accent ❤️ although I'm a beginner to data analytics so I believe that this would help me for sure! Thanks

@SimonSezIT 2 жыл бұрын

Thank you 😊

@Umb2344 4 жыл бұрын

Amazing

@SimonSezIT 4 жыл бұрын

Thank you Umberto

@Raaajzzz 2 жыл бұрын

This is really a great tutorial

@SimonSezIT 2 жыл бұрын

Glad you think so!

@TheJoshtheboss Жыл бұрын

Very clever.

@MarjanR77 4 ай бұрын

Thank you for your lecture , wish the Excel file will be available . seems that I should pay money

@RedBaron63650 2 жыл бұрын

The part of data cleaning on text manipulation is great !

@SimonSezIT 2 жыл бұрын

Glad you think so!

@RedBaron63650 2 жыл бұрын

@@SimonSezIT it really is. Please also read my comment below, if you have time, I would like to know if I am wrong or not.

@onenabeable 2 жыл бұрын

thank you so much... that help my work👍👍👍

@SimonSezIT 2 жыл бұрын

You're welcome!

@tamimhekmatpey2729 2 жыл бұрын

Thank you.

@SimonSezIT 2 жыл бұрын

You're welcome!

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

So fantastic

@SimonSezIT 2 жыл бұрын

Thanks! Cheers!

@JayeshPatil-nl5uc Жыл бұрын

loved it

@SimonSezIT Жыл бұрын

Glad you enjoyed it Jayesh Patil! If you need additional MS Excel tips, you can find them here -> kzfaq.info/sun/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI

@collinsangute301 2 жыл бұрын

thanks a lot for your tutorial, I really benefited from it, since I deal with a lot of data analysis in my course, back here in Kenya

@SimonSezIT 2 жыл бұрын

That is really good to hear! Glad you liked it.

@hapemokenela7388 4 жыл бұрын

Thakn you for this unique excel course. I was however unable to download the text file

@TapiwarudolphKanengoni 2 жыл бұрын

Awesome.

@SimonSezIT 2 жыл бұрын

Thanks you, appreciate the feedback!

@SantoshTiwari-rw6vc 2 жыл бұрын

Best Video Ever

@SimonSezIT 2 жыл бұрын

Thanks for watching ;-)

@mostaanhoseiniian4416 3 жыл бұрын

It is a useful tutorial.

@SimonSezIT 3 жыл бұрын

Thanks! Delighted to hear it!

@anju334 2 жыл бұрын

new subscriber. thank you for he video

@SimonSezIT 2 жыл бұрын

Thanks for the sub! And welcome, Anju :-)

@pokoknyamilan7536 3 жыл бұрын

So much thanks for your explanation, its help me a lot. So if I want to separate hire date, salary, dept, and job title at manual way using file ImportText1, I have to use the same way with the way to separate employee name? I mean, I have to create helper column to find where is semicolon (;) and count using formula LEN to?

@robertkinuthia4543 2 жыл бұрын

The good thing about this one can always come back and recheck

@SimonSezIT 2 жыл бұрын

Right on!

@makombe5809 2 жыл бұрын

The best ever.

@SimonSezIT 2 жыл бұрын

Thanks for watching!

@TheSacnep 4 жыл бұрын

Thank you for your tutorial. I am expecting the data cleaning tutorial with large and very big data set with challenge like missing values and date format in ~41245%

@granand 3 жыл бұрын

Hi Sachin Did you get any breakthrough in date issues ?

@naijatalkertive 3 жыл бұрын

Thanks

@SimonSezIT 3 жыл бұрын

You're Welcome!

@leighbrett 4 жыл бұрын

do you suggest cleaning your data before merging into one sheet or afterwards ?

@domdedable 3 жыл бұрын

Fantastic, I wish i had seen this years ago. \

@SimonSezIT 3 жыл бұрын

I'm glad it was useful!

@vickkyphogat 2 жыл бұрын

Awesome Video. I have an interview scheduled today. Lets see what happen!!

@SimonSezIT 2 жыл бұрын

Good luck with the interview, Vicky!

@vickkyphogat 2 жыл бұрын

@@SimonSezIT Thanks!!

@mbhla 2 жыл бұрын

Helpful tips for beginners, is the image sharp enough?

@marcelotapia7899 3 жыл бұрын

Thank you so much for this information. That helps me a lot.

@SimonSezIT 3 жыл бұрын

Glad it was helpful!

@leventis1991 3 жыл бұрын

Hello, I have an issue that i want your help with. I have a data set with multiple columns like the one in the example. My issue is that in some lines the entries are in the wrong column. For example, let's say that the name of the artist in the 10th row has been inputted in the "Genre" column and the genre of the album is in the "name" column. How can i make a new table that the name will be in the proper column. Please keep in mind that in each registry there is a character that specifies its type for example each name of the artist is something like "N_A Adele" or "N_A Bono" and "g_folk" or "g_rock"

@rafaujma7534 3 жыл бұрын

That's nice video. But what to do if one of the rows is missing something? For example genre. Excel would highlight that blank and you would have deleted that row altogether with all the data in it. How to avoid that? You can't scroll big file as it's time consuming.

@RedBaron63650 2 жыл бұрын

You are correct. I just commented about it. A single blank cell in a row with data, would case the row to be deleted and data would be lost.

@stewartdahamman 2 жыл бұрын

Either fill with zeros if appropriate for numerical blanks or fill with appropriate text using a formula that checks for contents in each cell, if it is correct- it provides the value in the cell, if false - it provides the text you wish to enter. You then select the newly generated column, copy and use paste values to replace the column with the blanks.

@Smuddpie Жыл бұрын

@@stewartdahamman I generally use find and replace to fill in blanks with zeros or “missing” or N/A as appropriate.

@Eduardo-cr8ri 2 жыл бұрын

Finally a working link to your spreadsheets 😅

@SimonSezIT 2 жыл бұрын

Most of the time, opening the link in a private window solves the problem. But glad this one worked out for you.

@aymanali8322 Жыл бұрын

Thank you for the tutorial, but I downloaded the data and it's already cleaned. Is there any chance to get the raw data?

@oddnumber8149 4 жыл бұрын

great video..i have a question..i import the txt file but some dates after applied value function return #value

@EminentJade 3 жыл бұрын

change format to dd/mm/yyyy and it will properly work, US format just ruined everything.

@soxpuff Жыл бұрын

I have a situation where I have 3 conditions (reasons) in 1 cell, under 1 field column that need to be tallied for reporting in relation to another field column. This data is manually entered on a form so there is no standard data to receive. Need to separate and standardize the data first. Your demo is helpful. And I'm considering having a new field column per condition. The problem lies in that there can sometimes be a variable number of conditions, they're not uniform. Is it best to add new rows under one field to tally the conditions, or new fields? How would that affect pivot table reporting? I assume if there are blank rows (but not in the field tallying the conditions) they won't show in the Pivot Table? The next question is how to automate that data clean up? If possible, via macros or power query?

@indraindo69420 4 жыл бұрын

Hey thank you for your amazing explanation. But, I experienced an issue to add "=value(text)" function to convert the dates into a numerical value as explained in 35.33, when I was trying to copy down the function to get a numerical value to each other dates the result appeared differently not like the result as appears on the video. The value for dates with slashes (exp:03/30/2013 on B2) went to "#VALUE!". Can you help me with this issue? I would much appreciate it

@dewopras6905 3 жыл бұрын

try to change 03/30/2013 to 30/03/2013. I think it's because of different format. The first read month/ date/ year but in another format of excel it reads date/ month/year.

@mohitbokade2691 2 жыл бұрын

I have faced the same issue

@sxgaston Жыл бұрын

How can I delete blank rows if I have other cells that are blank? Using your method of find and select special and choosing blanks it highlights 'All Blanks' not just empty rows. So if you have any other blanks in your data, say you didn't have a genre for Beatles in your example then select delete sheet rows it will also delete the row Beatles because it found a blank in that row.