MS Excel Tips - Five Simple Ways to Cleanse Your Excel Data

By BJ Johnston 

Photo: sp.life123.com

Usually we have little control over the data we import in either the format or the type. It could be a Comma Separated Value (CSV) file, from a database, or even from the a webpage but more than likely it will be disorganized and dirty.
We can start with the most simple of ways to tidy up and cleanse your data. These are my top five simple ways to start the data cleansing process.

1. Delete and unnecessary rows and columns, these could be either blank columns and rows, or indeed extra columns and rows of data you just do not need. It is easy to do this just highlight the columns/rows and hit Edit- Delete. For Excel versions 2007 onwards use Home Tab- Cells Group- Delete.
2. Resize your rows and columns. Sometimes all you can see is a cell full of ##### signs. Just double click the columns letter box at the right or the underneath the rows number to display the whole cell. In this way you can see the formatting and cells contents you are dealing with.
3. Erase unnecessary cells contents. They just clutter your worksheet. For Excel 2007 onwards Home Tab - Editing Group - Clear - Clear All. For earlier versions Edit - Clear - All command.
4. Use the CLEAN function. This is the command I use the most when starting to cleanse data. It will remove non printable text characters that sometimes end up as strange shapes on your worksheets. This cleaned up text can be stored in another column, usually adjacent to the original data which you can then work with. The CLEAN function uses the following syntax CLEAN(text). The text argument in this function is the text string or reference to the cell that contains the text string you want to clean. For example to clean data stored in cells D1 the you would use the syntax CLEAN(D1).
5. Use Find and Replace. This is one of the most commons commands to simply clean and tidy up your data. If you use Excel 2007 onwards then highlight the column or columns that contain the data you want to clean, use the Home Tab - Editing Group - Find and Select. Use the dialog boxes to enter what you want to find and then then want you want to replace it with. If you are using pre Excel 2007 then use Edit - Replace and the enter the same in the dialog box as in Excel 2007.

These five basic functions should begin to get any disorganized and dirty data you may have imported or inherited into a more user friendly and cleaner form to work with.

BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com/?page_id=2 a site that shares Excel tips and tricks with it's enthusiastic members. To join in the discussion, where users are working smarter and faster with Excel sign up for the FREE newsletter and as a bonus receive a FREE EBook- 50 Top Tips and Tricks. http://www.howtoexcelatexcel.com/

Article Source: http://EzineArticles.com/?expert=BJ_Johnston

Article Source: http://EzineArticles.com/6676083