Par défaut, vous pouvez saisir n’importe quelle valeur dans une cellule Excel.
Cela peut se révéler problématique notamment lors de la saisie d’informations dans une liste de données. En effet, saisir une date dans une cellule devant contenir un montant numérique ou saisir un chiffre à la place d’un nom peut entraîner des erreurs de calcul en cascade.
Excel sait parfaitement contrôler la saisie de valeurs dans une cellule et interdire des données incohérentes.
Cette fonctionnalité se nomme la validation des données.
Elle va vous permettre de définir le type de données autorisé dans une cellule et va même pouvoir afficher un message à l’utilisateur en cas d’incohérence.
Prenons un exemple simple :
Pour garantir la cohérence des données de ce tableau, il faudrait :
- N’autoriser que la saisie de nombre décimaux inférieurs à 200 dans la colonne « montant depenses »
- N’autoriser que les dates comprises entre le 1 er janvier et le 31 décembre 2020
- Créer une liste déroulante pour alimenter la colonne « Agence »
Autoriser uniquement des nombres ou des dates
Sélectionner les cellules de la colonne « Montant depenses » et Cliquer sur l’onglet Données.
Choisissez Validation des données dans le groupe « Outils de données »
Par défaut, la boîte de dialogue indique que tout est autorisé. Aucun contrôle n’est donc effectué sur la saisie.
Nous voulons indiquer à Excel que seules les valeurs numériques décimales inférieures à 200 doivent être autorisées.
Pour cela, cliquer sur la liste des autorisations
Excel vous permet d’autoriser uniquement des :
- Nombre entier, nombre décimaux
- Dates, heures
- Elément d’une liste déroulante
- Texte d’une longueur précise (Ex: un matricule doit toujours contenir 5 caractères)
Pour notre exemple, choisir « Décimal » et constater que vous pouvez encore plus restreindre les nombres autorisés avec des critères de type supérieur, inférieur, différent, compris entre…
Valider par OK. Pour vérifier si cela fonctionne, essayez de saisir un texte ou un nombre > 200, votre saisie sera refusée par Excel.
Procédez de même pour limiter la plage des dates autorisées dans la colonne « Date depense »
Créer une liste de validation
Notre société ne compte que trois agences : Nice, Lyon et Chartres. Pour pouvoir exploiter de manière optimale les données de notre liste notamment dans un tableau croisé dynamique, il faut impérativement les écrire à l’identique sur chaque ligne.
Chartre, chartres et chartes sont trois informations différentes pour Excel.
- Commencez par sélectionner les cellules où la validation de données doit s’appliquer. Dans notre exemple, nous sélectionnons C2:C100
- Cliquer sur l’onglet Données et choisissez Validation des données dans le groupe « Outils de données«
- Dans la boîte qui s’affiche, cliquez Liste dans la zone « Autoriser »
Pour indiquer quelles valeurs doivent s’afficher dans la liste vous pouvez :
- Saisir directement dans la zone « Source » la liste des valeurs autorisées en séparant chaque valeur par un point virgule.
- Saisir, au préalable, la liste des valeurs dans une autre zone de votre feuille de calcul.
Puis dans la zone « Source », sélectionner ce bloc de cellules comme source de valeurs de votre liste. Excel affiche alors les adresses du bloc de cellules source. - Cette deuxième solution est à prioriser si votre liste est amenée à évoluer
4. Laissez cocher les choix : liste déroulante et ignorer si vide (sauf si vous considérez que la saisie d’une valeur est obligatoire)
Constatez que dorénavant, une liste déroulante facilite le choix de l’agence et qu’aucune autre valeur n’est permise.
Modifier le message d’erreur en cas de non validation
Le message standard d’Excel n’est pas explicite et ne précise pas quelle est la règle de validation à respecter. C’est à vous d’indiquer tout cela.
Nous allons préciser que seules les dates de l’année 2020 sont autorisées
1.Commencez par renseigner les critères de validation souhaités si ce n’est pas déjà fait.
2. Puis dans l’onglet Options « Message de saisie« , indiquez un message d’aide à la saisie qui s’affichera sous forme d’info-bulle lorsque l’utilisateur tapera une valeur dans une des cellules concernées.
Ce message sera indicatif et non bloquant
3. Dans l’onglet « Alerte d’erreur« , saisissez le message à afficher lorsque la saisie contredit les critères de validation.
Ce message remplacera le message standard d’Excel et empêchera toute validation non conforme à la règle indiquée