Excel Liste Déroulante Dynamique : Comment la Créer ?

Vous en avez marre de devoir mettre à jour vos listes déroulantes dans Excel à chaque fois que vous ajoutez un nouvel élément ? Vous perdez du temps à ajuster la source de vos données et vous risquez des erreurs de saisie ?

Ce guide vous montre exactement comment créer une liste déroulante dynamique qui se met à jour toute seule. Vous allez découvrir 4 méthodes, de la plus simple à la plus experte, et tout ça sans écrire une seule ligne de macro VBA. Pour suivre les étapes, vous pouvez télécharger notre fichier d’exemple.

Pourquoi créer une liste déroulante dynamique dans Excel ? (Les Avantages)

Une liste déroulante classique, c’est bien, mais c’est figé. Si votre source de données change, la liste ne suit pas. Vous devez retourner dans le menu « Validation des données » et modifier la plage manuellement. C’est une perte de temps et une source d’erreurs.

Une liste déroulante dynamique résout ce problème. Elle est connectée à sa source de manière intelligente. Quand vous ajoutez ou supprimez un élément dans votre colonne de données, la liste déroulante se met à jour automatiquement. Voici les principaux avantages :

• Vous gagnez un temps fou : Plus besoin de mises à jour manuelles. Vous modifiez votre source, et c’est tout.
• Vous évitez les erreurs de saisie : En proposant toujours la liste de choix à jour, vous garantissez que les utilisateurs sélectionnent des valeurs correctes.
• Vos fichiers sont plus fiables : La maintenance est beaucoup plus simple. Le risque d’oublier de mettre à jour une liste est éliminé.
• Ça fait plus pro : Un tableau de bord ou un outil avec des listes qui s’actualisent seules est tout de suite plus sérieux et plus facile à utiliser pour tout le monde.

Méthode 1 : La Plus Simple avec les Tableaux Excel (Recommandée)

C’est de loin la méthode la plus simple et la plus efficace si vous avez une version récente d’Excel (2007 ou plus récent). Elle ne demande aucune formule compliquée. Le secret, c’est d’utiliser la fonction « Mettre sous forme de tableau ».

Un tableau Excel n’est pas juste une grille avec des couleurs. C’est un objet intelligent qui sait où il commence et où il finit. Quand vous ajoutez une ligne, le tableau étend sa plage automatiquement. C’est exactement ce dont on a besoin.

Étape 1 : Mettre ses données sous forme de Tableau (Ctrl+T)

La première chose à faire est de transformer votre simple liste de données en un vrai Tableau Excel.

1. Sélectionnez n’importe quelle cellule de votre liste de données (par exemple, la liste de vos produits ou de vos employés).
2. Allez dans l’onglet Accueil, puis cliquez sur « Mettre sous forme de tableau ».
3. Ou, encore plus simple, utilisez le raccourci clavier Ctrl + T (ou Cmd + T sur Mac).
4. Une petite fenêtre 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 si votre première ligne est un titre. Cliquez sur OK.

Votre liste a maintenant un nouveau design. Mais surtout, elle est devenue un objet dynamique.

Étape 2 : Créer la liste déroulante via la Validation des données

Maintenant que la source est dynamique, on va créer la liste déroulante qui s’y réfère.

1. Sélectionnez la ou les cellules où vous voulez faire apparaître votre menu déroulant.
2. Allez dans l’onglet Données, puis cliquez sur « Validation des données ».
3. Dans la fenêtre qui s’ouvre, sous l’onglet « Options », choisissez « Liste » dans le menu déroulant « Autoriser ».
4. Maintenant, le point clé. Dans le champ « Source », vous n’allez pas sélectionner les cellules comme d’habitude. Placez votre curseur dans la zone de la source, puis cliquez sur le titre de la colonne de votre tableau. Votre curseur va se transformer en une grosse flèche noire. Cliquez une seule fois. Excel va automatiquement insérer une référence structurée qui ressemble à `=INDIRECT(« Tableau1[NomDeVotreColonne] »)`.

Étape 3 : Tester l’ajout d’un nouvel élément

C’est le moment de vérité. Allez à la fin de votre liste de données source. Juste en dessous de la dernière ligne de votre tableau, tapez un nouvel élément et appuyez sur Entrée. Vous verrez que le tableau s’agrandit pour inclure cette nouvelle ligne.

Maintenant, retournez à votre cellule avec la liste déroulante. Cliquez dessus : le nouvel élément apparaît automatiquement dans la liste de choix, sans que vous ayez rien fait d’autre. C’est simple, rapide et fiable.

Méthode 2 : La Classique avec les fonctions DECALER et NBVAL

Avant l’arrivée des Tableaux, c’était LA méthode de référence. Elle est plus technique, mais elle reste très utile si vous travaillez sur d’anciennes versions d’Excel ou si vous avez besoin de plus de flexibilité. Le principe est de créer une « plage nommée » dont la taille s’ajuste grâce à des formules.

On va utiliser deux fonctions : DECALER (pour définir une plage à partir d’un point de départ) et NBVAL (pour compter le nombre d’éléments dans notre liste).

Comprendre la fonction DECALER

La fonction DECALER est un peu spéciale. Elle ne renvoie pas une valeur, mais une référence à une plage de cellules. Sa structure est : =DECALER(réf; lignes; colonnes; hauteur; largeur).

• réf : C’est le point de départ. Par exemple, la première cellule de votre liste.
• lignes/colonnes : C’est le décalage par rapport au point de départ. On mettra 0, car on veut commencer pile sur notre cellule de départ.
• hauteur/largeur : C’est la taille de la plage qu’on veut créer. La largeur sera 1 (une seule colonne). La hauteur, c’est là que la magie opère.

Comprendre la fonction NBVAL

La fonction NBVAL (NB pour « nombre », VAL pour « valeur ») est très simple : elle compte le nombre de cellules qui ne sont pas vides dans une plage. Si on l’applique à notre colonne de données, elle nous donnera exactement le nombre d’éléments dans notre liste. C’est ce chiffre qu’on utilisera pour l’argument « hauteur » de la fonction DECALER.

Tutoriel : Créer la plage nommée dynamique

On va maintenant combiner ces deux fonctions dans le « Gestionnaire de noms ».

1. Allez dans l’onglet Formules, puis cliquez sur « Gestionnaire de noms ».
2. Cliquez sur « Nouveau… ».
3. Dans le champ « Nom », donnez un nom simple et sans espace à votre plage, par exemple `MaListeDynamique`.
4. Dans le champ « Fait référence à : », effacez ce qu’il y a et tapez la formule magique.

Appliquer le nom à la validation de données

La partie difficile est faite. Maintenant, c’est comme pour une liste classique.

1. Sélectionnez la cellule où vous voulez la liste déroulante.
2. Allez dans Données > Validation des données.
3. Choisissez « Liste ».
4. Dans le champ « Source », tapez simplement =MaListeDynamique (ou le nom que vous avez choisi).

Et voilà. Quand vous ajoutez un élément dans la colonne A, NBVAL va compter un élément de plus, et DECALER va automatiquement agrandir la plage. Votre menu déroulant sera à jour.

Méthode 3 : La Plus Moderne avec Microsoft 365 (UNIQUE et FILTRE)

Si vous avez un abonnement Microsoft 365 (anciennement Office 365), vous avez accès à de nouvelles fonctions qui rendent ce processus encore plus simple. Ces « fonctions de tableaux dynamiques » changent la manière de travailler sur Excel.

Les deux fonctions qui nous intéressent sont UNIQUE et FILTRE. Elles permettent de créer une liste source qui se met à jour et se propage automatiquement dans les cellules en dessous. C’est ce qu’on appelle le « déversement ».

Cas 1 : Créer une liste dynamique et sans doublons avec UNIQUE

C’est un besoin très courant : avoir une liste déroulante qui non seulement se met à jour, mais qui supprime aussi automatiquement les doublons de la source. Avec la fonction UNIQUE, c’est un jeu d’enfant.

1. Placez-vous dans une cellule vide, à côté de votre colonne de données.
2. Tapez la formule =UNIQUE(A2:A100), en adaptant A2:A100 à votre plage de données. Si vous avez mis vos données sous forme de Tableau, la formule est encore mieux : =UNIQUE(Tableau1[NomDeColonne]).
3. Appuyez sur Entrée. Excel va automatiquement « déverser » la liste de valeurs uniques dans les cellules en dessous. Vous verrez un cadre bleu autour de la plage de résultats.
4. Maintenant, créez votre liste déroulante (Données > Validation des données > Liste).
5. Dans le champ « Source », sélectionnez la première cellule où vous avez mis votre formule UNIQUE (celle qui contient la formule), et ajoutez un dièse (#) à la fin. Par exemple : =C2#.

L’opérateur de déversement (#) dit à Excel de prendre comme source toute la plage de résultats de la formule, quelle que soit sa taille. Si vous ajoutez une nouvelle valeur (ou un doublon) à votre source, la formule UNIQUE se met à jour, et la liste déroulante aussi.

Cas 2 : Créer une liste dépendante avec FILTRE

La fonction FILTRE permet de créer des listes déroulantes dépendantes de manière très simple. Imaginez que vous ayez une liste de pays, et vous voulez que la seconde liste déroulante n’affiche que les villes du pays sélectionné.

La logique est la même qu’avec UNIQUE. Vous utilisez la fonction FILTRE dans une cellule pour générer la liste des villes, et vous référencez cette cellule avec l’opérateur # dans la source de votre deuxième liste déroulante.

Tableau Comparatif : Quelle Méthode Choisir ?

Vous hésitez entre les différentes options ? Voici un tableau simple pour vous aider à choisir la bonne méthode en fonction de votre situation.

Cas Avancé : Créer des Listes Déroulantes en Cascade (Dépendantes)

On arrive au niveau expert. Une liste déroulante en cascade (ou dépendante) signifie que le choix que vous faites dans une première liste influence les options disponibles dans une seconde liste. Par exemple, vous choisissez une catégorie (« Fruits »), et la deuxième liste n’affiche que les éléments de cette catégorie (« Pomme », « Banane », « Orange »).

Avec la méthode DECALER, c’est possible, mais la formule est plus complexe. Elle fait appel à deux autres fonctions : EQUIV (pour trouver la position d’un élément) et NB.SI (pour compter le nombre d’éléments correspondant à un critère).

Préparer les données sources

La clé du succès pour cette méthode est l’organisation de vos données. Vous devez avoir une colonne avec vos catégories et une colonne avec vos sous-catégories. Le point le plus important est de trier votre table de données par la colonne de catégorie principale. C’est obligatoire pour que la formule fonctionne.

La formule complète expliquée

L’idée est de créer une plage nommée pour la deuxième liste déroulante. Cette plage va utiliser DECALER, mais sa position de départ et sa hauteur seront calculées dynamiquement.

• La fonction EQUIV va trouver la première ligne qui correspond à la catégorie choisie dans la première liste déroulante. Ce sera le point de départ de notre plage.
• La fonction NB.SI va compter combien de fois cette catégorie apparaît dans la colonne. Ce sera la hauteur de notre plage.

FAQ – Problèmes Courants et Solutions

Même avec le meilleur tutoriel, on peut rencontrer des blocages. Voici les questions les plus fréquentes et leurs solutions.

Ma liste ne se met pas à jour, pourquoi ?

C’est souvent l’un de ces problèmes :

• Méthode Tableau : Avez-vous bien ajouté la nouvelle donnée DANS le tableau (juste en dessous) ? Si vous laissez une ligne vide, le tableau ne s’étendra pas.
• Méthode DECALER : Vérifiez la formule dans le Gestionnaire de noms. Une erreur fréquente est d’oublier le « -1 » après NBVAL si vous avez un titre. Vérifiez aussi que la plage dans NBVAL couvre bien toute la colonne (ex: `$A:$A`).
• Source TCD : Si votre source est un Tableau Croisé Dynamique (TCD), n’oubliez pas que le TCD ne s’actualise pas tout seul. Vous devez faire un clic droit > « Actualiser » sur le TCD pour que les nouvelles données apparaissent.

Comment supprimer les doublons de ma liste ?

La solution la plus simple et moderne est d’utiliser la méthode Microsoft 365 avec la fonction UNIQUE. C’est fait pour ça et c’est très efficace. Si vous n’avez pas M365, vous devrez passer par une étape intermédiaire : créer une colonne qui extrait les valeurs uniques avec une formule plus complexe (combinant INDEX, EQUIV et NB.SI) ou via l’outil « Supprimer les doublons » de l’onglet Données.

Peut-on ajouter un champ de recherche à la liste déroulante ?

Nativement, Excel ne propose pas de barre de recherche dans ses listes de validation de données. Lorsque vous ouvrez la liste, vous pouvez taper la première lettre d’un élément pour sauter directement dessus, mais ce n’est pas une vraie recherche. Pour avoir une recherche prédictive, il faut passer par des solutions plus avancées qui utilisent des macros (VBA).

La formule DECALER est volatile, est-ce un problème ?

Oui, DECALER est une fonction dite « volatile ». Cela signifie qu’elle se recalcule à chaque fois que vous faites une modification n’importe où dans votre classeur, même si ça ne la concerne pas. Sur un petit fichier, vous ne remarquerez aucune différence. Mais si vous utilisez des centaines de listes déroulantes avec DECALER dans un fichier très lourd avec beaucoup de calculs, cela peut ralentir les performances. C’est une autre raison pour laquelle la méthode des Tableaux est souvent préférable quand c’est possible.