10 Super Neat Ways to Clean Data in Excel

In this video, I will show you 10 simple ways to clean data in Excel.

Data forms the backbone of any analysis that you do in Excel. And when it comes to data, there are tons of things that can go wrong – be it the structure, placement, formatting, extra spaces, and so on.

Excel can be an amazing tool for data analysis. But we hardly get the data that can be used right away. And bad data leads to bad analysis.

Cleaning the raw data that you get in Excel will depend on what type and structure of data you get. There are a lot of things you can do before-hand to make sure your raw data get cleaned and ready for use.

Excel has a lot of formulas and functionalities that can help you clean your data. For example, you can use a formula such as TRIM to clean your data of leading, trailing, and double spaces. Or you can use the remove duplicate functionality to remove any occurrence of duplicate records.

The following data cleaning techniques are covered in this video:
0:00 Get Rid of Extra Spaces (leading, trailing, and extra spaces between words)
1:20 Select and remove All Blank Cells/rows
2:38 Convert Numbers Stored as Text into Numbers
5:00 Remove Duplicates cells/records
7:21 Highlight cells that contain Errors
9:24 Change Text to Lower/Upper/Proper Case to make it all consistent
10:54 Parse Data Using Text to Column
13:15 Spell Check (using a keyboard shortcut)
13:54 Delete all Formatting
14:33 Use Find and Replace to Clean Data in Excel

The data cleansing techniques covered in this video are some of the most common ones that you can use as soon as you get your raw data. Again, based on your dataset, you may need to add more to it.

Read the full tutorial here:

Also, I have made all of my Excel courses available for FREE. You can check these out using the below links:

#Excel #ExcelTips #ExcelTutorial

[trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.

Here are some other Excel tips videos you may find useful:

Also, I have made all of my Excel courses available for free. You can check these out using the below links:

[mahamohan]

I have taken to learning excel on a full time after my retirement in May 2017. First it started as a hobby and now it has become an obsession since it gives me a lot of happiness. I almost spend 4 to 5 hours going through all the excel videos that come in YouTube and practice each one of them. I can definitely say that the way you teach excel is simply fantastic. All the explanations are extremely crisp.
Hats off to your work

[Beluga]

0:08 Trim extra space
1:19 select blank cells. treat then all the same.
2:39 Convert numbers stored as text into numbers
4:59 Remove duplicates
7:21 Highlight errors
9:24 Change text to lower/UPPER/Proper case
10:54 break up concatenated text (e.g. street address) into separate columns
13:15 Spell check
14:33 Find and Replace
16:59 remove line breaks in text
17:47 Clean (out unwanted characters from) text data

[ashaydwivedi]

i was 8 years old when i watched this video. now im 15 and i wanna say that im the best at excel in the whole neighborhood, thank you sumit bansal

[muhammadhaider]

There are many excel teachers in the YouTube, but i must say the way you guys teach and summarise the functions are awesome. Well done and keep inspiring guys.

[CH]

I've looked at about 15 excel videos and this one was the best and most clearly explained without excessive chit chat..This Professor rocks! thanks.

[simonafullinfaw]

To the point and very precise. Didn't waste a single second. Thank you for providing this.

[aishwaryakk]

Feels like found Treasure in YouTube after many years, Great work

[prakashsathyapriya]

Really very much useful. Your teaching method of teaching is so easy to understand and your speech is nice. Very useful tips. Thank you

[jsoloughlin]

Excellent short presentation of very helpful tips. Thanks you.

[shdwctchr]

Thank you for taking the time to create this video!

[sumayaasif]

This video is a jem on internet. God bless you brother.

[timbabitelor]

I didn't know about using Ctrl + J in Find & Replace box.. thank you, that would be very very useful someday.

[isldgrl]

Straight to the point, covers each point thoroughly.

[larifari]

I just got your video and saw it only for one minute and I am exited about the first presented function!
Excellent !!!

[executiveengineerchitradur]

Superb!!! Thanks Sumith. The way of teaching is excellent and with in a very short span of time.

[ashokanselliah]

Excellent. hugely helpful. Many thanks...

[zhangxinyi]

I would say this is the most helpful video Ive watched recently although its already 2020! Thank you so much!

[mohamadehtheshams]

Bro you have EXCELLENT Knowledge on Excel, great

[Nabhishek]

You are simply Amazing I am a Corporate Graphic designer and time time I receive Excel Documents where I require to do some tweaks and I always face problems, but now I know a place to go to where I get all the solutions with absolutely no nonsense. :)