Skip to main content

Introduction to Excel and Spreadsheets

Spreadsheet Best Practices


Working with files

Never work from the original copy of your data.
In other words, keep your raw data raw. Always make a copy first, then use the copy to start cleaning, processing, and analyzing.

Use a descriptive file name.  For instance, a file named SEV_SmallMammalData_20200525.csv indicates the project the data is associated with a project (SEV),  the theme of the data (SmallMammalData) and also when this version of the data was created (20200525).This name is much more helpful than a file named mydata.xls.

After a major cleaning or processing step is done, save a version under a new file name This means you will have several files based on the same data set (original, cleaned, analysis). Use descriptive file names to retrace your steps, for example SEV_SmallMammalData_Cleaned_20200525.csv

Working with data entry

Create only one table per sheet.

And do not leave empty rows or columns. This is best practice is helpful for importing and exporting data, but there can be exceptions.

Enter data consistently.

Name columns consistently and without spaces or special characters. Use consistent formats, codes, spellings, etc. and do not mix data types in a single column. Enter data using consistent formats, for example, always use the same formats for dates. Order similar columns in multiple tables in a similar way.

Consider how to deal with missing data.

Options include

  • leave field empty (NULL = no value)
  • in numeric fields, use a distinct value such as 9999 to indicate a missing value
  • in text fields, use NA (“Not Applicable” or “Not Available”)
  • or use data flags in a separate column to qualify missing value.

Future-proofing your data


Store data in a file format that will be accessible by any (or many) applications now or in the future.

Such as text (TXT) or comma-separated values (CSV).


Some content on this page was adapted from the DataONE Education Module: Data Entry and Manipulation.