Cleaning Data in Excel | Excel Tutorials for Beginners

Рет қаралды 473,671

Күн бұрын

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

Пікірлер: 294

@aonamphuong4139 2 жыл бұрын

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

@harshnaidu7656 Жыл бұрын

This summary is helpful too who can not watch the full video.! keep it up man 👍

@mj-fm4bp Жыл бұрын

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)

@nehpets_enem 6 күн бұрын

THANKS MAN

@grantcirks362 2 жыл бұрын

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!

@ugochibenitazita4209 2 жыл бұрын

@Grant Cirks congratulations

@sodabaniazi6108 2 жыл бұрын

Congratulations!! I just wanted to ask if you did any online certification as well along watching his videos?

@grantcirks362 2 жыл бұрын

@@sodabaniazi6108 yes, I did the Google Data Analytics Professional Certificate

@sodabaniazi6108 2 жыл бұрын

@@grantcirks362 send you a connection request

@dongyan8717 2 жыл бұрын

Me too! The portfolio is huge! Thanks Alex and congrats

@polokomonama6526 Жыл бұрын

I cannot thank you enough for this boot-camp! Awesome video again Alex, simple but yet still informative!

@book_tudee Жыл бұрын

Your videos are a life changer! I never felt so confident in Excel until now! Keep making them!

@victorbegnini5754 8 ай бұрын

Alex, I cannot thank you enough for this type of video. Thank you for sharing your knowledge and experience with us.

@davidblackman7478 Жыл бұрын

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!

@saalemsadeque9516 2 жыл бұрын

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.👍

@searchbug 7 ай бұрын

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.

@evilrobots 7 ай бұрын

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!

@mmlewis1030 Жыл бұрын

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.

@sj1795 5 ай бұрын

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!!

@nickarrigo5540 2 жыл бұрын

another solid contribution by alex the analyst. your channel is a big asset to the data community!

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

The way you teach and make even any complicated task so simple to understand and learn is impressive!

@arcadevampire 2 жыл бұрын

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.

@awedaoluwanifemi8024 2 жыл бұрын

Great one Alex. I have certainly become a better Data analyst thanks to your videos

@chikwenduaustin2141 Жыл бұрын

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

@deraanagor1520 Жыл бұрын

@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.

@chikwenduaustin2141 Жыл бұрын

@@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.

@MyteeBumbleBee 6 ай бұрын

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.

@vivianemeli Жыл бұрын

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👌

@Tina-gp4rg Жыл бұрын

Thank you, and I appreciate your effort and the provided files for practice.

@equiwave80 Жыл бұрын

Hi Alex, This was the best video for cleaning in Excel that I have come across in KZfaq. 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!!!

@AlexTheAnalyst Жыл бұрын

So awesome to hear that! Thanks for watching!

@sunsetwithmee 5 ай бұрын

Hi Alex, I even removed ' . ' in the name column using proper(substitute(text,".","")) formula. Tnx for these excel tutorials... i learnt a lot.

@maitreyasharma 2 жыл бұрын

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.

@kirktheruler5572 Жыл бұрын

Thank you alex, and bravo for explaining the why behind use cases

@ryanj748 Жыл бұрын

Found my new favorite KZfaq channel!

@saoirb98 6 ай бұрын

Simple yet effective things you can do to clean data in Excel, very helpful thank you

@habibmaryam Жыл бұрын

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.

@meshInfo-Power 4 ай бұрын

Thank you Alex, you have really helped me in my data learning journey.

@edwingultom9362 Жыл бұрын

Thanks Alex! It's on point and really helpful.

@nathanielbalmes6597 4 ай бұрын

Very thankful for your help with this Excel data cleaning.

@nosecretsfishing3661 2 жыл бұрын

I can't wait to see you do a full excel project!

@tanuibrenda5511 8 ай бұрын

Thank you Alex. I'm learning a lot from your videos.

@queilamarques4646 Жыл бұрын

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

@rayyanzaidi3323 4 ай бұрын

you are absolutely a gem and best teacher out there

@isooloyede6919 2 ай бұрын

Thank you Alex. This has been very useful

@loslerner 2 жыл бұрын

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!

@shreya6115 Жыл бұрын

Hey, follow this guy. He has pivoted into data analyst and has really helpful videos kzfaq.info/get/bejne/l96Ep8Jmv9iYZoE.html

@manumaravilla Жыл бұрын

"Paste as a Value" is just the functionality of excel that i need... thanks sir

@amehagada2846 2 жыл бұрын

Thanks Alex for the wonderful job your are doing. I learnt a lot from this video

@AljIsHere128 2 жыл бұрын

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.

@Training_NRFIT Жыл бұрын

i m starting in the game , thanks a lot for this beginner videos, they re straight to the point and really helpful¡¡¡¡¡

@laneferm3539 Жыл бұрын

Hey Alex, Great video ! ! Much appreciated.

@AjStyles-ix6lq 10 ай бұрын

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

@ItsGibb 4 ай бұрын

Its a really helful tuitorial. I subcribed right away the minute it helped me with my problem. Thank you for your expert advice.

@nabatanzijovia5328 Жыл бұрын

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👌

@brianbc19 Жыл бұрын

Thanks Alex. Let's learn other cleaning excel functions.

@asmitatiwari819 Жыл бұрын

thankyou for this video! extremely insightful video

@TheTriangleOffense47 2 жыл бұрын

This video came right on time, thank you Alex

@shabanirashidi1263 2 жыл бұрын

Sure

@albedoguardian875 8 ай бұрын

I’m studying Data Analytics, hands down extremely helpful

@theabominabletrollman5763 6 ай бұрын

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

@larissasantos9354 3 ай бұрын

Amazing video! Thank you so much!

@Deekshi-ej8wc Ай бұрын

Thanks alex for being my bestie and also cleaning my confused mind into sorted . a big heart for you ❤️

@AkporHari Жыл бұрын

Great video again, Alex. Next - Full project

@stelios278 2 жыл бұрын

Thanks for your effort keep up the great work

@alfonsocordovacruz5244 21 күн бұрын

Very nice presentation!!! 🎉

@uesleicristian 8 ай бұрын

Thanks Alex, you are the best.

@lgminsight4875 10 ай бұрын

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.

@carvelialevi 16 күн бұрын

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 !

@StrumpanzerFuhrer Жыл бұрын

I wish i watched this before my job interview, it would have helped with everything!

@sasas2942 2 жыл бұрын

I was the 1K th like. :) So happy that this video exists! Thank you so much. You make Excel fun. :)

@joeltochukwu4199 2 жыл бұрын

Thanks for the video Alex. Kindly do a video on using Power Query to clean data.

@adwaamohamed2674 11 күн бұрын

VERY USEFUL THANK YOU

@danielblum5691 Жыл бұрын

Thank you for this video. I am taking the data analytics course through CareerFoundry and this was a helpful reference.

@woljin 2 жыл бұрын

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.

@xiskagaming3374 Жыл бұрын

Thanks so much, little changes make it clean 👌

@emmanuelorazulike2288 8 ай бұрын

thank you so much , your videos are educative . im glad to be your student

@berevaji5372 2 жыл бұрын

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.

@AlexTheAnalyst 2 жыл бұрын

So glad to hear it!

@shreya6115 Жыл бұрын

Hey, follow this guy. He has pivoted into data analyst and has really helpful videos kzfaq.info/get/bejne/l96Ep8Jmv9iYZoE.html

@joe_va 3 ай бұрын

Thank you so much Alex ❤

@darkangelus1987 2 жыл бұрын

great summary of data cleaning

@WaayoArag. 6 ай бұрын

Waaaw, I really appreciate that good work. Thank you✌🏾

@johannpauldalingay7329 Жыл бұрын

Hello Alex. I am an aspiring data analyst and I find your videos very helpful. :) Please make a video about work flow. 😀😀 Thank you! 😀😀

@sylvalineiroegbulem 9 ай бұрын

thanks alot. it really helped me

@castrolisole3773 4 ай бұрын

Great analytical skill⚡

@iheomaokam958 Жыл бұрын

great video Alex. Thaank you

@ElliottRopell 7 ай бұрын

I loved this! This helped a lot :)

@danielhutchinson6158 Жыл бұрын

Thank you Alex.

@haotran2314 2 жыл бұрын

Thank you!!! Plz make more videos 😇

@missy163 Жыл бұрын

Thanks Alex.I really larnt a lot

@ashhardamda 2 жыл бұрын

I knew these but this video was still helpful.

@ayanaxhye 9 ай бұрын

Thank you for taking time out to teach us these skills 🥹really appreciate

@fernandoherranz4095 2 жыл бұрын

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!

@fernandoherranz4095 2 жыл бұрын

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!

@ShubhamChawla 2 жыл бұрын

Great Video Alex!

@pseudo-black6343 6 ай бұрын

Thank you for your endless effort for making this amazing learning video.

@mitulsp8873 6 ай бұрын

where did you find the data set?

@pseudo-black6343 6 ай бұрын

Hey, you can get it from the description

@arabcode-7976 28 күн бұрын

THANK YOU SO MUCH

@louisadibe3189 6 ай бұрын

Great job!

@Q8Patriot 2 жыл бұрын

very helpful, thanks alot

@ajaycb590 Жыл бұрын

Amazing content Alex , it would be great if you could post few videos about ETL.

@haniehsartipi9700 Жыл бұрын

Great video Thanks a Ton

@marcet2022 Жыл бұрын

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