Créer une liste déroulante dynamique dans Excel
Une liste déroulante classique pointe vers une plage fixe : ajoutez une formation en bas de la liste et elle n’apparaît pas. Une liste dynamique s’agrandit toute seule. Trois méthodes existent (tableau de données, nom défini avec DECALER, formule UNIQUE sous Microsoft 365), ainsi que les listes en cascade, dont le contenu dépend d’un premier choix.
Le problème de la plage fixe
Une liste déroulante se crée par Données › Validation des données › Autoriser : Liste (voir aussi notre page validation des données et protection). La zone Source accepte trois types de contenu :
| Source | Exemple | Limite |
|---|---|---|
| Valeurs tapées | Excel;Word;Outlook | À modifier à la main dans chaque validation |
| Plage fixe | =$A$2:$A$6 | Une valeur ajoutée en A7 n’apparaît pas |
| Nom ou formule dynamique | =ListeFormations ou =$F$2# | S’adapte automatiquement : c’est l’objet de cette page |
=$A$2:$A$100 intègre les futures valeurs, mais affiche des dizaines de lignes vides dans la liste. Ce n’est pas une vraie solution.=Listes!$A$2:$A$6). Masquez ensuite la feuille Listes pour protéger les valeurs.Comparer les méthodes : simulateur
Modifiez la liste source de la colonne A : ajoutez des formations, un doublon ou une ligne vide, et observez ce que chaque méthode affiche dans la liste déroulante.
Mise en place pas à pas
Méthode 1 : tableau de données
- Cliquez dans la liste, Ctrl + L, cochez « Mon tableau comporte des en-têtes ».
- Création de tableau › Nom du tableau : tFormations.
- Formules › Définir un nom : nom ListeFormations, fait référence à
=tFormations[Formation]. - Validation des données, Source :
=ListeFormations.
La zone Source refuse la référence structurée directe : passez par un nom, ou tapez =INDIRECT("tFormations[Formation]").
Méthode 2 : nom dynamique DECALER
- Formules › Gestionnaire de noms › Nouveau.
- Nom : ListeFormations.
- Fait référence à :
=DECALER(Listes!$A$2;0;0;NBVAL(Listes!$A:$A)-1;1). - Validation des données, Source :
=ListeFormations.
Le -1 retire l’en-tête du comptage. Détails sur la fonction dans notre page DECALER, EQUIV, INDEX et INDIRECT.
Méthode 3 : TRIER + UNIQUE (Microsoft 365)
- Dans une cellule libre (F2) :
=TRIER(UNIQUE(FILTRE(A2:A100;A2:A100<>""))). - Le résultat se propage vers le bas.
- Validation des données, Source :
=$F$2#.
Le symbole # désigne toute la plage propagée, quelle que soit sa taille. Voir les fonctions matricielles.
Listes déroulantes en cascade
La seconde liste dépend du premier choix : on choisit une catégorie en B2, puis seulement les formations de cette catégorie en C2. Chaque liste de formations porte le nom exact de sa catégorie, et la source de la validation de C2 est =INDIRECT($B2). Cascades à 3 niveaux, noms avec espaces et remise à zéro : voir notre page listes déroulantes en cascade.
=INDIRECT(SUBSTITUE($B2;" ";"_")).Exercice de synthèse : fiche d’inscription avec listes dynamiques
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Formations : transformez la liste en tableau nommé tFormations, puis créez le nom ListeFormations.
- Feuille Inscriptions, colonne B : liste déroulante
=ListeFormations. Ajoutez « VBA » en bas du tableau et vérifiez qu’elle apparaît. - Feuille Listes : nommez les plages Bureautique, PAO et Data, ainsi que la liste des catégories (Categories).
- Colonne C : liste des catégories ; colonne D : liste en cascade
=INDIRECT($C2). - Colonne E : tarif de la formation choisie en D avec INDEX + EQUIV. Comparez avec la feuille Inscriptions (corrigé).