Data Validation

Data validation is a spreadsheet feature that restricts what a cell will accept. It is worth separating from the broader analytical sense of the phrase: validation as a process means checking whether data is accurate, while validation as a feature means preventing certain values from being entered at all. This entry is about the feature, and its value is preventive — it removes work that cleaning would otherwise have to do later.

The common form is a dropdown built from a fixed list, which removes, for values typed directly into the cell, the problem of one category arriving as “In Progress”, “in progress”, and “Started”. In Excel, values that are pasted or filled into the cell can bypass the check, so those paths need a separate review. Beyond lists, rules can require a number within a range, a date inside a window, a text length, or a custom formula condition — a length rule catches a malformed identifier at the moment it is typed, which moves the check from later cleanup to the point of entry. The rule still needs to be defined and maintained.

One setting decides whether any of it binds. Google documents that by default, “if you enter data in a cell that doesn’t match an item on the list, it is rejected”, with a warning mode available that lets the value through while flagging it. Warning mode suits an advisory list; rejection is what a column something downstream depends on requires. Choosing warning everywhere turns validation into decoration, and the sheet accumulates exactly the variants the rule was written to prevent.

Its limits are worth stating. Validation constrains only the cells it is applied to, so formula columns still need protecting separately; it applies to new entries rather than retroactively cleaning values already present, so existing data needs its own check (Excel can circle invalid entries, but it does not notify you on its own); and it is not a security control, since anyone with edit access can change the rule itself. Pairing it with conditional formatting gives the rule a visible counterpart, and documenting the allowed values somewhere in the file keeps the list meaningful to whoever inherits it.

References:

• Create a drop-down list
• Apply data validation to cells