Qu'est-ce que la validation des données dans Excel ? Guide complet pour ajouter, modifier et supprimer des règles de validation

Comprendre et mettre en œuvre les principes de validation des données est essentiel, que vous soyez analyste junior ou spécialiste confirmé. Dans ce tutoriel, nous allons voir comment effectuer une validation des données dans Excel. Nous passerons en revue les différentes techniques disponibles et montrerons comment ajouter, modifier et supprimer des règles de validation.

Si vous souhaitez réviser les bases d'Excel avant de commencer — formules principales, opérateurs, fonctions mathématiques, etc. — consultez notre mémo des formules Excel.

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

La validation des données dans Excel est une fonctionnalité qui contrôle les valeurs autorisées dans une cellule. Elle propose un éventail de techniques permettant de vérifier des valeurs existantes ou de créer des règles qui s'appliqueront aux futures saisies.

Pour trouver la fonctionnalité Validation des données dans Excel, procédez comme suit :

1. Ouvrez l’onglet Données.
2. Allez dans le groupe Outils de données.
3. Cliquez sur le bouton Validation des données.

Ouverture de la fonctionnalité Validation des données

La fenêtre contextuelle suivante s’affiche à l’écran.

Fenêtre contextuelle Validation des données

Plus loin dans ce tutoriel, nous verrons en détail comment configurer cette fenêtre, y compris comment modifier et supprimer vos règles de validation. Commençons par comprendre l’intérêt de la validation des données. 

Pourquoi la validation des données est-elle importante ?

La validation des données consiste à vérifier l’exactitude et la cohérence des données saisies dans une base ou qui vont l’être, afin de les stocker et de les traiter. Elle garantit que les données respectent des critères prédéfinis : type de données correct, valeurs dans un intervalle donné, longueur appropriée, etc.

Une validation robuste est indispensable pour plusieurs raisons. Elle prévient les erreurs de saisie et garantit que les informations utilisées pour l’analyse sont fiables et valides. Examinons de plus près les raisons principales de recourir à la validation des données.

Qualité des données

La validation empêche l’enregistrement de valeurs inexactes ou incohérentes. Elle réduit les erreurs potentielles, améliore la qualité globale des données et renforce la pertinence des analyses.

L’importance de disposer de données de qualité est parfaitement résumée par le principe GIGO (garbage in, garbage out) en informatique : analyser des données biaisées conduit à des résultats erronés et trompeurs.

Voici quelques exemples de valeurs aberrantes lorsque des règles de validation ne sont pas appliquées :

• Inscriptions universitaires : une université ne peut pas avoir un nombre d’étudiants décimal ; seuls les entiers sont valides.
• Dossiers médicaux : la température corporelle maximale d’une personne ne peut pas dépasser 50 °C.
• Âge : un champ d’âge doit accepter uniquement des valeurs réalistes, généralement entre 0 et 120.
• Adresses e-mail : un champ e-mail doit respecter un format spécifique incluant le caractère « @ ».
• Stocks : les quantités de produits dans un inventaire ne doivent pas être négatives.

N’oubliez pas que si la validation des données renforce l’intégrité, elle ne garantit pas l’exactitude absolue. Par exemple, dans un tableau « chats et chiens », une règle peut n’autoriser que « cat » ou « dog », sans empêcher qu’un chat soit étiqueté par erreur comme « dog ».

Intégrité et conformité

La validation des données est cruciale pour préserver l’intégrité des informations et se conformer aux normes et réglementations sectorielles.

Dans la santé, la conformité à la HIPAA est obligatoire pour protéger les données des patients. De même, les entreprises opérant dans l’UE doivent respecter le RGPD afin d’assurer la confidentialité et la sécurité des données personnelles. 

Nettoyage et prétraitement

On dit souvent que les analystes consacrent environ 80 % de leur temps à préparer les données et seulement 20 % à l’analyse. Comme le nettoyage et le prétraitement prennent une part importante du temps, appliquer des règles de validation en amont du flux de travail permet de gagner un temps précieux. 

Prise de décision

Analyser des données validées conduit à des résultats fiables et pertiniels, qui aident à prendre des décisions mieux fondées et basées sur les données. À long terme, la validation fait gagner du temps et de l’argent à l’entreprise et favorise une activité plus saine, efficiente et rentable.

Différentes techniques de validation des données dans Excel

Excel propose plusieurs approches de validation pour répondre à différents besoins. Par commodité, regroupons-les et décrivons chaque catégorie.

Restreindre le type de données et l’intervalle/longueur

Dans Excel, vous pouvez choisir parmi les types de données suivants : Whole number, Decimal, Date, Time et Text.

Créons maintenant un tableau pour examiner l’applicabilité de certaines conditions logiques à ces différents types. Les conditions incluent : between, not between, equal to, not equal to, greater than, less than, greater than or equal to et less than or equal to.

• Between et Not Between : ces conditions s’appliquent généralement aux valeurs numériques, aux dates et aux heures. Pour le text, between et not between renvoient à la longueur (nombre de caractères) de la cellule.

• Equal to et Not Equal to : applicables à tous les types de données. Pour le text, il s’agit d’une correspondance exacte.

• Greater than, Less than, Greater than or equal to et Less than or equal to : adaptées aux types whole number, date et time, mais pas directement au text sauf si l’on considère la longueur du texte ou, dans certains cas, l’ordre alphabétique.

Créer des listes déroulantes

Il peut être nécessaire de limiter les options de certaines cellules à un ensemble prédéfini de valeurs. Reprenons l’exemple où seules « cat » et « dog » sont des saisies valides. Pour empêcher « crocodiles » et autres, le plus simple est de créer une liste déroulante pour chaque cellule concernée. Voici les étapes détaillées.

1. Saisissez les éléments à afficher dans la liste déroulante. Vous pouvez aussi trier la liste en la sélectionnant, clic droit, Trier, puis choisissez la méthode.

Saisie des éléments de la liste déroulante

2. Convertissez la liste en tableau Excel. Sélectionnez une cellule de la plage et pressez Ctrl+T. Cette étape est importante : si vous ajoutez ou supprimez des éléments, les listes déroulantes dépendantes seront mises à jour automatiquement.

Conversion de la liste en tableau

3. Sélectionnez la ou les cellules où vous souhaitez ajouter la liste déroulante.
4. Ouvrez la fenêtre contextuelle Validation des données. Onglet Données > groupe Outils de données > bouton Validation des données.
5. Dans l’onglet Options, dans le champ Autoriser, choisissez Liste. Laissez coché Liste déroulante dans la cellule. Laissez coché Ignorer si vide si la cellule peut rester vide ; sinon, décochez :

Définir une liste déroulante comme méthode de validation

6. Placez le curseur dans la zone Source, sélectionnez la plage de votre liste puis cliquez sur OK. Excluez la ligne d’en-tête pour éviter qu’elle n’apparaisse comme option.

Sélection de la source de la liste déroulante

7. Sélectionnez la ou les cellules pour lesquelles vous avez créé cette validation. Un triangle déroulant apparaît à droite : cliquez et choisissez la valeur désirée.

Ouverture d’une liste déroulante dans une cellule

Appliquer des formules personnalisées

• range : plage de cellules Excel à laquelle nous voulons appliquer la validation basée sur une formule.
• uppermost_cell : première (la plus haute) cellule de la plage.

Uniquement nombres ou uniquement texte

Ces deux formules vérifient si le contenu d’une cellule est un nombre ou un texte. Utile pour distinguer valeurs numériques et non numériques, ou texte et non-texte.

=ISNUMBER(uppermost_cell)Propulsé par

=ISTEXT(uppermost_cell)Propulsé par

Texte uniquement en majuscules ou en minuscules

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))Propulsé par

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))Propulsé par

Aucun doublon

=COUNTIF(range, uppermost_cell)<=1Propulsé par

Valeurs commençant par une sous-chaîne précise

Cette formule vérifie si les n premiers caractères d’une chaîne correspondent exactement à une sous-chaîne. Elle renvoie TRUE ou FALSE. 

=EXACT(LEFT(uppermost_cell, n), substring)Propulsé par

Uniquement jours ouvrés ou uniquement week-ends

=WEEKDAY(uppermost_cell, 2)<=5Propulsé par

=WEEKDAY(uppermost_cell, 2)>5Propulsé par

Uniquement des dates passées

=uppermost_cell<TODAY()Propulsé par

Pas d’espaces en début ou fin

=uppermost_cell=TRIM(uppermost_cell)Propulsé par

Aucun espace

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")Propulsé par

Empêcher de dépasser un total

=SUM(range)<=total_cellPropulsé par

Pour accélérer votre maîtrise des formules dans Excel, suivez le cours Data Analysis in Excel, complet et axé pratique.

Comment utiliser la validation des données dans Excel

Pour plus de clarté, cette section est divisée en deux. La première montre comment configurer votre classeur pour intégrer des règles de validation. La seconde explique comment appliquer ces règles à des cellules déjà remplies. Voyons cela. 

Configurer un classeur Excel pour les règles de validation

1. Sélectionnez la ou les cellules où vous souhaitez appliquer la validation.
2. Ouvrez la fenêtre Validation des données (onglet Données > groupe Outils de données > Validation des données).
3. Dans l’onglet Options, dans le champ Autoriser, choisissez la méthode nécessaire. Par défaut, Toute valeur est sélectionné (aucune validation).

Sélection de la méthode nécessaire

4. Laissez coché Ignorer si vide si la ou les cellules peuvent rester vides ; sinon, décochez.

Gérer les cellules vides

5. Si vous avez choisi la méthode Liste, définissez la règle adaptée à votre type de données. Si vous avez choisi Personnalisée, saisissez la formule.

Choisir une condition logique pour restreindre l’intervalle/la longueur

Saisir les bornes inférieure et supérieure

Appliquer une formule personnalisée

6. Dans l’onglet Message de saisie, vous pouvez définir un Titre et un Message. Par défaut, il n’y a pas de message de saisie. 

Définir un message pour guider la saisie

Aperçu du message de saisie

7. Dans l’onglet Alerte d’erreur, choisissez le Style (Arrêt, Avertissement ou Information), le Titre et le Message d’erreur. Par défaut, le style est Arrêt.

Configurer un message d’erreur

Après avoir cliqué sur OK, testez en saisissant une valeur invalide dans une cellule vide concernée. À titre de comparaison, voici un exemple de message d’erreur pour chaque style. 

Message d’erreur de style Arrêt lors d’une valeur invalide

Message d’erreur de style Avertissement

Message d’erreur de style Information

Message d’erreur par défaut

Remarque : les styles Avertissement et Information n’empêchent pas la saisie de données invalides, contrairement au style Arrêt.

Appliquer la validation sur des cellules déjà remplies

Et si certaines cellules ciblées contiennent déjà des valeurs ? La fonctionnalité Validation des données gère la validation avant et après saisie. 

1. Sélectionnez les cellules visées. Certaines contiennent déjà des données.

Sélection des cellules, y compris celles déjà remplies

2. Ouvrez la fenêtre Validation des données (onglet Données > Outils de données > Validation des données).
3. Dans l’onglet Options, choisissez la méthode voulue dans Autoriser (par défaut Toute valeur).

Définir la validation pour les cellules sélectionnées

3. Sélectionnez une cellule du lot validé, ouvrez l’onglet Données, cliquez sur la flèche du bouton Validation des données (groupe Outils de données) et choisissez Entourer les valeurs non valides.

Entourer les données non valides dans Excel

Données non valides entourées

4. Modifiez chaque valeur invalide pour respecter les critères : le cercle disparaît alors automatiquement. Important : si vous supprimez simplement la valeur, le cercle reste.
5. Pour retirer les cercles de validation, sélectionnez une cellule du lot, onglet Données > Outils de données > Validation des données > Effacer les cercles de validation. 

Supprimer les cercles de validation

Comment modifier la validation des données dans Excel

1. Sélectionnez une cellule qui a déjà une validation.
2. Ouvrez la fenêtre contextuelle Validation des données (onglet Données > Outils de données > Validation des données).
3. Apportez les modifications nécessaires dans les onglets de la fenêtre Validation des données. Au besoin, reportez-vous à la section « Comment utiliser la validation des données ».
4. Dans l’onglet Options, cochez Appliquer ces modifications à toutes les autres cellules ayant les mêmes paramètres.
5. Cliquez sur OK.

Appliquer les modifications à toutes les cellules avec les mêmes paramètres

Comment supprimer la validation des données dans Excel

1. Sélectionnez une cellule qui a déjà une validation.
2. Ouvrez la fenêtre contextuelle Validation des données (onglet Données > Outils de données > Validation des données).
3. Dans l’onglet Options, cochez Appliquer ces modifications à toutes les autres cellules ayant les mêmes paramètres.
4. Cliquez sur le bouton Effacer tout.
5. Cliquez sur OK.

Supprimer la validation des données

Conclusion

Nous avons défini la validation des données, ses bénéfices, son implémentation dans Excel, les techniques disponibles, ainsi que la façon d’utiliser, de modifier et de supprimer la validation dans Excel.

Pour exploiter tout le potentiel d’Excel et apprendre à préparer, visualiser et analyser des données via des exercices pratiques — et découvrir des fonctionnalités avancées — essayez le parcours Excel Fundamentals. Vous pouvez aussi suivre des cours spécialisés comme Data Preparation in Excel ou Financial Modeling in Excel pour approfondir votre expertise. Ces ressources vous aideront à passer de la validation de base à la maîtrise des fonctionnalités avancées d’Excel.