It’s not uncommon to see start-up nonprofits using spreadsheets to organize information. Spreadsheets seem like a sufficient solution and non-profits assume database programs cost money, which start-ups don’t have. (Sumac Bronze is a fully functional database program available to small nonprofit organizations for free). Some will eventually move to a database once the organization gets bigger. Others will keep using spreadsheets with no intention of ever switching.
Generally speaking, using spreadsheets to organize your nonprofit’s data causes problems:
- The person who created the spreadsheet is often the only one who understands how it works. So when someone else needs the information, it may not be available.
- Spreadsheets do not impose editing controls. They let you enter dates in email columns and phone numbers in street address columns. Over time, data quality gets lower and lower.
- Spreadsheets are single-user. You cannot have two people sharing and using the contents of a spreadsheet.
- Spreadsheet supports only very simple data structures. Setting up records for contacts and recording their monthly donations, regular attendance at ticketed events, and occasional volunteer activities is complex and error prone if you have only a spreadsheet to work with.
- Spreadsheets do not let you create the rich organizational memory your nonprofit needs to thrive. See article: Time for a spreadsheet Intervention? The Critical Importance of Organizational Memory.
If you’re going to use them, however, you should make sure you’re using them correctly. Here are some tips for organizing your data more effectively in spreadsheets:
Have a separate spreadsheet for each logical group of data
If your organization manages both contacts and donations, don’t try to put all of this information into one spreadsheet. You will find that it just doesn’t work. Instead, put contact information in one spreadsheet and donation information in another. The data will be a lot easier to organize and use.
Use each row to represent a single record
For contacts put all the information about one contact (e.g. first name, last name, phone number) in a single row. Do not use rows to indicate attributes of records. For example, using “Board” and “Staff” as titles for groups of contacts:
| First Name | Last name | Phone number |
| Board |
| |
| John | Smith | 123-4567 |
| Jane | Doe | 567-8901 |
| Staff |
| |
| Alice | Jones | 543-9876 |
| Bob | Barton | 890-4321 |
The problem with doing this is that any re-organization (e.g. sort by last name) causes the information about contact types (board or staff) to be separated from the contact records themselves, like this:
| First Name | Last name | Phone number |
| Bob | Barton | 890-4321 |
| Jane | Doe | 567-8901 |
| Alice | Jones | 543-987 |
| John | Smith | 123-4567 |
| Board |
| |
| Staff |
|
To make the data more user-friendly and easy to transfer into a database, the data should instead be organized like this:
| Position | First Name | Last name | Phone number |
| Board | John | Smith | 123-4567 |
| Board | Jane | Doe | 567-8901 |
| Staff | Alice | Jones | 543-9876 |
| Staff | Bob | Barton | 890-4321 |
Put only one type of information in a column
When you create a spreadsheet, it may seem like a good idea to have a column designated for one of two things, say “Date or Comment,” but it will lead to confusion. Instead, make two columns, one for Dates and one for Comments.
Put only one value in each cell
If the column is Phone Number, don’t put two phone numbers in it. If a record needs two phone numbers, add a “Phone Number Two” column and put the second number in that column. Likewise, do not put an entire address in one column like this:
Address |
| 123 Main Street Happytown, ON, J0Y 1S1 |
| 265 Maple Avenue Appletown, MB K8R 8U8 |
| 12 Orangeview Drive Maple, NB H7Y 3W5 |
1289 Plum Street Crepeview, AB G9G 4L6 |
Almost every program that needs to process the data will be able to handle it more effectively if the pieces are broken up so that there is only one value in each cell. So if you are entering addresses, break the data into separate columns like this:
| Street | City | State | Postal Code |
| 123 Main Street | Happytown | ON | J0Y 1S1 |
| 265 Maple Avenue | Appletown | MB | K8R 8U8 |
| 12 Orangeview Drive | Maple | NB | H7Y 3W5 |
| 1289 Plum Street | Crepeview | AB | G9G 4L6 |
When you sort, make sure you select the whole spreadsheet first
This is very important. If you do not select the whole spreadsheet, you may split records. For example, if you have these two records of three columns each:
| First name | Last name | Phone number |
| John | Smith | 123-4567 |
| Jane | Doe | 567-8901 |
and you sort just the first two columns by last name, you end up with:
| First name | Last name | Phone number |
| Jane | Doe | 123-4567 |
| John | Smith | 567-8901 |
which gives each person the wrong phone number.
Colour a few (non-consecutive) rows to prevent mismatching caused by incorrect sorts
Using the example above, if we had coloured the first row like this:
| First name | Last name | Phone number |
| John | Smith | 123-4567 |
| Jane | Doe | 567-8901 |
then sorted by just the names, we would have produced this:
| First name | Last name | Phone number |
| Jane | Doe | 123-4567 |
John | Smith | 567-8901 |
which shows that the John Smith record has been broken up.
Don’t expect formulas to be preserved
The values contained within formulas will be lost as your data is converted from one format to another, preparatory to being imported into your new database.
If you have donation amounts and dates being entered into two columns:
e.g. 500.00 on January 15 and 600.00 on March 23
do not create a formula “=500+600” in one cell, and put “January 15,March 23” in another cell.
Instead, create columns for the separate donations (Donation Amount 1, Date 1, Donation Amount 2, Date 2).
Enter dates in a way that spreadsheet programs recognizes
Spreadsheet programs let you enter anything you want into each cell, but they only recognize certain things as dates. For example, most spreadsheet programs will not recognize “15 Jan” or “15 January” as a date.
If whatever you are entering is not automatically reformatted to date format (e.g. ISO standard yyyy mm dd or 2010 12 25) then what you have entered has not been recognized as a date and the software won’t be able to sort the data correctly.
Chances are if your spreadsheet program doesn’t recognize it as a date, neither will other programs, so if you have to convert your data for any reason (e.g. when moving to a database) you’re going to be in trouble.
Entering complete dates is good practice anyway, since while “15 Jan” may be meaningful on January 16, when you are entering the data, you will probably have forgotten which January 15 was intended 3 years down the road.
Do not use colour to indicate data
You may be tempted to use colour to group data. For instance, mark current contacts green and inactive ones blue and big donors magenta. But if you are planning to move to a database, this colour information will be lost when the data is exported from the spreadsheet.
Download Your Free Guide on Choosing CRM Software for Your Nonprofit!
