Finding the Right Tool for the Job

Determining the right data collection tool for a project takes time.  There are many factors to consider, such as internet availability, the number of data collectors, and the types of data being collected.  Additionally, each data collection tool has its own idiosyncrasies and is not appropriate for every project.

For data collection, my goal is to make sure that I am collecting high quality data in a format that allows it to be analyzed with minimal processing.  While this means I spend more time at the start of projects designing data collection tools, it reduces the time I spend during the life of a project handling poor quality data. Given that many of my evaluations last for years, I save time with this approach.

Through trial and error, I’ve found my four go-to options. Either alone or in combination, using Excel, Google, Access, or a website allows me to meet my goal. Over the next few weeks, I will consider each option in more detail, along with tips on how to use built-in features to increase data quality.

I am a huge fan of using Excel for data collection in some circumstances.  In my experience, most people are familiar with its layout and relatively comfortable using it.  As an added benefit, Excel is an extremely powerful program for data analysis. If you aren’t familiar with PowerPivot or the Data Analysis ToolPak, check out the linked videos.  Also, you can customize Excel through the use of VBA; the Mr. Excel message boards and Bill Jelen’s YouTube channel are good places to start if you want to learn more.

When to Use Excel

In what situations might Excel be a good choice for data collection?  I would recommend Excel if:

  • Data collectors have access to a computer for data entry;
  • Data collectors don’t have internet at all sites;
  • There are less than 10 data collectors;
  • You have means other than email to transmit data;
  • You want to use a mail-merge for reporting.

Key Issues: Internet Availability and Data Processing Time

For me, there are two deciding factors when considering Excel versus Google Sheets/Forms: internet availability and the number of data collectors.  If all of your data collectors have an internet connection, Google might be a better choice: data can be collected in real-time, and you won’t need to devise a strategy to transfer data.

The number of data collectors is important: will each data collector have his/her own Excel workbook for data entry?  At some point, you will likely need to get all of your data in a centralized location.  While you can consolidate workbooks using the “Consolidate” option, this isn’t appropriate for all kinds of data: you have to select a method of consolidation (sum, average, etc.).  Additionally, while there are other techniques available (VBA, merging in stats packages), time is the concern: how often are you collecting and reporting on data?  If it is fairly infrequently, this might not be a deal-breaker for you.  However, if you need to report often, you should weigh the amount of time you’ll spend every week, month, etc. processing data.

Excel’s Encryption Means my Data is Safe, Right?

If you need to collect personally identifiable information, don’t be lulled by Excel’s encryption.  While encryption in more recent releases of Excel is impressive, Microsoft cautions against relying on it to protect sensitive information. There are programs available (some of them free) that can open an encrypted workbook: a quick Google search is all it takes to find one.  However, depending on where you store your data and how it is transmitted, personally identifiable data collection doesn’t rule out Excel.

Personally Indefinable Data (PII) and Data Transmission

One last issue to consider is how are your data collectors going to send you their data. If you are collecting PII data, do not use email unless you are using encryption (this kind of encryption is effective!). If you work with state agencies, such as schools, any information sent from a state account is subject to the Freedom of Information Act. Personally, I advise against sending PII data via email in all circumstances.

Instead, consider storing your data in something like Dropbox or Box.  This way, it doesn’t have to be emailed, and you can control who has access.  Another method I have used is a flash drive like this one.  The drive is encrypted, password protected, and will erase itself if the wrong password is entered too many times. It does require coordination between my client and myself because the drive has to be mailed.