Skip to Main Content

Escape from Spreadsheet Hell

The website for the workshop! TL;DR we’ll teach you how to create spreadsheets optimized for reuse by you and others. Your future self will thank you.

Zero and NULL values

Zero (0) values

Zero (0) is a number. It is a measurement that shows that something "is not," did not occur, or occurred below a measurable level. A zero is not the same as a missing (was not observed) or invalid value. The only way zero should be encoded is with the '0' character as this ensures that it will be processed as a number.

Null values

Unlike zero, null values are not an absolute or something quantifiable; they're an unknown. As such they should not be stored as numbers (i.e. 0, -999, 999, etc.). The table below provides information on the best and worst ways to encode null values in a dataset.

Table 1. Commonly used null values, limitations, compatibility with common software and a recommendation regarding whether or not it is a good option.
Null Values Problems Compatibility Recommendation
0 Indistinguishable from a true zero   NEVER use
Blank Hard to distinguish values that are missing from those overlooked on entry. Hard to distinguish blanks from spaces, which behave differently. R, Python, SQL, Excel Best option
-999, 999 Not recognized as null by many programs without user input. Can be inadvertently entered into calculations.   Avoid
NA, na Can also be an abbreviation (e.g., North America), can cause problems with data type (turn a numerical column into a text column). NA is more commonly recognized than na. R Good option
N/A An alternate form of NA, but often not compatible with software [due to the '/']   Avoid
NULL Can cause problems with data type [i.e. text in numerical field]. SQL Good option
None Uncommon. Can cause problems with data type [i.e. text in numerical field]. Python Avoid
No data Uncommon. Can cause problems with data type, contains a space.   Avoid
Missing Uncommon. Can cause problems with data type.   Avoid
-, +, . Uncommon. Can cause problems with data type.   Avoid
Source: White et al. (2013). Nine Simple Ways to make it easier to (re)use your data. Ideas in Ecology and Evolution 6(2): 1–10, 2013 DOI: 10.4033/iee.2013.6b.6.f

Escape Coordinators

Megan O'Donnell
mno@iastate.edu
Data Services Librarian
ENT, EEOB, NREM, and Environment.

Kris Stacy-Bates
kksb@iastate.edu
Science and Technology Librarian
ABE, CCEE, Math, and Stats