Les listes déroulantes en cascade sur Excel

Les listes déroulantes en cascade sur Excel sont des listes de choix dont les choix possibles des listes inférieures sont conditionnés par les choix effectués dans les listes supérieures.

Sur Excel, les listes déroulantes en cascade sont très utiles pour créer des formulaires dans vos feuilles de calculs et orienter la saisie. Doter vos classeurs Excel de listes de choix en cascade permettra de contrôler la saisie des données dans les cellules et vous évitera des heures de correction et retraitement dans vos onglets. Ce sera notamment très utiles pour vos reportings Excel.

Pour créer sur Excel des listes déroulantes en cascade, il faut passer par la validation des données et mettre en place un contrôle des saisies. Dans ce tutoriel Excel, je vais vous expliquer comment mettre en place :

• des listes déroulantes en cascade à 2 niveaux ;
• des listes déroulantes en cascade à 3 niveaux ;
• des listes déroulantes en cascade à 4 niveaux ;
• des listes déroulantes en cascade à x niveaux.

Passer de 1 à plusieurs niveaux n’est pas compliqué puisqu’il s’agira de la même formule et des mêmes fonctions à utiliser.

Cette formation peut être complexe. Les fonctions Excel abordées ne sont pas simples à appréhender et la formule Excel finale est… 🤯

Je vous ai grandement simplifié la tâche. J’ai mis dans les modèles de Cleex, un modèle de listes déroulantes en cascade à télécharger 🤩. Vous pourrez ainsi récupérer l’onglet et le mettre dans votre reporting Excel.

Comment créer des listes déroulantes en cascacade sur Excel ?

Pour créer des listes déroulantes en cascade dans votre classeur Excel, il faut mettre en place un contrôle des données dans la partie validation des données de votre ruban Excel.

Il faudra entrer une formule qui indiquera à Microsoft Excel, de façon dynamique, la liste des choix possibles en fonction des données précédemment saisies.

La synthaxe de la formule Excel est très complexe et sera de la forme suivante :

=INDIRECT(ADRESSE(2;EQUIV(A2;$1:$1;0);1)&":"&ADRESSE(NBVAL(INDIRECT(GAUCHE(ADRESSE(2;EQUIV(A2;$1:$1;0));NBCAR(ADRESSE(2;EQUIV(A2;$1:$1;0)))-2)&":"&GAUCHE(ADRESSE(2;EQUIV(A2;$1:$1;0));NBCAR(ADRESSE(2;EQUIV(A2;$1:$1;0)))-2)));EQUIV(A2;$1:$1;0)))

Heureusement pour vous, un modèle de listes déroulantes en cascade est disponible en téléchargement dans les modèles de Cleex. 😁

La formule est à intégrer dans les options de la liste de choix (validation des données dans le ruban).

Je vais vous expliquer à présent comment fonctionne la formule que vous devez inscrire pour récupérer votre bloc de cellules. Il faudra également bien veiller à comprendre les subtilités liées à l’organisation des données dans votre classeur.

Organiser ses plages de données

Lorsque l’on cherche à faire une liste déroulante en cascade, le 1er point est d’organiser correctement ses données. Il va falloir organiser vos blocs de cellules de façon à ce que la formule puisse choisir la liste des choix de façon dynamique.

Dans la première colonne (ici E), il faut indiquer la liste des choix qui seront dans la première sélection. Dans mon cas, il s’agit des marques de véhicules.

Ensuite, pour ma part, je vais intégrer dans la validation des données, les listes des modèles des véhicules en fonction de la marque du véhicule. Pour cela, dans l’entête de votre colonne, il faut mettre la marque et dans les cellules dessous, les modèles des véhicules.

Pour avoir une liste déroulante en cascade à 3 niveaux, on ajoute une troisième condition en mettant en entête de colonne, les choix que l’on peut faire lors de la seconde sélection. On mettra en dessous les différents choix possibles.

Dans notre exemple, après avoir sélectionné la marque du véhicule, on peut saisir le modèle. Vu que je veux laisser à l’utilisateur la possibilité d’affecter une couleur au véhicule, il faudra mettre en entête de colonne le modèle du véhicule et en dessous, les différentes couleurs disponibles.

On pourrait continuer ainsi pour ajouter des niveaux. On mettrait en entête de nos blocs de choix les couleurs des véhicules et en dessous, la finition intérieure ou la puissance.

Cette solution implique que nous ne devons pas avoir 2 entêtes de colonnes identiques. Si c’est le cas, placez un suffixe pour les différencier. Ex: vert C1 vs vert 108.

La formule qui génère sur Excel des listes déroulantes en cascade

Il existe plusieurs solutions qui permettent d’obtenir des listes déroulantes en cascade. Dans cette formation Excel, je vais me concentrer sur une seule solution. Elle a un avantage majeur par rapport à toutes les autres formules utilisant des fonctions Excel différentes : elle est capable de générer des listes déroulantes en cascade avec des niveaux infinis.

Dans le fichier Excel disponible en téléchargement, j’ai utilisé la formule suivante :

=INDIRECT(ADRESSE(2;EQUIV(B2;$1:$1;0);1)&":"&ADRESSE(NBVAL(INDIRECT(GAUCHE(ADRESSE(2;EQUIV(B2;$1:$1;0));NBCAR(ADRESSE(2;EQUIV(B2;$1:$1;0)))-2)&":"&GAUCHE(ADRESSE(2;EQUIV(B2;$1:$1;0));NBCAR(ADRESSE(2;EQUIV(B2;$1:$1;0)))-2)));EQUIV(B2;$1:$1;0)))

Je vais vous expliquer la signification de cette formule Excel et comment l’adapter à votre classeur de données.

Les fonctions Excel utilisées dans cette synthaxe sont :

• La fonction INDIRECT
• La fonction ADRESSE
• La fonction EQUIV
• La fonction NBVAL
• La fonction GAUCHE
• La fonction NBCAR

L’objectif est pour nous d’indiquer à Microsoft Excel que nous souhaitons des listes de choix différentes en fonction des données saisies. Nous voulons donc lui donner des plages de données spécifiques comme liste de choix en fonction des cellules déjà saisies. L’information qu’Excel doit comprendre sera du type K2:K4 dans le cas de la sélection de la couleur pour la Peugeot 108.

La formule Excel INDIRECT permet à Excel de transcrire du texte en coordonnées de cellules ou en plages de données.

La formule ADRESSE permet de saisir les coordonnées d’une cellule en fonction de deux nombres. Le premier sera le numéro de la ligne. Le second sera le numéro de la colonne. Ex: ADRESSE(1;1) = A1.

La formule EQUIV permet de déterminer la position d’une valeur dans un bloc de cellules. Dans notre cas, la formule EQUIV renvoie le numéro de la colonne dans lequel se trouve l’entête que nous cherchons. Par exemple, dans le cas de la liste déroulante sur les couleurs du modèle Peugeot 108, la formule EQUIV(B2;$1:$1;0) indiquera que la valeur 108 se trouve dans 11e colonne de notre onglet Excel.

La formule GAUCHE permet de récupérer les premiers caractères à gauche de la valeur que nous indiquons. Dans notre cas, elle va nous permettre de récupérer la lettre de la colonne où se situent les données à intégrer.

La fonction Excel NBCAR permet de compter le nombre de caractères dans une colonne. Elle nous permet de savoir où s’arrête la sélection des valeurs dans les listes déroulantes.

Voyons le processus de manière plus visuel :

Intégrer la formule dans la validation des données

Lorsque l’on veut créer une liste déroulante dans Excel, nous avons plusieurs possibilités :

• Créer une liste de valeurs fixes dans la section validation des données ;
• Créer une liste de valeurs à partir d’une plage de données (nommée ou non) ;
• Mettre une formule pour avoir une liste déroulante dynamique ;
• Créer une plage de données nommée à partir d’une formule et définir cette plage comme source de valeurs.

Dans le cas de la création de listes déroulantes en cascade dans Excel, la seule solution viable est de générer un contrôle des données dans votre ruban grâce à la section validation des données. Il faudra mettre en tant que source de votre liste de contrôle la formule précédemment définie.

Pour définir notre formule Excel comme source des listes de choix, il faut se rendre dans Données > Validation des données, puis sélectionner Liste dans Autoriser.

Dans la cellule Source, il faudra y saisir la formule précédemment définie. Une fois fait, vous pourrez copier-coller la cellule afin d’étendre cette condition à tous les niveaux de vos listes déroulantes en cascade.

Comme vous pourrez le remarquer dans le fichier présent en téléchargement, j’ai simplié mon cas en plaçant les listes supérieures et inférieures dans des colonnes Excel adjacentes. C’est la meilleure façon de simplifier la future propagation de la formule.

Astuce de pro : créer un onglet pour vos listes

Lors de mes missions de contrôle de gestion et d’automatisation des reportings, l’une des bonnes pratiques que j’applique à chaque fois est d’isoler les choix de mes listes déroulantes dans un onglet à part.

Sauf pour les listes déroulantes en cascade, je viens nommer toutes mes plages de cellules afin de les appeler plus facilement. C’est très pratique et ça simplifie la lecture des formules et la maintenance du fichier.

Je vous invite à en faire de même.

Conclusion

Si vous vous êtes senti(e) un peu débordé(e) par ce tutoriel, c’est parfaitement logique. La formule pour obtenir une liste déroulante en cascade est complexe et demande beaucoup de connaissances.

Pour pallier à cela, je vous ai mis à disposition un exemple. Libre à vous de récupérer l’onglet Excel intéressant et de le modifier pour qu’il s’adapte à votre reporting Excel.

Bye ! 👋