Tuesday, November 22, 2016

Data Cleaning Tools In Microsoft Excel

We held a webinar on Data Cleaning in Excel. Well, there was some technical glitches but I think we have managed to find out the root cause and way around the issues to prevent similar glitch in future ones.

You can watch the video of the webinar here, might want to fast forward the glitched part.



As a compensation, I will be taking you through the crux of what I shared in the webinar with plenty easy to follow screenshots.

1. Text to Column
Sometimes, you copy data from other applications into Excel and they don't get arranged the way you want. Maybe you copied customer data records with first name, last name and email and instead of Excel putting them in different columns/fields, you get them sandwiched into one field. How do you easily remedy that?

Well, I am happy to introduce you to Text to Column.





To fix this, go to Data Menu, Text to Column and in a matter of 3, 4 clicks you are done. Even for a hundred thousand records table.





2. TRIM
Got text data with the spacing irregularly done? No worries, meet TRIM.

It can turn records that look like this:

Into this pleasant looking one:




3. PROPER and UPPER
Got text or names written with all lower case or terrible mix of the cases? Time to fix them looking Proper or all in UPPER.





4. Go To Special, Blanks
There is something definitely special about Go To Special beyond its name.

Have you got records arranged disorderly and you want to align the arrangement. Take for example, the one in the screenshot below.



With Go To Special, select Blanks. Then right click on the highlighted Blanks and Delete, shifting cells to the left. And, voila! It is done!











5. Removing Duplicates
Say you have a table with duplicate records. How do you remove the duplicate entries, leaving one unique record?

You guessed right: by using remove duplicates.

Here is a sample problem to attack.



First, we'll make Excel show us the duplicate records using conditional formatting. It's not a requirement. Just doing it for fun or maybe you really want to see the duplicate records before deleting them. 




Now let's remove the duplicate values. Go to Data Menu, Remove Duplicates.



See result below. Sparkling clean. No more greasy duplicates.



6. Text 2 Number
Sometimes, you import or copy numeric data into Excel and they show up as Text. For you to run regular calculations, with peace of mind, on them you need to convert them back to numbers. 

If you are lucky, which is the usual case, Excel will show you a diamond tool tip to guide you in converting the Text numbers to Number numbers😀.

Say today you weren't that lucky. No diamond tool tip. How do you proceed, and considering there are hundreds or thousands of records to fix at once.

Well, use what I call Text 2 Number.

Just type 1 in any empty cell, copy it and paste special on the problematic entries but as Value and Multiply.




That fixes it in one step for you.



7. Go To Special, Errors
How do you select Error cells in your report and maybe replace them with something more meaningful or delete them all at once?

Again, meet Go To Special.







8. Find and Replace Formula Parts
Find and Replace can work also on formula components. I often use it in making giant templates or in cleaning up my formulas after considerable report structure change.



9. Spelling Check and Auto Correct
Surprised? Well, Excel does have spell check and auto correct.



And those are the Data Cleaning tips we treated yesterday during the webinar.

Don't forget to forward this to friends and family members who will find this useful and love you more for it. And you should subscribe to our webinar directory to be in the loop of all future webinar announcements.

38 comments:

  1. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained!

    data science training in guntur

    ReplyDelete
    Replies
    1. Machine Learning Projects for Final Year machine learning projects for final year



      Deep Learning Projects assist final year students with improving your applied Deep Learning skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include Deep Learning projects for final year into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Deep Learning Projects for Final Year even arrange a more significant compensation.




      Python Training in Chennai
      Python Training in Chennai

      Angular Training Project Centers in Chennai

      Delete
  2. Cleaning agendas make it simpler for individuals to guarantee that all zones of their homes are tended to.best hardwood floor mop and vacuum cleaner

    ReplyDelete
  3. Great article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
    typeerror nonetype object is not subscriptable

    ReplyDelete

  4. Really, this article is truly one of the best, information shared was valuable and resourceful Very good work thank you.
    Data Scientist Training in Hyderabad

    ReplyDelete
  5. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it.
    machine learning course training in Hyderabad

    ReplyDelete
  6. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated.
    business analytics training in Hyderabad

    ReplyDelete
  7. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    Data Science Course in Bangalore

    ReplyDelete
  8. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data science training in bangalore

    ReplyDelete
  9. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete
  10. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  11. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  12. Thanks for posting the best information and the blog is very important.artificial intelligence course in hyderabad

    ReplyDelete
  13. I curious more interest in some of them hope you will give more information on this topics in your next articles.
    Best Data Science courses in Hyderabad

    ReplyDelete
  14. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Course Syllabus

    ReplyDelete
  15. Fantastic article I ought to say and thanks to the info. Instruction is absolutely a sticky topic. But remains one of the top issues of the time. I love your article and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  16. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .
    Data Analytics training in Bangalore

    ReplyDelete
  17. Terrific post thoroughly enjoyed reading the blog and more over found to be the tremendous one. In fact, educating the participants with it's amazing content. Hope you share the similar content consecutively.

    data science course in varanasi

    ReplyDelete
  18. Nice to be seeing your site once again, it's been weeks for me. This article which ive been waited for so long. I need this guide to complete my mission inside the school, and it's same issue together along with your essay. Thanks, pleasant share.
    Data Science training in Bangalore

    ReplyDelete
  19. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science training in chennai

    ReplyDelete
  20. Thanks for posting the best information and the blog is very important.data science course in Lucknow

    ReplyDelete
  21. Whenever I open an Excel file on my computer, a message appears stating that the file format does not match the file destination and is corrupted or unsafe. What is that mean, and how this issue can be fixed? Assignment Writing Services

    ReplyDelete
  22. So-called sash balances of various kinds are on the market, which seem to work rather well, especially when they are new; these work on the principle of winding and unwinding a spring. Be sure the win­dows are sufficiently counterbalanced in some way so that they operate freely and easily.https://www.westseattlewindowcleaner.com/pressure-washing-bellevue-homeowners-best-friend

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. In this blog some points you have mentioned for data tools for excel its really helpful and useful. I hope you will share more good updates. Now it's time to avail HYDRATING SUN SCREEN LOTION for more information.

    ReplyDelete
  27. Thank you for providing such beautiful content. Your blog provided some useful information. Keep on sharing.
    motorcycle accidents yesterday

    ReplyDelete