A Practical Guide to Using Spreadsheets

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.

GET MORE CONTENT LIKE THIS DELIVERED TO YOUR INBOX!

Sign up for our newsletter packed full of nonprofit help!

  • This field is for validation purposes and should be left unchanged.
Scroll to Top

Wait, don't go away empty handed.

Join 50,000 other nonprofits in getting the BEST nonprofit tips, tools, and how-to guides delivered right to your inbox!

Subscribe to our newsletter: