Comment créer une liste déroulante en cascade dans Excel

Construire une liste déroulante en cascade à 2 niveaux avec INDIRECT

Une liste en cascade à deux niveaux se monte en quatre temps, les deux premiers pour préparer les sous-listes et les deux derniers pour poser les menus dans le tableau. Tout repose sur INDIRECT, qui transforme le texte d’une cellule en référence, si bien que le mot Transport choisi en B2 désigne alors la plage nommée Transport.

Préparer les sous-listes sur une feuille à part

Les sous-listes se rangent sur une feuille qui ne sert qu’à elles, que le fichier d’exemple appelle Listes, à raison d’une catégorie par colonne. Chaque catégorie s’écrit en ligne 1 et ses détails juste en dessous, si bien que Transport occupe la colonne A avec Train, Taxi, Carburant et Péage, et Repas la colonne B.

L’orthographe de ces en-têtes compte plus que tout le reste, puisque chacun deviendra à la fois un choix du premier menu et le nom d’une plage. Une feuille à part les met aussi à l’abri d’un tri ou d’une suppression de lignes faite sur le tableau des dépenses.

Nommer chaque sous-liste d’après sa catégorie

INDIRECT ne cherche pas une colonne mais une plage qui porte exactement le nom écrit dans la cellule, c’est pourquoi chaque liste de détails doit recevoir le nom de sa catégorie. Excel le fait pour toi à partir de l’en-tête, une colonne après l’autre.

Nommer tout le bloc d’un coup irait plus vite, mais les colonnes les plus courtes emporteraient alors des cellules vides dans leur plage, et leur menu refuserait de s’ouvrir. Le Gestionnaire de noms, dans le même groupe, liste ensuite les quatre noms avec leur plage, et c’est là qu’on repère une faute avant qu’elle ne vide un menu.

Créer la première liste déroulante des catégories

La première liste est une liste déroulante ordinaire, dont les choix sont les en-têtes de la feuille Listes. Elle se pose sur toute la colonne Catégorie du tableau des dépenses, pour que chaque ligne ait son propre menu.

Une ligne sert de source aussi bien qu’une colonne, et elle t’évite de recopier les catégories ailleurs, où une faute de frappe finirait par les séparer de leurs plages. Les choix du menu reprennent ainsi exactement les noms que tu viens de créer.

Écrire la liste déroulante conditionnelle avec INDIRECT

La seconde liste est celle qui dépend de la première, et tout se joue dans son champ Source. Au lieu d’une plage fixe, elle reçoit une formule qui va chercher la plage nommée d’après la catégorie de sa propre ligne.

Laisse B2 sans dollar, car Excel décale alors la référence d’une ligne à l’autre, et la cellule C7 lit la catégorie de B7 comme C2 lit celle de B2. C’est ce qui étend la liste en cascade sur plusieurs lignes d’un seul réglage, alors qu’une référence figée en $B$2 ferait suivre à toute la colonne le choix de la première ligne.

Si B2 est encore vide au moment de valider, Excel signale que la source est reconnue comme erronée et demande s’il faut continuer, parce qu’INDIRECT ne trouve aucune plage au nom d’une cellule vide. Réponds Oui sans t’inquiéter, puisque le menu se remplira dès qu’une catégorie sera choisie.

Choisis maintenant Repas dans une ligne, et le menu de sa colonne Détail ne propose plus que Restaurant et Repas client, comme en ligne 3 du fichier d’exemple. Tout le montage tient pourtant à un fil, l’écriture de chaque catégorie, qui doit rester identique au nom de sa plage.

Comment fonctionne INDIRECT dans une liste déroulante conditionnelle

INDIRECT reçoit un texte et renvoie la plage que ce texte désigne, ce qui transforme le mot Transport écrit en B2 en référence à la plage nommée Transport. Sans elle, une source =B2 donnerait un menu d’un seul choix, le mot Transport lui-même, puisque la cellule serait lue comme une valeur et non comme un nom.

La comparaison entre le texte et le nom ignore les majuscules, si bien que transport écrit en minuscules retrouve sa plage. Elle ne pardonne en revanche ni un accent oublié ni un espace, et Hebergement sans accent ne mène à rien quand la plage s’appelle Hébergement.

Depuis sélection garde d’ailleurs les accents des en-têtes, mais remplace chaque espace par un trait de soulignement, parce qu’un nom de plage ne peut pas en contenir. Une catégorie Petit matériel donnerait ainsi une plage nommée Petit_matériel, qu’INDIRECT ne trouve pas sous son libellé d’origine, et la parade figure plus bas avec les autres causes d’un menu vide.

Faire une liste déroulante en cascade à 3 niveaux

Une liste à trois niveaux applique le même principe une fois de plus, la troisième colonne lisant la deuxième comme la deuxième lit la première. Chaque détail qui se subdivise reçoit sa propre sous-liste, nommée d’après lui, et la colonne Précision du fichier d’exemple s’appuie sur elles.

Un détail sans sous-liste, comme Taxi, laisse vide la case Précision de sa ligne, et Excel y refuse alors toute saisie puisque sa source ne mène nulle part. Les noms doivent aussi rester uniques d’un niveau à l’autre, car un nom n’existe qu’une fois par classeur et un détail ne peut donc pas s’appeler comme une catégorie.

Le même enchaînement mène à quatre ou cinq niveaux, mais chacun ajoute une colonne et une série de noms à tenir à jour. Au-delà de trois, un tableau unique filtré par FILTRE, décrit dans la section suivante, s’entretient plus facilement.

Créer une liste déroulante conditionnelle dynamique

Une plage nommée garde les cellules qu’elle avait à sa création, si bien qu’un Parking ajouté sous Péage n’apparaît pas dans le menu de Transport. Beaucoup de tutoriels proposent pour cela un nom dynamique bâti sur DECALER, mais INDIRECT ne sait pas le lire et renvoie l’erreur #REF!, ce qui vide le menu au lieu de l’allonger.

Deux méthodes font grandir la liste d’elles-mêmes, sans demander la même version d’Excel. Les tableaux structurés n’exigent pas Microsoft 365 et gardent la formule INDIRECT, alors que FILTRE demande Microsoft 365 mais se passe de tout nom.

Ranger chaque sous-liste dans un tableau structuré

Un tableau structuré s’agrandit dès qu’on écrit sous sa dernière ligne, et INDIRECT sait retrouver un tableau par son nom. Il suffit donc de nommer chaque tableau d’après sa catégorie, avec un préfixe qui le distingue de la plage nommée du même mot.

Un détail écrit sous la dernière ligne d’un tableau entre aussitôt dans son menu, sans rien renommer. Les tableaux peuvent d’ailleurs se toucher, colonne contre colonne, puisque chacun s’allonge de son côté sans empiéter sur son voisin.

Filtrer la sous-liste avec FILTRE dans Excel 365

La fonction FILTRE part d’un seul tableau à deux colonnes, une ligne par détail avec sa catégorie, et en extrait les détails de la catégorie choisie. Le champ Source d’une validation refuse pourtant FILTRE, si bien que la formule s’écrit dans une cellule d’aide et que la liste pointe sur son résultat.

TRANSPOSE couche le résultat sur la ligne, pour que la cellule d’aide d’une ligne ne déborde pas sur celle de la ligne suivante. Le dièse de M2# désigne ensuite tout ce que la formule a produit, quelle que soit sa longueur. Une ligne ajoutée au tableau Frais entre aussitôt dans le menu de sa catégorie, et les colonnes d’aide peuvent se masquer une fois la liste en place.

La première liste peut venir du même tableau, avec =UNIQUE(Frais[Catégorie]) dans une autre cellule d’aide, par exemple P2, et la source =$P$2#. Plus aucune catégorie ne s’écrit alors à deux endroits, ce qui supprime à la racine le risque d’un menu vide.

Faire une liste déroulante conditionnelle avec SI

Quand la première cellule n’a que deux valeurs possibles, la fonction SI suffit à choisir entre deux plages, sans nommer quoi que ce soit. La source de la liste Détail teste alors la catégorie de sa ligne et renvoie la plage qui lui correspond.

Le dollar devant B garde la colonne fixe et laisse la ligne suivre, exactement comme avec INDIRECT. La méthode s’essouffle pourtant dès la troisième catégorie, car chaque valeur de plus ajoute un SI imbriqué, et la formule devient vite plus longue que les listes qu’elle départage.

Pourquoi la liste déroulante conditionnelle reste vide

Un menu qui ne s’ouvre pas, ou qui s’ouvre sans rien, veut presque toujours dire qu’INDIRECT n’a trouvé aucune plage au nom de la catégorie. Quatre causes expliquent l’essentiel des cas, et elles se vérifient dans cet ordre.

Une catégorie qui contient un espace

Un nom de plage ne peut pas contenir d’espace, c’est pourquoi Depuis sélection a transformé Petit matériel en Petit_matériel. INDIRECT cherche pourtant Petit matériel tel qu’il est écrit dans la cellule, et la source doit donc remplacer l’espace, avec SUBSTITUE, avant de chercher.

Cette version fonctionne aussi pour les catégories sans espace, si bien qu’on peut l’employer d’emblée sur toute la colonne. Elle évite de reprendre la source le jour où une catégorie en deux mots rejoint la liste.

Une sous-liste nommée avec ses cellules vides

Une plage nommée qui descend plus bas que sa dernière valeur emporte des cellules vides, et le menu de sa catégorie ne s’ouvre alors plus du tout, sans le moindre message. C’est ce qui arrive quand on nomme tout le bloc des sous-listes d’un coup, puisque chaque nom descend alors jusqu’au bas de la plus longue colonne. Ouvre le Gestionnaire de noms et ramène chaque plage à sa dernière valeur, ou passe par les tableaux structurés, qui s’arrêtent d’eux-mêmes à leur dernière ligne.

Un en-tête renommé après la création des noms

Un nom ne suit pas l’en-tête dont il vient, si bien qu’une catégorie renommée en ligne 1 apparaît aussitôt dans le premier menu mais ne retrouve plus sa plage. Le même décalage naît d’une faute de frappe quand les catégories sont tapées ailleurs que dans ces en-têtes. Ouvre alors le Gestionnaire de noms, dans l’onglet Formules, et renomme la plage pour qu’elle reprenne mot pour mot la catégorie.

Un nom dynamique construit avec DECALER

Un nom défini par une formule DECALER s’allonge avec sa colonne, et une liste simple qui l’appelle directement en profite. INDIRECT ne sait pourtant pas le lire et renvoie #REF!, ce qui laisse le menu vide, c’est pourquoi une liste qui dépend d’une autre passe par les tableaux structurés décrits plus haut.

Repérer un détail qui ne correspond plus à sa catégorie

Une liste en cascade contrôle la saisie, mais elle ne revient jamais sur une valeur déjà choisie. Si tu passes la catégorie de Repas à Transport après avoir choisi Restaurant, la cellule Détail garde Restaurant sans le moindre avertissement, et la ligne devient incohérente. Excel ne vide pas cette cellule sans macro, mais deux de ses outils la signalent, l’un pour un contrôle ponctuel et l’autre pour une alerte permanente.

Entourer les données non valides

Excel sait comparer chaque cellule à la règle de validation qui la gouverne, et il entoure celles qui ne la respectent plus. Le contrôle porte sur toute la feuille d’un coup, ce qui en fait le bon réflexe avant d’envoyer un fichier.

Colorer l’erreur avec une mise en forme conditionnelle

Une mise en forme conditionnelle garde l’alerte visible en permanence, puisqu’elle se recalcule à chaque changement de catégorie. Sa formule vérifie, avec NB.SI, que le détail de la ligne figure bien dans la plage de sa catégorie.

La cellule Restaurant passe au rouge dès que sa catégorie devient Transport, et reprend son fond dès que tu choisis un détail du bon menu. La condition sur la cellule vide évite, elle, de colorer les lignes où aucun détail n’a encore été choisi.