Liste déroulante dépendante dans Excel : Configuration et dépannage

Dans ce guide, je vais vous expliquer comment créer des listes déroulantes dépendantes dans Excel, en commençant par une configuration simple et largement prise en charge, puis en passant à des approches plus flexibles et modernes, plus faciles à gérer à mesure que vos données augmentent.

Si vous débutez avec Excel, notre cours d'introduction à Excel couvre des compétences telles que la navigation dans l'interface, la compréhension des formats de données et l'utilisation des fonctions de base. De plus, je trouve que l'fiche de référence des formules Excel, que vous pouvez télécharger, est une référence utile car elle contient toutes les fonctions Excel les plus courantes.

Qu'est-ce qu'une liste déroulante dépendante dans Excel ?

Dans Excel, une liste déroulante dépendante est simplement une deuxième liste déroulante dont les options sont déterminées par la valeur sélectionnée dans une première cellule.

Un exemple courant est la hiérarchie Pays → Ville. Si vous sélectionnez l'Allemagne, le menu déroulant suivant se limitera à des villes telles que Berlin et Munich. En sélectionnant le Canada comme pays, la liste est automatiquement mise à jour pour afficher Toronto et Vancouver à la place.

C'est ici que les listes déroulantes standard listes déroulantes présentent des lacunes. À eux seuls, ils ne peuvent afficher qu'une liste fixe de valeurs, ce qui implique souvent l'affichage d'options non pertinentes ou non valides. Les menus déroulants dépendants résolvent ce problème en reliant les sélections entre elles, rendant ainsi la validation des données d'Excel plus précise et beaucoup plus utile dans les feuilles de calcul réelles.

L'exemple ci-dessus nous montre qu'en utilisant une liste déroulante standard n'est pas suffisante en soi, car elle peut afficher des choix non pertinents, comme toutes les villes, quel que soit le pays. Dans ce cas, les menus déroulants dépendants permettent de résoudre le problème en reliant plusieurs sélections entre elles, rendant ainsi la validation des données Excel plus intelligente et plus conviviale.

Comment créer une liste déroulante dépendante dans Excel (méthode classique)

Je vais vous expliquer comment utiliser la méthode classique, qui est l'approche la plus largement prise en charge pour créer une liste déroulante dépendante dans Excel. Il est compatible avec la plupart des versions d'Excel et s'appuie sur des données sources structurées et des plages nommées plutôt que sur des fonctionnalités avancées.

Étape 1 : Veuillez créer la liste déroulante principale.

Veuillez commencer par créer le premier menu déroulant en suivant les étapes ci-dessous :

• Veuillez saisir vos principales catégories dans une seule colonne. Par exemple : États-Unis, Royaume-Uni, Australie.
• Veuillez sélectionner la cellule où vous souhaitez placer le menu déroulant principal.
• Veuillez vous rendre sur Données → Validation des données.

• Dans le boîte Autoriser , veuillez sélectionner Liste.
• Dans la zoneSource , veuillez sélectionner vos catégories principales. Veuillez cliquer sur « » (OK).

Étape 2 : Veuillez préparer les listes des personnes à charge.

Ensuite, veuillez organiser les données qui apparaîtront dans le deuxième menu déroulant. Chaque liste dépendante doit être nommée, et le nom doit correspondre exactement à l'option principale correspondante dans le menu déroulant.

• Veuillez répertorier vos sous-éléments sous des en-têtes qui correspondent exactement à vos catégories principales.
• Veuillez mettre en évidence les éléments d'un groupe.
• Veuillez vous rendre dans la zone de nom (en haut à gauche, à côté de la barre de formule) et saisir le nom de la catégorie. Veuillez appuyer sur , puis sur Entrée.

• Veuillez répéter cette procédure pour chaque catégorie.

Étape 3 : Veuillez créer le menu déroulant dépendant.

Veuillez maintenant relier le deuxième menu déroulant au premier en suivant les étapes ci-dessous :

• Veuillez sélectionner la cellule où le menu déroulant dépendant doit apparaître.
• Ouvrir Validation des données et sélectionnez Liste.
• Veuillez définir la source de manière à ce qu'elle pointe dynamiquement vers la plage nommée qui correspond à la première sélection.
• Veuillez utiliser la fonction INDIRECT() pour pointer vers la cellule contenant la première liste déroulante.

En utilisant cette logique, si D2 indique « Australie », la fonction INDIRECT() indique à la deuxième liste : Veuillez rechercher la plage nommée Australia et afficher ces éléments.

Création d'une liste déroulante dépendante à l'aide de tableaux dynamiques (Excel moderne)

Si vous utilisez Excel 365 ou Excel 2021+, les tableaux dynamiques simplifient les listes déroulantes dépendantes en supprimant le besoin de plusieurs plages nommées. Au lieu de préparer des listes distinctes pour chaque option, Excel peut générer automatiquement la liste dépendante en fonction de la sélection actuelle.

Par exemple, supposons que les données se trouvent dans les colonnes A et B de votre feuille de calcul. Veuillez suivre ces étapes pour créer une liste déroulante dépendante dynamique :

• Veuillez créer la liste déroulante principale en suivant les étapes décrites dans la section précédente.

• Pour le deuxième menu déroulant dans la cellule suivante, veuillez saisir =FILTER(B:B, A:A = D2) afin d'afficher les éléments correspondants.

Dans ce cas, la syntaxe de la fonction d'FILTER() est la suivante : 

=FILTER(items, categories=main_cell)

Où : 

• items: La colonne contenant les valeurs de la liste déroulante dépendante.

• categories: La colonne contenant les valeurs de la liste déroulante principale

• main_cell: La cellule contenant l'élément sélectionné dans la liste déroulante principale.

Cette méthode présente les avantages suivants par rapport à l'approche classique des plages nommées :

• Mises à jour automatiques : Lorsque vous ajoutez un nouvel élément à votre tableau source, il apparaît immédiatement dans le menu déroulant, ce qui élimine le besoin de mises à jour manuelles par catégorie.
• Erreur de nom : Vous évitez les erreurs de dénomination et les incohérences orthographiques entre les noms et les sélections.
• Aucune maintenance du « Gestionnaire de noms » : Il n'est pas nécessaire de créer des dizaines de noms individuels pour chaque catégorie. La dépendance est visible dans une formule au lieu d'être masquée dans le Gestionnaire de noms.

Listes déroulantes dépendantes avec tableaux Excel

Utilisation des tableaux Excel rend les listes déroulantes dépendantes plus fiables lorsque vos données sont susceptibles d'augmenter.

Supposons que nous ayons enregistré les données Pays → Ville dans un tableau Excel. Désormais, lorsque vous ajoutez une nouvelle ville dans un pays existant, le tableau s'agrandit automatiquement. Tout menu déroulant dépendant créé à partir de ce tableau continue de fonctionner sans mise à jour des plages sources.

Cependant, il est important de garder à l'esprit les points suivants lorsque vous combinez des tableaux Excel avec la validation des données :

• La validation des données n'accepte pas toujours directement les références aux tableaux. Veuillez toujours diriger la validation des données vers une plage de débordement auxiliaire créée par FILTER().
• Les lignes vides dans les tableaux peuvent entraîner des valeurs vides dans les menus déroulants.
• Les plages de déversement bloquées empêchent la mise à jour de la liste. Si les cellules situées sous votre formule d'aide ne sont pas vides, la liste filtrée ne peut pas s'étendre et le menu déroulant dépendant apparaîtra alors incomplet ou ne fonctionnera pas.

Problèmes courants liés aux listes déroulantes dépendantes

Vous trouverez ci-dessous les problèmes courants que j'ai rencontrés lors de la création de listes déroulantes dépendantes dans Excel, ainsi que les solutions pour les éviter :

• Le menu déroulant affiche des valeurs vides : Ce problème survient généralement lorsque la plage source contient des cellules vides ou lorsqu'un résultat filtré ne renvoie aucune correspondance pour la sélection actuelle. Veuillez toujours vous assurer que vos données ne contiennent pas d'espaces vides inattendus.
• La liste dépendante ne se met pas à jour : Si le deuxième menu déroulant ne change pas après la mise à jour du premier, il est possible qu'Excel ne recalcule pas comme prévu ou que le lien de dépendance soit rompu. Pour résoudre ce problème, veuillez vérifier que la liste dépendante pointe vers la liste déroulante principale.
• Erreurs orthographiques : Étant donné que les menus déroulants dépendants reposent sur des correspondances exactes, une légère différence orthographique entre la sélection principale et les données source empêchera la liste de fonctionner. Pour résoudre ce problème, privilégiez l'utilisation de listes déroulantes dynamiques.
• Espaces supplémentaires dans les données sources : Les espaces avant ou après une valeur peuvent rendre deux valeurs identiques pour les utilisateurs, mais différentes pour Excel, ce qui peut entraîner des problèmes avec les filtres et la correspondance des noms. Veuillez utiliser la fonction TRIM() pour supprimer les espaces cachés dans vos données avant de créer les listes déroulantes dépendantes.
• La validation des données ne s'actualise pas comme prévu : Dans certains cas, Excel conserve en mémoire cache d'anciennes règles de validation. Pour résoudre ce problème, veuillez resélectionner la cellule, réappliquer la validation des données ou recalculer le classeur.

Meilleures pratiques pour les listes déroulantes dépendantes dans Excel

Afin de garantir la fiabilité, l'évolutivité et la facilité de maintenance de vos listes déroulantes dépendantes, je vous recommande de suivre la liste de contrôle ci-dessous :

• Veuillez assurer la cohérence des étiquettes : Les menus déroulants dépendants reposent sur des correspondances exactes. Standardisez la nomenclature, la casse et l'espacement dans vos données sources afin d'éviter les échecs silencieux.

• Veuillez éviter de coder en dur les plages : Veuillez éviter de diriger votre validation de données vers des cellules fixes telles que $A$1:$A$10, car les plages fixes cessent de fonctionner dès que les données augmentent. Dans la mesure du possible, veuillez utiliser des tableaux dynamiques ou des tableaux Excel afin que les nouvelles valeurs soient automatiquement prises en compte.

• Veuillez utiliser des tableaux ou des formules dynamiques lorsque cela est possible : Les tableaux et les tableaux dynamiques (FILTER()) réduisent la maintenance manuelle et facilitent la mise à jour de votre classeur au fil du temps, en particulier pour les fichiers partagés.

• Tester les cas limites dès le début : Veuillez vérifier ce qui se produit lorsque le menu déroulant principal est vide, modifié après la sélection ou pointe vers une catégorie sans valeurs correspondantes. Cette approche garantit que vos données se comportent comme prévu, sans aucune surprise.

Quand ne pas utiliser de listes déroulantes dépendantes

Bien que les listes déroulantes dépendantes soient très efficaces, elles ne constituent pas toujours l'outil approprié. Voici quelques cas où il est préférable de ne pas les utiliser :

• Lorsque les dépendances deviennent trop complexes : Si les sélections dépendent de nombreuses conditions ou de plusieurs niveaux, la logique peut rapidement devenir difficile à gérer et sujette à des erreurs dans Excel.
• Quand les formulaires ou les bases de données constituent des outils plus efficaces : Pour la saisie structurée de données, la saisie multi-utilisateurs ou les flux de travail nécessitant de nombreuses validations, les formulaires dédiés ou les systèmes basés sur des bases de données offrent un meilleur contrôle et une plus grande fiabilité.
• Considérations relatives aux performances pour les listes de très grande taille : Les ensembles de données volumineux peuvent ralentir le recalcul et rendre les menus déroulants peu réactifs, en particulier lorsque des formules dynamiques sont utilisées sur des milliers de lignes.

Conclusion

Les listes déroulantes dépendantes vous aident à garantir l'exactitude et l'utilisabilité des données en imposant des relations logiques entre les sélections plutôt que de s'appuyer sur des vérifications manuelles. Que vous préfériez l'approche classique des plages nommées ou les méthodes modernes de tableaux dynamiques, Excel vous offre plusieurs façons de les créer, vous permettant ainsi de choisir celle qui convient le mieux à votre version et à votre flux de travail. Je vous recommande de commencer par la méthode la plus simple, puis de passer à des solutions dynamiques basées sur des tableaux à mesure que vos données et vos modèles deviennent plus complexes.

Maintenant que vous êtes prêt à approfondir vos connaissances, je vous recommande de suivre notre cours Analyse de données dans Excel pour approfondir vos compétences dans Excel. Ce cours vous permettra de maîtriser les analyses avancées et de faire progresser votre carrière. Je vous recommande également de suivre notre cours Power Query intermédiaire dans Excel pour approfondir vos connaissances en matière de transformation des données et d'utilisation du langage M pour créer des fonctions dynamiques.