Introduction
- Good data organization is the foundation of any research project.
- Spreadsheets are good for data entry, but when doing data cleaning or analysis, it’s not easy to show or replicate what you did.
Formatting Data Tables in Spreadsheets
- Never modify your raw data. Always make a copy before making any changes.
- Keep track of all of the steps you take to clean your data.
- Organize your data according to tidy data principles.
- Record metadata in a separate plain text file.
Formatting Problems
- Avoid using multiple tables within one spreadsheet.
- Avoid spreading data across multiple tabs (but do use a new tab to record data cleaning or manipulations).
- Record zeros as zeros.
- Use an appropriate null value to record missing data.
- Don’t use formatting to convey information or to make your spreadsheet look pretty.
- Place comments in a separate column.
- Record units in column headers.
- Include only one piece of information in a cell.
- Avoid spaces, numbers and special characters in column headers.
- Avoid special characters in your data.
Dates as Data
- Use extreme caution when working with date data.
- Splitting dates into their component values can make them easier to handle.
Quality Assurance
- Always copy your original spreadsheet file and work with a copy so you don’t affect the raw data.
- Use data validation to prevent accidentally entering invalid data.
Exporting Data
- Data stored in common spreadsheet formats will often not be read correctly into data analysis software, introducing errors into your data.
- Exporting data from spreadsheets to formats like CSV or TSV puts it in a format that can be used consistently by most programs.