Excel data validation for reliable inputs

Data validation constrains what a user can type into a cell. It can require a whole number, a date within a range, a value from a list, or a result from a custom formula. The rule runs when an entry is made, which is early enough to prevent many errors from reaching a calculation.

It is not a security control. Pasting, filling, importing, and macros can bypass validation, and existing values are not checked when a rule is added. Use validation to make the correct entry easy and the common mistake hard, then use protection and reconciliation for the cases it cannot cover. A rule that blocks a legitimate entry creates a new problem: the user finds a way around it.

Match the rule to the field. A list suits a controlled vocabulary, a whole number suits a count, and a date rule suits a period. The table lists the common choices and the failure to watch for.

Prefer the simplest rule that catches the error you actually see. Over-validation pushes users towards pasting values or asking for protection to be removed. If a legitimate entry needs an exception, document the exception path rather than widening the rule until it accepts everything. A rule should describe the data, not the current row count.

A short fixed list can live in the validation dialog. Anything that changes should come from a range, a table column, or a spill range. A table column expands as categories are added, and a spilled UNIQUE formula updates without a manual copy.

Keep the source list clean. Remove blanks, avoid duplicate entries, and decide whether retired categories should remain valid for historical records. If the source is on another sheet, a defined name is the simplest way to point the rule at it, and it survives sheet renames better than a typed address. A list that contains a retired category invites new records to use it, so archive the category rather than leaving it in the source. If the list is long, put it on a dedicated sheet and name the range.

A custom rule returns TRUE for a valid entry. Write it against the top-left cell of the applied range and use relative references for the row being tested. For a duplicate check on A2:A200, =AND(LEN($A2)>0,COUNTIF($A$2:$A$200,$A2)=1) rejects a second occurrence and ignores blank rows.

Combine conditions with AND and OR. A date rule can require a start no earlier than today and an end after the start, for example =AND($B2>=TODAY(),$B2<=$C2). Keep the formula short enough to read; a rule that nobody can explain will be removed at the first false alarm. Test it against the first and last row of the applied range, because an incorrectly anchored relative reference often fails only at the boundary. A rule that fires on valid data is worse than no rule, because it teaches users to ignore alerts.

The error alert has three styles. Stop rejects the value, Warning asks whether to continue, and Information accepts anything after a message. Stop suits codes and rates, Warning suits a judgement call, and Information is rarely worth the interruption.

Write the message as an instruction, not a complaint. State the accepted format, the range, or the list to use. Put an input message on the cell for the normal case and reserve the error alert for the mistake. A message that says only invalid entry trains users to click through. Keep the input message short enough to read without moving the cell, and review the wording after the first week of use.