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:
✅ Free Excel Course (Basic to Advanced) –
✅ Free Dashboard Course –
✅ Free VBA course –
✅ Free Power Query Course –
#Excel #ExcelTips #ExcelTutorial
Tag: clear format trong excel, Clean Data in Excel, Data Cleaning in Excel, Data Cleansing in Excel, cleaning up raw data in excel, how to clean data in excel, data cleaning, Excel Data Cleaning, trim function in excel, top data cleaning tricks in excel, how to do data cleansing in excel, data cleansing, cleaning data in excel, cleaning up excel spreadsheet, Excel, Excel Tips, Excel Data, how to clean raw data in excel, clean raw data in excel, excel tutorial, clean raw data, Data cleaning formulas
Xem thêm: https://icongnghe.org/category/review
Nguồn: https://icongnghe.org
36 Bình luận. Leave new
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:
✅ How to Delete Blank Rows in Excel – https://www.youtube.com/watch?v=2t6gEKjTqvI
✅ How to Remove Leading and Trailing Spaces in Excel – https://www.youtube.com/watch?v=EGien2dwG9M
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) – https://trumpexcel.com/learn-excel/
✅ Free Dashboard Course – https://bit.ly/free-excel-dashboard-course
✅ Free VBA course – https://bit.ly/excel-vba-course
✅ Free Power Query Course – https://bit.ly/power-query-course
Hii Sumit. I need your help to set one format
I was searching for a command text to column and i learned it from this.
The good thing is you're not wasting the viewer's time. So much to the point only.
Hats off bro, brilliant 👏
Thank you so much! Very helpful.
Hi, I have a question.
My excel sheet has data in only 20 rows. But, if I scroll down, I can see rows up to 10,0000 rows.
All the rows from 20 to 10,000 are empty. I tried removing this blank cells using the above method. It doesn't work.
I just want the excel to show 1-20 rows. I don't want any blank rows after that.
Is it possible, if so how?
Love the way you converted text to number
Very good thank you but i want to ask you something would you plz make tutorial about stock in excel inventory
Hi Mr. Sumit Bansal.. I'm Jemimah. I watch your videos. I need your favor reading to solve excel sheet problem. Looking forward for your response.
9:18 That's a BIG no-no! You're giving a few unique cells a unique value ("not available") in a column were people expect all formulas to be identical. What when someone enters a missing number in one of the preceding columns? Still "not available", which is then wrong. Better put in cell I3 the formula IFERROR(E3/B3; "not available") and copy that to the entire range. It'll give the proper result AND a "not available" if the formula generates an error.
hi,
I have series of students numbers, such as 19009400, 18401940, 17128190,189182371, 191923801, 16129001, 17129300, 160293801. etc in one column. The first two digits 19,18,17,16 etc represent year. I want to count the number of students in each series of 19,18,17,16. I will be writing 19,18,17,16 in separate cells, and want to use as reference.
I am using Left(Cell,2) and extracting them in one column, then use countif, with refrence to new column.
Basically i want combine countif and left functions.
Can u help in this regards
Tahnk you
Make this video for android please
*ERROR* on Step 7 (Parse Data Using Text to Column): When you use the comma as a delimiter, the second and third items (City and Country) have a leading space. That is because in the original text, there is a space behind the comma (delimiter) which doesn't get processed and ends up in the resulting City and Country data. This is not a normal database format in which commas are NOT followed by spaces, but this example is a typical table with text.
For instance,
221B Baker St, London, UK
…would break down as follows:
221B Baker St
London (Leading space)
UK (Leading space)
The solution assumes that the comma is ALWAYS followed by a space. Perform the same steps as given, EXCEPT that in Step 2 of 3 on the Convert Text to Columns Wizard popup, instead of selecting Comma, select Other, then type in ", " (comma followed by a space, no quotes). That will rectify the problem.
Bro you have EXCELLENT Knowledge on Excel , great
Thanks so much it helps greatly ☺
Good Videos
I just got your video and saw it only for one minute and I am exited about the first presented function!
Excellent !!!
Thanks for sharing,buddy.So generous at all.
Love ur videos….I am not able to get the result for find and replace ( line break and removing brackets). Can u help me out..
How to make data from lots off diffrent collum to in a cell
Thanks!
working sheet is not shared
If I could give this video another Like I would. I've saved it as a favourite and keep coming back to it. Awesome job; well done.
I didn't know about using Ctrl + J in Find & Replace box.. thank you, that would be very very useful someday.
Thanks for the informative lecture. There is also a formula to apply when dates are resistant to routine formatting; the formula is =DATEVALUE(TEXT(A1,"dd-mm-yyyy")) . NB A1 is the cell number you want to change its Date format
Sir
Please make a video on how to clean data like sales register, ledgers etc in detailed formats downloaded from tally erp
Thanks 👍
Thank you! It gives me big helps..
Thank you for this video. I came across a spreadsheet and it was garbage. Now it's nice and clean!
Great.
You should have uploaded this excell sheet in description so that we could practise from this only
IFERROR
Keep going 💙
Why always no dataset
Awesome bro ..thank you for teaching
Superb brother 👌
Just discovered this now, love it ! THANK YOU 😀
Your voice 👌👌👌👌