Validation des données : Contrôler ce que les utilisateurs peuvent saisir

Tout analyste de données expérimenté connaît cette vérité fondamentale : une feuille de calcul n'a de valeur que si les données qu'elle contient sont exactes. Lorsque plusieurs personnes collaborent sur un même fichier, il est presque inévitable que quelqu'un orthographie mal un nom, saisisse une date dans le mauvais format, ou insère accidentellement du texte là où un nombre est attendu. Ces « mauvaises données » se répercutent, entraînant des formules cassées, des tableaux croisés dynamiques inexacts et des rapports erronés.

C'est ici que la fonctionnalité de validation des données d'Excel devient votre première ligne de défense. En définissant des règles strictes sur ce qui peut être saisi dans une cellule, vous prévenez les erreurs de manière proactive. Si vous créez des outils destinés à être utilisés par d'autres, la maîtrise de la validation des données est indispensable. C'est l'étape cruciale qui sépare une feuille de calcul brouillonne de la création de tableaux de bord dynamiques sur Excel professionnels et sans erreurs.

Dans ce guide complet, nous allons tout explorer, des listes déroulantes de base aux restrictions de données avancées basées sur des formules. Si vous débutez avec les feuilles de calcul, nous vous conseillons de consulter brièvement notre guide complet d'Excel pour les débutants avant de vous plonger dans ces contrôles de saisie avancés.

Qu'est-ce que la validation des données dans Excel ?

La validation des données est une fonctionnalité intégrée qui restreint le type de données ou les valeurs que les utilisateurs peuvent saisir dans une cellule. Considérez-la comme un videur pour les cellules de votre feuille de calcul. Lorsqu'un utilisateur essaie de saisir une valeur, la règle de validation des données vérifie si elle répond à vos critères prédéfinis. Si c'est le cas, la donnée est acceptée. Sinon, Excel rejette la saisie et affiche un message d'avertissement ou d'erreur.

Avec la validation des données, vous pouvez :

• Créer des menus déroulants prédéfinis pour standardiser les saisies de texte.
• Restreindre les saisies à des nombres entiers, des pourcentages ou des décimales dans une plage spécifique.
• Limiter les dates et les heures (par exemple, n'autoriser que les dates de l'exercice fiscal en cours).
• Contrôler la longueur du texte (par exemple, forcer un utilisateur à saisir exactement cinq caractères pour un code postal).
• Utiliser des formules personnalisées pour créer des règles dynamiques et très spécifiques.

Où trouver la validation des données

Avant de commencer à créer des règles, vous devez savoir où se trouve l'outil sur le ruban Excel :

1. Sélectionnez la cellule ou la plage de cellules que vous souhaitez restreindre.
2. Accédez à l'onglet Données sur le ruban Excel.
3. Recherchez le groupe Outils de données.
4. Cliquez sur le bouton Validation des données (il ressemble souvent à une petite cellule avec une coche verte et un cercle rouge d'interdiction).

Cliquer sur ce bouton ouvre la boîte de dialogue Validation des données, qui contient trois onglets : Options (où vous définissez la règle), Message de saisie (pour guider l'utilisateur avant qu'il ne tape) et Alerte d'erreur (pour définir ce qui se passe s'il enfreint la règle).

Créer votre première liste déroulante

Le cas d'utilisation le plus courant de la validation des données est la création d'une liste déroulante. Cela force les utilisateurs à choisir parmi une liste d'options prédéfinie, éliminant complètement les fautes d'orthographe et les variations (comme « RH », « Ressources Humaines » et « R.H. »).

Création de liste déroulante étape par étape

1. Sélectionnez les cellules où vous souhaitez que le menu déroulant apparaisse (ex. : B2:B10).
2. Ouvrez la boîte de dialogue Validation des données.
3. Sous l'onglet Options, cliquez sur le menu déroulant Autoriser et sélectionnez Liste.
4. Dans la zone Source, deux options s'offrent à vous :
   • Les saisir manuellement : Entrez vos éléments séparés par des virgules (ex. : Pending, Approved, Rejected).
   • Référencer une plage : Cliquez sur la flèche à côté de la zone Source et mettez en surbrillance une plage de cellules contenant les éléments de votre liste (ex. : =$Z$1:$Z$3). C'est la meilleure pratique, car vous pourrez facilement mettre à jour les cellules de la colonne Z plus tard sans modifier la règle de validation.
5. Assurez-vous que la case Liste déroulante dans la cellule est cochée, puis cliquez sur OK.

Désormais, chaque fois qu'un utilisateur cliquera sur n'importe quelle cellule de B2:B10, une petite flèche apparaîtra, lui permettant de sélectionner exactement ce que vous attendez comme saisie.

Restreindre la saisie des données : Nombres, Dates et Longueur de texte

Si les listes déroulantes sont idéales pour les catégories de texte, qu'en est-il des données numériques ou temporelles ? La validation des données propose également des catégories intégrées pour cela.

Nombres entiers et Décimales

Si vous créez un bon de commande, vous ne pouvez pas vendre 1,5 ordinateur portable. Vous avez besoin d'un nombre entier. À l'inverse, si vous demandez un pourcentage de remise, il vous faut une décimale.

• Sélectionnez Nombre entier ou Décimal dans le menu déroulant Autoriser.
• Sous Données, choisissez votre logique (ex. : « comprise entre », « supérieur à », « inférieur à »).
• Définissez vos paramètres. Par exemple, pour exiger une quantité positive, sélectionnez « supérieur à » et tapez 0 dans la case Minimum.

Dates et Heures

Vous pouvez empêcher les utilisateurs de saisir des dates passées ou des dates en dehors d'une période de reporting spécifique. Choisissez Date dans le menu déroulant Autoriser. Pour forcer les utilisateurs à saisir une date égale ou ultérieure à aujourd'hui, sélectionnez « supérieur ou égal à », et dans la case Date de début, tapez la fonction Excel dynamique : =TODAY().

Longueur de texte

Parfait pour standardiser des identifiants comme les numéros de sécurité sociale, les identifiants d'employés ou les numéros de téléphone. Sélectionnez Longueur de texte, choisissez « égal à », et saisissez 5 pour imposer une chaîne d'exactement 5 caractères (utile pour les codes postaux, par exemple).

Validation des données personnalisée avec des formules

Les options standard sont puissantes, mais vous finirez par rencontrer un scénario nécessitant une logique personnalisée. En sélectionnant Personnalisé dans le menu déroulant Autoriser, vous pouvez écrire votre propre formule. La règle ici est simple : votre formule doit être évaluée comme VRAI (la saisie est autorisée) ou FAUX (la saisie est rejetée).

L'écriture de ces restrictions peut parfois ressembler à la création de tests logiques complexes avec la fonction SI, mais vous n'avez pas besoin de la fonction SI elle-même : Excel évalue automatiquement l'instruction sous forme de booléen VRAI/FAUX.

Exemple 1 : Éviter les doublons

Si vous collectez des numéros de facture dans la colonne A, vous voulez éviter que quelqu'un ne saisisse deux fois le même numéro. Sélectionnez la colonne A (A2:A100), choisissez la validation Personnalisée et entrez cette formule :

=COUNTIF($A$2:$A$100, A2)=1

Cette formule compte combien de fois la nouvelle valeur saisie apparaît dans la colonne. Si elle apparaît exactement 1 fois, l'instruction est VRAI et la donnée est acceptée. Si elle apparaît plus d'une fois, elle est évaluée comme FAUX, ce qui déclenche une erreur.

Exemple 2 : Forcer le texte à commencer par des caractères spécifiques

Supposons que chaque identifiant d'employé doive commencer par « EMP- », suivi de chiffres. Pour imposer cela dans la cellule A2, utilisez cette formule personnalisée :

=LEFT(A2, 4)="EMP-"

Résumé de formules de validation personnalisées utiles

Objectif de validation	Exemple de formule personnalisée (pour la cellule A2)	Comment ça marche
Doit contenir du texte (pas de chiffres)	=ISTEXT(A2)	Évaluée à VRAI uniquement si la saisie est une chaîne de texte.
Doit comporter un nombre exact de mots (ex. : 2 mots)	=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))=1	Compte les espaces entre les mots pour s'assurer qu'exactement deux mots sont saisis.
Doit être une adresse e-mail (contient « @ »)	=ISNUMBER(SEARCH("@", A2))	Cherche le symbole « @ ». S'il est trouvé, SEARCH renvoie un nombre, ce qui rend ISNUMBER vrai.
La valeur ne peut pas dépasser la limite d'une cellule spécifique	=A2<=$B$1	S'assure que le montant saisi en A2 est inférieur ou égal à une limite de budget global en B1.

Personnaliser les messages de saisie et les alertes d'erreur

Une bonne feuille de calcul ne se contente pas de bloquer les mauvaises données ; elle guide poliment l'utilisateur sur la façon de saisir les bonnes. Les onglets Message de saisie et Alerte d'erreur dans la boîte de dialogue Validation des données sont essentiels pour une excellente expérience utilisateur.

Message de saisie

Il agit comme une infobulle. Lorsqu'un utilisateur clique sur la cellule validée, une petite boîte jaune apparaît. Vous pouvez lui donner un titre (ex. : « Formatage requis ») et un message (ex. : « Veuillez saisir la date au format JJ/MM/AAAA. »).

Alerte d'erreur

Lorsqu'un utilisateur enfreint la règle, Excel affiche une fenêtre contextuelle par défaut qui indique « Cette valeur ne correspond pas aux restrictions de validation des données définies pour cette cellule. » Ce n'est pas très utile. Vous pouvez personnaliser ce message d'erreur et choisir l'un des trois niveaux de gravité (Styles) :

• Arrêt (Croix rouge) : Le paramètre le plus strict. Il rejette complètement la mauvaise donnée et force l'utilisateur à réessayer ou à annuler.
• Avertissement (Triangle jaune) : Avertit l'utilisateur que la donnée enfreint la règle, mais lui propose un bouton « Oui » pour ignorer la règle et saisir la donnée quand même.
• Informations (Le « i » bleu) : Le paramètre le plus souple. Il informe simplement l'utilisateur de la règle mais accepte automatiquement la donnée non valide lorsqu'il clique sur « OK ».

Pour une stricte intégrité des données, utilisez toujours le style Arrêt.

Exemple pratique : Créer un formulaire de frais infaillible

Mettons cela en pratique dans un scénario du monde réel. Imaginez que vous créez un modèle de remboursement de frais. Si vous ne contrôlez pas les saisies, vous vous retrouverez avec un désordre qui vous obligera à passer des heures à utiliser l'IA pour nettoyer et transformer les données par la suite. Validons de manière proactive trois colonnes : Date, Catégorie et Montant.

1. La colonne Date (A2:A50) :
   • Sélectionnez la plage. Ouvrez Validation des données.
   • Onglet Options : Autoriser Date. Données : comprise entre.
   • Date de début : =TODAY()-30 (pas de dépenses datant de plus de 30 jours).
   • Date de fin : =TODAY() (pas de dates futures).
2. La colonne Catégorie (B2:B50) :
   • Sélectionnez la plage. Ouvrez Validation des données.
   • Onglet Options : Autoriser Liste.
   • Source : Travel, Meals, Supplies, Software.
   • Onglet Alerte d'erreur : Style Arrêt. Titre « Catégorie invalide ». Message « Veuillez sélectionner une catégorie approuvée dans le menu déroulant. »
3. La colonne Montant (C2:C50) :
   • Sélectionnez la plage. Ouvrez Validation des données.
   • Onglet Options : Autoriser Décimal. Données : supérieur à.
   • Minimum : 0 (empêche les demandes de remboursement négatives).

En appliquant ces trois règles simples, vous avez instantanément rendu votre formulaire de frais immunisé contre les erreurs d'utilisateur les plus courantes.

Trouver et supprimer la validation des données

Il arrive parfois que vous héritiez d'une feuille de calcul qui se comporte bizarrement, rejetant vos saisies sans raison apparente. Pour découvrir où les règles de validation des données sont appliquées :

1. Appuyez sur F5 pour ouvrir la boîte de dialogue « Atteindre ».
2. Cliquez sur Cellules...
3. Sélectionnez Validation des données et cliquez sur OK. Excel mettra en surbrillance chaque cellule de la feuille de calcul à laquelle une règle est appliquée.

Pour supprimer une règle, sélectionnez simplement les cellules restreintes, ouvrez la boîte de dialogue Validation des données, cliquez sur le bouton Effacer tout dans le coin inférieur gauche, puis appuyez sur OK.

Laissez l'IA gérer les formules de validation complexes

Bien que les listes déroulantes de base et les limites de dates soient simples, créer des formules personnalisées infaillibles (comme la correspondance de texte complexe de type RegEx) peut s'avérer être un casse-tête, même pour les utilisateurs avancés. Au lieu de vous battre avec la syntaxe et les fonctions imbriquées, essayez ExcelGPT. Vous pouvez décrire votre besoin en langage naturel (par exemple, « Crée une règle de validation qui s'assure que le texte saisi commence par 'PO-' et se termine par exactement 5 chiffres ») et obtenir instantanément la formule personnalisée exacte.

Cette approche pour écrire des formules avec l'IA accélère considérablement votre flux de travail, vous permettant de vous concentrer sur l'analyse des données plutôt que de passer un temps infini à dépanner les contrôles de votre feuille de calcul.

Foire aux questions

Puis-je copier des règles de validation de données d'une cellule à une autre ?

Oui. Vous pouvez copier une cellule contenant une validation de données, sélectionner vos cellules cibles, faire un clic droit, choisir Collage spécial et sélectionner Validation. Cela ne collera que les règles, sans modifier le formatage ou le texte existant des cellules cibles.

Pourquoi ma validation des données permet-elle aux utilisateurs de coller de mauvaises données ?

Il s'agit d'une limitation bien connue d'Excel. La validation des données ne se déclenche que lorsqu'un utilisateur saisit manuellement des données et appuie sur Entrée. Si un utilisateur copie une valeur non valide à partir d'une autre cellule et la colle (avec Ctrl+V), cela écrase complètement les règles de validation de la cellule de destination. Pour éviter cela, les utilisateurs doivent être formés à ne coller que les valeurs, ou vous devez utiliser des macros VBA pour restreindre l'action de collage.

Puis-je faire en sorte qu'une liste déroulante change en fonction d'une autre liste déroulante ?

Oui, c'est ce qu'on appelle une liste déroulante dépendante ou en cascade. Vous pouvez l'obtenir en utilisant la fonction INDIRECT dans la zone Source de vos options de validation des données, en référençant la cellule de la première liste déroulante. Cela nécessite de configurer quelques plages nommées, mais c'est très efficace pour catégoriser des données (ex. : sélectionner « Fruits » dans la colonne A modifie automatiquement la liste déroulante de la colonne B pour afficher « Pomme, Banane, Orange »).

Comment puis-je mettre en évidence les données non valides qui étaient déjà présentes avant l'application de la règle ?

Si vous appliquez une règle de validation des données à des cellules qui contiennent déjà des données, Excel ne supprime pas automatiquement les entrées erronées. Pour les trouver, allez dans l'onglet Données, cliquez sur la flèche à côté de Validation des données, et sélectionnez Entourer les données non valides. Excel dessinera des cercles rouges autour de tout contenu de cellule existant qui enfreint vos nouvelles règles.