Templates During Data Entry
Before data collection, planning how the data will be recorded into the template is vital. This includes ensuring that the template is consistent, has clearly defined headers, rows, and terms, and has mechanisms to reduce human error. These mechanisms are typically called forms within a data processing program such as Microsoft Excel, Microsoft Access, Google Forms, or OpenOffice Calc (see "Tools" below for more information).
Forms can be created in any of these programs so that they only allow a certain data type to be entered into a field. If the data are entered incorrectly, the form will reject such data entry. Forms can also constrain fields in the form of pull-down menus which use a controlled vocabulary to ensure that the data are entered in the correct locations.
When creating the form, give special consideration to how to label the columns and headers for the form. Good values are ones that are long enough to describe the data included in the cells below or next to it, but are short enough to make readability easier by either humans or machines.
Avoid using spaces or special characters (like * or +) in the name, as it may cause issues when inputting the data into a database.
When entering data that has a unit associated with it, be sure to include that unit in the header (such as "Height (cm)"). Also, include the datum for any geographical data (such as "NAVD88").
After Data Entry
After data have been collected, there are several things that can be done to check data entry. Either while the data are being entered in the first time, or afterwards, consider having another person reenter in the data. This facilitates a comparison between the data entered to make sure they are a match. Use a program to read the data back. This is useful as it allows one to check the data that have been entered into the system against the original data. Check the beginning and ending portions of your data for errors, and then randomly spot-check other values throughout the form. Lastly, consider graphing or mapping the data to ensure there are no unexpected outliers.
When using a template to store data, various spreadsheet programs can help process and visualize the data. However, some of these processes can cause problems. One example is that it is possible to select and sort a user-specified column from A-Z or vice-versa. Unless one selects the option to include all other columns, only one column will be sorted, leaving the others in an unsorted state. This means the connections between the various columns will be lost.
Data Storage and Metadata
Templates for data storage and metadata are also useful for standardizing procedures. Templates for data storage specify how to name files and folders consistently and stipulate where the data should be kept, either for short- or long-term storage.
Similarly, metadata should have a standard template. As notes and information about the data are added over time and by various people on the team, creating a standard metadata template that specifies how this information is entered will keep the metadata organized and easily readable.
Best Practices
- Data Templates
- Use forms when entering data so that some quality assurance is automatically taken care of during data entry.
- Consistency
- Be sure that the columns of data have only numbers, dates, or text and contain the same information.
- Names, codes, and formats must be consistent.
- Dates and geographic data should use the same format and datum (for geographic data).
- Efficient organization - Data that are organized efficiently can be read by statistical packages.
- Descriptive names
- Column names should be descriptive and should not contain strange symbols.
- The file name should be descriptive and concise.
- Standardize how missing data are represented. Decide how you are going to handle missing/null values before entering data. Be sure if you use a specific value to represent missing data, there is no chance that value will be used to represent actual data at a later time (e.g., using 9999 to represent missing data).
- Use a separate column to track and describe missing data across the rows.
- Create forms (i.e., in Google Forms, Microsoft Excel, Microsoft Access, or OpenOffice Calc) to control how the data are entered.
- Only allow certain data types (i.e., numbers, letters) to be entered in designated columns so that if the data type is entered incorrectly, the form will reject the entry.
- Consider having more than one person enter the data. Then you can compare the data entered to make sure it is a match.
- Using a program that can read data back to you is useful as it allows you to check the data that have been entered into the system against the original data.
- Check the beginning and end portions of your data for errors, and then randomly spot-check other values throughout the form.
- Consider graphing or mapping your data to ensure there are no unexpected outliers.
- Long-term Storage
- Use file formats consistently, and preferably formats that will remain readable in the long-term
- ASCII, UNICODE, non-proprietary, unencrypted, uncompressed
- Use comma-delimited ASCII files to represent tabular data
Tools
Disclaimer: Any use of trade, product, or firm names is for descriptive purposes only and does not imply endorsement by the U.S. Government.
- Googledocs Forms
A Web-based user-friendly data collection and data entry application based on forms using GoogleDocs. Forms provide a streamlined interface that allows you to easily enter data into a single file (usually a spreadsheet). Forms can provide data validation rules for data entry and reduce the risk of data entry error.
- Microsoft Excel
Excel is a very popular data-entry tool that also allows you to enforce data validation rules when entering data into a spreadsheet.
- Microsoft Access
Access is an easy-to-use relational database builder that also contains VBA-based form building. Forms in Access can also be created to enforce data validation rules for data entry.
- OpenOffice - Calc
Calc is part of the "OpenOffice" software suite, a free alternative to Microsoft Office. Calc is very similar to Microsoft Excel.
References
Page last updated 5/10/22.
