Skip to main content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

Introduction to Excel and Spreadsheets

Spreadsheet Best Practices

Excel is extremely useful, but it is very easy to access and change the data in individual cells, which means it is very easy to accidentally erase data or introduce errors. Here are a few tips for data management in Excel.

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

Create a “README” file to keep track of everything you do to your data.
This includes the sources of your data, field properties, data cleaning steps, Excel functions, etc.

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.