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.
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
And do not leave empty rows or columns. This is best practice is helpful for importing and exporting data, but there can be exceptions.
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.
Options include
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.
Such as text (TXT) or comma-separated values (CSV).
