Tout ce que vous devez savoir sur la validation des données dans Microsoft Excel

Que vous souhaitiez restreindre la saisie des données, garantir la cohérence ou éviter les erreurs, la validation des données dans Microsoft Excel est l'outil qu'il vous faut. Il peut également servir de filet de sécurité vital si vous envisagez de partager votre classeur avec des personnes qui découvrent Excel ou qui ne connaissent pas votre feuille de calcul.

La validation des données peut être facilement annulée ou annulée. Ce n'est pas une manière infaillible de garantir que les données saisies dans les cellules respectent les paramètres spécifiés.

Pour appliquer des règles de validation des données à une cellule ou une plage sélectionnée, accédez à l'onglet « Données » du ruban et cliquez sur la moitié supérieure de l'icône « Validation des données » si vous utilisez un écran large, ou sur la moitié gauche de l'icône si vous utilisez un écran plus étroit.

Types de règles de validation des données que vous pouvez définir dans Excel

Une fois que vous avez ouvert la boîte de dialogue Validation des données, vous verrez trois onglets. ​​​​​​

Parmi ces onglets, l'onglet Paramètres est l'endroit où vous pouvez créer les règles de validation des données. Voici les différentes options que vous pouvez sélectionner dans le champ Autoriser.

N'importe quelle valeur (par défaut)

Par défaut, les cellules d'Excel peuvent accepter n'importe quelle entrée, qu'il s'agisse de texte, de chiffres ou d'une formule. C'est pourquoi le champ Autoriser dans l'onglet Paramètres de la boîte de dialogue Validation des données est défini par défaut sur N'importe quelle valeur.

Bien que cette option puisse sembler redondante, en fait, elle est pratique si vous souhaitez autoriser toute saisie dans une cellule mais qu'un message apparaisse lorsque la cellule est sélectionnée. Faites défiler jusqu'à la section « Définition d'un message d'entrée de validation des données dans Excel » de cet article pour plus d'informations sur les messages d'entrée.

Nombre entier

L'option Nombre entier limite les entrées aux nombres entiers. Autrement dit, si un nombre décimal est saisi dans la cellule, il ne correspondra pas à la règle de validation des données. Après avoir sélectionné « Nombre entier » dans le champ Autoriser, vous verrez diverses autres options de restriction que vous pouvez appliquer pour spécifier davantage les paramètres que les nombres entiers doivent suivre.

Comme vous pouvez le voir dans la capture d'écran ci-dessus, sélectionner « Entre » dans le champ Données vous permet de saisir une valeur minimale et maximale.

En plus de spécifier les paramètres sous forme de valeurs, vous pouvez également référencer des cellules. Ici, la valeur minimale est saisie dans la cellule L2 et la valeur maximale est saisie dans la cellule L3.

Référencer des cellules plutôt que de saisir des valeurs dans ces champs signifie que vous pouvez facilement modifier les limitations sans avoir à relancer la boîte de dialogue.

Les modifications apportées à une règle de validation des données ne s'appliquent pas aux données existantes saisies dans une cellule. Ils ne sont appliqués que lorsque de nouvelles données sont saisies ou que des données existantes sont modifiées.

Décimal

La sélection de « Décimal » dans la liste Autoriser signifie que les cellules auxquelles la règle s'applique peuvent accepter les deux nombres entiers et décimaux. Comme pour l'option Nombre entier, lorsque vous sélectionnez « Décimal », d'autres options apparaissent qui vous permettent d'ajouter de la spécificité à la règle, et vous pouvez saisir des valeurs ou des références de cellule dans ces champs supplémentaires.

Liste

Lorsque vous sélectionnez « Liste » dans le champ Autoriser, seules les valeurs d'une liste prédéfinie sont autorisées et celles-ci apparaissent sous forme de menu déroulant lorsque la cellule est sélectionnée.

En plus de cliquer sur la flèche vers le bas dans une cellule contenant une liste déroulante, vous pouvez également sélectionner la cellule et utiliser le raccourci clavier Alt+Flèche vers le bas.

Deuxièmement, vous pouvez sélectionner une plage de cellules contenant les éléments que vous souhaitez voir apparaître dans la liste. Si les cellules constituent une colonne entière dans un tableau Excel formaté, la plage s'étendra et se rétrécira automatiquement lorsque des lignes seront ajoutées ou supprimées du tableau.

L'ordre des options dans la liste de validation des données est le même que l'ordre dans lequel elles sont disposées dans la plage source. Triez donc la plage source par ordre alphabétique ou numérique pour rendre la liste déroulante plus facile à utiliser.

Lorsque vous décidez quel type de source vous allez utiliser, demandez-vous si vous êtes susceptible d'ajouter plus d'options à la liste. Le moyen le plus efficace de créer une liste déroulante de variables consiste à formater les cellules source sous forme de tableau Excel.

Évitez de sélectionner une colonne entière comme source de validation des données, car cela empêche la ligne d'en-tête d'être incluse dans la liste déroulante et garantit que les performances de votre classeur ne sont pas affectées par la règle de validation des données.

Date

Lorsque vous sélectionnez « Date » dans le champ Autoriser, diverses autres options apparaissent où vous pouvez définir les paramètres de date plus en détail.

Lorsque vous saisissez une date dans l'un des champs de texte, assurez-vous d'utiliser un format de date reconnu en fonction des paramètres de votre système. Par exemple, pour le 31 décembre 2025, aux États-Unis, vous devrez saisir 31/12/2025et au Royaume-Uni, 31/12/2025. Excel vous indiquera si vous n'avez pas saisi un format reconnu.

De même, si vous référencez des cellules contenant des dates, assurez-vous qu’elles ont un format de numéro de date approprié.

Temps

Comme pour l'option Données dans le champ Autoriser de la boîte de dialogue Validation des données, la sélection de « Heure » ouvre d'autres options pour spécifier ce qui constitue une entrée valide.

Lorsque vous saisissez une heure dans l'un des champs, vous pouvez utiliser un format 12 heures ou 24 heures. Par exemple, en tapant 5:00:00 donne le même paramètre que la saisie 17:00:00. Il en va de même pour les cellules contenant des dates si vous insérez des références de cellules dans ces champs.

Longueur du texte

Choisissez « Longueur du texte » si vous souhaitez que la cellule ait une limitation de caractères. Dans cet exemple, les saisies dans les cellules sélectionnées sont limitées à 20 caractères.

Ne vous laissez pas berner par le mot texte dans la longueur du texte : l'entrée de la cellule peut être textuelle, numérique ou tout autre caractère. Si vous souhaitez limiter la saisie d'une cellule au texte uniquement, passez directement à la section suivante.

Coutume

Tout comme le formatage des nombres personnalisé, les règles de validation des données personnalisées d'Excel sont incroyablement puissantes et étendent considérablement les capacités du programme.

Après avoir sélectionné « Personnalisé », vous verrez un champ Formule au bas de la boîte de dialogue.

Cela signifie que vous pouvez écrire votre propre formule pour déterminer des paramètres particuliers pour une entrée valide.

Par exemple, pour autoriser le texte uniquement dans les cellules A1 à A20, sélectionnez ces cellules, ouvrez la boîte de dialogue « Validation des données », sélectionnez « Personnalisé » et tapez :

=ISTEXT(A1:A20)

Pour autoriser uniquement les chiffres, tapez :

=ISNUMBER(A1:A20)

Pour autoriser uniquement les entrées commençant par « HTG », utilisez un caractère générique :

=COUNTIF(A1:A20,"HTG*")

Enfin, pour autoriser les entrées uniques uniquement dans les cellules A1 à A5, tapez :

=COUNTIF($A$1:$A$5,A1)

Définition d'un message d'entrée de validation des données dans Excel

Le deuxième des trois onglets de la boîte de dialogue Validation des données d'Excel est Message d'entrée.

Cet onglet facultatif vous permet de définir un message à afficher lorsque des cellules contenant une règle de validation des données sont sélectionnées. Après avoir ouvert cet onglet et vérifié que la case en haut de la boîte de dialogue est cochée, tapez un titre et un message d'entrée, puis cliquez sur « OK ».

C'est ainsi que le message apparaît lorsqu'une cellule concernée est sélectionnée.

Les messages d'entrée n'affectent pas ce qui peut être saisi dans une cellule : ils agissent simplement comme des notifications d'aide pour vous rappeler, à vous ou à quelqu'un d'autre, ce qui doit être saisi dans la cellule.

Si vous modifiez la règle, n'oubliez pas de modifier le message d'entrée pour que tout s'aligne !

Définition d'une alerte d'erreur pour les entrées de données incorrectes dans Excel

Le troisième et dernier onglet de la boîte de dialogue Validation des données est Alerte d'erreur, qui vous permet de déclencher un message si les données saisies dans une cellule ne correspondent pas aux règles de validation des données.

Les alertes nouvellement définies n'apparaissent que lorsque de nouvelles données non valides sont saisies ou que des données existantes sont modifiées de manière incorrecte.

Une alerte d'erreur d'arrêt (voir le tableau ci-dessous) apparaît par défaut lorsque des données non valides sont saisies dans une cellule. Vous pouvez désactiver les alertes en décochant la case en haut de la boîte de dialogue après avoir ouvert l'onglet Alerte d'erreur.

Vous pouvez choisir parmi trois styles d'alerte : Arrêt, Avertissement et Informations.

Voici ce à quoi vous pouvez vous attendre pour chacun :

Quel que soit le style d'alerte que vous choisissez, saisissez un titre et un message d'erreur pour décider de ce qui apparaît à l'écran lorsqu'une entrée non valide est saisie. Voici à quoi ressemble une alerte Stop.

Cochez « Ignorer les espaces vides » dans l'onglet Paramètres de la boîte de dialogue Validation des données pour empêcher l'apparition d'une alerte lorsqu'une cellule est laissée vide.

Autres conseils de validation des données Excel

Voici quelques éléments supplémentaires que vous devez savoir lorsque vous utilisez la validation des données dans Microsoft Excel :

• Lorsque vous appliquez des règles de validation de données à une colonne d'un tableau Excel, elles s'appliquent automatiquement aux nouvelles lignes au fur et à mesure que vous les ajoutez.

• Pour mettre en évidence toutes les cellules contenant des valeurs qui ne respectent pas les règles de validation des données que vous avez définies, cliquez sur la flèche vers le bas à côté de l'icône Validation des données dans l'onglet Données du ruban, puis sélectionnez « Encercler les données invalides ». Vous verrez alors des cercles autour des cellules concernées.

• Pour sélectionner toutes les cellules d'une feuille de calcul auxquelles s'appliquent les règles de validation des données, appuyez sur F5 pour lancer la boîte de dialogue Aller à, cliquez sur « Spécial », cochez « Validation des données » et cliquez sur « OK ».

• Pour copier une règle de validation de données d'une cellule à une autre, sélectionnez d'abord une cellule ou une plage de cellules contenant la règle de validation de données que vous souhaitez copier. Ensuite, sélectionnez la cellule dans laquelle vous souhaitez dupliquer la règle et appuyez sur Ctrl+Alt+V pour lancer la boîte de dialogue Collage spécial. Enfin, cochez « Validation » et cliquez sur « OK ».

• Enfin, pour effacer les règles de validation des données des cellules sélectionnées, ouvrez la boîte de dialogue « Validation des données » et cliquez sur « Tout effacer ».

---

La validation des données n'est pas le seul outil que vous devez apprendre si vous souhaitez devenir un utilisateur expérimenté d'Excel. Par exemple, Power Query est idéal pour importer et mettre en forme des données, les tableaux croisés dynamiques sont essentiels pour une synthèse et une analyse rapides des données, et des fonctions polyvalentes, comme XLOOKUP, facilitent grandement la recherche et la récupération de données.