Excel : liste déroulante dépendante avec RECHERCHEX
Apprenez à créer une liste déroulante dépendante dans Excel avec validation des données, RECHERCHEX et INDIRECT pas à pas.
Créer une liste déroulante dépendante dans Excel est une technique très utile pour fiabiliser la saisie, accélérer le remplissage d’un formulaire et éviter les erreurs. Le principe est simple : une première liste permet de choisir une catégorie, puis une seconde liste n’affiche que les éléments associés à ce choix.
Dans ce tutoriel, vous allez apprendre une méthode complète pour construire ce type de mécanisme avec la validation des données et les fonctions RECHERCHEX et INDIRECT. L’objectif est d’obtenir une solution claire, maintenable et réutilisable dans un classeur professionnel.
Le tutoriel présente d’abord la logique de préparation des données, puis la création de la première liste déroulante, ensuite la mise en place de la seconde liste dépendante. Une variante avec tableau de correspondance et RECHERCHEX est également détaillée afin de rendre la structure plus robuste lorsque les sources évoluent.
Les exemples restent volontairement génériques pour que vous puissiez les adapter à des cas concrets comme Pays > Ville, Service > Employé, Produit > Référence ou Famille > Sous-catégorie.
Prérequis
- Connaître les bases d’Excel : saisie, sélection de cellules, formules simples.
- Savoir ouvrir le menu Données > Validation des données.
- Disposer d’une version d’Excel incluant RECHERCHEX. Si cette fonction n’est pas disponible, la logique de structure reste exploitable, mais les formules devront être adaptées.
- Comprendre qu’une liste déroulante dépendante repose avant tout sur une source de données propre et structurée.
Matériel nécessaire
- Microsoft Excel avec la validation des données
- Fonction RECHERCHEX disponible dans le classeur
- Jeu de données structuré avec catégories et sous-listes
- Éventuellement une feuille dédiée aux sources
Étapes
Comprendre le fonctionnement d’une liste déroulante dépendante
Avant de manipuler les outils, il est important de comprendre la logique générale. Une liste déroulante classique affiche toujours la même série de valeurs. Une liste déroulante dépendante, elle, change de contenu selon le choix effectué dans une autre cellule.
Prenons un exemple simple :
- en A2, l’utilisateur choisit une catégorie : Fruits, Légumes, Boissons ;
- en B2, la liste doit afficher uniquement les éléments correspondant à la catégorie choisie.
Pour obtenir ce résultat, Excel a besoin de deux choses :
- une première source contenant la liste des catégories ;
- une deuxième logique de renvoi capable de pointer vers la bonne sous-liste selon la valeur choisie.
La fonction INDIRECT est souvent utilisée pour transformer un texte en référence exploitable par Excel. De son côté, RECHERCHEX peut servir à retrouver le nom de la plage ou de la colonne à utiliser à partir d’un tableau de correspondance. L’association des deux permet d’éviter certaines limites d’une structure trop rigide.
En pratique, vous allez donc construire :
- des listes sources bien organisées ;
- des plages ou tableaux facilement appelables ;
- une validation des données pour la cellule parent ;
- une validation des données pour la cellule enfant, fondée sur le choix parent.
Cette approche est particulièrement pertinente dans les classeurs utilisés par plusieurs personnes, car elle réduit les variations de saisie et améliore la cohérence des données exportées ou analysées ensuite.
Préparer proprement les données sources
La réussite du montage dépend d’abord de la qualité de la structure source. Le plus simple consiste à créer une feuille dédiée, par exemple nommée Sources. Sur cette feuille, placez vos informations de manière lisible et stable.
Vous pouvez par exemple organiser la feuille ainsi :
- une zone pour la liste des catégories ;
- une zone pour chaque sous-liste ;
- un tableau de correspondance entre la catégorie visible et la source réelle à utiliser.
Exemple de structure conceptuelle :
- catégories visibles : Fruits, Légumes, Boissons ;
- liste Fruits : Pomme, Banane, Poire ;
- liste Légumes : Carotte, Tomate, Courgette ;
- liste Boissons : Eau, Jus, Thé.
Si vous utilisez INDIRECT directement, une méthode classique consiste à créer des plages nommées dont le nom correspond à la catégorie. Ainsi, si l’utilisateur sélectionne Fruits, Excel pourra appeler la plage nommée Fruits.
Cependant, cette méthode fonctionne mieux lorsque les intitulés respectent les contraintes de nommage des plages. Or, dans un contexte réel, les libellés visibles peuvent contenir des espaces, des accents, des caractères spéciaux ou des variantes d’écriture. C’est justement là qu’une table de correspondance combinée à RECHERCHEX devient intéressante.
Jeu de préparation recommandé :
- Créez une colonne Libellé affiché avec les valeurs destinées à l’utilisateur.
- Créez une colonne NomSource avec un identifiant technique propre, sans espace inutile et cohérent avec vos plages nommées.
- Créez les sous-listes elles-mêmes dans des colonnes ou zones séparées.
- Attribuez un nom à chaque sous-liste à partir de l’identifiant technique.
Exemple logique :
- Libellé affiché : Fruits frais
- NomSource : Fruits
Dans ce cas, la cellule de sélection pourra afficher Fruits frais, tandis que la seconde liste utilisera Fruits comme clé technique pour retrouver la plage nommée.
Cette séparation entre l’affichage et la technique rend votre fichier plus robuste. Vous pouvez modifier le libellé visible sans devoir reconstruire toutes les validations dépendantes, tant que l’identifiant technique reste inchangé.
Créer les sous-listes et leurs plages nommées
Pour qu’INDIRECT fonctionne correctement dans une validation de données, il est souvent pratique de s’appuyer sur des plages nommées. Chaque sous-liste doit donc recevoir un nom clair et stable.
Supposons que vos valeurs soient placées sur la feuille Sources dans plusieurs colonnes distinctes. Sélectionnez la plage contenant les éléments d’une sous-catégorie, puis attribuez-lui un nom dans la zone de nom ou via le gestionnaire de noms.
Exemple de noms possibles :
- Fruits
- Legumes
- Boissons
Quelques bonnes pratiques sont essentielles :
- évitez les espaces dans les noms techniques ;
- préférez des noms courts, explicites et cohérents ;
- conservez une logique uniforme, par exemple sans accent si vous voulez limiter les ambiguïtés ;
- n’utilisez pas des noms trop proches de références de cellules.
Si vos libellés métiers contiennent des espaces ou des caractères spéciaux, ne forcez pas l’utilisateur à choisir ces versions techniques. Utilisez plutôt un tableau de correspondance. C’est précisément ce qui permettra à RECHERCHEX de faire le lien entre la valeur affichée et la plage nommée réelle.
À ce stade, l’objectif est simplement de vérifier que chaque catégorie possède bien sa source. Une erreur fréquente consiste à nommer une plage avec un mot différent de la clé recherchée plus tard. Par exemple, si la clé technique attendue est Legumes mais que la plage a été nommée ListeLegumes, la formule avec INDIRECT ne renverra pas la bonne référence, sauf si la table de correspondance prévoit exactement ce nom.
Prenez donc le temps d’aligner la structure :
- une valeur de catégorie affichée ;
- une clé technique ;
- une plage nommée portant cette clé ou le nom renvoyé par RECHERCHEX.
Cette rigueur vous évitera beaucoup de difficultés au moment de configurer les validations.
Créer la première liste déroulante avec la validation des données
Une fois les sources prêtes, commencez par la liste parent, c’est-à-dire la cellule qui contrôle la seconde. Dans notre exemple, cette cellule peut être A2.
La procédure générale est la suivante :
- Sélectionnez la cellule destinée au premier choix.
- Ouvrez le menu Données > Validation des données.
- Choisissez un type de validation Liste.
- Indiquez comme source la plage contenant les catégories visibles.
Si votre liste des catégories se trouve sur une feuille de sources et qu’elle a été nommée, vous pouvez utiliser ce nom comme source. L’important est que l’utilisateur voie uniquement les valeurs métier souhaitées.
Exemples d’usages concrets :
- A2 : choix du pays
- A2 : choix du service
- A2 : choix de la gamme de produits
Après validation, testez immédiatement la cellule. Cliquez dessus et assurez-vous que la flèche de liste déroulante apparaît bien et que les options proposées correspondent exactement à vos catégories.
Profitez-en pour améliorer l’expérience utilisateur :
- ajoutez si nécessaire un message de saisie expliquant quoi choisir ;
- activez une alerte d’erreur pour empêcher l’entrée de valeurs manuelles non prévues ;
- évitez les doublons dans la source des catégories.
Il est également recommandé de réfléchir au comportement attendu si la cellule parent est vide. En effet, la seconde liste dépendante devra gérer ce cas. Dans une logique de formulaire, vous pouvez par exemple demander explicitement de choisir d’abord la catégorie avant de passer au détail.
À ce stade, vous disposez d’une liste simple, mais elle joue déjà un rôle de pilote pour tout le reste du mécanisme.
Mettre en place la liste dépendante avec INDIRECT
La méthode la plus connue pour une liste dépendante dans Excel consiste à faire pointer la validation de la seconde cellule vers une plage nommée dont le nom correspond à la valeur choisie dans la première cellule.
Supposons que :
- A2 contienne la catégorie choisie ;
- B2 doive afficher la sous-liste correspondante ;
- les plages nommées portent exactement les noms des catégories.
Dans la cellule B2, ouvrez Données > Validation des données, choisissez Liste, puis indiquez comme source une formule de type :
=INDIRECT(A2)Le principe est le suivant : si A2 contient Fruits, INDIRECT interprète ce texte comme une référence nommée et renvoie la plage Fruits. La liste déroulante de B2 affiche alors les éléments de cette plage.
Cette approche est élégante, rapide et très répandue. Elle présente toutefois une contrainte majeure : la valeur sélectionnée dans la liste parent doit correspondre exactement au nom technique exploitable dans Excel.
Voici les limites les plus fréquentes :
- les espaces dans les libellés visibles ;
- les accents ou caractères spéciaux ;
- les changements de dénomination métier ;
- la nécessité de présenter un texte plus convivial que le nom technique.
Si vos catégories affichées sont déjà propres et compatibles avec les noms de plages, cette solution peut suffire. Par exemple, avec des catégories comme Nord, Sud, Est, Ouest, vous pouvez aller très vite.
En revanche, si vous devez afficher des valeurs comme Boissons chaudes ou Produits d’entretien, il est préférable d’utiliser une couche de correspondance. C’est précisément le rôle de la méthode de l’étape suivante, qui combine RECHERCHEX et INDIRECT.
Utiliser RECHERCHEX pour relier le libellé affiché à la bonne source
La combinaison RECHERCHEX + INDIRECT permet de dissocier ce que l’utilisateur voit de ce qu’Excel exploite techniquement. C’est souvent la meilleure approche dans un classeur professionnel.
Le principe est le suivant :
- la cellule parent affiche un libellé métier ;
- un tableau de correspondance associe ce libellé à un nom de source technique ;
- RECHERCHEX retrouve ce nom technique ;
- INDIRECT transforme ce nom en référence de plage pour la seconde liste.
Supposons un tableau de correspondance sur la feuille Sources :
- colonne A : Libellé affiché
- colonne B : NomSource
Exemple conceptuel :
- Fruits frais → Fruits
- Légumes de saison → Legumes
- Boissons froides → Boissons
Dans ce cas, la validation de la cellule B2 peut s’appuyer sur une logique de type :
=INDIRECT(RECHERCHEX(A2;Sources!A:A;Sources!B:B))La lecture est simple :
- RECHERCHEX cherche la valeur de A2 dans la colonne des libellés affichés ;
- il renvoie le nom technique correspondant ;
- INDIRECT transforme ce texte en plage nommée ;
- la validation de données utilise cette plage comme source de la liste.
Cette méthode apporte plusieurs avantages :
- vous pouvez modifier le texte visible sans casser la structure technique ;
- vous pouvez utiliser des noms de plages propres, courts et sans ambiguïté ;
- vous centralisez la logique dans un tableau facile à relire ;
- vous rendez la maintenance beaucoup plus simple quand le nombre de catégories augmente.
Il faut toutefois veiller à la cohérence complète de la chaîne :
- le libellé affiché doit exister dans la liste parent ;
- ce même libellé doit être présent dans le tableau de correspondance ;
- le nom renvoyé par RECHERCHEX doit correspondre à une plage nommée existante.
Si l’un de ces maillons manque, la liste dépendante ne fonctionnera pas correctement. Dans un environnement de production, il est donc judicieux de tester systématiquement chaque catégorie après configuration.
Rendre la structure plus fiable avec des cellules intermédiaires
Selon les versions d’Excel et selon la complexité du classeur, il peut être plus confortable de passer par une cellule intermédiaire qui calcule d’abord le nom technique, plutôt que d’insérer toute la logique directement dans la validation des données.
Exemple de stratégie :
- A2 : catégorie choisie
- C2 : nom technique calculé via RECHERCHEX
- B2 : liste dépendante basée sur INDIRECT(C2)
Dans C2, vous pouvez utiliser une formule du type :
=RECHERCHEX(A2;Sources!A:A;Sources!B:B)Puis, dans la validation des données de B2, indiquer comme source :
=INDIRECT(C2)Cette méthode présente plusieurs avantages pratiques :
- elle facilite le débogage : vous voyez immédiatement quelle clé technique est renvoyée ;
- elle simplifie la validation des données ;
- elle permet de vérifier plus facilement les erreurs de correspondance ;
- elle améliore la lisibilité du fichier pour une autre personne.
Si vous ne souhaitez pas afficher cette colonne d’assistance, vous pouvez la masquer. L’important est qu’elle reste documentée dans la logique du classeur.
Dans de nombreux contextes professionnels, cette approche intermédiaire est préférable à une formule longue directement dans la validation, surtout lorsque le fichier doit être repris, audité ou transmis.
Vous pouvez même aller plus loin en ajoutant d’autres contrôles autour de cette cellule, par exemple un message si la catégorie choisie n’a pas de correspondance. Cela permet d’identifier rapidement une rupture entre la liste parent, la table de correspondance et les plages nommées.
Gérer les cas particuliers : espaces, cellules vides et changements de choix
Une liste déroulante dépendante fonctionne bien dans un exemple simple, mais les cas réels imposent souvent quelques ajustements. Voici les principaux points à anticiper.
1. La cellule parent est vide
Si l’utilisateur n’a encore rien choisi dans la première liste, la seconde ne devrait idéalement rien proposer. Selon la structure retenue, une cellule intermédiaire peut rester vide tant que le choix parent n’est pas fait. Cela évite d’afficher une erreur ou une liste inadaptée.
2. Le choix parent change après sélection du détail
Si l’utilisateur choisit d’abord une catégorie puis un élément associé, et qu’il modifie ensuite la catégorie, la valeur déjà présente dans la seconde cellule peut devenir incohérente. Excel ne réinitialise pas toujours automatiquement cette cellule. Il faut donc prévoir une règle d’usage : soit l’utilisateur efface manuellement la seconde cellule, soit vous mettez en place un contrôle supplémentaire dans votre processus.
3. Les libellés contiennent des espaces
Les espaces sont souvent une source de difficultés avec INDIRECT lorsqu’on veut faire correspondre directement la valeur visible à un nom de plage. C’est justement une raison forte d’utiliser RECHERCHEX pour renvoyer un identifiant technique propre.
4. Les catégories sont renommées
Si vous avez séparé libellé affiché et nom technique, vous pouvez faire évoluer l’affichage avec un impact limité. Sans cette séparation, renommer une catégorie peut casser la validation dépendante.
5. Des valeurs sont ajoutées aux sous-listes
Quand une sous-liste évolue, assurez-vous que la plage nommée couvre bien les nouvelles cellules. Si la plage est figée et trop courte, les nouveaux éléments n’apparaîtront pas dans la liste déroulante. Une organisation rigoureuse des sources est donc essentielle.
Ces points ne sont pas secondaires : ce sont eux qui distinguent un exemple théorique d’un fichier réellement fiable au quotidien.
Tester, sécuriser et documenter le classeur
Une fois la mécanique mise en place, prenez le temps de la tester méthodiquement. Cette phase est importante, car une liste dépendante peut sembler correcte alors qu’une ou deux catégories restent mal reliées.
Voici une méthode de vérification simple :
- Testez chaque valeur de la liste parent.
- Vérifiez que la liste enfant affiche bien uniquement les éléments attendus.
- Contrôlez le comportement quand la cellule parent est vide.
- Essayez de modifier une catégorie visible dans la table de correspondance pour vérifier l’effet sur la structure.
- Ajoutez un élément dans une sous-liste et confirmez son apparition dans la validation.
Pensez également à documenter votre fichier, surtout s’il est destiné à être partagé. Une simple feuille d’explication peut suffire, avec :
- le rôle de la feuille Sources ;
- la signification des colonnes Libellé affiché et NomSource ;
- la liste des plages nommées utilisées ;
- les cellules de formulaire concernées par la dépendance.
Cette documentation interne est précieuse pour la maintenance. Sans elle, une autre personne risque de modifier un libellé, déplacer une plage ou supprimer une colonne de correspondance sans comprendre les conséquences.
Enfin, si votre classeur sert à collecter des données, vérifiez aussi l’impact en aval : filtres, tableaux croisés, formules, importations ou exports. Une liste dépendante bien conçue améliore fortement la qualité des données, mais seulement si toute la chaîne reste cohérente.
En résumé, la création technique n’est qu’une partie du travail. La fiabilité sur la durée dépend surtout de la qualité des tests et de la clarté de la structure.
Erreurs fréquentes & dépannage
- Confondre libellé affiché et nom de plage : si la valeur visible ne correspond pas exactement à une plage nommée, INDIRECT seul ne suffit pas.
- Oublier le tableau de correspondance : dès que les catégories contiennent des espaces, accents ou intitulés métiers complexes, l’absence de correspondance rend la solution fragile.
- Nommer les plages de façon incohérente : une simple différence d’orthographe entre la clé technique et le nom réel de la plage casse la dépendance.
- Utiliser des sources mal structurées : doublons, cellules vides au mauvais endroit, ou plages incomplètes produisent des listes incohérentes.
- Ne pas retester après modification : renommer une catégorie ou ajouter une sous-liste sans vérification peut introduire des erreurs silencieuses.
- Laisser une ancienne valeur dans la cellule dépendante après changement de la catégorie parent : cela peut créer une incohérence de saisie.
Astuces & pour aller plus loin
- Créez une feuille Sources dédiée pour centraliser catégories, correspondances et sous-listes.
- Séparez toujours l’affichage de la technique dans les fichiers professionnels : un libellé pour l’utilisateur, une clé propre pour Excel.
- Utilisez une cellule intermédiaire avec RECHERCHEX pour simplifier le débogage et comprendre immédiatement ce que renvoie la recherche.
- Adoptez une convention de nommage stable pour les plages : courte, explicite et homogène.
- Testez chaque catégorie une par une après toute modification de la structure source.
- Ajoutez des commentaires ou une feuille d’aide si le fichier doit être maintenu par d’autres personnes.
- Réfléchissez à l’expérience utilisateur : message de saisie, alerte d’erreur et ordre logique des choix améliorent l’usage du formulaire.
Questions fréquentes
À quoi sert une liste déroulante dépendante dans Excel ?
Elle permet d’afficher dans une seconde liste uniquement les choix liés à une première sélection. Cela réduit les erreurs de saisie et rend les formulaires plus rapides à remplir.
Pourquoi utiliser RECHERCHEX avec INDIRECT ?
RECHERCHEX permet de convertir un libellé visible en nom technique de source, puis INDIRECT transforme ce nom en référence exploitable par la validation des données. Cette combinaison rend la structure plus souple.
Peut-on créer une liste dépendante avec INDIRECT seul ?
Oui, si la valeur choisie dans la première liste correspond exactement à une plage nommée valide. Dès que les libellés visibles deviennent plus complexes, une table de correspondance est souvent préférable.
Que faire si les catégories contiennent des espaces ou des accents ?
Il est conseillé de dissocier le libellé affiché et le nom technique. Le libellé peut rester lisible pour l’utilisateur, tandis que la clé technique, retrouvée avec RECHERCHEX, pointe vers une plage nommée propre.
Pourquoi utiliser une cellule intermédiaire au lieu d’une formule complète dans la validation ?
Parce qu’elle facilite le contrôle et le débogage. Vous voyez immédiatement le nom technique renvoyé par RECHERCHEX, ce qui aide à détecter une correspondance manquante ou incorrecte.
La seconde liste se vide-t-elle automatiquement quand la première change ?
Pas nécessairement. Selon la structure du fichier, une ancienne valeur peut rester affichée dans la cellule dépendante même si elle n’est plus cohérente avec le nouveau choix parent. Il faut donc le vérifier dans votre processus.
Conclusion
Créer une liste déroulante dépendante dans Excel avec la validation des données est une excellente manière de rendre un classeur plus fiable et plus professionnel. La méthode la plus simple repose sur INDIRECT, mais la combinaison avec RECHERCHEX apporte une vraie valeur dès que les libellés affichés diffèrent des noms techniques.
La clé du succès ne réside pas seulement dans la formule, mais surtout dans la préparation rigoureuse des données sources, la cohérence des plages nommées et la mise en place d’un tableau de correspondance clair. En adoptant cette approche, vous obtenez une solution maintenable, lisible et adaptée aux besoins réels d’un fichier partagé.
Une fois la logique maîtrisée, vous pourrez la réutiliser dans de nombreux scénarios : formulaires internes, catalogues produits, référentiels géographiques, listes de services ou encore outils de suivi. C’est un excellent levier pour améliorer la qualité des données dans Excel.