Liste déroulante Excel : Configuration, conseils et dépannage

Avez-vous déjà cliqué sur une petite flèche dans une cellule Excel et vu s'afficher une liste d'options ? Il peut s'agir d'une liste de services, de régions, de statuts ou de catégories. Si vous répondez oui, vous avez déjà utilisé une liste déroulante. 

Les listes déroulantes Excel guident les utilisateurs vers une saisie cohérente, réduisant ainsi les risques de fautes de frappe ou d'entrées incorrectes. Que ce soit pour remplir un outil de suivi de projet, créer une feuille de budget ou concevoir un formulaire destiné à d'autres utilisateurs, les menus déroulants permettent de garder une organisation claire et prévisible.

Dans ce guide, je vais vous montrer comment créer ces listes à partir de zéro, les personnaliser en fonction de vos besoins, les corriger en cas de problème et même créer des versions plus dynamiques et interactives pour des flux de travail avancés. Il n'est pas nécessaire d'être un expert en Excel pour commencer ; un tableur fonctionnel et quelques points de données suffisent. 

Si vous débutez avec Excel, notre cours 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'aide-mémoire des formules Excel, que vous pouvez télécharger, est une référence utile car il contient toutes les fonctions Excel les plus courantes.

Création de listes déroulantes de base dans Excel

Maintenant, voyons comment créer une liste déroulante dans Excel.

Construction étape par étape

Pour créer une liste déroulante dans Excel, procédez comme suit :

Étape 1 : Préparez les données sources.

Avant de créer une liste déroulante, déterminez les éléments à inclure dans la liste. Vous pouvez saisir ces choix directement lors de la configuration du menu déroulant ou les répertorier dans les cellules de votre feuille de calcul.

Étape 2 : Application de la validation des données

Lorsque votre liste est prête :

• Sélectionnez la cellule ou la plage de cellules où vous souhaitez que le menu déroulant apparaisse.

• Veuillez vous rendre sur la page Données du ruban et cliquez sur Validation des données.

• Dans la boîte de dialogue qui s'affiche, sous Autoriser, sélectionnez Liste.

• Dans la zone Source, saisissez vos valeurs directement ou faites référence à la plage de cellules où se trouve votre liste.

Étape 3 : Finalisation et essais

Après avoir vérifié que vous avez saisi la plage correcte :

• Presse OK pour terminer.
• Veuillez cliquer sur l'une des cellules validées. Vous verrez une petite flèche à droite, où vous pourrez sélectionner votre entrée dans la liste.

Si vous saisissez manuellement un élément que vous n'avez pas prédéfini dans la cellule, vous obtiendrez une erreur. Cette validation permet d'éviter les erreurs lors de la saisie des données.

Listes dynamiques sous forme de tableaux (Table Magic)

Si vous avez besoin d'un contrôle plus précis sur vos listes, vous pouvez utiliser des tableaux Excel pour créer des listes dynamiques. Veuillez suivre les étapes ci-dessous :

• Veuillez sélectionner votre liste de sources et appuyez sur Ctrl + T (ou allez dans l'onglet onglet Insertion > Tableau).
• Veuillez vous assurer de cocher « Mon tableau comporte des en-têtes ».

• Donnez un nom significatif à votre tableau sous le titre Conception de tableaux .

• Sélectionnez la plage de cellules où votre liste déroulante doit apparaître, puis sélectionnez les données. Données > Validation des données > Liste.

• Dans le champ « Source », veuillez saisir =INDIRECT("DepartmentList[Department]")

Lorsque vous convertissez votre liste source en tableau, vous permettez à Excel d'inclure automatiquement les nouveaux éléments dans la liste déroulante au fur et à mesure qu'ils sont ajoutés. 

Consultez la fiche récapitulative des raccourcis Excel pour découvrir comment améliorer votre productivité en apprenant les raccourcis des différentes fonctionnalités d'Excel.

Comment ajouter ou supprimer des éléments d'une liste déroulante

À un moment donné, vous devrez peut-être mettre à jour votre liste déroulante. Je vais vous montrer comment supprimer ou ajouter des éléments à la liste déroulante.

Si vous avez créé votre liste déroulante à l'aide d'une saisie manuelle, veuillez simplement ajouter le nouvel élément à la fin de la liste.

Vous pouvez également ajouter le nouvel élément à votre plage de cellules si vous avez sélectionné « Source » comme plage de cellules.

Si vous faites référence à votre liste à partir d'un tableau Excel (comme celui que j'ai montré précédemment), saisissez la nouvelle valeur sous la dernière ligne. Excel étendra et mettra automatiquement à jour le tableau, ce qui mettra également à jour votre liste.

Comment supprimer une liste déroulante

Vous pouvez supprimer une liste déroulante de votre feuille Excel sans supprimer les données que vous avez déjà saisies.

Pour supprimer une liste déroulante créée à l'aide de la validation des données :

• Sélectionnez la cellule ou la plage contenant le menu déroulant.
• Veuillez vous rendre sur la page Données > Validation des données.
• Dans la boîte de dialogue, veuillez cliquer sur « Supprimer ». Effacer tout > OK.

Cette méthode supprime la règle de validation et la flèche déroulante. Les valeurs existantes des cellules restent inchangées, mais ne sont plus limitées aux options précédentes du menu déroulant.

Si vous utilisez des listes déroulantes ou des contrôles ActiveX :

• Veuillez vous rendre sur la page Développeur > Mode Conception.
• Sélectionnez le contrôle, puis appuyez sur Supprimer sur votre clavier.

Veuillez noter que vos données existantes resteront intactes même après la suppression des règles de validation pour la liste déroulante.

Techniques avancées : Listes dynamiques et dépendantes

Maintenant que vous avez appris les bases des listes déroulantes dans Excel, voyons comment créer des listes flexibles pour des utilisations avancées.

Listes déroulantes dynamiques

Les listes déroulantes dynamiques se mettent à jour automatiquement lorsque vous modifiez les données source. Si votre liste contient des doublons, il est recommandé d'utiliser d'abord la fonction « UNIQUE() » pour extraire les valeurs distinctes. Par exemple, si vos données se trouvent dans « A2:A21 », vous pouvez utiliser la formule ci-dessous à un autre endroit pour créer une liste plus claire pour votre menu déroulant.

=UNIQUE(A2:A21)

Vous pouvez ensuite utiliser cette plage de sortie comme source pour votre menu déroulant.

Vous pouvez également utiliser la fonction d'OFFSET() si votre liste s'allonge, mais que vous ne souhaitez pas la convertir en tableau formel. 

=OFFSET(ListData!$A$2, 0, 0, COUNTA(ListData!$A:$A) -1)

Les listes déroulantes dynamiques sont utilisées dans les formulaires en direct, les feuilles de suivi ou les feuilles de calcul collaboratives. Cette fonctionnalité garantit des mises à jour automatiques lorsque les utilisateurs saisissent des données ou lorsque celles-ci sont modifiées.

Menus déroulants dépendants (en cascade)

Les listes déroulantes dépendantes (listes déroulantes en cascade) sont des ensembles de listes déroulantes dans lesquels les choix d'une liste dépendent de la sélection effectuée dans une autre. Ils sont particulièrement adaptés aux données hiérarchiques telles que les catégories et les sous-catégories.

Lors de la création des listes déroulantes dépendantes, vous devez d'abord créer des plages nommées pour chaque groupe de sous-éléments. Le deuxième menu déroulant utilise la fonction INDIRECT() pour référencer la plage nommée correspondant à la première sélection.

Étape 1 : Préparez vos données sources.

Créez une liste de catégories et de sous-catégories dans des colonnes distinctes en suivant un ordre particulier. Veuillez vous assurer que chaque sous-catégorie est nommée en utilisant le nom de « Catégorie » approprié tel qu'il apparaît dans la liste.

Étape 2 : Créer la première liste déroulante

Dans le premier menu déroulant, veuillez sélectionner la catégorie principale. Veuillez utiliser« Données » > « Validation des données » > «Liste » et définissez la source à partir de la plage de la colonne « Catégorie ». Cette étape devrait être similaire à celle que nous avons utilisée précédemment.

Étape 3 : Créer la liste déroulante dépendante

Ensuite, configurez le menu déroulant des sous-catégories. Veuillez vous rendre dans « » (Données > Validation > Liste d'). Pour la source, veuillez sélectionner la cellule dans la première liste déroulante.

=INDIRECT(A2)

Étape 4 : Veuillez vérifier le menu déroulant.

Veuillez vérifier que les articles sont correctement classés dans les colonnes « Catégorie » et « Sous-catégorie ».

Voici les problèmes courants à surveiller lors de l'utilisation de listes déroulantes dépendantes :

• Veuillez vous assurer que les plages de sous-catégories nommées correspondent au texte dans le menu déroulant de la catégorie principale. Il ne doit y avoir aucun espace supplémentaire et respecter la casse.

• Si INDIRECT() renvoie une erreur d'#REF!, veuillez vérifier que les plages nommées existent et correspondent aux valeurs de la liste principale.

Personnalisation et expérience utilisateur

Vous pouvez rendre vos listes déroulantes plus flexibles afin d'améliorer l'expérience utilisateur et la convivialité. Dans cette section, je vais vous montrer comment personnaliser les listes déroulantes pour différents cas d'utilisation.

Messages d'entrée et alertes d'erreur

Excel vous permet d'ajouter de courts messages aux cellules déroulantes afin d'aider les utilisateurs à faire le bon choix. Pour configurer un message d'entrée :

• Veuillez sélectionner la cellule déroulante.
• Accédez à Données > Validation des données.
• Veuillez passer à l'onglet Message d'entrée.
• Veuillez saisir un titre et un message, par exemple « Veuillez sélectionner un service dans la liste ».

La flèche déroulante apparaîtra avec un message à côté de la cellule sélectionnée. Cela aidera l'utilisateur à comprendre les données requises pour le champ.

Vous pouvez créer des alertes « Erreur » qui s'affichent si quelqu'un tente de saisir des données qui ne correspondent pas aux options du menu déroulant. Pour personnaliser cette fonctionnalité :

• Accédez à Validation des données > Alerte d'erreur .
• Veuillez sélectionner le style d'alerte : Arrêt (empêche la saisie de données non valides), Avertissement (avertit mais autorise la modification) ou Information (affiche des informations mais autorise la poursuite de la saisie)
• Veuillez fournir un message expliquant la restriction, par exemple « Veuillez sélectionner un service valide dans la liste ».

Veuillez toujours utiliser un langage clair et convivial dans les messages de saisie et les alertes d'erreur afin d'améliorer la clarté des messages.

Menus déroulants consultables

Dans les versions récentes d'Excel, telles que Microsoft 365 et Excel pour le web, vous pouvez utiliser la fonctionnalité de menu déroulant avec option de recherche, en particulier lorsque vous parcourez une longue liste d'éléments. Lorsque vous cliquez sur la flèche déroulante, vous pouvez commencer à saisir du texte et Excel filtre la liste en fonction de votre saisie. Cette fonctionnalité est importante lorsque vous travaillez avec de longues listes, telles que des noms de clients, des références de produits ou des noms de pays.

Cependant, les menus déroulants consultables ne sont disponibles que dans les versions récentes d'Excel et non dans les versions de bureau plus anciennes telles qu'Excel 2016 ou 2019. Si vous utilisez une version antérieure d'Excel, veuillez utiliser des listes déroulantes ou des contrôles de formulaire avec fonctionnalités de recherche intégrées via VBA pour créer des listes déroulantes consultables.

Autoriser d'autres entrées ou la saisie manuelle

Il peut arriver que vous souhaitiez ajouter des éléments à votre liste sans passer par la validation initiale. Si vous souhaitez ajouter des données personnalisées :

• Sélectionnez la cellule ou la plage de cellules où vous souhaitez que le menu déroulant apparaisse.
• Accédez à Données > Validation des données.
• Dans l'onglet Alerte d'erreur, veuillez décocher la case « Afficher une alerte d'erreur après la saisie de données non valides ».

Lorsque vous désactivez cette option, vous pouvez saisir n'importe quelle valeur qui ne figure pas dans la liste déroulante.

Bien que la saisie manuelle offre une plus grande flexibilité, elle peut entraîner des données incohérentes ou invalides si les utilisateurs commettent des erreurs de frappe ou saisissent des valeurs inattendues. Cela réduit également l'avantage d'avoir une liste contrôlée.

Pour traiter ces entrées non répertoriées :

• Utilisez la mise en forme conditionnelle pour signaler les cellules dont les valeurs ne figurent pas dans la liste validée afin qu'elles soient vérifiées.

• Veuillez créer des colonnes d'aide qui vérifient la validité. Par exemple, veuillez utiliser COUNTIF() pour vérifier si l'entrée existe dans la liste source.

• Vérifiez et mettez à jour régulièrement vos listes de sources afin d'y ajouter les nouvelles entrées valides.

Consultez notre cours sur le formatage conditionnel dans Google Sheets pour découvrir comment appliquer le formatage conditionnel afin de valider les données et obtenir rapidement des informations pertinentes.

Contrôles de formulaire et ActiveX pour des fonctionnalités améliorées

Si vous avez besoin d'une flexibilité et d'une fonctionnalité encore plus grandes pour vos listes déroulantes, Excel propose des contrôles avancés tels que Form Control et les zones de liste combinées ActiveX.

Boîtes combinées de contrôle de formulaire

Les listes déroulantes des contrôles de formulaire fonctionnent comme des listes déroulantes standard, mais permettent aux utilisateurs de les lier à d'autres cellules. Cette méthode est utile lorsque vous intégrez des listes déroulantes à des formulaires ou à des tableaux de bord.

Pour utiliser les listes déroulantes :

• Veuillez vous rendre sur la page Développeur .
• Cliquez sur Insérer > Contrôles de formulaire, puis sélectionnez Liste déroulante.

• Veuillez dessiner la zone de liste déroulante sur votre feuille de calcul.

• Cliquez avec le bouton droit sur le contrôle et sélectionnez Formater le contrôle.

• Dans l'onglet Contrôle, définissez la « Plage d'entrée » et une « Liaison de cellule » où l'index de l'élément sélectionné apparaîtra.

Vous remarquerez que la liste déroulante renvoie un nombre correspondant à la position de l'élément dans la liste. Vous pouvez récupérer la valeur réelle à partir de la position de l'élément à l'aide de la fonction INDEX().

Les contrôles de formulaire sont préférables lors de la création de tableaux de bord ou de rapports interactifs. Ils peuvent également être utilisés dans des scénarios où VBA n'est pas nécessaire, mais où vous avez besoin d'un formatage plus flexible que celui offert par la validation de données standard.

Boîtes de contrôle ActiveX

Les boîtes de contrôle ActiveX offrent davantage de puissance et de personnalisation, notamment le contrôle des polices, la saisie automatique et la possibilité de déclencher des macros en fonction de l'interaction de l'utilisateur.

Pour ajouter les boîtes de contrôle ActiveX

• Accédez à Développeur > Insérer > Contrôles ActiveX > Liste déroulante.

• Veuillez dessiner la boîte sur la feuille.
• Cliquez avec le bouton droit de la souris et sélectionnez Propriétés pour configurer les paramètres tels que « ListFillRange » et « LinkedCell », puis personnalisez les polices, les couleurs d'arrière-plan et les styles de bordure.

L'avantage d'utiliser la liste déroulante ActiveX réside dans la plus grande flexibilité qu'elle offre en matière de mise en forme des polices, des couleurs et de la disposition. Il permet également la programmation événementielle pour les formulaires et les applications hautement interactifs. Cette fonctionnalité s'intègre aux macros et à l'automatisation.

Cependant, les contrôles ActiveX ne fonctionnent que sous Windows et ne sont pas pris en charge dans Excel pour Mac ou Excel Online. Ils sont également plus lourds que les contrôles de formulaire et peuvent ralentir les performances dans les classeurs volumineux. Pour une utilisation avancée, des connaissances en VBA peuvent être nécessaires.

Résolution des problèmes courants

Même avec des fonctionnalités avancées, vous pouvez rencontrer des problèmes lorsque vous utilisez des listes déroulantes dans Excel. Explorons les pièges courants et comment résoudre ces problèmes.

Veuillez énumérer les erreurs et les corrections.

Voici les problèmes courants et les solutions pour les résoudre :

• Options vides dans le menu déroulant : Votre liste déroulante peut contenir des lignes vides ou vierges. Pour résoudre ce problème, veuillez vous assurer que votre plage source ne contient pas de cellules vides.
• Éléments manquants dans le menu déroulant : Certains éléments peuvent ne pas apparaître dans votre liste déroulante. Pour résoudre ce problème, veuillez vérifier que la plage ou le tableau source comprend tous les éléments attendus. Veuillez vérifier que la plage source de la validation des données couvre l'intégralité de la liste pour les listes basées sur une plage et ajustez si nécessaire.
• Références incorrectes ou obsolètes : Si vos références sont incorrectes, veuillez vérifier que la formule ou la liste de la source de validation des données pointe vers la feuille de calcul et la plage de cellules appropriées. Veuillez également vérifier qu'il n'y a pas de fautes de frappe ou de modifications accidentelles dans les noms des plages.
• La liste déroulante n'apparaît pas ou la flèche déroulante est manquante : Si vous supprimez la validation des données, la liste déroulante peut ne pas apparaître. Veuillez toujours vous assurer que le type de validation est « Liste » et réappliquez la validation des données chaque fois que vous effacez la mise en forme.

Débogage avancé

Certaines de ces problèmes peuvent être liés à des erreurs de formule ou à des comportements de débordement associés aux listes dynamiques et aux plages nommées :

• Erreurs #REF ! : Cette erreur se produit lorsqu'une formule ou une plage nommée fait référence à une cellule, une feuille ou un tableau supprimé. Veuillez vérifier et mettre à jour les plages nommées ou les formules qui utilisent les fonctions d'OFFSET(), d'INDIRECT() ou de tableau dynamique afin de corriger cette erreur.

• Erreurs #SPILL ! : Cette erreur se produit lorsqu'une formule de tableau dynamique telle que « UNIQUE() » tente d'afficher des valeurs, mais que d'autres données bloquent la zone de débordement. Veuillez toujours vérifier qu'aucune cellule fusionnée n'est vide afin que la formule puisse remplir les cellules adjacentes.

• Désalignement dynamique du tableau : Si vous créez votre liste à l'aide de fonctions telles que UNIQUE(), SORT() ou FILTER(), la taille de la sortie peut varier. Par conséquent, veuillez utiliser une plage nommée dynamique ou faire référence à l'ensemble de la colonne de sortie de la formule.

Je vous recommande de suivre notre cours Fonctions avancées d'Excel pour en savoir plus sur le décalage et les plages dynamiques dans Excel.

Conclusion

Les listes déroulantes dans Excel sont utiles pour guider la saisie des données, garantir la cohérence et améliorer la convivialité générale de vos feuilles de calcul. De la création de listes de base et de liens dynamiques vers des tableaux à la sélection en cascade et aux contrôles de formulaire personnalisés, ces outils contribuent à rendre les feuilles de calcul plus interactives, plus précises et plus conviviales. 

La maîtrise des techniques de menu déroulant réduit les erreurs, améliore la cohérence des données et jette les bases de solutions de tableur plus professionnelles et évolutives. Je vous encourage à perfectionner vos compétences en apprenant à intégrer la fonctionnalité de menu déroulant à Power Query ou en utilisant VBA pour bénéficier d'une automatisation et d'une intelligence encore plus poussées dans vos workflows Excel.

Si vous souhaitez approfondir vos compétences en Excel, je vous recommande de suivre notre cours Analyse de données dans Excel. Ce cours vous permettra de maîtriser les techniques d'analyse avancées et de donner un nouvel élan à votre carrière. Je vous recommande également de suivre notre cours Power Query intermédiaire dans Excel pour en savoir plus sur la transformation des données et l'utilisation du langage M pour créer des fonctions dynamiques.