Most errors in a spreadsheet do not come from formulas but from data typed by hand. An agent written three different ways, a date typed as text, a negative quantity entered by mistake: each of these details distorts totals, lookups and pivot tables. Data validation prevents these errors at the source, by limiting what can be typed into a cell.
In Excel you find it under Data, Data Validation; in LibreOffice Calc under Data, Validity; in Google Sheets under Data, Data validation.
Drop-down lists
This is the most common use and the one with the most immediate benefit. Instead of typing the status of a case by hand, the user picks it from a menu.
A list written into the rule
For a few stable values you can type them directly into the rule, separated by semicolons (or commas, depending on regional settings): Open;In progress;Closed. It is quick but awkward to maintain, because adding an item means editing the rule in every cell.
A list taken from a range
The better solution is to keep the allowed values on a separate sheet, for example a "Lists" sheet with one column per category, and point the rule at that range as its source. To add an item you simply type it into the list.
In Excel it pays to turn the source list into a table and give it a name: that way the range grows by itself when you add values at the bottom, and the validation rule never needs touching.
Dependent lists
A classic case: you choose a region and the second menu shows only the provinces in that region. In Excel this is done by creating a named range for each region (with the same name as the region, without spaces) and using =INDIRECT(A2) as the source of the second list. It works, but it is fragile: if the region name contains spaces or apostrophes the range is not found. In versions with dynamic arrays you can instead use a FILTER formula in a helper area and point the validation at its result.
Restricting numbers, dates and lengths
Beyond lists, validation lets you enforce precise limits:
| Type | Example rule | Typical use |
|---|---|---|
| Whole number | between 1 and 999 | ordered quantities |
| Decimal | greater than or equal to 0 | amounts, weights |
| Date | between 1 Jan and 31 Dec of the current year | entry dates |
| Text length | equal to 16 | fixed-length codes |
Limits can refer to cells too: "date less than or equal to =TODAY()" prevents recording events in the future.
Custom rules with a formula
The "Custom" option accepts any formula that returns TRUE or FALSE. The formula is written with the first cell of the selected range in mind, using relative references that adapt to the other cells.
Some useful examples, assuming the selection starts at A2:
- no duplicates in a column:
=COUNTIF($A$2:$A$1000, A2)=1; - only text starting with a prefix:
=LEFT(A2, 3)="PR-"; - consistency between two fields, for example an end date not earlier than the start date:
=C2>=B2; - no leading or trailing spaces:
=A2=TRIM(A2).
The rule against duplicates is especially valuable for customer codes, reference numbers and any identifier that must be unique.
Input and error messages
Each rule can have two messages:
- the input message, which appears when you select the cell and explains what to enter ("Enter the date as dd/mm/yyyy");
- the error message, which appears when the value breaks the rule.
For the error you can choose the style. "Stop" blocks the entry; "Warning" and "Information" show the message but let you carry on. For critical fields always use the block: a warning is dismissed with one click.
The main limit: copy and paste
Validation checks what is typed, but in Excel and LibreOffice a value pasted from another cell can overwrite the rule itself, along with the formatting. Rules also do not check data that was already there before they were created.
That is why, after setting rules on an existing sheet, it is worth using the command that highlights invalid data: in Excel it sits in the validation menu as "Circle Invalid Data", and draws a red circle around every cell that breaks the rule. Google Sheets flags invalid cells with a small marker in the corner.
In my work, when I prepare a template that other people will fill in, I pair validation with sheet protection: input cells stay unlocked and validated, everything else is protected. It is the simplest way to get usable data back.
In short
- Validation stops input errors before they reach the calculations.
- Drop-down lists should be fed from a separate range, ideally a named one.
- Custom formula rules cover duplicates, prefixes and consistency between fields.
- Use the "Stop" style for critical fields, with a clear input message.
- Pasting can bypass the rules: check for invalid data from time to time.
