Data Cleaning in Excel | 8 Tricks to Make You a Pro User
ฝัง
- เผยแพร่เมื่อ 29 มิ.ย. 2024
- Learn to clean any data using these 8 data cleaning tricks.
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video:
view.flodesk.com/pages/634964...
Learn to clean any set of data using these 8 steps:
1. Text to Columns to separate text
2. Remove Duplicates with unique formula and replace feature
3. Proper to remove inconsistent capital letters
4. Remove spacing with trim formula
5. Flash fill to automatically separate city and country
6. Changing numbers to percentages
7. Text to values for further calculations
8. Removing blank cells from a database
For these steps, we try make it realistic by creating a scenario where we're working at Amazon as analysts and need to send this to our manager.
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📚 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑💻 LinkedIn - / careerprinciples
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Separating Text
1:55 - Removing Duplicates
3:49 - Letter cases
4:38 - Spacing fixes
6:18 - Splitting text with Flash Fill
7:02 - Difficulties with percentage formats
8:20 - Text to Number
10:04 - Removing Blank Cells
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
![อาจารย์ยอด : ลุงแว่วเล่าเรื่องผี [ผี]](http://i.ytimg.com/vi/Fd2Qsy4dqpM/mqdefault.jpg)
✅Learn Excel Here: www.careerprinciples.com/courses/excel-for-business-finance
Thanks!
Wow😊
Love the go to Special feature, I didn't think about using it for deleting blank rows like that. Thanks!
Your method of teaching on youtube is very useful for deaf person because you put subtitles on your video
A couple little tips…
On renaming variables to remove duplicates… After using the unique formula, type in the corrected names in the next column. Then, create a new column next to the brand column and use xlookup to bring in the new brand names.This also allows you to change any or all values very quickly without having to do separate find and replace. I also use countif on my xlookup reference table to make sure my number of observations don't get goofed up as I'm creating new labels.
To split your city and country, you can use =TEXTSPLIT(J3," "). You can use the same formula instead of Text to Columns when splitting first and last names.
You can use =NUMBERVALUE(N3,",") to convert your price to a number in a single formula
Or you can use =value(substitute()) combined.
I watched your videos before taking my data analysis with excel class and just completed it with a 92.8%. My final exam with the Testout certification was also a 92.8%. Thank you!
You can also choose Find and put in Nike* and replace with Nike to replace all text containing Nike at once instead of two separate steps. Love Excel.
This is just amazing! Please do more of these clean up examples
Hey Kenji your inputs and knowledge on various excel features is immensely helpful and applicable . You have become god level in this!👏
I appreciate that!
As always, thank you Kenji!
Great video! Amazing tips here!
Kenji, new sub here but man your excel teaching knowledge is absolutely at the level for all to understand and grasp quickly. Keep it up!
Awesome, thank you!
Substitute was kinda cool! I've been using Ctrl + H to this day, will definitely try the formula. Thanks!
Loved this Sir Kenji!
For the text to number section, it may be easier to do select that column then use find and replace (Ctrl + H, I think). Find comma, replace with full stop, replace all (when the column is highlighted)
Awesome, investing a few minutes learning shortcuts to clean data can save hours of manual work on the job.
Thank you for sharing Kenji!
Great thank, really Great thanks you.
You are a Great teacher
I'm not just learning, i'm enjoying it.thank you sir
thank you - this is super hands-on and helpful
You're very welcome!
Thanks for the knowledge that I can apply in my job.
Glad it was helpful!
A quicker way to convert text to a number is using the text to columns function without any delimiters selected. Works great for dates too.
thank you Kenji. That was really helpful 🤓
Glad it was helpful!
Keep the good stuff up! Your vids are really good
Glad you like them!
You are the best Sir i learn many things
What a tremendous way. 😇😇😇
Would love if you also include videos on Google Sheets as a lot of tech companies are using it
Second! I was waiting a video about this, thanks!
Hope you enjoyed it!
Nice ❤️
Kindly make video on web data scraping with full explanation... 🙏
Thanks for sharing good content!!!
Thanks for visiting!
Great stuff
Good tutorial
I would always start by creating a copy of hte origianl sheet in teh workbook and work on that copy. That way, the original data would always be available alongside the cleansed set in case of later queries. In the final version I would delete all of the original and interim columns after converting all data from formulae to vallues.
Thanks👍👍👍
Excel is magical 😍
Love text to columns!
Yes sir!
Some really cool tricks!!
Thank you!
Thank you
Very Nice...
Thank youu :)
Hi Kenji, I want to create a waterfall chart with secondary axis. For example, for Gross profit I also want to add dots for percentages, better for both comparative years (2021 & 2022)
you deserve a Excel Samurai medal
Thank you 😊
You're welcome 😊
Bro ur such a gem 💎 always keep it up bro take love 💖
Thank you so much 😀
well made video!
Thank you!
Top tier
First! Thanks for the info!😁
You got it!
9.05 Simply we can also use find ","and replace "." 🙃
Thanks
Thank you for your support! Much appreciated :)
I have a serious question for you. Do you believe that your degree compared to a certificate of skill acknowledgment would have the same value if it was not required for investment banking? I keep wondering why there is not a certificate for skills in the finance industry similar to computer certifications like network+,C++, etc. I would have preferred to get student loans for half the amount to learn in half the time through something like the CFA institute. If you could create a trade type school for finance where students get certifications, The Kenji Institute has a good ring to it.
Hi Kenji,
Can you help me setting the date into format mm/dd/yyyy and make it default.
Wow
Thanks Kenji....we can multiply numbers with 1% to get percentage 😃 am I correct?
Yeah that works! Same as multiplying by 0.01 and subsequently same as dividing by 100
Which laptop u use sir
when you wrote the 100 for the percentage are you able to delete the 100 after youve changed your numbers to a percentage without affecting the data?
Yes, the value was just their so he had something to copy to use in the paste special.
Hello, in the last technique to delete blanks, I got this error when I delete them: Cannot use that command on overlapping sections.
how can someone in Nigeria buy the course?
Sir in 2016 Excel the short key of column space (Ctrl+shift and plus) it's not working... Why this short key is not working??
DONE
Kenji I need your help with an Excel problem. I think it's on Data Analysis or Data Wrangling, or something like that. Honestly, I'm not even sure. How do I connect with you to get your insight? I'd like to solve this
8:10 Control ALT V 選擇性貼上
11:15 Control G >> Blanks >> Control Minus
Why can't we Brand name filter containg Nike and alt+; and then Ctrl+D..
Trim doesn’t work with accounting format numbers from email to excel
Thank you so much.❣️ Ignore the naysayers.😊
Okay
Class
hello, I am using Excel 2019. in this UNIQUE function is not available or not,I did this exercise file,in file no UNIQUE FUNCTION.
Hey
now if we delete old "product" column the new column give us error !!!
i cant use unique formula why?
3rd Tip or cleaning was unnecessary and in the second tip, you didn’t need to convert it to number using the value function. You could have easily multiplied the output of SUBSTITUTE function by 1 making the value a number and not a string anymore.
Sir, please tell me your skincare 🥹🥹
Good video but, Sir you are speking in 5 x speed very fast
Sorry for that will try go slower next time
Well, an Expert would use Power Query instead...
H😊
bh
Not to be a hater but I really do not belive that this is "Pro User". Is barely the minimum and also the working method is so time consuming and inefficient.
Sorry to hear Bogdan, open to hearing what a better working method would have been on excel?
@@KenjiExplains So first of all in a real case scenario this raw set of data would have a recurrence of probably even once a day which means that at least once a day you would have to clean it. Therefore I think that the problem is not how to clean the data but how to clean it fast and present it error proof to the person that needs it. In order to do this I would not use the tools in excel which are great for one off cases, but I would rather create 2 small dbs, one for clients and on for products. Then I would use lookup formulas and mapping to bring up the information that I need when updating the requested set of data. I would need to setup this once, and then update it regularly fairly easy and fast. This structure would allow me quite a few things: 1) copy&paste update. In this way the report would be done in literally less than a minute 2) error checking. 3) enrichment of the report with aditional data. 4) proper presentation in a dynamic table. But of course there are more ways to do the same thing. In my honest opinion what separates a Pro level user from a basic user is the speed and the semplicity to do things.
Meh.. Incredibly basic stuff. You should cover power queries and power pivots if you're focusing on data science.
Right Jay. So to do cleaning again on another similar table, I would use Power Query and change the data source. Thanks for sharing Kenji.
Jay no offence, but for a large majority of viewers here, it's very useful to have someone like Kenji being so nice to hand out these tips for free.
The tips help save time and increase efficiency for those unaware of this information
And I agree on the power pivot and power query thing
What data scientist is using excel lmfao
Hey