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
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!
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)
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.
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)
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
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.
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.
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.
@@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.
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
✅Learn Excel Here: www.careerprinciples.com/courses/excel-for-business-finance
Thanks!
Wow😊
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!
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!
This is just amazing! Please do more of these clean up examples
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.
Love the go to Special feature, I didn't think about using it for deleting blank rows like that. Thanks!
As always, thank you Kenji!
Awesome, investing a few minutes learning shortcuts to clean data can save hours of manual work on the job.
Thank you for sharing Kenji!
I'm not just learning, i'm enjoying it.thank you sir
Great thank, really Great thanks you.
You are a Great teacher
Loved this Sir Kenji!
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!
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.
Excel is magical 😍
Substitute was kinda cool! I've been using Ctrl + H to this day, will definitely try the formula. Thanks!
Love text to columns!
Yes sir!
Thanks👍👍👍
You are the best Sir i learn many things
Great video! Amazing tips here!
thank you Kenji. That was really helpful 🤓
Glad it was helpful!
Thanks for the knowledge that I can apply in my job.
Glad it was helpful!
Good tutorial
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)
Nice ❤️
Kindly make video on web data scraping with full explanation... 🙏
you deserve a Excel Samurai medal
Keep the good stuff up! Your vids are really good
Glad you like them!
Very Nice...
Thank youu :)
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.
What a tremendous way. 😇😇😇
Second! I was waiting a video about this, thanks!
Hope you enjoyed it!
thank you - this is super hands-on and helpful
You're very welcome!
9.05 Simply we can also use find ","and replace "." 🙃
Thank you
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)
Which laptop u use sir
Some really cool tricks!!
Thank you!
Thanks for sharing good content!!!
Thanks for visiting!
Hi Kenji,
Can you help me setting the date into format mm/dd/yyyy and make it default.
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
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??
Why can't we Brand name filter containg Nike and alt+; and then Ctrl+D..
Bro ur such a gem 💎 always keep it up bro take love 💖
Thank you so much 😀
Thanks
Thank you for your support! Much appreciated :)
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
well made video!
Thank you!
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.
Hello, in the last technique to delete blanks, I got this error when I delete them: Cannot use that command on overlapping sections.
First! Thanks for the info!😁
You got it!
Trim doesn’t work with accounting format numbers from email to excel
Do we have to pay for chatgpt ?
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.
Top tier
now if we delete old "product" column the new column give us error !!!
how can someone in Nigeria buy the course?
Wow
DONE
8:10 Control ALT V 選擇性貼上
11:15 Control G >> Blanks >> Control Minus
Okay
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.
Well, an Expert would use Power Query instead...
Thank you so much.❣️ Ignore the naysayers.😊
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.
Hey
Class
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
bh
H😊
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
Would love if you also include videos on Google Sheets as a lot of tech companies are using it
Hey
Great stuff
Thank you 😊
You're welcome 😊