Validation des données Excel : listes déroulantes et règles de saisie
La validation des données est l’outil n°1 pour fiabiliser les saisies dans un classeur Excel partagé. Listes déroulantes, interdiction des doublons, contrôle des formats, messages personnalisés : tout passe par cet outil unique, souvent sous-exploité.
- 🎓 Débutant à intermédiaire
- ⏱ 15 min de lecture
- 📋 Toutes versions Excel
Les 5 exemples de cette page pré-construits dans un fichier Excel. Manipulez les listes déroulantes et validations pendant votre lecture.
- ✔ La validation des données fiabilise les saisies en imposant des règles (listes, formats, formules).
- ✔ Les 2 usages principaux : listes déroulantes (80% des cas) et validation par formule personnalisée.
- ✔ La meilleure pratique pro : combiner un tableau structuré (Ctrl+T) avec
INDIRECTpour des listes dynamiques qui s’étendent automatiquement. - ✔ Toujours personnaliser le message d’erreur pour aider l’utilisateur à comprendre ce qu’on attend de lui.
1À quoi sert la validation des données ?
La validation des données contrôle ce que les utilisateurs peuvent saisir dans une cellule. Au lieu de laisser tout le monde taper n’importe quoi, vous imposez des règles : une liste de valeurs autorisées, un intervalle numérique, un format précis, ou une règle personnalisée par formule.
Les cas d’usage professionnels sont nombreux :
- Formulaires de saisie (bulletins, devis, commandes)
- Classeurs partagés en équipe
- Tableaux de bord avec critères filtrables
- Fichiers transmis à des clients ou collègues non experts Excel
Sans validation : fautes de frappe, doublons, formats incohérents (« PARIS », « paris », « Paris » avec espace), valeurs aberrantes (un prix négatif, une quantité à 999999).
Avec validation : saisies standardisées, listes déroulantes, alertes automatiques, données propres et exploitables dans les tableaux croisés dynamiques.
2Accéder à la validation des données
Trois méthodes pour ouvrir la boîte de dialogue de validation :
- Ruban : onglet Données → groupe Outils de données → Validation des données
- Raccourci clavier :
Alt + D + L(version française d’Excel) - Depuis un tableau structuré : sélectionnez la colonne puis Validation des données (la règle s’étend automatiquement aux nouvelles lignes)
La boîte de dialogue contient 3 onglets :
| Onglet | Rôle |
|---|---|
| Options | Définit la règle de validation (type de critère, plage de valeurs, etc.) |
| Message de saisie | Infobulle qui apparaît quand l’utilisateur sélectionne la cellule |
| Alerte d’erreur | Message affiché si la saisie viole la règle définie |
3Les 7 types de critères disponibles
Excel propose 7 types de critères. Chacun répond à un besoin précis :
| Type | Usage typique | Exemple |
|---|---|---|
| Nombre entier | Quantités, comptages | Entre 1 et 100 |
| Décimal | Pourcentages, taux | ≥ 0 et ≤ 1 |
| Liste | Listes déroulantes | Paris ; Lyon ; Marseille |
| Date | Dates contraintes | ≥ aujourd’hui |
| Heure | Plages horaires | Entre 09:00 et 18:00 |
| Longueur du texte | Codes, références | Exactement 5 caractères |
| Personnalisé | Règles complexes par formule | Formule VRAI/FAUX |
Les types Liste et Personnalisé couvrent 80% des besoins professionnels. Les 5 autres sont utiles mais plus rarement employés. Concentrez-vous sur ces deux-là en priorité.
4Créer une liste déroulante (la fonctionnalité la plus utilisée)
La liste déroulante est l’usage principal de la validation des données. Trois méthodes de création, de la plus simple à la plus robuste.
4a. Liste saisie en dur
Pour une liste courte et stable (quelques valeurs qui ne bougeront jamais), saisissez directement les valeurs dans le champ Source, séparées par des points-virgules :
En version française d’Excel, le séparateur est le point-virgule (;). En version anglaise, c’est la virgule (,). Si vous copiez un exemple trouvé sur un site anglophone, pensez à convertir.
4b. Liste basée sur une plage de cellules
Pour une liste plus longue ou susceptible d’évoluer, saisissez vos valeurs dans une plage de cellules et référencez-la dans le champ Source :
Avantage : vous pouvez modifier les valeurs dans la plage sans rouvrir la boîte de validation. Inconvénient : si vous ajoutez une ville en A11, elle n’apparaît pas automatiquement dans la liste.
4c. Liste dynamique avec tableau structuré ⭐ (recommandé)
La méthode professionnelle. Convertissez votre plage en tableau Excel (Ctrl+T), donnez-lui un nom explicite (ex. tblVilles), puis utilisez cette formule comme source :
La liste s’étend automatiquement quand vous ajoutez de nouvelles valeurs au tableau. Vous ajoutez « Toulouse » en bas de la colonne → elle apparaît instantanément dans toutes les listes déroulantes qui utilisent cette source. À privilégier pour tout usage professionnel durable.
4d. Liste depuis une autre feuille
Pour pointer vers une source située sur une autre feuille du classeur, préfixez la plage du nom de la feuille avec un point d’exclamation :
Dans les anciennes versions d’Excel (2010 et antérieures), cette syntaxe directe ne fonctionne pas. Il faut alors créer une plage nommée (Formules → Gestionnaire de noms) et l’utiliser comme source de la liste.
Comparatif des 3 méthodes de liste
Quelle méthode choisir pour votre cas d’usage ? Ce tableau synthétique vous aide à décider :
| Méthode | Avantage | Limite | À utiliser quand |
|---|---|---|---|
| Saisie directe (ex. « Livré;En cours ») | Très rapide à mettre en place, aucune cellule mobilisée | Non maintenable : chaque modification impose de rouvrir la boîte de dialogue | Petites listes (2 à 5 valeurs), qui ne changeront jamais |
| Plage de cellules (ex. =$A$2:$A$10) | Simple, facile à modifier depuis les cellules | Non dynamique : l’ajout d’une valeur hors plage n’est pas pris en compte | Listes fixes dont on connaît la taille à l’avance |
| Tableau + INDIRECT ⭐ (ex. =INDIRECT(« tblVilles[Ville] »)) | Dynamique : la liste s’étend automatiquement avec les nouvelles entrées | Syntaxe un peu plus technique, demande de connaître les tableaux structurés | Usage professionnel, listes évolutives, classeurs partagés |
5Listes en cascade (choix pays → liste des régions)
Les listes en cascade sont la technique la plus puissante de la validation des données : une deuxième liste déroulante s’adapte au choix fait dans la première. C’est le cas d’usage pro le plus recherché.
Exemple : vous choisissez un pays dans la liste 1 (France, Belgique ou Suisse) → la liste 2 affiche uniquement les régions de ce pays.
Étape 1 : préparer les données sources
Organisez vos données en colonnes, une par groupe :
| A | B | C | |
|---|---|---|---|
| 1 | France | Belgique | Suisse |
| 2 | Île-de-France | Flandre | Vaud |
| 3 | Auvergne-Rhône-Alpes | Wallonie | Genève |
| 4 | Occitanie | Bruxelles-Capitale | Berne |
| 5 | Bretagne | — | Zurich |
Créer 3 plages nommées : sélectionnez A2:A5 → zone de nom (à gauche de la barre de formule) → tapez France et validez. Répétez pour Belgique (B2:B5) et Suisse (C2:C5).
Étape 2 : créer la 1re liste (pays)
Dans la cellule du formulaire (ex. F6), créez une liste classique :
Étape 3 : créer la 2e liste (région) avec INDIRECT
Dans la cellule suivante (ex. F7), utilisez INDIRECT qui va interpréter le nom de la plage selon le pays choisi :
Quand l’utilisateur choisit « France » en F6, INDIRECT devient =France et affiche la plage France. S’il choisit « Belgique », INDIRECT devient =Belgique. Et ainsi de suite.
Les noms de plages Excel ne peuvent pas contenir d’espaces, de tirets, ni commencer par un chiffre. Si un de vos pays s’appelle « Royaume-Uni », la plage nommée doit s’écrire Royaume_Uni avec un underscore.
Dans ce cas, utilisez : =INDIRECT(SUBSTITUE(F6;"-";"_")) pour convertir automatiquement.
Pays → région → ville. Catégorie → sous-catégorie. Client → contrat. Produit → variante. La technique est identique, on peut enchaîner 3 niveaux de cascade sans problème.
6Validation avec formule personnalisée
Le type Personnalisé permet d’écrire une formule qui renvoie VRAI (saisie acceptée) ou FAUX (saisie refusée). C’est là que la validation devient vraiment puissante. Voici 5 exemples prêts à coller.
6a. Empêcher les doublons dans une colonne
Pour une colonne de codes clients en A6:A12, avec anti-doublons :
La fonction NB.SI compte combien de fois la valeur saisie apparaît dans la plage. Si le résultat est 1 (seulement celle qu’on vient de saisir), la saisie est acceptée. Si c’est 2 ou plus, c’est un doublon, refusé.
6b. Forcer un préfixe (codes commençant par REF-)
Pour une colonne de codes produits en A17:A30 :
La fonction GAUCHE extrait les 4 premiers caractères et les compare au préfixe attendu. Parfait pour les codes produits, clients ou projets normalisés.
6c. Détecter les espaces parasites
Pour une colonne de villes en A27:A32, refus des saisies avec espaces avant/après/doubles :
Si le texte nettoyé des espaces parasites est identique à la saisie, c’est qu’il n’y en avait pas. Sinon, refus. Très utile pour éviter « Paris », « Paris » (avec espace final) et « Paris » (avec espace double) dans la même base.
6d. Dates futures uniquement
Pour une colonne de dates d’échéance en A37:A42 :
Idéal pour les dates d’échéance, livraison prévue, rendez-vous à venir.
6e. Combiner plusieurs conditions avec ET()
Pour des règles exigeantes, on combine plusieurs conditions avec ET(). Exemple : le code saisi en A47:A52 doit commencer par REF- ET faire exactement 10 caractères :
La formule accepte REF-A00123 (10 caractères, commence bien par REF-), refuse REF-A12 (trop court) et refuse CODE-A0012 (mauvais préfixe). Même logique avec OU() pour accepter plusieurs formats alternatifs.
Avant de coller une formule dans la validation, testez-la dans une cellule vide. Si elle renvoie VRAI ou FAUX correctement selon la valeur saisie, elle est prête. Si elle renvoie une erreur, corrigez d’abord avant de l’intégrer à la validation.
7Messages de saisie et alertes d’erreur
Message de saisie (infobulle d’aide)
L’onglet Message de saisie affiche une infobulle quand l’utilisateur sélectionne la cellule, avant qu’il tape quoi que ce soit. Idéal pour guider la saisie : « Saisir un montant en euros, sans symbole » ou « Format attendu : AAAA-MM-JJ ».
Alerte d’erreur : 3 styles au comportement différent
L’onglet Alerte d’erreur contrôle ce qui se passe si l’utilisateur saisit une valeur non conforme :
| Style | Comportement | Quand l’utiliser |
|---|---|---|
| Arrêt (défaut) | Interdit la saisie invalide, oblige à corriger | Formulaires stricts, données critiques |
| Avertissement | Prévient, mais permet de forcer si l’utilisateur insiste | Flexibilité contrôlée, valeurs inhabituelles autorisées |
| Information | Informe simplement, n’empêche rien | Sensibilisation, bonne pratique |
Le message d’erreur par défaut d’Excel est générique et peu utile pour l’utilisateur. Prenez 30 secondes pour le personnaliser : titre explicite (« Code client invalide ») et message clair (« Le code doit commencer par CLI-. Exemple : CLI-001 »). Vos utilisateurs vous remercieront.
8Outils méconnus mais précieux
Entourer les données invalides
Excel peut entourer en rouge toutes les cellules qui ne respectent pas la validation. Très utile quand vous appliquez une validation sur un tableau existant contenant déjà des données.
Accès : Données → flèche sous Validation des données → Entourer les données non valides.
Pour supprimer les cercles : même menu → Effacer les cercles de validation.
Copier une validation sur d’autres cellules
Trois méthodes :
- Recopie classique (Ctrl+C / Ctrl+V) : copie tout, y compris mise en forme et formules. Efficace mais écrase la mise en forme de destination.
- Collage spécial → Validation (Alt+E+S puis N) : copie uniquement la règle de validation, sans toucher au contenu ni à la mise en forme. La méthode propre.
- Poignée de recopie : tirer la poignée étend automatiquement la validation aux cellules adjacentes.
9Erreurs courantes et solutions
Cause : la source de la liste pointe vers une plage fixe (ex. $A$2:$A$10). Quand vous ajoutez une valeur en A11, elle n’est pas prise en compte.
Solution : convertissez la plage en tableau structuré (Ctrl+T) et utilisez =INDIRECT("tblNom[Colonne]") comme source. Voir section 4c.
Cause : un copier-coller classique remplace la validation par celle (ou l’absence) de la cellule source.
Solution : utilisez Collage spécial → Valeurs uniquement (Alt+E+S puis V) pour ne pas écraser la règle.
Cause : la plage source contient des cellules vides entre les valeurs, ou la plage est trop large par rapport au contenu réel.
Solution : redéfinissez la source pour couvrir exactement les cellules remplies, ou utilisez un tableau structuré qui gère automatiquement les bornes.
Cause : le nom de la plage contient un caractère interdit (espace, tiret, chiffre en début) ou n’existe pas encore dans le Gestionnaire de noms.
Solution : vérifiez dans Formules → Gestionnaire de noms. Renommez si besoin avec des underscores. Voir l’encadré ⚠️ de la section 5.
Cause : souvent due à un collage depuis le web, un autre classeur, ou une fonction « Effacer tout » (Ctrl+A puis Suppr) qui efface aussi les règles.
Solution : refaites la validation, et pour l’avenir préférez Effacer le contenu plutôt que Effacer tout.
10Questions fréquentes
Pourquoi ma validation des données ne fonctionne pas ?
Les causes les plus fréquentes sont au nombre de 5 : (1) la liste source contient des cellules vides qui créent des trous dans la déroulante, (2) la plage source n’est pas dynamique et n’intègre pas les nouvelles valeurs, (3) un copier-coller a écrasé la règle sans prévenir, (4) la feuille contient des données saisies avant l’ajout de la validation (utilisez alors « Entourer les données non valides » pour les repérer), (5) le séparateur utilisé est une virgule au lieu du point-virgule dans une liste en dur. Dans tous les cas, rouvrez la boîte Données → Validation des données pour vérifier que la règle est toujours en place.
Peut-on avoir une autocomplétion dans une liste déroulante ?
Nativement, les listes déroulantes de la validation des données n’ont pas d’autocomplétion. Pour obtenir ce comportement, deux solutions : soit utiliser une liste déroulante de formulaire (onglet Développeur → Insérer → ActiveX Combo Box), soit passer par VBA. Depuis Excel 365, une amélioration est en cours de déploiement pour ajouter nativement l’autocomplétion aux listes de validation, mais elle n’est pas encore disponible partout.
Peut-on mettre plusieurs validations sur la même cellule ?
Non, une seule validation active par cellule. Si vous avez besoin de combiner plusieurs règles (ex. « doit être un nombre entier ET doit être dans une liste autorisée »), utilisez une validation personnalisée avec une formule qui combine les conditions avec ET() ou OU().
Quelle différence entre validation et mise en forme conditionnelle ?
La validation empêche la saisie erronée en amont (contrôle avant la saisie). La mise en forme conditionnelle signale visuellement une valeur après qu’elle a été saisie (contrôle après). Les deux sont souvent complémentaires : validation pour bloquer les erreurs critiques, MFC pour colorer les valeurs inhabituelles mais autorisées.
Les listes déroulantes fonctionnent-elles sur Excel mobile et Excel web ?
Oui pour les listes classiques (saisie en dur ou plage simple). En revanche, les listes en cascade avec INDIRECT ne fonctionnent pas toujours correctement sur Excel mobile (iOS et Android) et peuvent avoir un comportement dégradé sur Excel web. Pour un classeur destiné à être utilisé sur mobile, préférez les listes simples ou les tableaux structurés sans INDIRECT.
Comment protéger la validation pour qu’elle ne soit pas contournée ?
Protégez la feuille (Révision → Protéger la feuille) en ne laissant déverrouillées que les cellules de saisie. Les règles de validation ne pourront plus être modifiées. Astuce complémentaire : dans l’onglet Alerte d’erreur de la validation, gardez le style « Arrêt » (par défaut) pour que l’utilisateur ne puisse pas forcer une saisie invalide.
Comment supprimer une validation existante ?
Sélectionnez la cellule (ou la plage), ouvrez Données → Validation des données, et cliquez sur le bouton Effacer tout en bas à gauche de la boîte de dialogue. La règle est supprimée, le contenu de la cellule reste intact.
🎓 Évitez les erreurs dans vos fichiers Excel partagés
Saisies incohérentes, doublons, formats variables : autant de problèmes qui font perdre des heures en entreprise. Une formation Excel personnalisée vous permet de mettre en place des classeurs fiables, adaptés à vos processus métier, avec accompagnement sur vos cas concrets.
🎓 Découvrir la formation Excel
📥 Télécharger le fichier compagnon
+Pour aller plus loin
Vous maîtrisez la validation des données ? Voici les fiches qui se marient le mieux avec cet outil :