How To Clean Your Data

In an earlier post, I told you how to move data from Excel to Access and vice versa. In that post, I told you that you should, if you want a smooth process with glitches, clean your data. Now, I am going to tell you what that means and how to go about doing so.

Of course, we do not always have control over the format and type of data that we import from an external data source, (i.e.,  a database, text file, or Web page). So, prior to analyzing the data, you need to clean it up. Luckily, Excel has myriad features to help you clean your data so that it is precisely the format that you would like. When you get lucky, the task is straightforward and there is a specific feature that does the job for you. Like you can use Spell Checker to clean up any misspelled words in columns that contain comments or descriptions. Or, if you need to remove duplicate rows, you can quickly do this using the Remove Duplicates dialog.

And then there are times, when you may have a need to manipulate one or more columns by using a formula to convert the imported values into new values. (i.e., if you need to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column’s formulas to values, and then removing the original column. But let’s get on with it, shall we?

Follow the steps below to learn how:

1. Import your data from an external data source.
2. Create a backup copy of your original data in a separate workbook.
3. Be sure your data is in a tabular format of rows and columns with similar data in each column, all columns and rows visible, and no blank rows within the range. For best results, use an Excel table.
4. Do tasks that don’t require column manipulation first, such as spell-checking or using the Find and Replace dialog box.
5. Next, do tasks that do require column manipulation. The general steps for manipulating a column are:
   1. Insert a new column (B) next to the original column (A) that needs cleaning.
   2. Add a formula that will transform the data at the top of the new column (B).
   3. Fill down the formula in the new column (B). In an Excel table, a calculated column is automatically created with values filled down.
   4. Select the new column (B), copy it, and then paste as values into the new column (B).
   5. Remove the original column (A), which converts the new column from B to A.

I hope this has been helpful to you.

—