Vous en avez assez de mettre à jour manuellement vos listes déroulantes dans Excel à chaque ajout ? Vous perdez du temps et risquez des erreurs de saisie à cause d’une liste qui n’est plus à jour ? Comment faire pour que votre menu déroulant s’actualise tout seul ?
Cet article vous montre, étape par étape, comment créer une liste déroulante dynamique. C’est une liste qui se met à jour automatiquement lorsque vous ajoutez ou supprimez des éléments de votre source de données. Nous verrons plusieurs méthodes, adaptées à votre version d’Excel et à votre besoin, le tout sans utiliser de macro.
Quelle méthode choisir pour votre liste déroulante dynamique ?
Le choix de la bonne technique dépend de deux choses : votre version d’Excel et ce que vous voulez faire. Il n’y a pas une seule bonne réponse, mais il y a une méthode plus simple pour chaque cas. Utiliser la bonne vous fera gagner beaucoup de temps.
Pour vous aider à choisir rapidement, voici un tableau simple. Identifiez votre situation et suivez la méthode recommandée. C’est le moyen le plus direct d’arriver au résultat que vous cherchez.
| Votre besoin | Votre version d’Excel | Méthode recommandée |
|---|---|---|
| Une liste simple, qui se met à jour quand on ajoute un élément en bas. | Toutes les versions | Méthode 1 : Le Tableau Excel |
| Une liste plus complexe, avec une source de données pas toujours bien rangée. | Toutes les versions (surtout avant 2019) | Méthode 2 : Fonctions DECALER/NBVAL |
| Une liste simple, qui doit automatiquement supprimer les doublons et être triée. | Microsoft 365 (ou Excel 2021) | Méthode 3 : Fonctions UNIQUE/FILTRE |
| Deux listes liées (le choix dans la première change les options de la seconde). | Toutes les versions | Section : Listes en cascade |
Méthode 1 : La plus simple et rapide avec un Tableau Excel (Toutes versions)
Dans 80% des cas, cette méthode est la meilleure. Elle est facile à mettre en place, fiable et fonctionne sur toutes les versions récentes d’Excel. Le principe est de dire à Excel que vos données sont un « Tableau » officiel. Une fois que c’est fait, Excel comprend que cette plage de cellules est un bloc dynamique.
L’avantage principal, c’est que le tableau étend sa plage automatiquement lorsque vous ajoutez une nouvelle ligne juste en dessous. Votre liste déroulante, qui dépend de ce tableau, se mettra donc à jour sans que vous n’ayez rien à faire.
Étape 1 : Mettre vos données sous forme de Tableau
Commencez par votre liste de données source. C’est la liste des éléments que vous voulez voir apparaître dans votre menu déroulant. Assurez-vous qu’elle a un titre (un en-tête).
- Sélectionnez n’importe quelle cellule dans votre liste de données.
- Utilisez le raccourci clavier Ctrl + L (ou Cmd + L sur Mac).
- Une petite fenêtre « Créer un tableau » apparaît. Vérifiez que la plage de cellules est correcte et que la case « Mon tableau comporte des en-têtes » est cochée. Puis, cliquez sur OK.
Vos données sont maintenant dans un tableau structuré. Vous le remarquerez au changement de couleurs et à l’apparition de flèches de filtre sur les titres.
Étape 2 : Nommer la colonne de données qui vous intéresse
Pour que la validation des données retrouve facilement votre liste, il faut lui donner un nom. C’est plus propre et plus facile à gérer que d’utiliser des références de cellules comme `$A$2:$A$10`.
- Allez dans l’onglet Formules du ruban Excel.
- Cliquez sur Gestionnaire de noms.
- Dans la fenêtre qui s’ouvre, cliquez sur Nouveau…
Une nouvelle fenêtre « Nouveau nom » apparaît. Vous devez remplir deux champs :
- Nom : Donnez un nom simple et sans espace à votre liste. Par exemple : `ListeClients`.
- Fait référence à : C’est la partie la plus importante. Effacez ce qui est écrit et entrez une formule qui pointe vers la colonne de votre tableau. Si votre tableau s’appelle `Tableau1` et votre colonne `Clients`, la formule est : `=Tableau1[Clients]`.
Cliquez sur OK, puis Fermer. Votre plage nommée dynamique est prête.
💡 Astuce rapide : Pour trouver le nom de votre tableau, cliquez dessus. L’onglet « Création de tableau » apparaît. Tout à gauche, vous trouverez une case « Nom du tableau ». Vous pouvez le renommer ici pour plus de clarté, par exemple `T_Clients`.
Étape 3 : Créer la liste déroulante avec la Validation des données
C’est la dernière étape. Vous allez maintenant créer le menu déroulant dans la cellule de votre choix.
- Sélectionnez la cellule (ou les cellules) où vous voulez placer la liste déroulante.
- Allez dans l’onglet Données.
- Cliquez sur Validation des données.
- Dans l’onglet Options (ou « Paramètres » selon la version), sous « Autoriser », choisissez Liste.
- Dans le champ Source, tapez le signe égal (=) suivi du nom que vous avez défini à l’étape 2. Par exemple : `=ListeClients`.
- Cliquez sur OK.
Votre liste déroulante est créée. Pour vérifier qu’elle est bien dynamique, retournez à votre tableau source et ajoutez un nouvel élément sur la ligne vide juste en dessous. Revenez ensuite sur votre liste déroulante : le nouvel élément doit apparaître automatiquement.
Méthode 2 : La méthode universelle avec DECALER et NBVAL (Anciennes versions)
Cette méthode est plus ancienne et un peu plus technique, mais elle reste très puissante. Elle est utile si vous ne pouvez pas utiliser un Tableau Excel, ou si vous travaillez sur de très vieilles versions du logiciel. Elle repose sur la combinaison de deux fonctions : NBVAL et DECALER.
Voici comment ça marche :
- La fonction NBVAL compte le nombre de cellules non vides dans une colonne. Elle nous dit combien d’éléments il y a dans notre liste.
- La fonction DECALER crée une plage de cellules dynamique. On lui donne un point de départ, et on lui dit de quelle hauteur (combien de lignes) doit être la plage, en utilisant le résultat de NBVAL.
Le résultat est une plage nommée qui s’ajuste parfaitement à la taille de votre liste de données.
Étape 1 : Préparer la formule dans le Gestionnaire de noms
Comme pour la méthode précédente, tout se passe dans le Gestionnaire de noms. C’est là que nous allons stocker notre formule « magique ».
Supposons que votre liste de données se trouve dans la Feuille1, dans la colonne A, et qu’elle commence à la cellule A2 (avec un titre en A1). La formule à utiliser sera la suivante :
Formule à copier : =DECALER(Feuil1!$A$2;0;0;NBVAL(Feuil1!$A:$A)-1)
Cette formule peut faire peur, mais elle est logique. Voici ce que chaque partie signifie :
| Paramètre | Dans notre formule | Explication |
|---|---|---|
réf (référence de départ) |
Feuil1!$A$2 |
On dit à Excel de commencer à la première cellule de notre liste. Les symboles `$` (référence absolue) sont importants pour que la référence ne bouge pas. |
lignes (décalage vertical) |
0 |
On ne veut pas décaler le point de départ vers le bas. |
colonnes (décalage horizontal) |
0 |
On ne veut pas non plus décaler le point de départ sur le côté. |
hauteur (taille de la plage) |
NBVAL(Feuil1!$A:$A)-1 |
C’est la partie clé. On compte toutes les valeurs dans la colonne A, puis on retire 1 pour ne pas compter le titre. C’est la hauteur dynamique de notre liste. |
Maintenant, mettons cette formule en place :
- Allez dans l’onglet Formules > Gestionnaire de noms.
- Cliquez sur Nouveau…
- Donnez un nom à votre plage, par exemple `ListeProduits`.
- Dans le champ « Fait référence à », collez la formule DECALER que nous venons de voir. Adaptez `Feuil1!$A$2` et `Feuil1!$A:$A` à votre propre fichier.
- Cliquez sur OK.
Étape 2 : Lier la liste déroulante à la plage nommée
Cette étape est la même que pour la méthode du Tableau. Il suffit de créer la liste déroulante et de lui indiquer le nom que vous venez de créer.
- Sélectionnez la cellule où vous voulez le menu déroulant.
- Allez dans Données > Validation des données.
- Choisissez Autoriser : Liste.
- Dans Source, tapez le nom de votre plage : `=ListeProduits`.
- Cliquez sur OK.
Désormais, chaque fois que vous ajouterez un produit à la fin de la colonne A, la fonction NBVAL mettra à jour son calcul, la fonction DECALER ajustera la taille de la plage, et votre liste déroulante affichera le nouvel élément.
Méthode 3 : La solution moderne et facile avec Microsoft 365 (UNIQUE, FILTRE)
Si vous avez un abonnement Microsoft 365 ou une version récente d’Excel (2021 et plus), oubliez DECALER. Excel a introduit de nouvelles fonctions, appelées « tableaux dynamiques », qui rendent ce processus incroyablement plus simple.
Les deux fonctions qui nous intéressent sont UNIQUE et TRIER. Comme leur nom l’indique, UNIQUE supprime les doublons d’une liste, et TRIER la classe par ordre alphabétique. On peut les combiner pour créer une liste source parfaite, propre et à jour.
Étape 1 : Créer la liste source dynamique avec une seule formule
L’avantage de cette méthode est qu’on n’a plus besoin du Gestionnaire de noms. On crée une nouvelle liste, visible directement dans la feuille de calcul.
- Choisissez une cellule vide dans votre feuille de calcul, là où vous voulez que votre liste source propre apparaisse. Par exemple, en C2.
- Tapez la formule suivante, en remplaçant `A2:A50` par la plage de vos données brutes :
=TRIER(UNIQUE(A2:A50)) - Appuyez sur Entrée.
Excel va automatiquement créer une nouvelle liste qui se « déverse » dans les cellules en dessous. Cette liste est triée et ne contient aucun doublon. Elle est entourée d’une bordure bleue pour indiquer qu’il s’agit d’un tableau dynamique.
Étape 2 : Lier la validation des données à cette nouvelle liste
Maintenant, il ne reste plus qu’à dire à la liste déroulante de se baser sur ce nouveau tableau dynamique. Et c’est là qu’intervient une nouvelle notation très simple.
- Sélectionnez la cellule pour votre menu déroulant.
- Allez dans Données > Validation des données.
- Choisissez Autoriser : Liste.
- Dans le champ Source, cliquez sur la première cellule de votre liste dynamique (la cellule C2 dans notre exemple).
- Ajoutez un symbole dièse (#) juste après la référence de la cellule. La source doit donc être : `=C2#`.
💡 C’est quoi ce symbole # ? Dans les versions modernes d’Excel, le dièse après une référence de cellule signifie « prendre toute la plage du tableau dynamique qui commence ici« . C’est ce qui rend la liste déroulante dynamique. Si votre liste en C2 s’allonge, `=C2#` s’adaptera automatiquement.
Cliquez sur OK. C’est terminé. Si vous ajoutez une nouvelle valeur (même un doublon) à votre plage de données brutes, la formule en C2 se mettra à jour, et la liste déroulante suivra instantanément. C’est la méthode la plus propre et la plus efficace pour les utilisateurs de Microsoft 365.
Niveau Expert : Créer des listes déroulantes en cascade (liées)
Une fois que vous maîtrisez la liste déroulante dynamique simple, la question suivante est souvent : « Comment faire pour que le choix dans une première liste filtre les options d’une deuxième ? ». C’est ce qu’on appelle des listes déroulantes en cascade ou dépendantes. Par exemple, vous choisissez un pays dans la liste 1, et la liste 2 ne vous propose que les villes de ce pays.
La logique est de nommer chaque sous-liste avec le nom de l’option de la première liste. Ensuite, on utilise la fonction INDIRECT pour appeler la bonne plage nommée.
Solution universelle avec la fonction INDIRECT
Cette méthode fonctionne sur toutes les versions d’Excel.
- Préparez vos données : Organisez vos données en colonnes. Le titre de chaque colonne doit correspondre exactement à une option de votre première liste déroulante. Par exemple, si votre première liste contient « France », « Espagne », « Italie », vous devez avoir une colonne avec le titre « France » contenant des villes françaises, une colonne « Espagne », etc.
- Créez les plages nommées : Vous devez créer un nom pour chaque colonne de sous-catégories.
- Sélectionnez le titre et les données de la première colonne (ex: « France » et les villes françaises).
- Allez dans Formules > Gestionnaire de noms > Nouveau.
- Le nom doit être exactement le même que le titre (ex: `France`). Faites cela pour chaque colonne.
- Créez la première liste déroulante : C’est une liste simple qui se base sur les titres de vos colonnes (France, Espagne, Italie).
- Créez la deuxième liste déroulante (la liste en cascade) :
- Sélectionnez la cellule pour la deuxième liste.
- Allez dans Données > Validation des données > Liste.
- Dans le champ Source, tapez la formule :
=INDIRECT(A1), où A1 est la cellule contenant votre première liste déroulante. - Cliquez sur OK.
La fonction INDIRECT prend le texte de la cellule A1 (par exemple « France ») et le transforme en une référence à la plage nommée `France`. Ainsi, la deuxième liste affiche dynamiquement le contenu de la bonne plage.
Solution moderne pour Microsoft 365 avec FILTRE
Avec Microsoft 365, c’est encore plus simple. On peut utiliser la fonction FILTRE.
- Organisez vos données : Ayez deux colonnes. La première avec la catégorie (Pays) et la deuxième avec la sous-catégorie (Ville).
- Créez la première liste déroulante : Créez une liste unique et triée des pays avec la formule `=TRIER(UNIQUE(A2:A10))`. Liez votre première liste déroulante à cette plage avec la notation `#`.
- Créez la source de la deuxième liste : Dans une cellule vide, utilisez la fonction FILTRE. Si votre première liste est en D2, la formule est :
=FILTRE(B2:B100; A2:A100=D2). Cette formule filtre la colonne des villes (B) pour ne garder que celles où la colonne pays (A) est égale au choix fait en D2. - Liez la deuxième liste déroulante : Dans la validation des données de votre deuxième liste, utilisez la référence à la cellule de la formule FILTRE, suivie du symbole `#`.
Cette méthode est plus souple car elle ne nécessite pas de créer des dizaines de plages nommées.
Téléchargez notre fichier d’exemple pour pratiquer
La meilleure façon d’apprendre est de pratiquer. Pour vous aider à maîtriser ces différentes techniques, nous avons préparé un fichier Excel qui contient des exemples fonctionnels pour chaque méthode expliquée dans cet article. Vous pourrez analyser les formules, voir comment les plages sont nommées et tester par vous-même.
N’hésitez pas à télécharger ces ressources pour vous entraîner :
- Téléchargez notre fichier d’exercice complet : il contient les 3 méthodes et le cas des listes en cascade.
- Consultez aussi le fichier d’exemples de Microsoft : pour d’autres cas d’usage de la validation de données.
Comment faire si ma source de données est sur un autre onglet ?
C’est un cas très courant. Les méthodes fonctionnent parfaitement. Le plus simple est de créer un Nom dans le Gestionnaire de noms. Lorsque vous définissez la plage dans le champ « Fait référence à », incluez le nom de l’onglet dans la référence, par exemple : `=Feuil2!$A$2:$A$50`. Une fois le nom créé, vous pouvez l’utiliser dans la validation des données sur n’importe quel autre onglet.
Ma liste déroulante affiche des vides, comment les supprimer ?
Cela arrive souvent avec la méthode DECALER/NBVAL si votre colonne contient des formules qui renvoient « » (vide). Pour corriger ça, il faut utiliser une formule plus complexe avec NB.SI. Mais la solution la plus simple est d’utiliser la méthode Microsoft 365. La formule =FILTRE(MaPlage; MaPlage<> » ») crée une nouvelle liste source qui exclut automatiquement toutes les cellules vides. Liez ensuite votre liste déroulante à cette nouvelle source.
Peut-on créer une liste déroulante avec des cases à cocher ?
Non, la fonction de validation des données ne permet pas de créer une liste avec des cases à cocher directement dans le menu déroulant. Une liste déroulante permet de choisir une seule valeur parmi une liste. Pour des choix multiples, il faut utiliser des contrôles de formulaire (cases à cocher) placés directement sur la feuille, mais ce n’est plus une liste déroulante.
La formule DECALER me renvoie une erreur, pourquoi ?
L’erreur la plus fréquente avec DECALER vient d’un mauvais calcul de la hauteur. Souvent, c’est parce que la fonction NBVAL compte aussi le titre de la colonne. C’est pour cela qu’il faut faire `NBVAL(…)-1` si votre plage de comptage inclut le titre. Assurez-vous aussi que vos références de cellules (`$A$2`) sont correctes et que la feuille de calcul est bien précisée (`Feuil1!`).
