Comment faire une validation des données sur Excel
Pour faire une validation des données sur Excel, sélectionne les cellules à contrôler, puis ouvre Données et Validation des données. Choisis dans la liste Autoriser ce que la cellule accepte, comme un nombre entier entre deux bornes, une date de l’année ou un texte de cinq caractères, et Excel refuse ensuite toute saisie qui sort de cette règle.
La validation de données ne surveille pourtant que ce qu’on tape au clavier, et un copier-coller ou des données déjà présentes lui échappent. Le classeur d’exemple suit des commandes, de la référence au code postal, et chaque colonne y porte une des règles de l’article. Son dernier onglet garde des saisies fautives, faites avant les règles, pour apprendre à les débusquer.
Validation des données
Télécharge le classeur de l’article pour refaire chaque étape sur les mêmes données.
Où trouver la validation des données dans Excel
La validation des données se trouve dans l’onglet Données du ruban, au groupe Outils de données. Son bouton cache une petite flèche, qui déroule trois commandes, Validation des données, Entourer les données non valides et Effacer les cercles de validation. La première ouvre la fenêtre où se règle tout ce qui suit, et les deux autres contrôlent les données déjà saisies.
Cette fenêtre compte trois onglets, où l’onglet Options fixe la règle, l’onglet Message de saisie affiche une aide quand on clique sur la cellule, et l’onglet Alerte d’erreur prévoit le sort d’une saisie hors règle. Le bouton Effacer tout, en bas à gauche, retire la règle des cellules sélectionnées.
Choisir ce que la cellule accepte dans la liste Autoriser
La liste Autoriser de l’onglet Options décide du type de valeur accepté, parmi huit réglages. Tout, le réglage de départ, n’impose rien. Nombre entier, Décimal, Date et Heure bornent une valeur, Longueur du texte compte ses caractères, Liste crée un menu déroulant, et Personnalisé laisse écrire sa propre formule.
Dès qu’on quitte Tout, le champ Données propose la comparaison, « comprise entre » par défaut, et les bornes se tapent dans les champs du dessous. L’onglet « Commandes » du classeur garde cinq commandes sans aucune règle, avec dix lignes jaunes pour refaire chaque geste de l’article.
Onglet « Commandes », le stock disponible dans la cellule jaune I2
| A | B | C | D | E | F | G | I | |
|---|---|---|---|---|---|---|---|---|
| 1 | Référence | Date | Paiement | Quantité | Prix unitaire | Livraison | Code postal | Stock disponible |
| 2 | CMD-0001 | 05/01/2026 | Carte | 12 | 4,50 € | 09:30 | 75011 | 40 |
| 3 | CMD-0002 | 12/01/2026 | Virement | 3 | 129,00 € | 14:00 | 69003 | |
| 4 | CMD-0003 | 20/01/2026 | Chèque | 25 | 2,80 € | 10:15 | 06000 | |
| 5 | CMD-0004 | 02/02/2026 | Carte | 8 | 39,90 € | 16:45 | 33000 | |
| 6 | CMD-0005 | 09/02/2026 | Virement | 40 | 1,20 € | 11:00 | 59000 |
Accepter un nombre entier entre deux bornes
Une quantité se commande par unités entières, et la règle Nombre entier refuse donc tout le reste. Les quatre gestes qui suivent l’appliquent à la colonne des quantités, et ils valent pour tous les types de la liste.
- 01Dans l’onglet « Commandes », sélectionne les quantités, de
D2àD16. - 02Ouvre Données, puis Validation des données.
- 03Dans l’onglet Options, choisis Nombre entier dans la liste Autoriser, et garde « comprise entre » dans le champ Données.
- 04Tape
1comme minimum et40comme maximum, puis valide par OK.
Excel refuse désormais une quantité de 41, comme 2,5, 0 ou le mot « dix », mais il accepte 1 et 40, puisque les bornes font partie de la plage. Une cellule vide reste permise tant que la case Ignorer si vide est cochée, ce qui laisse remplir la ligne dans l’ordre qu’on veut.
Borner un prix avec Décimal
Un prix compte des centimes, et la règle Nombre entier le refuserait, c’est pourquoi la colonne du prix unitaire prend Décimal. Dans l’onglet « Règles prêtes », elle accepte un prix compris entre 0,01 et 500 €. Un prix de 12,50 € passe sans difficulté, mais un prix nul ou un prix de 600 € sort de la règle.
Limiter une date ou une heure à une plage
Les réglages Date et Heure fonctionnent comme les nombres, avec des bornes écrites comme dans une cellule. La colonne des dates de l’onglet « Règles prêtes » n’accepte que l’année 2026, du 01/01/2026 au 31/12/2026, si bien qu’une commande datée du 15/03/2025 est refusée, comme le mot « demain ». La colonne des livraisons accepte une heure entre 08:00 et 18:00, ces deux heures comprises, et refuse donc une livraison à 18:30.
Imposer la longueur d’un code postal
Le réglage Longueur du texte compte les caractères de la saisie, et la colonne des codes postaux exige « égale à » 5. Un code de quatre chiffres comme 7500 est refusé, de même qu’un code de six chiffres. Le zéro de tête tend un piège, car 06000 tapé dans une cellule au format Standard devient le nombre 6000, qui ne compte plus que quatre caractères et sort de la règle. La colonne G du classeur est donc au format Texte, où 06000 garde ses cinq caractères.
Faire dépendre la validation d’une autre cellule
Une borne peut se lire dans une cellule de la feuille plutôt que se taper dans la fenêtre, et la règle suit cette cellule à chaque changement. Dans l’onglet « Règles prêtes », le maximum des quantités vaut =$I$2, la cellule jaune du stock disponible, au lieu de 40. Tape cette référence dans le champ du maximum, avec ses dollars, pour que la règle lise toujours la même cellule.

Passe le stock à 60, et une quantité de 50 devient acceptable. Si tu remets ensuite le stock à 40, cette quantité de 50 sort de la règle, mais Excel la laisse en place, car une règle ne contrôle que la saisie et jamais une valeur déjà là. Seule la commande Entourer les données non valides, présentée plus bas, signale ensuite la cellule.
Écrire une validation personnalisée avec une formule
Le réglage Personnalisé accepte une saisie quand sa formule renvoie VRAI, et la refuse quand elle renvoie FAUX. Il prend le relais des autres réglages dès que la règle regarde plusieurs cellules à la fois, comme pour interdire un doublon dans une colonne.
- 01Sélectionne les références, en partant de la première, de
A2àA16. - 02Ouvre Données, puis Validation des données, et choisis Personnalisé dans la liste Autoriser.
- 03Tape la formule dans le champ Formule, en commençant par le signe égal, puis valide par OK.
Interdire les doublons avec NB.SI
La fonction NB.SI compte combien de fois la saisie figure dans la colonne, et la règle exige qu’elle n’y figure qu’une fois. Les dollars bloquent la plage, mais A2 reste sans dollar, pour que chaque cellule de la colonne se compare elle-même au reste.
=NB.SI($A$2:$A$16;A2)=1Refuse une référence déjà saisie plus haut ou plus bas dans la colonne.
Tape CMD-0001 dans la cellule jaune A7, et la saisie est refusée, puisque cette référence existe déjà en A2. Quand une référence en double entre malgré tout par un collage des seules valeurs, la première cellule enfreint la règle autant que la seconde.
Combiner plusieurs critères avec ET
Une seule règle peut vérifier plusieurs critères quand sa formule les réunit dans ET, qui exige qu’ils soient tous vrais. La colonne des références de l’onglet « Règles prêtes » demande ainsi un début en CMD-, une longueur de huit caractères et une valeur unique, avec GAUCHE et NBCAR pour les deux premiers.

=ET(GAUCHE(A2;4)="CMD-";NBCAR(A2)=8;NB.SI($A$2:$A$16;A2)=1)N’accepte qu’une référence en CMD- de huit caractères, jamais deux fois la même.
CMD-0006 passe, mais CMD-6 et CMD-00066 sont refusées pour leur longueur, et FAC-0006 pour son début. Le signe égal ne distingue pas les majuscules des minuscules, et « cmd-0006 » passe donc aussi.
Une formule écrite pour la première cellule
La formule s’écrit pour la première cellule de la plage, ici A2, puis Excel la décale sur les autres cellules comme une formule recopiée. Rouvre la règle sur A5, et elle se lit =ET(GAUCHE(A5;4)="CMD-";NBCAR(A5)=8;NB.SI($A$2:$A$16;A5)=1). Sélectionne donc la plage de haut en bas, pour que la cellule active soit aussi celle pour laquelle tu écris la formule.
Créer une liste de validation des données
Le réglage Liste remplace la saisie par un menu déroulant, qui ne propose que les valeurs prévues. Pour la colonne du paiement de l’onglet « Règles prêtes », le champ Source contient Carte;Virement;Chèque, trois valeurs séparées par des points-virgules dans un Excel en français. Une saisie au clavier reste possible, mais « Espèces » est refusé, puisqu’il ne figure pas dans la liste, et « carte » en minuscules l’est aussi.
Deux articles du blog traitent les listes déroulantes en détail. L’un explique comment modifier une liste déroulante selon la forme de sa source, l’autre comment construire une liste déroulante conditionnelle qui dépend d’un premier choix. La fiche du lexique sur la liste déroulante en donne la définition.
Afficher un message quand la cellule est sélectionnée
L’onglet Message de saisie affiche une bulle d’aide dès qu’on clique sur la cellule, avant même la première frappe. Laisse cochée la case Quand la cellule est sélectionnée, puis remplis les champs Titre et Message de saisie. Le titre tient en 32 caractères et le message en 254, ce qui laisse la place d’une ou deux phrases qui disent quoi taper.
Chaque colonne de l’onglet « Règles prêtes » affiche ainsi son message, comme « Tape un nombre entier, entre 1 et le stock disponible de la cellule I2 » pour les quantités. La personne lit la consigne avant de taper, et l’alerte d’erreur s’affiche donc bien moins souvent.

Régler l’alerte d’erreur et ses trois styles
L’onglet Alerte d’erreur règle la réponse d’Excel à une saisie hors règle, tant que la case Quand des données non valides sont tapées reste cochée. Sa liste Style propose trois réponses, et les champs Titre et Message d’erreur remplacent le message générique d’Excel par le tien, en 225 caractères au plus.
- Stop refuse la saisie et propose seulement de recommencer ou d’annuler. C’est le style de départ, et le seul qui bloque la saisie.
- Avertissement demande s’il faut continuer, et la valeur entre quand la personne répond oui. La colonne des prix de l’onglet « Règles prêtes » l’emploie, parce qu’un prix inhabituel peut rester juste.
- Informations signale l’écart, puis laisse passer la valeur d’un simple clic sur OK. La colonne des livraisons l’emploie pour une heure hors horaires.

Une valeur acceptée sous Avertissement ou Informations reste cependant hors règle. Un prix de 600 € entré ainsi sera entouré comme les autres le jour où l’on cherchera les données non valides.
Repérer les données non valides déjà saisies
La commande Entourer les données non valides retrouve les données hors règle qu’un collage des seules valeurs ou une saisie plus ancienne a laissées dans la feuille. Elle trace un cercle rouge autour de chaque cellule qui enfreint sa règle, dans toute la feuille active, sans rien modifier.
L’onglet « À contrôler » garde douze commandes saisies avant les règles, qui cachent six fautes. La commande entoure sept cellules, une de plus que de fautes, parce que la référence CMD-0104 apparaît deux fois, en A5 et en A10, et que les deux exemplaires sortent de la règle. Les cinq autres fautes sont une quantité de 60 au-delà du stock, une date de 2025, un code postal de quatre chiffres, un paiement en espèces et une livraison à 19:30.

Onglet « À contrôler », en rouge les sept cellules que la commande entoure
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Référence | Date | Paiement | Quantité | Prix unitaire | Livraison | Code postal |
| 2 | CMD-0101 | 06/01/2026 | Carte | 12 | 4,50 € | 09:30 | 75011 |
| 3 | CMD-0102 | 08/01/2026 | Virement | 5 | 89,00 € | 14:00 | 69003 |
| 4 | CMD-0103 | 13/01/2026 | Carte | 60 | 2,80 € | 10:15 | 31000 |
| 5 | CMD-0104 | 15/01/2026 | Chèque | 8 | 39,90 € | 16:45 | 33000 |
| 6 | CMD-0105 | 15/12/2025 | Virement | 20 | 1,20 € | 11:00 | 59000 |
| 7 | CMD-0106 | 21/01/2026 | Carte | 3 | 129,00 € | 08:30 | 13001 |
| 8 | CMD-0107 | 26/01/2026 | Carte | 15 | 6,40 € | 15:30 | 7500 |
| 9 | CMD-0108 | 29/01/2026 | Virement | 10 | 22,00 € | 09:00 | 44000 |
| 10 | CMD-0104 | 02/02/2026 | Chèque | 6 | 14,90 € | 13:15 | 67000 |
| 11 | CMD-0110 | 05/02/2026 | Carte | 25 | 3,10 € | 17:00 | 06000 |
| 12 | CMD-0111 | 09/02/2026 | Espèces | 4 | 49,00 € | 10:45 | 35000 |
| 13 | CMD-0112 | 12/02/2026 | Virement | 9 | 18,50 € | 19:30 | 21000 |
Une fois les valeurs corrigées, la commande Effacer les cercles de validation, sous la même flèche, retire les cercles restants. Relance la commande Entourer après chaque série de corrections, pour vérifier qu’aucune cellule n’a été oubliée.
Comment supprimer une validation des données sur Excel
Pour supprimer une validation des données, sélectionne les cellules, ouvre Données puis Validation des données, et clique sur Effacer tout avant de valider par OK. La règle, le message de saisie et l’alerte disparaissent ensemble, mais les valeurs déjà saisies restent en place. Une restriction de validation des données désigne cette même règle, et elle se supprime donc par le même bouton.
Retrouver toutes les cellules qui portent une règle
Une règle ne se voit pas dans la grille, et une feuille reprise d’un collègue peut en cacher. Ouvre Accueil, Rechercher et sélectionner, puis Validation données, et Excel sélectionne d’un coup toutes les cellules qui en portent une. L’onglet « Règles prêtes » en compte 105, les quinze lignes de ses sept colonnes. La fenêtre Atteindre, ouverte par F5, mène au même résultat par son bouton Cellules.
Pourquoi la validation de données ne fonctionne pas
Quand une validation laisse passer une valeur interdite, cette valeur est presque toujours arrivée par un collage, et non par une saisie au clavier. Trois autres causes produisent le même effet.
- Un collage a fait entrer la valeur. Excel ne contrôle jamais ce qu’on colle, et un collage ordinaire efface en plus la règle de la cellule. Une quantité de 999 copiée depuis une cellule sans validation entre ainsi dans la colonne bornée à 40, et la cellule qui la reçoit n’est plus bornée. Le collage spécial des seules valeurs garde la règle, mais Excel laisse passer le 999 de la même façon.
- La valeur était là avant la règle. Les données existantes ne sont jamais contrôlées, ni quand la règle est créée, ni quand sa borne change. La commande Entourer les données non valides les retrouve.
- L’alerte laisse passer la valeur. Les styles Avertissement et Informations acceptent la valeur après confirmation, et seul Stop bloque la saisie.
- La formule vise la mauvaise ligne. Quand la formule d’une règle personnalisée n’est pas écrite pour la première cellule de la plage, elle teste une ligne voisine de celle qu’on remplit.
La commande Validation des données grisée
Le bouton Validation des données se grise dès que la feuille est protégée, et il redevient actif quand la protection est levée. La protection de feuille empêche en effet de créer ou de modifier une règle, c’est pourquoi aucune feuille du classeur d’exemple n’est protégée. Le bouton se grise aussi quand plusieurs feuilles sont groupées, et un clic sur l’onglet d’une seule feuille suffit à le réactiver.
Questions fréquentes
Une validation s’applique à une colonne entière quand on clique sur la lettre de la colonne avant d’ouvrir la fenêtre, et la règle couvre alors toutes les lignes de la feuille. Elle couvre aussi l’en-tête, qui se retrouve hors règle, comme « Quantité » sous une règle de nombre entier. Mieux vaut donc commencer la sélection sous l’en-tête, ou appliquer la règle à la colonne d’un tableau.
Une règle appliquée à toute une colonne d’un tableau Excel se recopie sur chaque ligne ajoutée au tableau, y compris quand on tape une valeur juste sous sa dernière ligne. C’est la façon la plus simple de garder une règle à jour sur une liste qui s’allonge, sans revenir sur la plage.
Une validation se copie seule, sans les valeurs, par le collage spécial. Copie une cellule qui a la règle, sélectionne les cellules de destination, puis ouvre Collage spécial et choisis Validation. Seule la règle arrive, avec son message et son alerte, et la destination garde ses valeurs et ses couleurs. Une borne écrite comme une référence, telle que =$I$2, lit alors la cellule I2 de la feuille de destination.
Une liste de validation n’accepte qu’une valeur par cellule, et une seconde sélection remplace la première. Pour cumuler plusieurs choix dans une cellule, il faut une macro, comme celle de la liste déroulante à choix multiple.
La validation agit avant la saisie, en refusant ou en signalant une valeur hors règle, alors que la mise en forme conditionnelle colore une cellule après coup, sans jamais rien refuser. Les deux se complètent sur un fichier rempli à plusieurs, puisque la couleur repère aussi les valeurs entrées par un collage.