La validation des données dans Excel ne se résume pas au chemin Données > Validation des données > Liste. Le mécanisme sous-jacent, les limites de portabilité entre versions et les pièges au déploiement sur des classeurs partagés méritent un examen plus technique que ce que proposent la plupart des guides pas-à-pas.
Validation des données Excel : ce que le moteur autorise réellement
La liste déroulante repose sur la règle de validation de type « Liste » appliquée à une cellule ou une plage de cellules. Lorsque vous définissez une source, Excel stocke cette règle dans les métadonnées de la feuille, pas dans la cellule elle-même.
A découvrir également : Comment protéger un fichier Excel par mot de passe ?
Deux modes de source coexistent. Le premier consiste à saisir les valeurs directement dans le champ Source, séparées par des points-virgules. Le second pointe vers une plage de cellules, éventuellement située sur une autre feuille du même classeur.
La distinction a des conséquences concrètes sur la maintenance. Une source en dur impose de rouvrir la boîte de dialogue de validation pour chaque ajout. Une source liée à une plage se met à jour dès que la plage est modifiée, à condition que la référence couvre les nouvelles entrées.
A voir aussi : Comment envoyer un e-mail à Zalando ?
Référence fixe ou plage dynamique
Avec une référence fixe (par exemple $A$1:$A$5), tout élément ajouté en A6 ne sera pas proposé dans le menu déroulant. Nous recommandons de structurer la source sous forme de tableau Excel (Ctrl+T) : la plage s’étend automatiquement à chaque nouvelle ligne insérée.
L’alternative est d’utiliser la fonction DECALER combinée à NBVAL pour construire une plage dynamique nommée. Cette approche fonctionne sur toutes les versions d’Excel, y compris les plus anciennes, mais elle est moins lisible qu’un tableau structuré.
Liste déroulante Excel sans source de données : quand et pourquoi
Saisir les valeurs directement dans le champ Source (séparées par des points-virgules) reste pertinent dans un seul cas : une liste courte, stable et sans risque de modification ultérieure. Typiquement, une liste Oui/Non ou un choix entre trois statuts figés.
Dès que la liste dépasse cinq ou six éléments, ou qu’un autre utilisateur est susceptible de la faire évoluer, pointer vers une plage de cellules est la seule option maintenable. La saisie directe ne permet ni tri, ni recherche, ni extension sans intervention manuelle dans la règle de validation.
Déployer une liste déroulante sur plusieurs cellules Excel
Sélectionnez la plage de destination avant d’ouvrir la boîte de validation. La règle s’applique alors à toutes les cellules sélectionnées en une seule opération. Pour étendre la validation à de nouvelles lignes après coup, utilisez le Collage spécial > Validation plutôt que de reconfigurer la règle.
Messages de saisie et alertes d’erreur
L’onglet « Message de saisie » de la boîte de validation permet d’afficher une info-bulle lorsque l’utilisateur sélectionne la cellule. L’onglet « Alerte d’erreur » définit le comportement quand une valeur hors liste est saisie.
Trois niveaux d’alerte existent :
- Arrêt : bloque toute valeur non présente dans la liste. C’est le seul mode qui empêche réellement la saisie libre.
- Avertissement : affiche un message mais laisse l’utilisateur confirmer une valeur hors liste.
- Informations : signale simplement que la valeur ne figure pas dans la liste, sans blocage.
Le mode par défaut est « Arrêt ». Si vous choisissez « Avertissement » ou « Informations », la liste déroulante ne garantit plus l’intégrité des données.
Limites de la validation des données entre versions Excel
Une liste déroulante créée dans Excel pour Microsoft 365 fonctionne dans les versions de bureau antérieures, à condition que la source ne repose pas sur des fonctions récentes ou des types de données liés. Microsoft indique que certaines fonctionnalités récentes, notamment les types de données liés, ne sont pas prises en charge de manière identique selon Excel pour Microsoft 365, Excel pour le web et les autres versions.
Sur Excel pour le web, la validation des données est partiellement prise en charge. Vous pouvez utiliser une liste déroulante existante, mais la configuration avancée (plages nommées dynamiques, DECALER) peut ne pas se comporter de la même façon.
Le collage contourne la validation
Un point que nous observons régulièrement en entreprise : coller une valeur dans une cellule validée ne déclenche pas l’alerte d’erreur. Le Ctrl+V écrase la règle sans avertissement. Ce comportement est documenté par Microsoft. La validation des données est une aide à la saisie, pas un dispositif de sécurité.
Pour un contrôle strict, il faut combiner la validation avec une macro VBA sur l’événement Worksheet_Change, ou recourir à la protection de feuille en verrouillant les cellules concernées.
Liste déroulante dynamique avec tableau Excel structuré
La méthode la plus robuste pour créer une liste déroulante qui s’adapte automatiquement repose sur les tableaux structurés. Voici la procédure :
- Saisissez vos valeurs dans une colonne, puis convertissez la plage en tableau via Insertion > Tableau (ou Ctrl+T).
- Dans la validation des données de la cellule cible, référencez la colonne du tableau avec la syntaxe =NomDuTableau[NomDeColonne].
- Chaque nouvelle ligne ajoutée au tableau apparaît immédiatement dans la liste déroulante, sans modification de la règle.
- Si le tableau se trouve sur une autre feuille, la référence fonctionne de la même manière à condition de passer par un nom défini dans le Gestionnaire de noms.
Cette approche élimine le recours à DECALER ou INDIRECT, deux fonctions volatiles qui peuvent ralentir un classeur volumineux.
Nommer la source pour simplifier la maintenance
Dans le Gestionnaire de noms (Formules > Gestionnaire de noms), créez un nom pointant vers la colonne du tableau. Utiliser un nom défini plutôt qu’une référence brute rend la formule de validation lisible par n’importe quel autre utilisateur du classeur.
Ce nom peut ensuite alimenter plusieurs listes déroulantes réparties sur différentes feuilles, avec un point de mise à jour unique.
La prochaine fois que vous déployez un classeur partagé avec des listes déroulantes, testez systématiquement le comportement au collage et vérifiez la compatibilité avec Excel pour le web. Ces deux vérifications évitent la majorité des remontées d’anomalies en production.
