Data Cleaning in Excel | 8 Tricks to Make You a Pro User

[@KenjiExplains]

✅Learn Excel Here: www.careerprinciples.com/courses/excel-for-business-finance

[@solomonbhandari-young4154]

Thanks!

[@jansensee6753]

Wow😊

[@MuhammadKashif-ql8cx]

Your method of teaching on youtube is very useful for deaf person because you put subtitles on your video

[@ChasingMyLiberty]

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!

[@mikedulrich]

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

[@FollowerOfAs-Salaf]

Or you can use =value(substitute()) combined.

[@matattz]

This is just amazing! Please do more of these clean up examples

[@AsDallas]

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.

[@vikramadityarathore97]

Hey Kenji your inputs and knowledge on various excel features is immensely helpful and applicable . You have become god level in this!👏

[@KenjiExplains]

I appreciate that!

[@j_orpz]

Love the go to Special feature, I didn't think about using it for deleting blank rows like that. Thanks!

[@chillOwly]

Great video! Amazing tips here!

[@mohamednasrallahkelfah1047]

I'm not just learning, i'm enjoying it.thank you sir

[@Micfri123]

As always, thank you Kenji!

[@user-qp1un7qo2d]

Thanks

[@KenjiExplains]

Thank you for your support! Much appreciated :)

[@Rohit_M]

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!

[@KenjiExplains]

Awesome, thank you!

[@JoeMaule]

Awesome, investing a few minutes learning shortcuts to clean data can save hours of manual work on the job. Thank you for sharing Kenji!

[@hisokaamorou4211]

Loved this Sir Kenji!

[@waynez8136]

you deserve a Excel Samurai medal

[@Kevin-bz4nt]

Keep the good stuff up! Your vids are really good

[@KenjiExplains]

Glad you like them!

[@Ready_Set_Boom]

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.

[@andreugiro2807]

Second! I was waiting a video about this, thanks!

[@KenjiExplains]

Hope you enjoyed it!

[@mariospetropoulos4213]

thank you Kenji. That was really helpful 🤓

[@KenjiExplains]

Glad it was helpful!

[@AbbasAdejonwo]

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)

[@michaelquach7046]

Love text to columns!

[@KenjiExplains]

Yes sir!

[@gulzamanqayyum]

Substitute was kinda cool! I've been using Ctrl + H to this day, will definitely try the formula. Thanks!

[@amadoumane7600]

Great thank, really Great thanks you. You are a Great teacher

[@jplzee]

Would love if you also include videos on Google Sheets as a lot of tech companies are using it

[@shahhussain4402]

You are the best Sir i learn many things

[@BenDieselBasicAutomotive]

Thanks for the knowledge that I can apply in my job.

[@KenjiExplains]

Glad it was helpful!

[@oluyomifadipe5634]

Excel is magical 😍

[@muhammadkhalil5712]

Nice ❤️ Kindly make video on web data scraping with full explanation... 🙏

[@chrishaynes8994]

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?

[@Ready_Set_Boom]

Yes, the value was just their so he had something to copy to use in the paste special.

[@mr_andy]

Hi Kenji, Can you help me setting the date into format mm/dd/yyyy and make it default.

[@RayBlake]

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.

[@AL-Andalussi007]

Thanks👍👍👍

[@CuocSongQuanhTa204]

Hello, in the last technique to delete blanks, I got this error when I delete them: Cannot use that command on overlapping sections.

[@shamshadqazi3520]

What a tremendous way. 😇😇😇

[@muhammadkhalil5712]

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

[@yuxinjiang6801]

thank you - this is super hands-on and helpful

[@KenjiExplains]

You're very welcome!

[@shani1great]

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)

[@Geetha-wy3rq]

9.05 Simply we can also use find ","and replace "." 🙃

[@AdeKaryadi]

Good tutorial

[@GraciousJohnMlambo]

Great stuff

[@camlex6310]

Some really cool tricks!!

[@KenjiExplains]

Thank you!

[@MrDhunpagla]

Thanks Kenji....we can multiply numbers with 1% to get percentage 😃 am I correct?

[@AbbasAdejonwo]

Yeah that works! Same as multiplying by 0.01 and subsequently same as dividing by 100

[@silentxmoon867]

Thank youu :)

[@pereg4574]

Thanks for sharing good content!!!

[@KenjiExplains]

Thanks for visiting!

[@tarnishedone9924]

Thank you

[@Tina.23mittal]

Which laptop u use sir

[@hunterburns]

Bro ur such a gem 💎 always keep it up bro take love 💖

[@KenjiExplains]

Thank you so much 😀

[@andymarci6766]

First! Thanks for the info!😁

[@KenjiExplains]

You got it!

[@tiruveedipradeepkumar1487]

Very Nice...

[@sarvanandhamthirunavukkara4699]

Thank you 😊

[@KenjiExplains]

You're welcome 😊

[@surajmohammed1371]

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.

[@krystygem4248]

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

[@onyiaugochukwu4309]

how can someone in Nigeria buy the course?

[@sowrabkumar6535]

Why can't we Brand name filter containg Nike and alt+; and then Ctrl+D..

[@ChasingMyLiberty]

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.

[@schubertpeter79]

well made video!

[@KenjiExplains]

Thank you!

[@mohammednaseem9655]

Do we have to pay for chatgpt ?

[@sakoherman5375]

now if we delete old "product" column the new column give us error !!!

[@mehaupadhyay117]

Trim doesn’t work with accounting format numbers from email to excel

[@charlie5116]

8:10 Control ALT V 選擇性貼上

[@charlie5116]

11:15 Control G >> Blanks >> Control Minus

[@datahistory2411]

i cant use unique formula why?

[@solomonbhandari-young4154]

Top tier

[@flourishwithMJ]

Thank you so much.❣️ Ignore the naysayers.😊

[@dakshbhatnagar]

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.

[@isawhimfirstpodcast]

Wow

[@adrealme2336]

Sir, please tell me your skincare 🥹🥹

[@anveshdyava2989]

DONE

[@Donald-George]

Hey

[@lemuwaadachi6867]

Okay

[@stigcorneer8306]

Well, an Expert would use Power Query instead...

[@MohanLal-tv1ix]

Good video but, Sir you are speking in 5 x speed very fast

[@KenjiExplains]

Sorry for that will try go slower next time

[@willzinner8813]

Class

[@Boddy955]

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]

Sorry to hear Bogdan, open to hearing what a better working method would have been on excel?

[@Boddy955]

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

[@Donald-George]

bh

[@jayshek5303]

Meh.. Incredibly basic stuff. You should cover power queries and power pivots if you're focusing on data science.

[@txreal2]

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.

[@usmanafzal504]

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

[@usmanafzal504]

And I agree on the power pivot and power query thing

[@goosty17]

What data scientist is using excel lmfao

[@Donald-George]

Hey