Data Validation Rules

Data validation rules

Any spreadsheet other people fill in will receive data you did not expect. Validation at the point of entry is far cheaper than cleaning afterwards and prevents the silent errors that cleaning misses.

Method

1. Constrain to lists where values are categorical. A dropdown eliminates spelling variation entirely, which is the largest source of aggregation errors.
2. Set ranges on numbers and dates. Bounds catch transposed digits and wrong-year entries at the moment they happen.
3. Write the input message. Explaining what is expected before the error prevents most errors (see form-design).
4. Write helpful error messages. What is wrong and what is acceptable, since a generic rejection leads to workarounds (see error-messages).
5. Protect formula cells. Locking calculated cells prevents the common accident of typing over a formula (see spreadsheet-collaboration).
6. Highlight invalid existing data. Validation applies to new entry, so existing violations need flagging separately.
7. Keep reference lists on a separate sheet. Maintained in one place and referenced, rather than duplicated into each dropdown.

Boundaries

Validation constrains entry and can be bypassed by pasting, so it reduces rather than eliminates bad data. Over-constraining frustrates users who have legitimate exceptions. Genuine data entry at volume belongs in an application with real validation (see form-design).