Excel Crash Course - Data Cleaning in Excel - Microsoft Excel Tutorial
HTML-код
- Опубликовано: 22 авг 2024
- ✅ Download your free course exercise file here and follow along ➡️ www.simonsezit...
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.lpa...
🌍 Get unlimited training with Simon Sez IT’s 150+ courses ⏩ www.simonsezit...
🚀 Level up your skills with a free 30-day trial to our complete training library of 8,500+ videos ⏩ www.simonsezit...
💻 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...
LinkedIn: / simon-sez-it
FaceBook: / simonsezit
🔔 Subscribe to our channel:
www.youtube.co...
If you enjoyed the video, please give a thumbs up 👍🏽 ;-)
✅ Download your free course exercise file here and follow along ➡ www.simonsezit.com/article/excel-crash-course-data-cleaning-in-excel-in-1-hour/
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?
@@rmhamilton0914same here
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.
We are so happy to hear that!
What an effective instructor. Very happy to see this tutorial. I was half asleep and still got it all.
I’m glad you liked it!
This helped a lot in my internship, thank you so much!
Glad it helped!
Where
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!
You are welcome Ajay. Thanks for watching
This video is very clear and at a decent pace, better than most of the excel tutorial videos.
Glad you enjoyed! Thanks 👍
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.
Great to hear!
interested in doing the same course, could you suggest me a university that gives the same distance learning? Here Roberto from Mozambique
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
Glad you liked it!
This was so good! I even used "flash fill" to separate the entire table. It took less than 1 minute. THANK YOU!
You are welcome! Lots of additional free Excel tips here 👉 ruclips.net/p/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc & here 👉 ruclips.net/p/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
& Pivot Table training here 👉 ruclips.net/p/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u
So interesting, You make excel look easy. Thanks
Thank you, Conrad! Cheers!
fantastic explanation of the basics of data cleaning in excel without charging any fee..thats the sign of a true tutor(guru)
Glad you liked it!
Thank you so much for providing this content and, for also providing the data downloads so that we can work through the examples.
You are very welcome Graham! Enjoy!
Thank you! A good refresher for me as a Data Analyst.
Delighted to hear it Hurairah!
Thanks for your helpful data corrections🎉🎉🎉❤❤❤😊
This is perfect, short, and to the point lecture.
This is an amazing explanation for Data Cleaning step by step. Thank you.
Glad you found it useful!
WTF!!! "Flash Fill" wow. mind blown. now THAT'S how you end a video with a grand finale!
wonderful, well done, have been waiting for this video for ages....
Thank you and Glad you liked it!
Very helpful and straight forward tutorial, kudos to Deborah Ashby 👍
Thank you very much! very pleased to hear you've enjoyed the tutorial!
You are a fantastic trainer!
thanks, much appreciated!
The part of data cleaning on text manipulation is great !
Glad you think so!
@@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.
You treated topics i have long searched for... Thank you!
Very well explained! Loved it!!
Earned a Subscribe!
Much appreciated. Thanks for watching!
There should be more likes...you did a good job on explaining this!
Glad you liked it!
The good thing about this one can always come back and recheck
Right on!
This review is incredible. Thank you so much.
Glad it was helpful!
Thanks a lot this platform is great for learning
Glad you like it
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
Thank you 😊
Thank you very much for this wonderful crash course. you are AMAZING!
Thanks Hassan! I appreciate you watching and commenting!
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%
Hi Sachin Did you get any breakthrough in date issues ?
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
That is really good to hear! Glad you liked it.
Thank you so much for all the wonderful videos you all produce. This one in particular is quite informative and helpful. Cheers.
Thank you for your lecture , wish the Excel file will be available . seems that I should pay money
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.
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.
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.
@@stewartdahamman I generally use find and replace to fill in blanks with zeros or “missing” or N/A as appropriate.
This indeed is an excellent video. I cannot thank you enough.
You're very welcome!
This is exactly what I have been looking for, thank you so much.
new subscriber. thank you for he video
Thanks for the sub! And welcome, Anju :-)
Thank you for this course
You're welcome 😊 Thank you for watching and commenting!
Many thanks Simon.
You are very welcome! 😃
Thankyou . This was helpful.
Thank you for watching Kajal!
Thank you so much Debora
Happy to help Charles!
Really Useful Well Explained Tutorial Thank You Deb :):):)
Glad you found it useful Darryl
Excellent Explanation
Glad you liked it!
I am grateful for this session. Very educative.
A very wonderful tutorial
Thank you! Cheers!
Awesome Video. I have an interview scheduled today. Lets see what happen!!
Good luck with the interview, Vicky!
@@SimonSezIT Thanks!!
Thank you for the tutorial. Nonetheless, when I click on the link to access the free course file, I must register for an account and select a subscription.
Thank you Deborah!
You are so welcome!
Very useful, thank you so much
I’m pleased this was helpful to you.
again you rocks ma'am !!!
this was incredibly helpful and easy to follow along, thank you
Happy to learn it was helpful, Saminu Hanni :-)
THANK YOU!
Welcome!
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?
Thanks so much for the video
No worries!
This was very helpful. Thank you!
very helpful , thank you.
You’re most welcome, glad you’ve enjoyed the tutorial!
Incredibly useful video. Please do another on the same topic.
Thank you.
Hello there, long time excel and number cruncher here. I have a comment and a question about clearing the blank (empty) rows, as you described in the video. The system works, but in my opinion there is a catch and the user need to be careful. What happens if the data set is large, and we don't have the time to scroll down and glance at it, and there aren't only blank rows, but also blank cells, within a row with data in it... I other words, for testing sake, let's assume that row 3 has cell C3 that is blank, but the rest of the row has cells with data in it, and we follow the steps outlined in the video, the presence of a single cell in a row otherwise with data, will cause the entire row being deleted, and we would lose data.
If we "blank" C3, (The Beach Boys) and use the Go To Special => Select => Blanks=> (all blank rows are selected AND C3 is selected as well, then as you say we go: Delete => Delete Sheet Rows .. row 3 and the lovely 1966 Pet Sounds album is gone.
The system outlined and explained in the video works *only* if we don't have any empty cells in a row with data.
Best Video Ever
Thanks for watching ;-)
Thank you. Like it. Love it heartily.
Thanks for listening
Thanks a lot.🙂
Thank you so much
It is a useful tutorial.
Thanks! Delighted to hear it!
The best ever.
Thanks for watching!
Great tutorial, thanks
Glad you liked it
Thanks alot for this.. Its really helpful
Thank you so much 👌
Most welcome 😊
This is very helpful. Thank you very much!
Glad it was helpful!
Very nicely done video!
Thanks, glad you like the video!
thank you for the great content!!!
Glad you enjoy it!
Thanks for this, I am grateful
You're very welcome
Thank you.
You're welcome!
Thank you
Amazing
Thank you Umberto
Thanks
You're Welcome!
Thank you for the tutorial, but I downloaded the data and it's already cleaned. Is there any chance to get the raw data?
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?
wow
Amazing teaching techniques 😍
Thanks a lot 😊
Very clever.
Thakn you for this unique excel course. I was however unable to download the text file
Thank you so much!
No problem
Awesome.
Thanks you, appreciate the feedback!
thank you so much... that help my work👍👍👍
You're welcome!
do you suggest cleaning your data before merging into one sheet or afterwards ?
This is really a great tutorial
Glad you think so!
loved it
Glad you enjoyed it Jayesh Patil! If you need additional MS Excel tips, you can find them here -> ruclips.net/p/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI
So fantastic
Thanks! Cheers!
Awesome. Thank you so much.
Any time!
Excellent presentation.
Glad you liked it! Thanks for watching !
nice tutorial but the file you provided was already...i dont know how to say this
the file was "solved" there was no mistakes in that file. i just assumed and did the same things you have showed in the video. thank you
Yes, we gave the model answer. Good feedback, maybe next time we'll give the unsolved file!
@@SimonSezIT Yes provide the file with errors next time please. As the only reason to download it is to go through the tutorial solving the errors for muscle memory. And we have this video for reference. Really clear and simple video though, good work.
Finally a working link to your spreadsheets 😅
Most of the time, opening the link in a private window solves the problem. But glad this one worked out for you.
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"
wow!! thank you so much
No worries!
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?
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.
Helpful tips for beginners, is the image sharp enough?
Thank you so much for this information. That helps me a lot.
Glad it was helpful!
great video..i have a question..i import the txt file but some dates after applied value function return #value
change format to dd/mm/yyyy and it will properly work, US format just ruined everything.
would you please share a link of the raw data that you've used for practice
The last example: or just use "Text to Columns".