search
top

How to Convert Raw Data into Usable Format in MS Excel?

ShareShare on FacebookShare on Google+Email this to someoneTweet about this on TwitterShare on LinkedIn

Data can be gathered and recorded by various methods and tools to undergo further analysis. The data obtained is not always in a form which can be directly subjected to further data processing or data analysis. It may be inconsistent, may have missing values or may contain errors. Many data pre-processing tools are available to deal with these difficulties but if you prefer only MS Excel for your data handling job or if you don’t want to switch to other tools, you don’t need to scratch your head.

 

MS Excel offers some very easy and quick functions to convert your raw data into usable format. Let us have a look at the common functions of Excel to make your data processing more meaningful. The menu items in the discussion refer to MS Excel 2010, 2013 and 2016.

 

  • Cleaning Data: Many text functions are available in MS Excel which lets you clean your data. Cells containing no values leads to misunderstanding while creating charts, reports and even for doing further analysis. Sometimes the data is not available in standard format. There are text functions like Upper, Lower and Proper which can convert data in required format. Let us see some of the commonly used functions for converting your data in usable format.
    • Removing Blanks: Blank cells should either be filled with ‘0’ or ‘NA’. To do this, press ctrl+G, choose special and select Blanks. This will highlight all the blank cells in your active sheet. You can either enter 0 or NA and press ctrl+Enter to fill all the blank cells with any of the above values at once.
    • Removing Non-Printable Characters: Clean function removes all non-printable characters from text
    • Treating Extra Spaces: Trim function removes extra spaces from text
    • Changing Case: A proper function has been applied in the following example to convert the names of the cities in proper case. Data in proper case improves readability of the data.

Proper Case

  • Splitting Data: Sometimes the data is obtained in a format which cannot be directly used. For example, in the following sheet, Column G represents area which contains city and pin code both. To apply any functions on city, you should first split column G values. To achieve that select the column, go to Data tab, Data tools group→Text to Columns.

Splitting Columns 1

The data in your source column may be separated by a tab, semicolon, comma, space or a special character. Choose appropriate delimiter and destination cell to split your data.

Splitting Columns 2

The destination cells now contain city and pin code in separate columns.

Splitting Columns Result

  • Dealing with Duplicate Values: Sometimes it is required to be able to locate duplicate values in the database without losing any data, while some other times duplicate values are not required. Depending on your requirement, you can deal with duplicate values in the excel spreadsheet in one of the following ways.
    • Highlight Duplicates: Select the column, go to Home tab, Styles group→Conditional formatting→Highlight Cell Rule→Duplicate Values

    Highlight Duplicates

    You can choose the format in which duplicate values will be displayed.

    Highlight Duplicates with Color

    • Remove Duplicates: Go to Data tab, Data Tools group→Remove Duplicates. Excel identifies all columns with duplicate values and lets you choose multiple columns for removing duplicates. Be warned at this moment… because selecting even single a single column to remove duplicates will cause the whole record to be deleted.

Remove Duplicates

Although Remove Duplicates allows multiple column selection for entering the criteria, it affects the records, not the columns with duplicates. It means that even if you select a single column to remove duplicates, the rows with duplicate values in the selected columns are removed from excel database.

Remove Duplicates Reordered

  • Validating Data: Data validation plays an important role in putting constraints on the data while it is recorded or entered. If the data is already available, data validation let us identify data violating constraints. For getting hold of the irrelevant data, select the column, go to Data tab, Data tools group→Data Validation→Data Validation…

 

Data Validation

At this point, in settings tab, you can set which type of data is allowed in the selected column along with the constraints on the data. If you do not bother about blank cell values, check Ignore blank. The other two tabs, Input Message and Error Alert are used for restricting further data entry in the selected column.

The option in Data Validation→Circle Invalid Data will encircle the cells violating your constraints for the column.

 

Circle Invalid Data

If you try to change the values not satisfying the constraint, an error message will be displayed. A more relevant, user-defined error message can be displayed here by entering the message in the third tab ‘Error Alert’ in previous dialog box.

 

Validation Rule Trigger

 

  • Dealing with Errors: If Excel cannot properly evaluate a formula or does not identify a proper value in a cell, it is considered as an error. Without worrying about manually locating such errors in your database, you can leave it to Excel to do that. Conditional Formatting lets you identify and display errors in your database. To do this, select the column(s), go to Home tab, Styles group→Conditional formatting→Highlight Cell Rule→More Rules.

Highlighting Errors

All types of errors will be highlighted for further actions.

Highlight Errors with Color
There are many similar formulas and operations offered by Excel to convert it into user understandable format. Application of these simple functions makes your data ready for further analysis.

19 Responses to “How to Convert Raw Data into Usable Format in MS Excel?”

  1. Albertina says:

    Amazing! Its truly remarkable article. I have got much clear idea on the topic.

  2. Dani says:

    Thanks for sharing your thoughts on MS excel.

  3. Laurinda says:

    Hi there, nice post and good arguments at this place. I am actually enjoying reading it.

  4. Rebecca says:

    Hello There. I found your blog thru msn. This is a very well written article.
    I’ll be sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll definitely return.

  5. Florian says:

    It’s my first visit of this blog. This blog contains amazing and genuinely good stuff designed for readers.

  6. Jacquelyn says:

    Hi there, keep up writing.

  7. Eli says:

    I’m amazed, I must say. Rarely do I encounter a blog that’s both equally educative and interesting, and without a doubt, you’ve hit the nail on the head.
    The issue is something which not enough folks are speaking intelligently about.
    I am very happy I stumbled across this during my hunt for
    something concerning this.

  8. Hanken says:

    You absolutely come with fantastic articles. Bless you for sharing your website.

  9. Heyden says:

    Many thanks for the ideas.

  10. Grap White says:

    Taking note of the well-written article made me to jump over happiness.

  11. Kyrie says:

    Many thanks for displaying these effective, trusted, educational and fun tips about the topic.

  12. yehoes says:

    After examine a couple of the weblog posts on your web site now, and I truly like your way of blogging. I bookmarked it to my bookmark web site list and will be checking again soon. Pls check out my website as effectively and let me know what you think.

  13. Hansford says:

    I just want to tell you that I’m very new to weblog and honestly liked you’re web page. Probably I’m planning to bookmark your blog . You absolutely have amazing posts. Thanks a bunch for sharing with us your website page.

  14. James says:

    This is exceedingly informative and has helped folks like me to attain their desired goals. Your valuable tutorial is useful to a person like me and my colleagues.

  15. Byod says:

    I just want to say I am just very new to blogging and site-building and really savored this blog site. More than likely I’m want to bookmark your site . You surely have superb well written articles. With thanks for revealing your blog.

  16. Eastwood says:

    I simply want to tell you that I am just very new to blogging and seriously liked your web page. Most likely I’m want to bookmark your blog . You definitely have excellent articles and reviews. Cheers for revealing your webpage.

  17. Thanks for the sensible critique. Me and my neighbor were just preparing to do a little research about this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such fantastic info being shared freely out there.

  18. Generally I don’t learn article on blogs, however I would like to say that this write-up very forced me to try and do it! Your writing style has been amazed me. Thanks, very great article.

  19. Swati says:

    Complex topics explained in easy to understand language with actual screen snapshots every step of the way. I tried hands on looking at this and it really helped. Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *

top