Créer des listes déroulantes en cascade dans Excel

Pour faciliter la saisie et la sélection de données dans vos feuilles de calcul Excel, utilisez des listes déroulantes en cascade : le contenu d'un menu changera en fonction du choix fait précédemment. Une fonction puissante et pratique !

Vous voulez sélectionner des données dans une liste déroulante plutôt que d'avoir à taper et à retaper toujours les mêmes infos ? Facile ! Nous avons consacré une fiche pratique pour vous aider à créer une liste déroulante dans Excel. Avec une simple liste déroulante, vous gagnez du temps, vous évitez les fautes de frappe, vous uniformisez la saisie pour réaliser plus facilement des cumuls… Cela pourrait amplement vous suffire.

Mais dans la présente fiche pratique, nous vous proposons d'aller encore plus loin. Vous allez apprendre à créer des listes déroulantes en cascade, qu'on appelle aussi listes déroulantes dépendantes, listes déroulantes liées, listes déroulantes dynamiques ou encore listes intelligentes. Le principe : le choix effectué par l'utilisateur dans une première liste conditionne le contenu proposé par la deuxième liste, et ainsi de suite. On pourrait, par exemple, choisir un pays dans une première liste ; la deuxième liste s'adapte alors automatiquement pour ne présenter que des villes du pays sélectionné par l'utilisateur dans la première liste ; et la troisième liste s'adapte au choix de la ville sélectionnée pour ne présenter que les sites touristiques de cette ville.

Inutile de posséder une version récente d'Excel pour placer ces listes déroulantes en cascade dans vos feuilles de calcul, nous vous proposons différentes méthodes selon le tableur installé sur votre ordinateur. Si vous utilisez le tout dernier Excel pour Microsoft 365 sur Windows/Mac (sur abonnement) ou Excel pour le Web (gratuit ou payant), les fonctions spécifiques que nous vous présentons vont un peu vous faciliter la vie, mais au détriment de la compatibilité.

Nos autres méthodes ont le mérite de fonctionner avec toutes les versions (ou presque) d'Excel pour Windows et pour Mac, c'est-à-dire les plus récentes comme celles datant de plus de 15 ans. De plus, les tableurs compatibles Excel tels que LibreOffice Calc sauront reprendre ces feuilles de calcul avec leurs listes déroulantes en cascade. Ces tableurs compatibles Excel sont également en mesure de créer leurs propres listes déroulantes, en cascade ou non, avec quelques différences de paramétrage. La présente fiche pratique se cantonne à Excel.

Comment créer des listes déroulantes en cascade dans toutes les versions d'Excel ?

Nous n'utilisons pas ici les dernières fonctions d'Excel 365 pour rester compatible avec toutes les versions du tableur, et avec beaucoup de ses concurrents. Mais les deux exemples qui suivent vous démontrent que, même avec une version d'Excel qui date un peu, créer des listes en cascade réclame très peu d'effort !

Trois listes déroulantes en cascade dans Excel

Pour ce premier exemple, nous allons créer trois listes en cascade… mais vous pouvez évidemment vous en tenir à deux ! Ou, au contraire, poursuivre la démarche pour en ajouter d'autres. La première liste affichera quelques pays, la deuxième liste n'affichera que des villes du pays sélectionné dans la première liste, et la troisième liste n'affichera que des sites touristiques de la ville sélectionnée dans la deuxième liste.

Comme souvent dans Excel, il existe plusieurs méthodes pour parvenir au même résultat. Cette première méthode n'impose pas de nommer les plages de cellules où figurent les éléments de vos listes, ni de redéfinir le nom si vous y ajoutez après coup des éléments. Par ailleurs, les noms de champs d'Excel imposent des contraintes (le nom ne doit pas contenir d'espace, de trait d'union, d'apostrophe…) dont on s'affranchit ici, ce qui permet de dérouler des listes affichant des libellés avec des espaces et d'autres caractères spéciaux.

Nos longues formules pourraient vous intimider, mais rassurez-vous, elles sont facilement adaptables à vos données, même si vous débutez avec Excel ! Mieux, votre feuille de calcul affichera une alerte – par exemple, le fond de la cellule devient rouge – si un nouveau choix dans une liste de niveau supérieur rend caduques les choix déjà faits dans les sous-listes. En revanche, on va le voir, vos listes en cascade (hormis la dernière, donc sauf la troisième dans notre exemple) ne doivent pas comporter de doublons.

Pour commencer, d'un double clic sur l'onglet Feuil1, nommez par exemple la première feuille de calcul BDLieux. Sur la première ligne, à partir de A1, inscrivez les libellés qui constitueront votre première liste déroulante, ici les pays. Ces éléments peuvent contenir des espaces, des traits d'union, des apostrophes… Dans la liste déroulante, ils apparaîtront dans l'ordre que vous choisissez ici (les Trier est possible via l'onglet Accueil ou l'onglet Données). Ne laissez pas de cellule vide. Si vous souhaitez plus tard étoffer la liste, il suffira d'ajouter des pays dans les colonnes juste à droite (F, G, etc.), sans en laisser de vides.
La colonne en dessous d'un pays alimentera la deuxième liste déroulante, elle comporte ici quelques villes de ce pays. Là encore, espaces et traits d'union sont acceptés, et l'ordre d'apparition que vous indiquez sera respecté. En revanche, cette méthode n'autorise pas les doublons, nous avons dû préciser Valence (Fr) et Valence (Esp) pour bien différencier les villes homonymes de Valence en France et en Espagne (l'autre méthode présentée plus loin autorise les doublons). Pour ajouter des villes à tel ou tel pays, tapez tout simplement les communes en fin de colonne.
Dans une autre feuille de calcul (mais ça marche aussi dans la même), nommée pour l'exemple BDSites, reportez en en-tête de colonne le nom de chaque ville, en respectant scrupuleusement la même orthographe que précédemment. Ici, on a choisi de commencer en cellule A1, il faudra adapter les formules ci-dessous si vous partez d'une autre cellule. Les villes n'ont pas besoin d'être classées par ordre alphabétique ni même d'être regroupées par pays. En dessous d'une ville, listez par exemple ses sites touristiques. Les couleurs n'ont aucune utilité.
Vous allez maintenant définir deux noms qui correspondent à deux plages de cellules. Sous l'onglet Formules, cliquez sur Gestionnaire de noms.
Dans cette boîte de dialogue Gestionnaire de noms, qui peut aussi s'afficher en tapant la combinaison de touches Ctrl+F3 (ou Fn+Ctrl+F3) sur PC, pressez le bouton Nouveau.
Dans le champ Nom, tapez Choix1.
Notez que, sur PC, vous pouvez attraper le coin inférieur droit de la fenêtre pour l'agrandir.
Dans le champ Fait référence à, copiez-collez cette formule :
=DECALER(BDLieux!$A$1;;;;NBVAL(BDLieux!$A$1:$IV$1))
NBVAL compte le nombre de cellules non vides de la sélection. Sur notre exemple, le nom Choix1 correspond donc à une plage qui s'étend de la cellule A1 à la cellule E1.
Si vous voulez adapter cette formule à vos propres données :
► BDLieux est le nom de la feuille de calcul où se trouvent les données des deux premières listes.
► $A$1 est la référence absolue de la cellule (A1) où est inscrit le nom du premier pays.
► $IV$1 est une référence arbitraire qui vous laisse de la marge : elle vous permet d'inscrire jusqu'à 256 pays (colonne IV).
► Mais votre première liste pourrait comporter plus de 256 éléments, par exemple 500 si vous indiquez : BDLieux!$A$1:$SF$1 (puisque SF est la 500^e colonne d'une feuille Excel).
Via le bouton Nouveau de la boîte de dialogue Gestionnaire de noms, créez un deuxième nom, Choix2, qui Fait référence à cette formule, que vous copiez-collez :
=DECALER(BDSites!$A$1;;;;NBVAL(BDSites!$A$1:$IV$1))
Là encore, il est facile d'adapter cette formule à vos propres données. Toutes les données de vos trois listes pourraient, bien entendu, être stockées dans une seule feuille de calcul, au lieu des deux feuilles (BDLieux et BDSites) de notre exemple.
Enfin, toujours via le bouton Nouveau de la boîte de dialogue Gestionnaire de noms, créez un troisième nom. Nous l'avons appelé ici MaxListe, il comporte une valeur arbitraire de 100 en tapant dans le champ Fait référence à :
100 ou =100 (si vous tapez juste 100, Excel remplacera de toute façon cette valeur par la formule =100).
On précise ainsi que les sous-listes peuvent au maximum contenir un maximum de 100 éléments. Adaptez cette valeur au contenu de vos listes en gardant un peu de marge.
Votre boîte de dialogue Gestionnaire de noms doit comporter maintenant trois noms.
Nous avons appelé Saisie la feuille de calcul où doit s'effectuer la sélection dans les listes déroulantes en cascade. Créez, par exemple, la première liste déroulante en A2. Sélectionnez cette cellule A2 puis cliquez sur l'onglet Données.
Sous l'onglet Données, cliquez sur Validation des données.
Dans la boîte de dialogue Validation des données, sélectionnez Autoriser > Liste, et dans la zone Source, tapez la formule :
=Choix1
Validez en pressant OK.
Testez votre première liste déroulante. Dans notre exemple elle présente les pays.
Sélectionnez une valeur (un pays) dans cette liste, évitez de laisser cette cellule A2 vide pour l'instant, sinon Excel affichera une alerte (non bloquante) quand vous allez créer les listes déroulantes suivantes.
Placez-vous sur la cellule où devra apparaître la deuxième liste déroulante (ici en B2). Sous l'onglet Données, cliquez sur Validation des données. Sélectionnez Autoriser > Liste, et, dans la zone Source, copiez-collez la formule suivante :
=DECALER(Choix1;1;EQUIV(A2;Choix1;0)-1;NBVAL(DECALER(INDEX(Choix1;1);1;EQUIV(A2;Choix1;0)-1;MaxListe;));1)

Si vous souhaitez adapter cette formule à vos propres données, changez éventuellement la référence à la cellule A2 si votre première liste déroulante ne se situe pas en cellule A2.
Testez cette deuxième liste déroulante : elle doit vous présenter les sous-éléments correspondant au choix fait en cellule A2, donc dans notre exemple les villes du pays sélectionné en A2. Évitez de laisser la cellule B2 vide pour l'instant : sélectionnez une ville.
Sur notre exemple, la troisième liste déroulante doit être créée en sélectionnant au préalable la cellule C2. Sous l'onglet Données, cliquez sur Validation des données. Sélectionnez Autoriser > Liste, et dans la zone Source, copiez-collez la formule suivante :
=DECALER(Choix2;1;EQUIV(B2;Choix2;0)-1;NBVAL(DECALER(INDEX(Choix2;1);1;EQUIV(B2;Choix2;0)-1;MaxListe;));1)
Là encore, si vous souhaitez adapter cette formule à votre fichier Excel, une référence importante à vérifier est B2, il s'agit de la cellule où s'est effectué le choix dans la deuxième liste déroulante (choix d'une ville).
Testez cette troisième liste, qui s'adapte au choix fait dans la deuxième liste déroulante.
Si vous choisissez par la suite un autre pays, les informations déjà choisies dans les sous-listes (Ville et Musée) ne sont plus pertinentes. Il n'est pas possible de les réinitialiser sans programmer, mais…
… vous pouvez alerter l'utilisateur par un message visuel, par exemple en passant la valeur erronée sur fond rouge grâce à la Mise en forme conditionnelle d'Excel. Voici comment…
Sélectionnez la cellule B2 (ou, chez vous, la cellule de la deuxième liste déroulante) puis cliquez sur l'onglet Accueil > Mise en forme conditionnelle > Nouvelle règle.
Dans la liste Type de règle, sélectionnez Utiliser une formule…
Dans la zone en dessous, copiez-collez cette formule :
=ESTERREUR(EQUIV(B2;DECALER(Choix1;1;EQUIV(A2;Choix1;0)-1;NBVAL(DECALER(INDEX(Choix1;1);1;EQUIV(A2;Choix1;0)-1;MaxListe;));1);0))
Cette formule renvoie la valeur VRAI si la ville ne correspond pas au pays sélectionné : Excel appliquera dans ce cas le format de cellule que vous allez préciser. Pour adapter cette formule à vos propres données, notez qu'elle fait notamment référence aux cellules A2 (qui contient la valeur de la première liste déroulante) et B2 (valeur de la deuxième liste déroulante).
Avant de refermer la fenêtre Nouvelle règle de mise en forme, cliquez sur le bouton Format et, dans la boîte de dialogue qui s'affiche, cliquez par exemple sur l'onglet Remplissage puis sur la couleur Rouge afin que le fond de la cellule devienne rouge en cas d'erreur.
Afin que la troisième liste (sur notre exemple celle des musées) passe également en rouge si sa valeur n'est pas pertinente par rapport au choix fait dans la deuxième liste, sélectionnez la cellule de la troisième liste (ici C2) et créez de même une règle de mise en forme via l'onglet Accueil > Mise en forme conditionnelle > Nouvelle règle.
La formule analyse cette fois le contenu des cellules B2 et C2. Dans la boîte de dialogue, sélectionnez Utiliser une formule, copiez-collez la formule ci-dessous puis pressez le bouton Format pour choisir, par exemple, un Remplissage de couleur Rouge.
=ESTERREUR(EQUIV(C2;DECALER(Choix2;1;EQUIV(B2;Choix2;0)-1;NBVAL(DECALER(INDEX(Choix2;1);1;EQUIV(B2;Choix2;0)-1;MaxListe;));1);0))
Tout choix antérieur ne correspondant plus au choix de la liste de niveau supérieur sera ainsi marqué en rouge. La liste déroulante vous permet, bien sûr, de corriger ce choix.
Pour modifier, supprimer ou dupliquer les règles, passez par l'onglet Accueil > Mise en forme conditionnelle > Gérer les règles.
Vos trois listes déroulantes fonctionnent, vous avez terminé !
Vous aimeriez pouvoir sélectionner à la suite plusieurs triades Pays/Ville/Musée ? Convertissez dans ce cas la plage de saisie (cliquez sur n'importe quelle cellule, par exemple C2) en un tableau via l'onglet Accueil > Mettre sous forme de tableau…
Lorsque la cellule sélectionnée est la dernière du tableau (en bas à droite du tableau), pressez la touche Tab pour ajouter une ligne à ce tableau. Ici, en cellule C2, vous pressez Tab… Excel ajoute une ligne vide à votre tableau.
Les listes déroulantes sont à votre disposition pour remplir chaque nouvelle ligne, ou pour modifier une ligne déjà remplie.
Cette méthode s'inspire de l'un des nombreux exemples du site de Jacques Boisgontier, qui fourmille d'astuces pour les utilisateurs avancés d'Excel souhaitant notamment se familiariser avec le langage VBA.

Trois listes en cascade avec doublons possibles, et récupération du prix du produit sélectionné

Dans ce deuxième exemple compatible avec toutes les versions d'Excel, on accepte les doublons, par exemple, pour le bleu, qu'une robe soit bleue et qu'une veste soit bleue également. Mais il va falloir nommer chaque liste, ce qui implique que les libellés de vos listes déroulantes ne comportent ni espaces, ni apostrophes, ni traits d'union : vous devrez le cas échéant remplacer ces caractères par un trait de soulignement _ comme dans New_York ou Total_HT, car c'est ainsi que les nommera Excel. Nous vous donnerons quand même une petite astuce pour pouvoir taper des espaces…

Vous allez voir comment afficher trois listes déroulantes liées (Genre > Produit > Couleur), et en fonction du dernier choix, récupérer le prix du produit concerné.

Dans une première feuille de calcul que vous nommez par exemple Param, notez les éléments de votre première liste déroulante, ici Homme et Femme. Ces libellés ne doivent pas contenir d'espaces et apostrophes ou traits d'union, ils serviront à nommer des plages de cellules. Si vous tenez quand même à afficher au moins des libellés avec espaces ("Enfant et bébé", par exemple, nous vous donnons la formule adaptée).
Sélectionnez cette plage de cellules et nommez-la Genre en tapant ce mot dans la Zone Nom.
En dessous de chaque élément, tapez les éléments qui alimenteront la deuxième liste, ici des types de vêtements. Là encore, n'utilisez pas de caractère interdit pour le nommage (espace, trait d'union, apostrophe, etc.), mais la méthode permet une tolérance pour les espaces. Remarquez que Veste et Pantalon peuvent très bien apparaître dans chaque sous-liste.
Quand tous les éléments sont inscrits, sélectionnez les données de la première colonne, y compris l'en-tête (ici Homme). Cliquez sur l'onglet Formules.
Sous l'onglet Formules, cliquez sur Depuis sélection.
Le libellé inscrit en première ligne va servir à nommer la plage de cellules en dessous. Vérifiez que la case Ligne du haut (et elle seule) est cochée et pressez OK. La plage A2:A3 s'appelle maintenant Homme. Notez que la première cellule, en A1, qui a servi à nommer la plage, ne fait pas partie de cette plage nommée.
Faites de même pour les autres colonnes. Sur notre exemple, sélectionnez les cellules de la colonne Femme (y compris ce libellé) et, sous l'onglet Formules, cliquez sur Depuis sélection, cochez uniquement Ligne du haut et pressez OK.
Votre feuille de calcul comporte déjà trois noms, visibles dans la Zone Nom. Si vous cliquez sur l'un d'eux, Excel sélectionne la plage concernée. La zone nommée Femme englobe par exemple les cellules B2, B3 et B4.
Un peu plus loin dans la feuille, tapez les données qui s'afficheront dans votre troisième liste. Le libellé inscrit en première ligne est important : il doit reprendre exactement le nom affiché dans la première liste (ici Homme ou Femme) suivi du nom exact d'un élément affiché dans la deuxième liste (Veste, Pantalon…), toujours sans espace. À chaque fois, dans la colonne juste à côté, nous avons précisé le prix du produit (mais rien ne vous y oblige) : la veste noire pour homme coûte ainsi 150 €, le pantalon blanc pour femme coûte 65 €, etc. Ces colonnes de prix n'ont pas besoin d'être nommées.
Comme vous l'avez fait précédemment, nommez chaque plage (sans la colonne de prix) en sélectionnant l'en-tête et ses éléments (ci-dessous D1:D3). Cliquez sur l'onglet Formules > Depuis sélection > Ligne du haut > OK. La plage D2:D3 s'appelle maintenant HommeVeste.
Faites de même pour chaque colonne…
Si vous avez créé une catégorie de premier niveau "Enfant et bébé" et une sous-catégorie "Chaussures et chaussons" (donc avec des espaces), la plage nommée doit s'appeler Enfant_et_bébéChaussures_et_chaussons.
Votre feuille de calcul comporte à présent plusieurs noms.
Dans une autre feuille de calcul, créez la première liste déroulante, par exemple en cellule B2, cliquez sur l'onglet Données > Validation des données > Autoriser > Liste et copiez-collez ou tapez la formule ci-dessous dans la zone Source :
=Genre
Testez votre liste déroulante et sélectionnez une valeur…

… si vous ne sélectionnez aucune valeur dans la liste, un message de mise en garde (sans conséquences) s'affichera quand vous créerez les listes liées suivantes. Dans ce cas, cliquez sur Oui.
Sélectionnez la cellule où doit apparaître la deuxième liste déroulante, ici en C2, cliquez sur l'onglet Données > Validation des données > Autoriser > Liste et copiez-collez ou tapez la formule ci-dessous dans la zone Source :
=INDIRECT(B2)
ou, si vous avez laissé des espaces dans les éléments listés par la deuxième liste :
=INDIRECT(SUBSTITUE(B2;" ";"_"))
La fonction SUBSTITUE remplace les éventuels espaces par des traits de soulignement _.
La fonction INDIRECT renvoie une plage de cellules nommée, dont le nom est inscrit en B2.
Pour adapter cette formule, remplacez B2 par la référence de la cellule où se trouve la première liste déroulante.
Testez la liste et sélectionnez un élément.
Pour la troisième liste déroulante, créée par exemple en D2, cliquez sur l'onglet Données > Validation des données > Autoriser > Liste et copiez-collez ou tapez la formule ci-dessous dans la zone Source :
=INDIRECT(B2&C2)
ou, si vous avez laissé des espaces dans les éléments de vos listes :
=INDIRECT(SUBSTITUE(B2&C2;" ";"_"))
Testez, là encore, la liste.
Pour récupérer le prix de l'article sélectionné, copiez-collez (et adaptez éventuellement) la formule suivante :
=SIERREUR(INDEX(DECALER(INDIRECT(B2&C2);;1);EQUIV(D2;INDIRECT(B2&C2);0));"")
ou, si vous avez laissé des espaces dans les éléments de vos listes :
=SIERREUR(INDEX(DECALER(INDIRECT(SUBSTITUE(B2&C2;" ";"_"));;1);EQUIV(D2;INDIRECT(SUBSTITUE(B2&C2;" ";"_"));0));"")
Cette formule n'affiche rien (un texte vide "") si la récupération du prix renvoie une erreur.
Appliquez au besoin un format comptable ou autre à cette cellule affichant le prix.
Si vous souhaitez saisir plusieurs produits à la suite, transformez cette plage de cellules en tableau. Sélectionnez n'importe quelle cellule et cliquez sur l'onglet Accueil > Mettre sous forme de tableau. Confirmez que le tableau comporte des en-têtes (il s'agit des libellés Genre, Produit, Couleur, Prix sur la première ligne).
Facultatif : pour ne pas que des listes déroulantes de filtrage s'affichent à droite de chaque en-tête du tableau, sélectionnez une cellule du tableau et, sous l'onglet vert Création de tableau qui s'affiche, décochez Bouton de filtre.
Testez vos listes déroulantes et différents articles pour vérifier que tout fonctionne.
Pour ajouter une ligne, placez-vous sur la dernière cellule du tableau (celle en bas à droite, ici E2) et pressez la touche Tab.
Excel ajoute une ligne au tableau et met à votre disposition les listes déroulantes pour y saisir les informations.
Vous pouvez ainsi saisir une liste de produits. Si vous modifiez après coup des valeurs déjà saisies et que le cas n'est pas prévu (ci-dessous en ligne 4, on a changé Femme en Homme, et le cas d'une robe bleue pour homme n'est pas prévu), aucun prix ne s'affiche.
Pour modifier le contenu de vos listes déroulantes, reportez-vous à nos explications pour ajouter un élément à une liste avec une plage nommée.
Nous vous avons donné la méthode pour utiliser des libellés avec espaces dans vos listes déroulantes, mais vous pourriez autoriser en plus les apostrophes et les traits d'union en compliquant les formules :
=INDIRECT(SUBSTITUE(SUBSTITUE(SUBSTITUE(B2&C2;" ";"_");"'";"_");"-";"_"))

Comment créer des listes déroulantes en cascade avec Excel 365 ?

Combinées aux tableaux, qui simplifient déjà grandement la manipulation de données en colonnes, les nouvelles fonctions d'Excel pour Microsoft 365 rendent la création de listes en cascade encore plus facile. Si vous disposez de cette version de la suite bureautique, qui ne s'appelle plus Office mais Microsoft 365 (pour Windows, Mac et le Web, sur abonnement mensuel ou annuel uniquement), et/ou si vous utilisez Excel pour le Web, essayez l'une de ces deux méthodes. Les fichiers seront également utilisables dans les applications pour iPhone et pour mobiles Android.
Ci-dessous, nous vous présentons deux exemples, le premier où les données des listes déroulantes en cascade sont toutes contenues dans un seul tableau (sous la forme d'une base de données), la seconde méthode où les données des listes déroulantes sont stockées dans des tableaux séparés.

Si votre fichier Excel risque d'être utilisé ou modifié dans des versions plus anciennes d'Excel ou avec des tableurs compatibles, exploitez plutôt les autres méthodes présentées dans cette fiche pratique.

Excel 365 : toutes les données des listes déroulantes sont dans un seul tableau

Stocker les données de vos listes dans un seul tableau – on parle aussi de stockage sous forme de base de données – peut s'avérer pratique. Pour cet exemple, nous avons choisi quelques villes situées dans 9 pays de 4 continents. Les trois listes liées entre elles permettront donc de sélectionner un continent, puis un pays de ce continent, puis une ville de ce pays. Les continents et pays apparaissent à plusieurs reprises dans les lignes du tableau, mais ils ne figureront évidemment qu'une seule fois dans les listes déroulantes en cascade. Les villes non plus n'ont pas besoin d'être uniques : votre tableau peut très bien contenir les villes homonymes de Valence en France et en Espagne, donc Europe>France>Valence et Europe>Espagne>Valence, nous verrons comment régler le problème en fin d'exemple.

La première ligne de vos données doit contenir les en-têtes de colonnes, ce qui rendra vos formules plus compréhensibles. Gros avantages des tableaux Excel par rapport aux plages de cellules que vous nommez vous-même : les en-têtes contenant des espaces sont parfaitement gérés, par exemple l'en-tête Chiffre d'affaires HT, auquel vous pourrez faire référence dans une formule du type :
=TRIER(MonTableau [Chiffre d'affaires HT])
donc : trier le tableau appelé MonTableau sur la colonne appelée Chiffre d'affaires HT.
Pour convertir une plage de cellules en tableau, cliquez sur n'importe quelle cellule puis sur l'onglet Accueil > Mettre sous forme de tableau. Sélectionnez un style qui vous convient et laissez cochée la case Mon tableau comporte des en-têtes.
Quand une cellule de ce tableau Excel est sélectionnée, un onglet Création de tableau (ou équivalent) s'affiche en vert, à droite du ruban.
Cet onglet vous permettra d'accéder aux options du tableau (style, etc.) et de le renommer : nous l'appelons ici TableauLieux.
Dans une cellule à côté, ici dans la cellule E2 de cette feuille de calcul que nous avons appelée Données, on inscrit une formule qui exploite deux nouvelles fonctions d'Excel pour Microsoft 365 (TRIER et UNIQUE). Cette formule récupère les continents, sans doublons et triés par ordre alphabétique :
=TRIER(UNIQUE(TableauLieux[Continent]))

Mais vous pourriez préférer une liste non triée :
=UNIQUE(TableauLieux[Continent])
Ou, pour conserver toutes les valeurs, y compris les doublons :
=TableauLieux[Continent]
Afin que vos formules soient bien lisibles, restez sur cette cellule contenant la formule (ici E2), cliquez dans la Zone Nom, en haut à gauche de l'écran, et tapez ListeContinents pour nommer cette cellule.
Tout est prêt pour créer la première liste déroulante. Dans une autre feuille de calcul (nous l'avons appelée Formulaire), cliquez sur l'onglet Données puis sur Validation des données.
Dans Autoriser, choisissez Liste.
Dans la zone Source, inscrivez :
=ListeContinents#
ListeContinents est la cellule que vous avez nommée plus haut, et le signe # est extrêmement important : il indique à Excel 365 qu'il doit prendre en compte toutes les valeurs renvoyées par la formule (donc toute la liste des continents), et pas seulement le résultat de la première cellule. La nouvelle version d'Excel appelle ce signe # l'opérateur de plage renversée.
Testez votre liste : elle déroule tous les continents inscrits dans le tableau Excel. Remarquez aussi que nous avons nommé cette cellule C3 en tapant ChoixContinent dans la Zone Nom.
De retour dans la feuille Données contenant le tableau, inscrivez la deuxième formule, par exemple en cellule F2 (cellule à nommer, au passage, en tapant ListePays dans la Zone Nom). Cette formule filtre les pays en ne conservant que ceux dont le continent est égal à celui sélectionné dans la première liste déroulante (cellule ChoixContinent), puis n'en conserve que les valeurs uniques, qu'elle trie enfin :
=TRIER(UNIQUE(FILTRE(TableauLieux[Pays];TableauLieux[Continent]=ChoixContinent)))
Dans la feuille de calcul Formulaire, ajoutez une deuxième liste déroulante : onglet Données > Validation des Données > Autoriser : Liste.
Dans la zone Source, la formule est (n'oubliez pas d'ajouter le signe #) :
=ListePays#
Nommez ChoixPays cette cellule C4.
De retour dans la feuille Données, inscrivez, par exemple en cellule G2, la dernière formule, pour récupérer la liste des villes correspondant au pays sélectionné dans la liste déroulante, triée et sans doublon :
=TRIER(UNIQUE(FILTRE(TableauLieux[Ville];TableauLieux[Pays]=ChoixPays)))
Nommez ListeVilles cette cellule G2, toujours pour rendre vos formules plus compréhensibles.
Dans la feuille Formulaire, ajoutez une troisième et dernière liste déroulante (onglet Données > Validation des données) dont la formule de la zone Source est :
=ListeVilles#
Cette cellule C5 de la feuille de calcul Formulaire est nommée ChoixVille.
Vos trois listes déroulantes en cascade sont créées !
Excel ne réinitialise pas, hélas, le formulaire quand on change la sélection d'une liste. Ci-dessous, nous avons remplacé France par Italie : la ville de Nantes précédemment choisie reste affichée. Peut-être dans une prochaine version d'Excel… (Notez que la réinitialisation est possible, mais par programmation.)
En revanche, si vous ajoutez une ligne de données en fin de tableau…
… Excel en tient compte automatiquement et l'ajoute au tableau, les colonnes Continent, Pays et Ville se mettent à jour. Idem si vous insérez ou supprimez des lignes dans le tableau.
Et automatiquement aussi, les formules tiennent compte des nouvelles valeurs. L'Afrique s'ajoute ici à la liste des continents.
Notez que si aucune valeur n'est sélectionnée dans les listes déroulantes en cascade, certaines formules renvoient une erreur, ici l'erreur #CALC!
Ce qui se règle facilement en ajoutant à la formule un test avec la fonction SIERREUR : elle renvoie soit la valeur testée (donc ici la liste des pays correspondant aux continents), soit la valeur que vous indiquez en cas d'erreur (ici un libellé vide ""). Dans la cellule ListePays, la formule devient donc :
=SIERREUR(TRIER(UNIQUE(FILTRE(TableauLieux[Pays];TableauLieux[Continent]=ChoixContinent)));"")
Et selon le même principe, en cellule ListeVilles, la formule devient :
=SIERREUR(TRIER(UNIQUE(FILTRE(TableauLieux[Ville];TableauLieux[Pays]=ChoixPays)));"")
Pour qu'Excel détecte les doublons, il faut bien sûr que les libellés soient toujours inscrits de la même manière. Aucun problème si un libellé est parfois inscrit en capitales et parfois non (FRANCE, France) : les doublons sont supprimés et seule la première graphie est retenue. En revanche, si vous écrivez tantôt Royaume-Uni et tantôt Royaume Uni ou Royaume_Uni, Excel y verra trois libellés distincts.
Si vous filtrez certaines données du tableau d'origine, vos listes déroulantes en cascade présentent quand même toutes les données de ce tableau.
Si vous masquez les lignes et colonnes du tableau ou des formules matricielles, les listes déroulantes affichent quand même toutes les données.
Mais attention : si, pour masquer le contenu d'une cellule, vous lui avez appliqué un format personnalisé ;;; (trois points-virgules d'affilée) (par exemple en cellule Données!E5 qui contient le libellé Europe renvoyé par la formule matricielle), alors la liste déroulante affichera un élément vide.
Pour l'exemple, ajoutons deux colonnes au tableau. Pour chaque ville, on précise le gentilé et le nombre d'habitants, et l'on veut que ces informations s'affichent quand on choisira une ville dans la troisième liste déroulante.
Voici les formules qui permettent de récupérer ces infos correspondant à la ville sélectionnée dans la troisième liste en cascade, par exemple avec la fonction FILTRE d'Excel pour Microsoft 365 :
=FILTRE(TableauLieux[Gentilé];TableauLieux[Ville]=ChoixVille)
Attention ! Cette fois, si vos sous-listes comportent un même libellé – la ville de Valence existe en France et en Espagne –, vous allez avoir un problème car le filtrage renverra deux villes…
Vous réglerez le problème en faisant porter le filtrage à la fois sur le pays et sur la ville, et non plus seulement sur la ville. Chaque condition à tester est inscrite entre parenthèses, et le signe multiplié * correspond à un ET logique, car on veut que la première condition ET la deuxième condition soient impérativement réunies. Ce qui donne, pour obtenir le gentilé :
=FILTRE(TableauLieux[Gentilé];(TableauLieux[Pays]=ChoixPays)*(TableauLieux[Ville]=ChoixVille))
De même, pour obtenir la population :
=FILTRE(TableauLieux[Population];(TableauLieux[Pays]=ChoixPays)*(TableauLieux[Ville]=ChoixVille))
On peut maintenant choisir l'une ou l'autre ville de Valence et obtenir le bon gentilé et la population correspondante :
À l'intérieur de la formule, vous pourriez bien sûr ajouter un test sur un troisième critère, par exemple :
(TableauLieux[Continent]=ChoixContinent)*(TableauLieux[Pays]=ChoixPays)*(TableauLieux[Ville]=ChoixVille)

Excel 365 : les données des listes déroulantes sont réparties dans plusieurs tableaux

Exemple 2 : dans ce deuxième exemple, voyons le cas de listes stockées dans des tableaux séparés, pour le cas où cette situation vous correspondrait mieux. Nous allons créer 4 listes en cascade pour sélectionner d'abord une région parmi les 18 régions de France, puis un département de cette région, puis un arrondissement de ce département (subdivision administrative comportant plusieurs communes), puis une commune de cet arrondissement.

L'exemple est, là encore, facilement adaptable à vos propres données, mais si vous voulez nous suivre, les données 2022 sont récupérées au format CSV sur cette page du site de l'Insee. Nous les avons réorganisées et débarrassées des colonnes inutiles. Chaque liste est stockée dans une feuille de calcul de notre classeur Excel.

La liste des régions est dans un tableau que nous avons appelé TabRégions : clic sur une cellule quelconque de cette liste, onglet Accueil > Mettre sous forme de tableau. La colonne REG indique le code Insee de la région.

Ce tableau est renommé TabRégions via l'onglet Création de tableau > Nom du tableau. Dans nos formules, on pourra alors faire référence à la colonne TabRégions[NomRégion] et à la colonne TabRégions[REG].
Dans une autre feuille, la liste des départements est conservée dans un tableau qu'on nomme TabDépartments.
La liste des arrondissements est dans un tableau nommé TabArrondissements.
Et la liste des communes est dans un tableau nommé TabCommunes.
Dans une feuille que nous avons appelée Utilisateur, nous allons créer nos quatre listes déroulantes en cascade pour sélectionner : Région (en C3, cellule nommée SelREG) ; Département (en C4, nommée SelDEP) ; Arrondissement (en C5, nommée SelARR) ; Commune (en C6, nommée SelCOM). Voici comment créer et alimenter ces listes déroulantes…
Dans une dernière feuille que nous avons appelée Listes, en cellules B2, on récupère la liste des régions, en supprimant les éventuels doublons, avec la formule :
=UNIQUE(TabRégions[NomRégion])

Ou, si on veut en plus que cette liste soit triée par ordre alphabétique croissant :
=TRIER(UNIQUE(TabRégions[NomRégion]))
Ou, si on veut en plus que cette liste soit triée et débarrassée des cellules vides :
=TRIER(UNIQUE(FILTRE(TabRégions[NomRégion];NON(ESTVIDE(TabRégions[NomRégion])))))
En feuille Utilisateur, on crée maintenant la liste déroulante en cellule SelREG : onglet Données > Validation des données > Autoriser = Liste.
Dans la zone Source, on récupère le contenu de la cellule B2 (où nous avions inscrit la formule), mais aussi le contenu des autres cellules renvoyées par cette formule, et ce grâce au signe #. Soit :
=Listes!$B$2#
Si nous avions par exemple nommé LstREG cette cellule B2, nous aurions pu écrire :
=LstREG#
On teste notre feuille Utilisateur : la première liste déroulante présente bien les régions de France.
Pour récupérer le code Insee de la région sélectionnée dans la liste déroulante (ici le code 27 de la Bourgogne-Franche-Comté), on peut par exemple inscrire cette formule dans la feuille Listes, en nous aidant de la nouvelle fonction FILTRE apparue dans Excel pour Microsoft 365 :
=FILTRE(TabRégions[REG];TabRégions[NomRégion]=SelREG)
Pour info, comme il existe parfois plusieurs méthodes pour obtenir le même résultat dans Excel, nous aurions pu par exemple utiliser la nouvelle fonction RECHERCHEX d'Excel 365 :
=RECHERCHEX(SelREG;TabRégions[NomRégion];TabRégions[REG])

Ou encore avec la fonction plus classique RECHERCHEV (le chiffre 2 correspond à la deuxième colonne de notre tableau TabRégions) :
=RECHERCHEV(SelREG;TabRégions;2;FAUX)
Dans la cellule à côté, on récupère les départements de ce Code région :
=FILTRE(TabDépartements[NomDépartement];TabDépartements[REG]=C2)
Ce qui nous permet de créer (onglet Données > Validation des données) la liste déroulante des Départements grâce à la formule dans la zone Source :
=Listes!$D$2#
En E2, on récupère le code du département sélectionné dans la liste déroulante (ici 71 pour la Saône-et-Loire) grâce à la formule :
=FILTRE(TabDépartements[DEP];TabDépartements[NomDépartement]=SelDEP)
En F2, on récupère la liste des arrondissements correspondant à ce code du département :
=FILTRE(TabArrondissements[NomArrondissement];TabArrondissements[DEP]=E2)
Ce qui nous permet de créer la liste déroulante des arrondissements de ce département avec cette formule dans la zone Source :
=Listes!$F$2#
En G2, on récupère le code de l'arrondissement sélectionné dans la liste déroulante (ici 713 pour Charolles) grâce à la formule :
=FILTRE(TabArrondissements[ARR];TabArrondissements[NomArrondissement]=SelARR)
Enfin, en H2, on récupère la liste des plus de cent communes correspondant à cet arrondissement :
=FILTRE(TabCommunes[NomCommune];TabCommunes[ARR]=G2)
Ce qui, via l'onglet Données > Validation des données, permet de créer la quatrième et dernière liste déroulante en cascade, celle des communes de cet arrondissement :
=Listes!$H$2#
Vos listes déroulantes peuvent ainsi présenter plusieurs dizaines, voire centaines, d'éléments.