How to Clean up Excel Data

Using Excel can be easy or a real pain, and sometimes the difference is how the data is organized. If you are able to set up the data yourself, you have more control (see related articles). Often, though, we are handed a spreadsheet and told to create something meaningful, such as a sales report or a summary of data, and this is where the challenges can arise. Fear not - there is hope. Try out some of these techniques the next time you have a messy or unusable worksheet, and see if the results are not easier to work on.

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• A PC with MS Office Excel - most versions have the same keyboard and toolbar controls, and for this article I use Office 2003.

1. You can often PARSE the data into more usable parts.
   This technique splits up a column by a certain number of characters.
   Use it to make 2 columns out of one.
   See the related artice on PARSING Excel data.
2. You can also do what is called a GLOBAL Change.
   Example: if you have the character # appear in many places in your worksheet and you want to change this to the word "Number", you can use the FIND AND REPLACE feature.
   I use the keyboard shortcut CTRL+H.
   Try this - enter one word or character in the top space and the replacement in the lower one, then click REPLACE ALL.
3. Use this easy formula to take the first or last characters from one column and make a new column with it.
   Say you have an ID number in cell A2. It has 3 characters and 5 numbers, and you want to split off the 3 characters into a new column.
   The formula to put in the first cell of the new column is =left(a2,3).
   This will make a new column with only the first 3 characters of the original.

Tips & Warnings

• Try out a few examples before you work on a live document, so you can see the results.
• Never make changes to the original worksheet until you are very comfortable with the process.
• Save a copy of your original to work on and practice.
• When you make a significant change and like the results, save the workbook.
• If your results do not work out, use CTRL+Z to UNDO, or close the workbook and do NOT save it, so it will only have the last saved version