Excel Crash Course - Data Cleaning in Excel - Microsoft Excel Tutorial
ฝัง
- เผยแพร่เมื่อ 25 เม.ย. 2019
- ✅ 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:
th-cam.com/users/simonsezittra...
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!
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
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
Thank you so much for all the wonderful videos you all produce. This one in particular is quite informative and helpful. Cheers.
This is perfect, short, and to the point lecture.
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 is an amazing explanation for Data Cleaning step by step. Thank you.
Glad you found it useful!
wonderful, well done, have been waiting for this video for ages....
Thank you and Glad you liked it!
You treated topics i have long searched for... Thank you!
This is exactly what I have been looking for, thank you so much.
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 very much for this wonderful crash course. you are AMAZING!
Thanks Hassan! I appreciate you watching and commenting!
This helped a lot in my internship, thank you so much!
Glad it helped!
Where
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 👉 th-cam.com/play/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc.html & here 👉 th-cam.com/play/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI.html
& Pivot Table training here 👉 th-cam.com/play/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u.html
I am grateful for this session. Very educative.
Very helpful and straight forward tutorial, kudos to Deborah Ashby 👍
Thank you very much! very pleased to hear you've enjoyed the tutorial!
This indeed is an excellent video. I cannot thank you enough.
You're very welcome!
Thank you! A good refresher for me as a Data Analyst.
Delighted to hear it Hurairah!
Incredibly useful video. Please do another on the same topic.
Thank you.
This was very helpful. Thank you!
Really Useful Well Explained Tutorial Thank You Deb :):):)
Glad you found it useful Darryl
this was incredibly helpful and easy to follow along, thank you
Happy to learn it was helpful, Saminu Hanni :-)
Thanks for your helpful data corrections🎉🎉🎉❤❤❤😊
This video is very clear and at a decent pace, better than most of the excel tutorial videos.
Glad you enjoyed! Thanks 👍
This review is incredible. Thank you so much.
Glad it was helpful!
Very well explained! Loved it!!
Earned a Subscribe!
Much appreciated. Thanks for watching!
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!
So interesting, You make excel look easy. Thanks
Thank you, Conrad! Cheers!
There should be more likes...you did a good job on explaining this!
Glad you liked it!
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!
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?
Thanks alot for this.. Its really helpful
again you rocks ma'am !!!
Thank you. Like it. Love it heartily.
Thanks for listening
You are a fantastic trainer!
thanks, much appreciated!
Thank you for this course
You're welcome 😊 Thank you for watching and commenting!
Thanks for this, I am grateful
You're very welcome
Thank you so much Debora
Happy to help Charles!
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 😊
WTF!!! "Flash Fill" wow. mind blown. now THAT'S how you end a video with a grand finale!
Many thanks Simon.
You are very welcome! 😃
Thank you for your lecture , wish the Excel file will be available . seems that I should pay money
Very nicely done video!
Thanks, glad you like the video!
thank you for the great content!!!
Glad you enjoy it!
Thakn you for this unique excel course. I was however unable to download the text file
Thank you so much!
No problem
Thank you Deborah!
You are so welcome!
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.
Thanks a lot this platform is great for learning
Glad you like it
This is very helpful. Thank you very much!
Glad it was helpful!
Very useful, thank you so much
I’m pleased this was helpful to you.
Excellent Explanation
Glad you liked it!
Thank you so much
Great tutorial, thanks
Glad you liked it
Awesome. Thank you so much.
Any time!
Thankyou . This was helpful.
Thank you for watching Kajal!
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 ?
wow
Amazing teaching techniques 😍
Thanks a lot 😊
Thanks a lot.🙂
A very wonderful tutorial
Thank you! Cheers!
very helpful , thank you.
You’re most welcome, glad you’ve enjoyed the tutorial!
Thanks so much for the video
No worries!
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?
THANK YOU!
Welcome!
Thank you so much 👌
Most welcome 😊
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.
wow!! thank you so much
No worries!
Very clever.
Thank you
This is really a great tutorial
Glad you think so!
new subscriber. thank you for he video
Thanks for the sub! And welcome, Anju :-)
Excellent presentation.
Glad you liked it! Thanks for watching !
thank you so much... that help my work👍👍👍
You're welcome!
The good thing about this one can always come back and recheck
Right on!
Amazing
Thank you Umberto
loved it
Glad you enjoyed it Jayesh Patil! If you need additional MS Excel tips, you can find them here -> th-cam.com/play/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI.html
do you suggest cleaning your data before merging into one sheet or afterwards ?
Awesome.
Thanks you, appreciate the feedback!
So fantastic
Thanks! Cheers!
Thank you.
You're welcome!
Best Video Ever
Thanks for watching ;-)
The best ever.
Thanks for watching!
Helpful tips for beginners, is the image sharp enough?
It is a useful tutorial.
Thanks! Delighted to hear it!
Awesome Video. I have an interview scheduled today. Lets see what happen!!
Good luck with the interview, Vicky!
@@SimonSezIT Thanks!!
Fantastic, I wish i had seen this years ago.
\
I'm glad it was useful!
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.
Thanks
You're Welcome!
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?
Thank you so much for this information. That helps me a lot.
Glad it was helpful!
Thank you for the tutorial, but I downloaded the data and it's already cleaned. Is there any chance to get the raw data?
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.
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"
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
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.
I have faced the same issue
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.
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.
The last example: or just use "Text to Columns".
Hello, can u upload all the Excel files that you've used here?
would you please share a link of the raw data that you've used for practice