Introduction
When you build a spreadsheet, you may want to think ahead about the eventuality of importing your information into a software package at some point in the future. The time to plan your spreadsheet is at the beginning, before time passes and you have added hundreds of entries.
Below are some considerations for best practices in your spreadsheet, so that your data can be easily imported into a software package when the time comes.
The 10 items below each show you a situation we feel is a risk of making you manually reformat data before popular software packages will be able to deal with the data. In each case we also show how you can structure the data in a way more easily imported.
The grey highlighting in each shows the focus of the example.
1) Almost always bad: Having the data for one client (or vendor, etc) spread across multiple rows.
Safe: Having all the data for a given client (or vendor, etc) in one row. Computers like one row per record.
2) Risky: Having the person’s first and last name in the same cell.
What if there are three names? Sometimes people have a compound first name, ‘Mary Jane Smith’, and sometimes they have a compound last name, like ‘Tammy Thomas Halston’. What are the odds that a computer can split those up into first and last names correctly?
If you want to send out automated emails, you may wind up starting the email “Hi Tammy Thomas” (from the example below).
Safe: Having separate columns for first name, last name.
3) Risky: Having the person’s honorific in the same cell as their name (like Ms., Mrs., Mr., Dr.).
Safe: Having a separate column for the honorific.
4) Risky: Having the person’s title in the same cell as their name.
Safe: Having separate columns for first name, last name, and title.
5) Very Risky: Multiple phone numbers in one cell, along with labels for each of them.
When using software on a phone, some packages may let you dial a phone number by touching on it. This may not work if the phone number is actually a combination of two or more numbers, or has labels mixed in.
Safe: Having each phone number in its own column, with a separate column for a label for that phone number (like ‘office’ or ‘cell’).
6) Risky: Multiple email addresses in one cell.
Safer: Having as many ‘Email’ columns as needed so as to keep each email in its own cell.
7) Risky: Having the whole address in one cell.
What if at some point you want to know which cities or zip codes most of your business comes from? So you want to sort your data based on what city someone is in? If the city and zip code and the rest are all mixed into one cell, how can the computer do that?
Safe: Having separate columns for street, apartment, city, state, zip, country
8) Risky: If you divide the list into ‘territories’, having the occasional row that is the territory label.
Safe: Having a column labelled ‘territory’, and each row has a label (like ‘The Southtowns’). The label does get duplicated, but this approach is easily digestible by computers.
9) Risky: Having a business name and a business category label in the same cell (example: ‘Johnson and Assoc, Attorneys’).
Safe: Putting the business category in its own column.
10) Risky: Mixing your clients and your vendors in one list.
Safe: Having a ‘type’ column with a label like ‘client’ or ‘vendor’.
If you want to plan your spreadsheet for easy import later into a software package, we hope this document helps you create one that won’t require reformatting a lot of data.
Thanks.
Brian Jacobs