Validating Input: Lists, Limits and Rules

Why validate input?

A reliable workbook starts with clean data at the point of entry. A region typed as “nord” instead of “Nord”, a negative quantity or a date in 2062 silently skews totals, SUMIFS (SOMME.SI.ENS) formulas and PivotTables. Rather than fixing things afterwards, Excel lets you prevent incorrect entries with data validation.

You will find it under Data > Data Tools > Data Validation. The dialog box has three tabs:

• Settings: the rule type (Allow) and its parameters;
• Input Message: a tooltip that appears when the cell is selected;
• Error Alert: what happens when the entry violates the rule.

Rule types

Allow	Typical use	Example parameters
Whole number	quantities, headcounts	between 1 and 100
Decimal	prices, rates	greater than or equal to 0
List	regions, statuses, products	source =ListeRegions
Date	order dates	between 01/01/2026 and 31/12/2026
Text length	codes, references	equal to 8
Custom	any rule expressed as a formula	=NB.SI($A$2:$A$200;A2)=1

Select the whole range to protect (for example E2:E200) before opening the dialog box: the rule applies to every selected cell.

Drop-down lists

The List type displays an arrow in the cell. You can type the source directly (Nord;Sud;Est;Ouest in a French-language Excel), but it is better to place it on a settings sheet:

1. on a sheet named Listes, enter the regions in a column;
2. select them and type ListeRegions in the Name Box (to the left of the formula bar), then press Enter;
3. in the validation, choose List and enter the source =ListeRegions.

Adding a region then happens in one place only. If the list is a column of a structured table, define a name that points to that column: a validation source does not directly accept the Table[Column] syntax, but it does accept a name.

In Microsoft 365, a list can also point to a dynamic result: if J2 contains =TRIER(UNIQUE(Ventes[Produit])) (SORT(UNIQUE(...))), the source =$J$2# automatically follows the size of the list.

Messages and alerts

An input message guides the user (“Whole quantity between 1 and 100”). The error alert has three styles:

• Stop: the entry is rejected (only Retry and Cancel are offered);
• Warning: Excel asks for confirmation, and the user can override it;
• Information: a simple notice, the value is accepted.

For data that feeds calculations, choose Stop and a message that says what to do, not just “error”.

Custom rules

The Custom type accepts a formula that must return TRUE (VRAI). It is written for the first cell of the selection, with relative references for the cell being tested:

=NB.SI($A$2:$A$200;A2)=1            rejects a duplicate in column A
=ET(E2>=1;E2<=100;ENT(E2)=E2)       whole number between 1 and 100
=ESTNUM(CHERCHE("@";F2))            the address contains an @

The limits, and how to compensate for them

Validation only checks entry. A copy and paste replaces the target cell, rule included, and data present before the rule was created is not checked. Two habits help:

• Data > Data Validation > Circle Invalid Data: Excel circles in red every cell that breaks its rule; Clear Validation Circles removes them;
• to paste without destroying the rules, use Paste Special > Values.

Finally, validation does not replace the consistency checks you will see at the end of the course: it filters typing errors, not reasoning errors.