Excel Crash Course - Data Cleaning in Excel - Microsoft Excel Tutorial
Вставка
- Опубліковано 5 вер 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!
This helped a lot in my internship, thank you so much!
Glad it helped!
Where
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!
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
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 video is very clear and at a decent pace, better than most of the excel tutorial videos.
Glad you enjoyed! Thanks 👍
WTF!!! "Flash Fill" wow. mind blown. now THAT'S how you end a video with a grand finale!
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 👉 ua-cam.com/play/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc.html & here 👉 ua-cam.com/play/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI.html
& Pivot Table training here 👉 ua-cam.com/play/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u.html
So interesting, You make excel look easy. Thanks
Thank you, Conrad! Cheers!
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 for your helpful data corrections🎉🎉🎉❤❤❤😊
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 ?
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! A good refresher for me as a Data Analyst.
Delighted to hear it Hurairah!
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!
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.
Thank you for your lecture , wish the Excel file will be available . seems that I should pay money
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.
This is an amazing explanation for Data Cleaning step by step. Thank you.
Glad you found it useful!
This is perfect, short, and to the point lecture.
Very helpful and straight forward tutorial, kudos to Deborah Ashby 👍
Thank you very much! very pleased to hear you've enjoyed the tutorial!
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?
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.
There should be more likes...you did a good job on explaining this!
Glad you liked it!
You are a fantastic trainer!
thanks, much appreciated!
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.
Very well explained! Loved it!!
Earned a Subscribe!
Much appreciated. Thanks for watching!
Awesome Video. I have an interview scheduled today. Lets see what happen!!
Good luck with the interview, Vicky!
@@SimonSezIT Thanks!!
The good thing about this one can always come back and recheck
Right on!
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?
You treated topics i have long searched for... Thank you!
wonderful, well done, have been waiting for this video for ages....
Thank you and Glad you liked it!
Thank you for the tutorial, but I downloaded the data and it's already cleaned. Is there any chance to get the raw data?
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.
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 😊
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
Thank you very much for this wonderful crash course. you are AMAZING!
Thanks Hassan! I appreciate you watching and commenting!
Many thanks Simon.
You are very welcome! 😃
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"
Thank you so much for all the wonderful videos you all produce. This one in particular is quite informative and helpful. Cheers.
new subscriber. thank you for he video
Thanks for the sub! And welcome, Anju :-)
Thankyou . This was helpful.
Thank you for watching Kajal!
Excellent Explanation
Glad you liked it!
Thank you for this course
You're welcome 😊 Thank you for watching and commenting!
Really Useful Well Explained Tutorial Thank You Deb :):):)
Glad you found it useful Darryl
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?
This indeed is an excellent video. I cannot thank you enough.
You're very welcome!
I am grateful for this session. Very educative.
This is exactly what I have been looking for, thank you so much.
Incredibly useful video. Please do another on the same topic.
Thank you.
Thakn you for this unique excel course. I was however unable to download the text file
Thank you so much Debora
Happy to help Charles!
A very wonderful tutorial
Thank you! Cheers!
do you suggest cleaning your data before merging into one sheet or afterwards ?
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.
Thank you Deborah!
You are so welcome!
would you please share a link of the raw data that you've used for practice
Thank you so much
THANK YOU!
Welcome!
This was very helpful. Thank you!
Thanks a lot.🙂
Thanks so much for the video
No worries!
Best Video Ever
Thanks for watching ;-)
wow
Amazing teaching techniques 😍
Thanks a lot 😊
this was incredibly helpful and easy to follow along, thank you
Happy to learn it was helpful, Saminu Hanni :-)
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
Hello, can u upload all the Excel files that you've used here?
very helpful , thank you.
You’re most welcome, glad you’ve enjoyed the tutorial!
Helpful tips for beginners, is the image sharp enough?
The best ever.
Thanks for watching!
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.
It is a useful tutorial.
Thanks! Delighted to hear it!
again you rocks ma'am !!!
Thanks alot for this.. Its really helpful
Thank you so much 👌
Most welcome 😊
Great tutorial, thanks
Glad you liked it
Thank you. Like it. Love it heartily.
Thanks for listening
Thank you
This is very helpful. Thank you very much!
Glad it was helpful!
Great video. Can you tell me why the FIND or LEN functions are giving me something like this =LEN([@Column2])
It means you are referencing a cell within a defined excel table. You can still change the cell reference with the actual cell address/number, eg. =LEN(A6), =LEN($A$6), by manually typing it in the formula.
@@SimonSezIT Thanks, that makes sense.
Thank you.
You're welcome!
Thanks
You're Welcome!
What we can do if we have a few empty cells?
Thanks for this, I am grateful
You're very welcome
Very nicely done video!
Thanks, glad you like the video!
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.
Amazing
Thank you Umberto
Very clever.
please i need the data sets for this tuotrial..how do i get it
thank you for the great content!!!
Glad you enjoy it!
thank you so much... that help my work👍👍👍
You're welcome!
Where can I get the course material to work with?
This is really a great tutorial
Glad you think so!
Excellent presentation.
Glad you liked it! Thanks for watching !
So fantastic
Thanks! Cheers!
Thank you so much!
No problem
Awesome.
Thanks you, appreciate the feedback!