Spring Clean for Excel Spreadsheet Data

Spring Clean for Excel Spreadsheet Data

Spring Clean for Excel Spreadsheet DataMisspelled 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:

  1. Import the data from an external data source.
  2. Create a backup copy of the original data in a separate workbook.
  3. 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.
  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.

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!

No Comments Yet.

Leave a comment

You must be Logged in to post a comment.