Spring Clean for Excel Spreadsheet Data
Misspelled words, stubborn trailing spaces, unwanted prefixes, improper cases, and non-printing characters make a bad first impression. And that is not even a complete list of ways your data can get dirty. Roll up your sleeves. It is time for a major spring clean for Excel Spreadsheet Data.
The basic steps for cleaning data are as follows:
- Import the data from an external data source.
- Create a backup copy of the original data in a separate workbook.
- Ensure that the 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.
- Do tasks that don't require column manipulation first, such as spell-checking or using the Find and Replace dialog box.
- Next, do tasks that do require column manipulation. The general steps for manipulating a column are:
- Insert a new column (B) next to the original column (A) that needs cleaning.
- Add a formula that will transform the data at the top of the new column (B).
- Fill down the formula in the new column (B). In an Excel table, a calculated column is automatically created with values filled down.
- Select the new column (B), copy it, and then paste as values into the new column (B).
- Remove the original column (A), which converts the new column from B to A.
To periodically clean the same data source, consider recording a macro or writing code to automate the entire process. There are also a number of external add-ins written by third-party vendors that you can consider using if you don't have the time or resources to automate the process on your own. Come back every week for more Tips & Tricks from DMS - Direct Marketing Solutions!