Nettoyer des données prend du temps. Les empêcher d’arriver en désordre en prend beaucoup moins. La validation des données d’Excel permet de définir ce que l’on peut saisir dans chaque cellule : uniquement des nombres compris entre certaines limites, uniquement des dates d’une période donnée, uniquement des valeurs d’une liste ou des textes d’une longueur précise. Si quelqu’un essaie de saisir autre chose, Excel l’avertit ou l’en empêche purement et simplement. C’est le meilleur moyen de garder propres les modèles partagés, les formulaires et les registres quotidiens. Dans ce guide, vous verrez comment l’utiliser.
Où se trouve la validation des données
Sélectionnez les cellules à contrôler et allez dans Données > Validation des données. La fenêtre comporte trois onglets :
- Options : le type de donnée autorisé et ses conditions.
- Message de saisie : un texte d’aide qui s’affiche lorsqu’on sélectionne la cellule.
- Alerte d’erreur : ce qui se passe quand quelqu’un saisit une donnée non valide.
Listes déroulantes
C’est l’usage le plus courant. Il convient aux colonnes dont l’ensemble de valeurs est fermé : services, statuts d’une commande, pays, catégories.
- Saisissez la liste des valeurs autorisées dans une feuille auxiliaire, par exemple dans
Listes!A2:A8. - Sélectionnez les cellules de destination.
- Dans Autoriser, choisissez Liste.
- Dans Source, saisissez
=Listes!$A$2:$A$8.
Désormais, chaque cellule affiche une flèche avec les options. Fini « En attente », « en attente » et « Att. » dans la même colonne.
Conseil : convertissez la liste auxiliaire en tableau (Ctrl+T) et utilisez un nom défini comme source. Ainsi, lorsque vous ajoutez des valeurs au tableau, la liste déroulante se met à jour toute seule.
Nombres compris dans une plage
Pour les quantités, les prix ou les pourcentages :
- Autoriser : Nombre entier ou Décimal.
- Données : comprise entre.
- Minimum et maximum : par exemple, entre 0 et 100 pour un pourcentage de remise, ou supérieur à 0 pour un prix.
Dates valides
Pour éviter les dates impossibles ou hors période :
- Autoriser : Date.
- Données : comprise entre.
- Date de début :
=DATE(2026;1;1). - Date de fin :
=AUJOURDHUI(), pour interdire les dates futures.
Cette règle oblige en outre à saisir de vraies dates, et non des textes qui ressemblent à des dates, ce qui évite bien des problèmes ultérieurs avec les filtres et les tableaux croisés dynamiques.
Longueur du texte
Utile pour les codes comportant un nombre fixe de caractères, comme un code postal à cinq chiffres ou un identifiant fiscal :
- Autoriser : Longueur du texte.
- Données : égale à 5.
Combinez cela avec le format texte dans la cellule pour conserver les zéros initiaux.
Règles personnalisées avec des formules
L’option Personnalisé accepte n’importe quelle condition que vous pouvez exprimer avec une formule renvoyant VRAI ou FAUX :
- Éviter les doublons dans la colonne A :
=NB.SI($A:$A;A2)=1. - Exiger un arobase dans une adresse e-mail :
=ESTNUM(CHERCHE("@";B2)). - Sans espaces :
=B2=SUBSTITUE(B2;" ";""). - Uniquement des majuscules :
=EXACT(C2;MAJUSCULE(C2)).
Messages de saisie et d’erreur
Un bon message de saisie explique ce qui est attendu : « Saisissez le prix hors TVA, en euros, avec deux décimales ». Cela évite des questions et des e-mails.
Dans l’alerte d’erreur, vous pouvez choisir le style :
- Arrêt : empêche de saisir la donnée. Utilisez-le pour les règles strictes.
- Avertissement : avertit, mais permet de continuer si l’utilisateur confirme.
- Informations : se contente d’informer.
Rédigez des messages clairs : au lieu de « Valeur non valide », préférez « La remise doit être comprise entre 0 % et 50 % ».
Limites à connaître
- Coller des données contourne la validation. Si quelqu’un copie et colle des valeurs depuis une autre feuille, Excel ne vérifie pas les règles. Vous pouvez le contrôler avec Données > Validation des données > Entourer les données non valides.
- Elle ne s’applique pas aux données existantes. Les règles ne contrôlent que ce qui est saisi à partir de maintenant. Utilisez l’option précédente pour trouver les anciennes valeurs non conformes.
- Ce n’est pas une mesure de sécurité. Toute personne disposant de droits de modification peut supprimer la validation. Pour l’éviter, protégez la feuille.
Validation + protection de la feuille
Un modèle robuste combine les deux :
- Déverrouillez les cellules dans lesquelles l’utilisateur doit écrire.
- Appliquez la validation des données à ces cellules.
- Protégez la feuille pour que les formules, les en-têtes et les règles ne puissent pas être modifiés.
Le résultat est un formulaire dans lequel on ne peut saisir que des données correctes aux bons endroits.
Questions fréquentes
Comment copier la validation vers d’autres cellules ?
Copiez une cellule qui contient la validation, sélectionnez la destination et utilisez Collage spécial > Validation.
Que faire des données qui étaient déjà incorrectes ?
Avant d’appliquer la validation, nettoyez la colonne : supprimez les espaces, harmonisez les valeurs et éliminez les doublons avec Supprimer les doublons Excel. Ensuite, utilisez « Entourer les données non valides » pour vérifier ce qui reste.
La validation des données fonctionne-t-elle dans Google Sheets et LibreOffice ?
Oui. Les deux programmes proposent la validation des données avec des listes déroulantes, des plages numériques et des règles personnalisées. Si vous partagez un modèle Excel, les règles de base sont conservées lorsqu’il est ouvert dans ces programmes.
Puis-je créer des listes déroulantes dépendantes ?
Oui. Par exemple, faire en sorte que la liste déroulante « Ville » n’affiche que les villes du pays choisi dans la colonne précédente. On y parvient avec des noms définis et la fonction INDIRECT comme source de la liste.
Comment supprimer la validation d’une plage ?
Sélectionnez les cellules, ouvrez Données > Validation des données et cliquez sur Effacer tout. Les valeurs déjà saisies ne changent pas.
Conclusion
La validation des données est le moyen le plus économique de garder vos feuilles propres : listes déroulantes pour les valeurs fermées, limites pour les nombres et les dates, longueur pour les codes et formules pour vos propres règles. Associez-la à des messages clairs et à la protection de la feuille, et vos modèles recevront des données correctes dès le premier jour. Si vous devez nettoyer ce qui existe déjà, nos outils gratuits vous aident à le faire en quelques minutes.
Essayez nos outils gratuits
Comparez, convertissez, fusionnez et nettoyez vos feuilles de calcul depuis votre navigateur, sans rien installer.
