Do you use Excel or another spreadsheet program to store, organize, and share your files?  Do you wish you could use it more effectively?  If so, you are not alone.

If you are just starting a project now, you have options.  You can take a few minutes at the beginning of the process to consider:

  • What kinds of data will you be collecting?
  • How do they relate to each other?
  • Will you need a discrete ID number for each value?
  • What is the finest level of granularity you may need to sort by in the future?
  • Will your data need to be machine-readable (i.e. easily exported and transformed by another program)?
  • Will it be collected or read by other researchers?

Best Practices for Spreadsheet Design

  • Start with a paper draft
  • Include a Header Line 1st row (or record)
  • Label each column with a short but descriptive name
  • Names should be unique
  • Use letters, numbers, or “_” (underscore)
  • Do not include blank spaces or symbols (+ – & ^ *)
  • Columns of data should be consistent
  • Naming conventions should be consistent
  • Columns should include only a single kind of data
  • Use a standard for Date and Time (e.g. YYYYMMDD T hh:mmss.s TZD)
  • Use spatial coordinates for Latitude/Longitude (e.g. +/- DD.DDDDD)
  • MOST IMPORTANTLY, DOCUMENT EVERYTHING as you go.  Make a key of row and column header meanings.  Keep a record (in a text read-me file, in XML, in Word, etc.) of what you did to your data as you make changes.