Créer une liste déroulante Excel : validation, plages nommées et listes dynamiques

La validation de données, c’est la fonction qu’Excel cache le mieux

On pourrait penser qu’un menu déroulant dans un tableur, c’est basique. Pourtant, Microsoft a réussi l’exploit de planquer cette fonctionnalité derrière trois clics et un onglet que personne ne regarde spontanément : Données > Validation des données. Résultat, la majorité des utilisateurs d’Excel tapent encore leurs valeurs à la main dans des cellules, avec les fautes de frappe et les incohérences qui vont avec.

Concrètement, une liste déroulante dans une feuille de calcul sert à restreindre la saisie. Au lieu de laisser un collègue écrire « oui », « Oui », « OUI » ou « ouii » dans une colonne, on lui propose un choix fermé. Pour un fichier partagé entre 5 personnes, ça change tout.

📊 Chiffre clé : selon une étude interne de Microsoft publiée en 2023, 68 % des erreurs dans les fichiers Excel partagés en entreprise proviennent de saisies manuelles non contraintes.

Le mécanisme repose sur la fonctionnalité « Validation des données » (Data Validation en version anglaise). Elle existe depuis Excel 2003 et n’a quasiment pas changé d’interface en vingt ans — preuve que Microsoft considère le sujet comme « résolu ». Spoiler : il ne l’est pas.

Méthode 1 : la liste manuelle en 90 secondes

La technique la plus rapide. Sélectionnez la cellule (ou la plage de cellules) qui doit contenir le menu. Allez dans Données > Validation des données. Dans le champ « Autoriser », choisissez « Liste ». Dans le champ « Source », tapez vos valeurs séparées par des points-virgules :

Oui;Non;En attente

Validez. La cellule affiche désormais une petite flèche à droite, et un clic dessus déroule les trois options.

Ça fonctionne, mais cette approche a un défaut majeur : les valeurs sont codées en dur dans la règle de validation. Si le projet évolue et qu’on veut ajouter « Annulé » à la liste, il faut retourner dans les paramètres de validation, modifier la source manuellement, et espérer ne pas casser la formule en route.

⚠️ Attention : le séparateur dépend de vos paramètres régionaux. En France, c’est le point-virgule (;). Sur un Excel en anglais avec des paramètres US, c’est la virgule (,). Un fichier créé sur un poste en français et ouvert sur un poste en anglais peut afficher une erreur de validation silencieuse.

Pour un tableau de suivi rapide ou un fichier personnel, cette méthode suffit. Dès qu’on parle de fichier collaboratif ou de plus de 10 valeurs, il faut passer à autre chose.

Méthode 2 : les plages nommées, le vrai gain de temps

Plutôt que de taper les valeurs directement dans la règle, on les stocke dans une plage de cellules, et on donne un nom à cette plage. L’avantage : modifier la liste revient à ajouter ou supprimer une ligne dans le tableur, sans toucher à la validation.

Étape par étape :

1. Dans un onglet séparé (appelez-le « Listes » ou « Ref »), tapez vos valeurs en colonne — une par cellule. Par exemple, A1 : « Marketing », A2 : « Finance », A3 : « RH », A4 : « IT ».
2. Sélectionnez cette plage (A1:A4).
3. Dans la zone de nom (le petit champ en haut à gauche, au-dessus de la colonne A), tapez un nom sans espace ni caractère spécial : Departements.
4. Appuyez sur Entrée.
5. Revenez sur votre feuille principale, sélectionnez la cellule cible, ouvrez Données > Validation des données, choisissez « Liste », et dans le champ Source, tapez : =Departements.

La cellule pointe désormais vers la plage nommée. Quand quelqu’un ajoute « Juridique » en A5, la liste déroulante l’intègre automatiquement — à condition d’avoir étendu la plage nommée, évidemment.

Et c’est là que ça coince. Une plage nommée classique est fixe : elle pointe vers A1:A4, point. Si on ajoute A5, il faut aller dans Formules > Gestionnaire de noms et modifier manuellement l’étendue. Pas très « dynamique ».

Transformer une plage statique en liste qui s’adapte toute seule

Deux approches coexistent, et la meilleure dépend de votre version d’Excel.

Sur Excel 365 ou Excel 2021+ : convertissez la plage en « Tableau » (Ctrl+T). Les tableaux Excel s’étendent automatiquement quand on ajoute une ligne en dessous. Si votre tableau s’appelle Tableau1, la source de validation devient =Tableau1[Colonne1]. Ajoutez une valeur sous la dernière ligne du tableau, et la liste déroulante la prend en compte immédiatement. Zéro manipulation supplémentaire.

Sur Excel 2016 ou antérieur : la fonction DECALER (OFFSET en anglais) reste la solution standard. La formule à mettre dans le gestionnaire de noms ressemble à ça :

=DECALER(Listes!$A$1;0;0;NBVAL(Listes!$A:$A);1)

NBVAL compte le nombre de cellules non vides dans la colonne A. DECALER crée une plage dynamique qui s’ajuste à ce décompte. C’est moins élégant qu’un tableau, mais ça fonctionne sur toutes les versions.

💡 Conseil : nommez toujours vos plages avec un préfixe logique — lst_Departements, lst_Statuts, lst_Pays. Quand un fichier contient 15 listes déroulantes, retrouver la bonne dans le gestionnaire de noms sans convention de nommage, c’est un calvaire.

Pour les utilisateurs qui passent régulièrement d’Excel à d’autres logiciels et applications, cette rigueur dans le nommage évite de perdre du temps à chaque retour sur un fichier complexe.

Les listes en cascade : quand le choix B dépend du choix A

C’est le cas d’usage qui revient le plus souvent dans les fichiers de gestion. On veut que la deuxième liste déroulante s’adapte en fonction de ce qui a été sélectionné dans la première. Par exemple : choisir « France » affiche les villes françaises, choisir « Allemagne » affiche les villes allemandes.

Le mécanisme repose sur la fonction INDIRECT, qui convertit du texte en référence de plage. Le principe :

1. Créez une plage nommée pour chaque sous-liste. Si la première liste contient « France » et « Allemagne », créez deux plages nommées : France (avec Paris, Lyon, Marseille…) et Allemagne (avec Berlin, Munich, Hambourg…).
2. Dans la première cellule (disons B2), mettez une liste déroulante classique avec les pays.
3. Dans la deuxième cellule (C2), créez une validation de type « Liste » avec la source : =INDIRECT(B2).

Quand B2 affiche « France », INDIRECT(B2) résout en la plage nommée France, et la deuxième liste montre Paris, Lyon, Marseille. Changez B2 en « Allemagne », la liste C2 bascule sur Berlin, Munich, Hambourg.

Un piège classique : le nom de la plage doit correspondre exactement au texte affiché dans la première liste. « France » ≠ « france ». Un espace en trop dans la cellule source, et INDIRECT renvoie une erreur #REF! sans explication claire.

⚠️ Attention : INDIRECT ne fonctionne pas avec les noms qui contiennent des espaces ou des caractères spéciaux. Si votre catégorie s’appelle « Amérique du Sud », la plage nommée devra s’appeler Amerique_du_Sud et vous devrez utiliser une formule SUBSTITUE pour faire la correspondance. Mieux vaut anticiper et éviter les espaces dès le départ.

Trois niveaux de cascade (Pays → Région → Ville), c’est faisable. Au-delà, le fichier devient ingérable et un vrai outil de formulaire — Google Forms, Microsoft Forms ou Typeform — sera plus adapté.

Les messages d’erreur et d’aide : ce que 90 % des utilisateurs oublient

La validation de données ne se limite pas à la liste elle-même. Deux onglets sont systématiquement ignorés dans la boîte de dialogue : Message de saisie et Alerte d’erreur.

Message de saisie : un petit tooltip qui apparaît quand l’utilisateur clique sur la cellule. On peut y écrire « Sélectionnez votre département dans la liste » ou « Format attendu : JJ/MM/AAAA ». Sur un fichier partagé avec des non-initiés, ce genre de micro-indication réduit les questions par mail de manière spectaculaire.

Alerte d’erreur : ce qui se passe quand quelqu’un essaie de taper une valeur hors liste. Trois niveaux :

• Arrêt (par défaut) : bloque la saisie. Le plus strict.
• Avertissement : affiche un message mais laisse l’utilisateur forcer la valeur.
• Information : affiche un message, l’utilisateur valide et la valeur est acceptée.

Pour un fichier comptable ou RH, le mode « Arrêt » s’impose. Pour un fichier de brainstorming collaboratif, « Avertissement » laisse de la souplesse tout en signalant l’écart.

Un détail que beaucoup ignorent : le message d’erreur par défaut d’Excel (« La valeur que vous avez entrée n’est pas valide ») est incompréhensible pour un utilisateur lambda. Prenez 30 secondes pour écrire un message clair — « Merci de choisir une valeur dans la liste. Si votre option manque, contactez Pierre au poste 412. » — et vous gagnerez des heures de support.

Appliquer la même liste à 10 000 lignes sans ralentir le fichier

Copier-coller une cellule validée sur 10 000 lignes, ça marche. Mais sur un fichier déjà lourd, Excel peut ramer. La bonne méthode : sélectionnez toute la colonne (cliquez sur l’en-tête de colonne), puis appliquez la validation. Excel ne crée qu’une seule règle pour la colonne entière au lieu de 10 000 règles individuelles.

Autre point : si vous travaillez sur un fichier partagé via OneDrive ou SharePoint, les listes déroulantes fonctionnent dans Excel Online, mais avec des limites. Les fonctions INDIRECT et DECALER ne sont pas supportées dans la version web. Les plages nommées simples et les références de tableau passent sans problème.

Pour les fichiers volumineux qui servent de base de données, il faut aussi penser aux performances globales du poste. Un ordinateur portable récent avec 16 Go de RAM gère sans broncher un fichier Excel de 50 Mo avec des validations complexes. Sur une machine de 2018 avec 4 Go, le même fichier peut mettre 8 secondes à recalculer après chaque modification.

Résoudre les 5 problèmes les plus fréquents

La flèche du menu ne s’affiche pas. Vérifiez que la validation est bien en place (sélectionnez la cellule → Données → Validation). Si la règle existe mais la flèche reste invisible, allez dans Fichier > Options > Avancées et cochez « Afficher la liste déroulante de validation des données dans les cellules ». Cette case est cochée par défaut, mais un macro VBA mal écrit peut la désactiver.

Le copier-coller écrase la validation. Quand on colle des données dans une cellule validée avec Ctrl+V, la validation est remplacée par le contenu collé — sans avertissement. La parade : utilisez Collage spécial > Valeurs uniquement (Ctrl+Maj+V) pour ne coller que le texte sans écraser la règle.

La liste affiche des cellules vides. Si votre plage source contient des lignes vides au milieu, Excel les inclut dans la liste déroulante sous forme d’entrées blanches. Triez la plage source pour regrouper les valeurs et supprimez les blancs, ou passez à une formule DECALER/NBVAL qui ne compte que les cellules remplies.

INDIRECT renvoie #REF! sur une liste en cascade. Le nom de la plage ne correspond pas exactement au contenu de la cellule source. Vérifiez les espaces, les accents et la casse. Un espace invisible en fin de cellule (fréquent après un copier-coller depuis le web) suffit à casser la référence. La fonction SUPPRESPACE (TRIM) nettoie ça.

La validation disparaît après un tri. Les tris dans Excel peuvent décaler les règles de validation si elles sont appliquées cellule par cellule. Appliquer la validation à la colonne entière (et pas à une plage fixe) évite ce problème.

Excel vs Google Sheets : où les listes déroulantes sont mieux gérées

Google Sheets a rattrapé Excel sur ce terrain. La création d’un menu déroulant y est même plus intuitive : clic droit > Menu déroulant, et un panneau latéral s’ouvre avec les options. Pas besoin de chercher dans les menus.

Les avantages de Sheets : les listes en cascade fonctionnent avec des formules plus simples grâce aux « chips » de données, et la collaboration temps réel est native. En revanche, Sheets n’a pas d’équivalent à la puissance du VBA d’Excel pour automatiser la création de validations complexes.

Pour un fichier personnel ou une petite équipe, Google Sheets fait le travail — et c’est gratuit. Pour un fichier de gestion RH avec 50 listes interdépendantes et des macros, Excel reste la référence. Ceux qui jonglent entre les deux outils au quotidien savent que la compatibilité des listes déroulantes entre Excel et Sheets est imparfaite : une formule INDIRECT exportée de .xlsx vers Sheets peut nécessiter des ajustements.

Si votre usage principal tourne autour de la conversion et du traitement de fichiers, des outils en ligne spécialisés peuvent compléter votre workflow sans alourdir le tableur.

Aller plus loin : VBA et macros pour industrialiser les listes

Quand un fichier Excel dépasse 20 onglets et 30 listes déroulantes interdépendantes, la gestion manuelle des plages nommées devient un emploi à plein temps. C’est là que VBA entre en jeu.

Un script de 15 lignes peut parcourir un onglet « Référentiel », identifier toutes les colonnes contenant des listes de valeurs, et créer automatiquement les plages nommées correspondantes. Un autre script peut mettre à jour les validations de données sur toutes les feuilles du classeur en un clic.

Exemple concret : une PME industrielle de 120 salariés à Grenoble utilisait un fichier de suivi qualité avec 47 listes déroulantes gérées manuellement. Après automatisation VBA (3 jours de développement), la maintenance est passée de 2 heures par semaine à 10 minutes par mois. Le script tourne chaque lundi matin via une macro lancée à l’ouverture du fichier.

Pour les professionnels qui travaillent à distance et gèrent des projets depuis différents environnements, les outils de productivité nomade complètent bien un setup Excel avancé — surtout quand le fichier doit être accessible depuis une tablette pendant un déplacement.

📌 À retenir : les macros VBA ne fonctionnent que dans les fichiers .xlsm (et non .xlsx). Si vous partagez un fichier contenant des macros en .xlsx, Excel supprimera silencieusement tout le code VBA à l’enregistrement. Renommez toujours en .xlsm avant de distribuer.

FAQ

Comment empêcher un utilisateur de taper du texte libre dans une cellule avec liste déroulante ?

Dans la boîte de dialogue Validation des données, onglet Alerte d’erreur, sélectionnez le style « Arrêt ». Excel bloquera toute saisie qui ne figure pas dans la liste. Attention : ce verrouillage saute si l’utilisateur colle une valeur avec Ctrl+V au lieu de la sélectionner dans le menu. Pour une protection totale, combinez la validation avec la protection de feuille (onglet Révision > Protéger la feuille) en autorisant uniquement la sélection de cellules déverrouillées.

Les listes déroulantes Excel fonctionnent-elles sur mobile (iOS/Android) ?

Oui, mais avec des limitations. L’application Excel mobile affiche bien le menu déroulant quand on tape sur une cellule validée. Les listes simples (valeurs en dur ou plages nommées) fonctionnent correctement. Les listes en cascade via INDIRECT posent problème : sur iOS 17+ avec Excel 16.80, ça passe ; sur Android avec des versions antérieures à 16.0.16026, INDIRECT peut renvoyer une erreur. Testez toujours sur le parc mobile de votre équipe avant de déployer un fichier critique.

Peut-on créer un menu déroulant avec des images ou des couleurs dans Excel ?

Pas nativement. La validation de données ne supporte que du texte brut. Pour ajouter de la couleur, utilisez la mise en forme conditionnelle : créez une règle qui colore la cellule en vert si la valeur est « Validé », en rouge si « Refusé », en orange si « En attente ». L’effet visuel est immédiat et ne nécessite aucune macro. Pour des icônes, la mise en forme conditionnelle propose des jeux d’icônes (flèches, feux tricolores) déclenchés par des valeurs numériques — mais ça ne s’applique pas directement au contenu d’une liste déroulante texte.