Cleaning Data in Excel | Excel Tutorials for Beginners
Вставка
- Опубліковано 8 лип 2024
- Take my Full Excel for Data Analytics Course! www.analystbuilder.com/course...
Excel is one of the most used skills in the data world. In this series we will be walking through all of the most important topics that Data Analysts need to know in order to be proficient in Excel.
Excel Data Cleaning File:
github.com/AlexTheAnalyst/Exc...
Unlocked by Z by HP:
Click this link (www.clkmg.com/learnmedia/alex...) to participate in the data challenges and have a chance to win the prizes! And don't forget to RSVP for the hackathon on Saturday, March 12th from 7am PST/8:30am IST until 1pm PST/2:30am IST: hopin.com/events/hp-hackathon (Spots limited)
"No purchase necessary. Ends April 30, 2022. See official rules at www.hp.com/us-en/workstations/industries/data-science/unlocked-with-z/rules.html for how to enter, eligibility, odds, prize details, and restrictions. "
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON/MERCH
Patreon Page - / alextheanalyst
Alex The Analyst Shop - teespring.com/stores/alex-the...
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
Instagram: @Alex_The_Analyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
Thanks Alex. To sum up this video:
- Remove duplicate
- Edit name: use UPPER, Lower or Proper function
- Use filter to look for spelling errors
- Trim white space: TRIM function
- Issue with numerical data: currency, dates
This summary is helpful too who can not watch the full video.! keep it up man 👍
To hop in on the helping train, timestamps:
3:45 Remove duplicate
5:20 Edit name: use UPPER, Lower or Proper function
8:02 Use filter to look for spelling errors
10:42 Trim white space: TRIM function
12:20 Issue with numerical data - currency
13:50 Issue with date type data
15:52 Apply the formula after calculation (get rid of reference to cells)
THANKS MAN
Hey Alex, I know this isn't related to this video but it is your most recent one. I've watched a TON of your videos and have been teaching myself how to become a Data Analyst for the past year (with your help obviously). I just accepted an offer to be a Data Analyst at a company in my city and I just wanted to thank you for all of the information and advice you put out into the world. Your videos will have literally changed my life! Keep up the great work!
@Grant Cirks congratulations
Congratulations!!
I just wanted to ask if you did any online certification as well along watching his videos?
@@sodabaniazi6108 yes, I did the Google Data Analytics Professional Certificate
@@grantcirks362 send you a connection request
Me too! The portfolio is huge! Thanks Alex and congrats
Hi Alex, this is perfect timing from my perspective. I teach marketing analytics and one thing I emphasise to students is that it is important to be aware of data quality issues. The students are required to submit an assessment where they do basic cleaning of a dataset in Excel before they create pivot tables/charts and a dashboard. So, this video is perfect and I am sharing this with my students.👍
Your videos are a life changer! I never felt so confident in Excel until now! Keep making them!
I cannot thank you enough for this boot-camp! Awesome video again Alex, simple but yet still informative!
This video is exactly the type of content I look for. It helps me tremendously to be able to walk through a task with someone experienced and see the steps they take themselves to get to where they are going. On to the project!
Alex, I cannot thank you enough for this type of video.
Thank you for sharing your knowledge and experience with us.
Thanks Alex. This has been incredibly useful. Ingesting terrible data sets probably stops a ton of newbie data analysts and ruins their confidence. It certainly made me pause and question my grasp of the data analytics process. Sometimes a casual walkthrough and helpful tips like this video does wonders as it has for me. Thanks again.
Loved your tutorial on 'Cleaning Data in Excel' for beginners. You've got such a friendly and approachable way of explaining things. The step-by-step tutorial is super easy to understand. Thanks for the awesome tutorial - keep 'em coming.
another solid contribution by alex the analyst. your channel is a big asset to the data community!
Alex, I love all your videos and content. However, this video was my favorite of your Excel Bootcamp videos. I know the dataset was small and simplified for us, but I felt like you did a wonderful job breaking down the data cleaning process. So, the skills we learned in this video can easily be applied/transferred over to much larger datasets. I so appreciate everything you do for us. As always, THANK YOU!!
This was really helpful. First time on your channel. I have this very dirty big dataset I needed to work on and was confuse on how to drop irrelevant columns, it was soothing when I heard you say, I could trim the columns and delete any column that isn't relevant to my analysis.
Thank you👌
Great video. Agree with all points. For a new dataset (never seen before and not big data), knowing your shortcuts, you'd clean it much faster than r/python/sql etc. Excel although unsexy is very useful if the turnaround time is important.
Hi Alex, I am so grateful with what you pulled out here. Your videos has really helped me in my career as a data analyst. The decisions towards the skills I need as a data analyst and also the project I have to pull too that got me my first job. Thanks a lot
@Chikwendu Austin please do you work as a data analyst in Nigeria. I decided to switch to data analysis and am happy I came across this awesome channel.
Please can you tell me how long it took you to get a job in Nigeria (if you work for a Nigerian company) and where you found the job or how you applied. Thank you.
@@deraanagor1520 the company I work for actually were the ones that enrolled me in the training process to learn some basic skills…so I was retained as a data analyst.
Great one Alex. I have certainly become a better Data analyst thanks to your videos
The way you teach and make even any complicated task so simple to understand and learn is impressive!
"Paste as a Value" is just the functionality of excel that i need... thanks sir
Hi Alex, This was the best video for cleaning in Excel that I have come across in UA-cam. I learnt a lot of concepts with respect to what we need to do when it comes to data cleaning in any new data set...
Thanks for all your efforts Alex!!!
So awesome to hear that! Thanks for watching!
Really great Alex!!
Just one suggestion
we could have cleaned up Prior column to identify secretaries, vice-presidents etc.
So now we would also be able to answer questions like how many secretaries or VP eventually became presidents etc.
Alex, I am so grateful to you. Thank you so much for sharing your knowledge. I learned new Excel functions and your delivery of this important information was exceptional. Thank you Alex. This was a very helpful presentation.
Hi Alex, I even removed ' . ' in the name column using proper(substitute(text,".","")) formula. Tnx for these excel tutorials... i learnt a lot.
Found my new favorite UA-cam channel!
Thank you, and I appreciate your effort and the provided files for practice.
Thank you for this boot camp Alex. I enjoyed it such that I wasn't even aware of the time. I was wondering why the concluding message. Found it really interesting and gave me a glimpse of data cleaning. I now understand why a data analysts job involves more of cleaning than analysis. I am grateful I stumbled upon thi spage.
Hey Alex ,
Thanks brother I have been watching your video from many past months, I got a good job today and honestly I was never aware that excel jobs are also high paying I think it’s just because of your resources and my hardwork ❤
Keep up the content good 😊 god bless u bro
Thanks, Alex!
Resumindo este vídeo:
- Remover células duplicadas;
- Editar nome: use a função MAIÚSCULA, MINÚSCULA E E PRI.MAIÚSCULA;
- Usar o filtro para procurar erros de ortografia
- Aparar espaço em branco: função COMPACTAR
- Emissão com dados numéricos: moeda, datas
Thank you alex, and bravo for explaining the why behind use cases
I can't wait to see you do a full excel project!
I’m returning to a data analyst position after taking a few years off to do some other things. This was an excellent refresher. Thank you!
Hey, follow this guy. He has pivoted into data analyst and has really helpful videos ua-cam.com/video/axTt_5Zr46M/v-deo.html
Thanks Alex! It's on point and really helpful.
I’m studying Data Analytics, hands down extremely helpful
you are absolutely a gem and best teacher out there
Thank you Alex, you have really helped me in my data learning journey.
I had forgotten I did this video a few months back. But I did enjoy understanding the step by step process needed of what to take care first to look at and work from there.
Thank you Alex. I'm learning a lot from your videos.
On my journey through the boot camp...
I learned a lot in this one. Don't forget to back up your raw file!
tomorrow i have an interview for market research at japan company, one of the question is about cleaning data in excel. thankyou for making this video!! you helped me a lot! thank you so much! break a leg for me !
Simple yet effective things you can do to clean data in Excel, very helpful thank you
Quick trivia - the 'random number' at 15:15? It's the number of days since December 31, 1899.
This is how Excel handles dates and times. Each day has a value of 1.
Jan 1, 1900 = 1. 11/22/63 = 23337. I watched this video on 45202.
Times during the day are portions of one expressed as a decimal.
6:00 am is 0.25. Noon is 0.5.
The 1899 start date was chosen so that excel could recognize and work with any date in the 20th century. Unfortunately, negative numbers are considered out of range and generate an error message, so Excel cannot work with dates before 1/1/1900.
Cheers!
Love the videos!
Thank you Alex. This has been very useful
Thanks alex for being my bestie and also cleaning my confused mind into sorted . a big heart for you ❤️
Thanks Alex for the wonderful job your are doing. I learnt a lot from this video
I learned Ctrl-Y to go forward from this video which was cool. Two things I would have done differently is 1) standardize dates to yyyy-mm-dd 2) Kept decimals in salaries so it doesn’t get read as integers
Very thankful for your help with this Excel data cleaning.
Hey Alex, Great video ! ! Much appreciated.
i m starting in the game , thanks a lot for this beginner videos, they re straight to the point and really helpful¡¡¡¡¡
I wish i watched this before my job interview, it would have helped with everything!
Great video again, Alex.
Next - Full project
Thank you so much Alex ❤
Thanks Alex, you are the best.
VERY USEFUL THANK YOU
Thanks for your effort keep up the great work
This video came right on time, thank you Alex
Sure
Thank you Alex.
Thank you for this video. I am taking the data analytics course through CareerFoundry and this was a helpful reference.
Hello Alex. I am an aspiring data analyst and I find your videos very helpful. :)
Please make a video about work flow. 😀😀
Thank you! 😀😀
Hi Alex, thank you for all the content you provide it has really been helpful. Could you by chance share a video on how to clean up massive amounts of data that actually needs to be organized prior to being properly tabulated, that has a lot of white space and black rows etc? Many of the videos online are just for beginners to advanced with data thats already somewhat clean and takes 4-5mins to get ready.
If your using Excel for your data manipulation I'd recommend turning all data sets into a table. Most of what you're explaining is auto corrected in the table conversation process. That and the file will be 90% smaller (without having to make it binary) if saved in a table with defined formates per column. Just my two cents man.
Amazing video!
Thank you so much!
I was the 1K th like. :) So happy that this video exists! Thank you so much. You make Excel fun. :)
Woow Alex thank you so much for this video, you have clearly taught me and for sure am gaining skills. thank you keep it up👋👍 you are such an excellent teacher👌
thankyou for this video! extremely insightful video
Very nice presentation!!! 🎉
Thanks, this was great! Very explanatory and to the point. As someone who is self-studying to become a Data Analyst, your content is a life saver.
So glad to hear it!
Hey, follow this guy. He has pivoted into data analyst and has really helpful videos ua-cam.com/video/axTt_5Zr46M/v-deo.html
Thanks for the video Alex. Kindly do a video on using Power Query to clean data.
Thanks so much, little changes make it clean 👌
Thanks Alex!
Great Video Alex!
Its a really helful tuitorial. I subcribed right away the minute it helped me with my problem. Thank you for your expert advice.
great summary of data cleaning
Thanks Alex. Let's learn other cleaning excel functions.
Thanks Alex.I really larnt a lot
Thanks Alex!!
I loved this! This helped a lot :)
Amazing content Alex , it would be great if you could post few videos about ETL.
thanks Alex
A couple of things I do when I'm working on files similar to this is first duplicate the worksheet so that my original is all the way to the left, and this stays untouched. I also check on what my client wants in terms of data formats like dates, which as you said are very fussy. Somebody recommended going with YYYY/MM/DD as a date standard, but I'd say go with what your client or boss tells you. Thanks for sharing!
And as somebody mentioned below, I also almost immediately convert my data set into a colored table so I can more easily see blanks and such, as well as remove dupes and the like. Thanks!
thank you so much , your videos are educative . im glad to be your student
Thank you!!! Plz make more videos 😇
I knew these but this video was still helpful.
Thanks for this
thanks alot. it really helped me
great video Alex. Thaank you
Waaaw, I really appreciate that good work. Thank you✌🏾
Great analytical skill⚡
Great video Thanks a Ton
Thank you.
Great job!
very helpful, thanks alot
Great video!
thanks alex
Hi Alex, great videos! Is there a more effective way to do find and replace. If you have a dataset similar to this one but larger and you have maybe 10 presidents' name that you have to constantly change, is there a quicker way to update their names besides find and replace? Thanks. Keep up the good work
Thank you !
!
Thanks!
Thank you for this video. I'm taking Google Analytics Certification with coursera.
this was really help, I'm really grateful for this. I'm starting o feel like an expert in excel. woohoo!!!!
amazing work
Sweet and shot 😍
Hi Alex, this was very simple and beginner friendly data cleaning can you make one advanced level data cleaning video?
Thank you for taking time out to teach us these skills 🥹really appreciate