Tutoriel Excel : les listes déroulantes

Dans les formulaires Web, les enquêtes et les sondages, il peut s'avérer très utile de limiter les choix d'une sélection à l'aide d'une simple liste déroulante. Cela est également possible dans un tableur Excel, mais le processus n'est pas très connu ni très intuitif.

Dans Access, vous pouvez limiter les entrées utilisateur en obligeant les utilisateurs à choisir une valeur dans un contrôle de liste. Les applications Office utilisent les mêmes fonctionnalités dans les listes déroulantes intégrées. Par exemple, les contrôles Couleur de surbrillance et Couleur de police de la plupart des barres d'outils de mise en forme utilisent cet outil flexible. Cliquez simplement sur le petit triangle situé à droite de l'icône pour afficher une liste de choix.

Vous pouvez créer le même type de contrôle pour vos utilisateurs dans une feuille Excel, mais le processus n’est pas intuitif. L'option est dans la fonctionnalité de validation des données. Heureusement, une fois que vous savez que la fonctionnalité existe, elle est facile à mettre en œuvre. Vous n'avez besoin que de deux choses : une liste et une cellule de saisie de données. La feuille suivante montre une simple liste déroulante dans une feuille Excel.

Les utilisateurs cliquent sur la flèche déroulante pour afficher une liste d'éléments. Si un utilisateur tente de saisir quelque chose qui ne figure pas dans la liste, Excel la refuse.

Dans ce tutoriel, vous apprendrez à créer une liste déroulante dans Excel (le processus ne prend que quelques secondes), ainsi que tout ce que vous pouvez faire avec cela :

• Comment créer une liste déroulante dynamique avec la fonction DECALER,
• Copier et sélectionner les listes déroulantes dans une feuille de calcul,
• La création d'une liste déroulante Excel conditionnelle,
• La création des menus déroulants en cascade.

Ce tutoriel été réalisé sous la version Excel 2016 mais il est aussi valable pour les versions 2007, 2010 et 2013.

Créer une liste déroulante Excel

Une liste déroulante est un excellent moyen de donner à l'utilisateur une option de sélection dans une liste prédéfinie. Elle peut être utilisée pour obliger un utilisateur à remplir un formulaire ou pour créer des tableaux de bord Excel interactifs. Les listes déroulantes sont assez courantes sur les sites Web / applications et sont très intuitives pour l'utilisateur.

Dans cette section, vous apprendrez les étapes à suivre pour créer une liste déroulante Excel avec trois méthodes :

-Utilisation les données des cellules.

-Saisie manuelle des données.

-Utilisation de la formule DECALER.

 Créer une liste déroulante en utilisant les données des cellules :

Supposons que vous ayez une liste comme ci-dessous qui représente certaines villes :

Voici les étapes à suivre pour créer une liste déroulante Excel à partir de ce tableau de villes :

Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante.

Allez dans Données -> Outils de données -> Validation des données.

Dans la boîte de dialogue « Validation des données », dans l'onglet « Paramètres », sélectionnez « Liste » comme critère de validation. Dès que vous sélectionnez Liste, le champ « source » apparaît.

Dans le champ source, entrez = $A$2:$B$7 ou cliquez simplement dans le champ  « Source » et sélectionnez les cellules à l'aide de la souris, puis cliquez sur OK. Cela insérera une liste déroulante dans la cellule que vous avez sélectionné au début dans l’exemple c’est la cellule D2.

Assurez-vous que l'option Liste déroulante dans la cellule est cochée (ce qui est coché par défaut). Si cette option est décochée, la cellule n’affiche pas de liste déroulante. Toutefois, vous pouvez entrer manuellement les valeurs dans la liste.

Si vous souhaitez créer des listes déroulantes dans plusieurs cellules à la fois, sélectionnez toutes les cellules dans lesquelles vous souhaitez la créer, puis suivez les étapes ci-dessus. Assurez-vous que les références de cellule sont absolues (telles que $A$2) et non relatives (telles que A2, A$2 ou $A2).

Créer une liste déroulante en saisissant les données manuellement :

Dans la première méthode que nous avons vue, les références de cellule sont utilisées dans le champ « Source ». Vous pouvez également ajouter des éléments directement en les saisissant manuellement dans ce champ.

Par exemple, supposons que vous souhaitiez afficher deux options, Vrai et Faux, dans le menu déroulant d’une cellule. Voici comment vous pouvez directement le saisir dans le champ « source » de « validation des données » :

Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante (la cellule A1 dans cet exemple) puis allez dans Données -> Outils de données -> Validation des données.

Dans la boîte de dialogue Validation des données, dans l'onglet « Paramètres », sélectionnez Liste comme critère de validation et le champ « source » va apparaître.

Dans le champ source, entrez Vrai; Faux, ceci pour la version 2016 d’Excel pour les anciennes version c’est avec une virgule : Vrai, Faux

Assurez-vous que l'option « Liste déroulante dans la cellule » est cochée et cliquez sur OK.

Cela créera une liste déroulante dans la cellule sélectionnée. Tous les éléments répertoriés dans le champ « source », séparés par un point-virgule (une virgule dans les anciennes versions d’Excel), sont répertoriés dans différentes lignes du menu déroulant comme des options de cette liste ils sont affichés sur des lignes différentes dans la liste déroulante.

Créer une liste déroulante en utilisant des formules Excel :

Outre la sélection dans les cellules et la saisie manuelle des données, vous pouvez également utiliser une formule dans le champ source pour créer une liste déroulante Excel.

Toute formule qui renvoie une liste de valeurs peut être utilisée pour créer une liste déroulante dans Excel.

Par exemple, supposons que vous ayez le jeu de données comme indiqué ci-dessous :

Voici les étapes à suivre pour créer une liste déroulante Excel à l'aide de la fonction DECALER :

Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante (la cellule B1 dans cet exemple).

Allez dans Données -> Outils de données -> Validation des données puis dans la boîte de dialogue « Validation des données », dans l'onglet « Paramètres », sélectionnez « Liste » comme critère de validation et vous allez voir que dès que vous sélectionnez Liste, le champ « source » apparaît.

Dans le champ Source, entrez la formule suivante : =DECALER($A$2 ;0 ;0 ;6).

Assurez-vous que l'option Liste déroulante dans la cellule est cochée et cliquez sur OK.

Cela créera une liste déroulante répertoriant tous les noms de couleurs (comme indiqué ci-dessous).

On va voir maintenant comment fonctionne la fonction DECALER/OFFSET :

Dans le cas ci-dessus, nous avons utilisé une fonction DECALER (OFFSET) pour créer la liste déroulante. Elle retourne une liste d'éléments de la plage A2: A7.

Voici la syntaxe de la fonction DECALER: = DECALER (référence ; lignes ; colonnes ; [hauteur] ; [largeur])

Il faut cinq arguments pour lesquels nous avons spécifié la référence en tant que A2 (le point de départ de la liste). Les lignes / colonnes sont spécifiées avec la valeur 0 car nous ne souhaitons pas décaler la cellule de référence. La hauteur est spécifiée à 6 car il y a six éléments dans la liste

Maintenant, lorsque vous utilisez cette formule, il retourne un tableau contenant la liste des six couleurs en A2: A7. Notez que si vous entrez la formule dans une cellule, sélectionnez-la et appuyez sur F9, vous verrez qu'elle renvoie un tableau des noms de couleurs.

Création d'une liste déroulante dynamique dans Excel (avec DECALER /OFFSET)

La technique ci-dessus consistant à utiliser une formule pour créer une liste déroulante peut également être étendue pour créer une liste déroulante dynamique. Si vous utilisez la fonction DECALER, comme indiqué ci-dessus, même si vous ajoutez d'autres éléments à la liste, le menu déroulant ne sera pas mis à jour automatiquement. Vous devrez le mettre à jour manuellement chaque fois que vous modifierez la liste. Donc on va voir un moyen de le rendre dynamique (et ce n’est qu’un ajustement mineur dans la formule):

Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante (la cellule B1 dans cet exemple) et allez dans Données -> Outils de données -> Validation des données.

Dans la boîte de dialogue Validation des données, dans l'onglet « Paramètres », sélectionnez « Liste » comme critère de validation et dans le champ source qui apparaît entrez la formule suivante :

 = DECALER($A$2; 0; 0; NBVAL($A$2:$A$100))

Dans cette formule, j'ai remplacé l'argument 6 par NBVAL($A$2:$A$100). La fonction NBVAL compte les cellules non vides dans la plage A2: A100. Par conséquent, la fonction DECALER s'ajuste pour inclure toutes les cellules non vides.

Pour que cela fonctionne, il ne doit pas y avoir de cellules vides entre les cellules remplies sinon vous aurez un résultat comme ci-dessous :

Vous pouvez aussi tester si vous ajouter une nouvelle entrée ou un nouvel élément dans la table des couleurs la liste déroulante va changer automatiquement le nouvel élément va être ajouté à la liste et c’est pourquoi on dit qu’il est dynamique.

Gérer les listes déroulantes dans Excel

Copier-coller les listes déroulantes :

Vous pouvez copier-coller les cellules avec la validation des données dans d'autres cellules, ce qui copiera également la validation des données.

Par exemple, si vous avez une liste déroulante dans la cellule B1 et que vous souhaitez également l'appliquer à C1: C6, copiez simplement la cellule B1 et collez-la dans C1: C6. Cela va copier la liste déroulante et la rendre disponible dans C1: C6 (avec la liste déroulante, cela copiera également le formatage).

Si vous souhaitez uniquement copier le menu déroulant et non le formatage, procédez comme suit :

Copiez la cellule contenant le menu déroulant puis sélectionnez les cellules dans lesquelles vous souhaitez copier le menu déroulant et après, allez dans Accueil -> Coller -> Collage Spécial. Dans la boîte de dialogue « Collage spécial », sélectionnez l’options de « validation » dans le collage et cliquez sur OK.

Cela ne fera que copier le menu déroulant et non le formatage de la cellule copiée.

Remarque : Vous devez faire attention lorsque vous travaillez avec des listes déroulantes dans Excel. Lorsque vous copiez une cellule (ne contenant pas de liste déroulante) sur une cellule contenant une liste déroulante, la liste déroulante est perdue. Le pire dans tout cela est qu’Excel n’affiche aucune alerte ni invite pour informer l’utilisateur que la liste déroulante sera écrasée.

On va voir comment modifier l’apparence de toutes les cellules contenant les listes déroulantes pour éviter ce genre de problème.

Sélectionner toutes les cellules contenant une liste déroulante

Il est parfois difficile de savoir quelles cellules contiennent la liste déroulante. Par conséquent, il est logique de marquer ces cellules en leur attribuant une bordure distincte ou une couleur de fond, et au lieu de vérifier manuellement toutes les cellules, il existe un moyen rapide de sélectionner toutes les cellules contenant des listes déroulantes (ou toute règle de validation des données). Pour ce faire allez à Accueil -> Rechercher et sélectionner -> Sélectionner les cellules.

Dans la boîte de dialogue « Sélectionner les cellules », sélectionnez « Validation des données ».

La validation des données comporte deux options: « Toutes » et « Identiques ». « Toutes » sélectionneraient toutes les cellules auxquelles une règle de validation de données est appliquée. Et « Identiques » sélectionnerait uniquement les cellules ayant la même règle de validation de données que celle de la cellule active.

Cliquez sur OK.

Cela sélectionnerait instantanément toutes les cellules auxquelles une règle de validation de données était appliquée (ceci inclut également les listes déroulantes).

Maintenant, vous pouvez simplement formater les cellules (donner une bordure ou une couleur d’arrière-plan) de manière à ce qu’elles soient visuellement visibles et que vous ne copiez pas accidentellement une autre cellule dessus.

Parfois, vous pouvez avoir plusieurs listes déroulantes et vous souhaitez que les éléments affichés dans la deuxième liste déroulante dépendent de ce que l'utilisateur a sélectionné dans la première. On va voir dans la partie suivante comment créer ce genre de liste.

Créer une liste déroulante Excel Dépendante/conditionnelle

Quand une liste déroulante dépend du contenu d’une autre liste on l’appelle une liste déroulante dépendante ou conditionnelle. Vous trouverez ci-dessous un exemple de liste déroulante conditionnelle / dépendante :

Il est facile de créer des listes déroulantes dépendantes simples dans Excel. Tout ce dont vous avez besoin est quelques plages nommées et la formule INDIRECT. Cette méthode fonctionne avec toutes les versions d'Excel 2007, 2010 et 2013.

Dans l’exemple ci-dessus, les éléments énumérés dans la liste déroulante « Nom » dépendent de la sélection effectuée dans « Sexe ». Voyons maintenant comment créer cela.

Voici les étapes à suivre pour créer une liste déroulante dépendante ou conditionnelle dans Excel:

Sélectionnez la cellule où vous voulez la première liste (principale) déroulante dans cet exemple c’est la liste « Sexe ».

Allez dans Données -> Validation des données. Cela ouvrira la boîte de dialogue de validation des données.

Dans la boîte de dialogue de validation des données, dans l'onglet Paramètres, sélectionnez « Liste » et dans le champ « Source », spécifiez la plage contenant les éléments à afficher dans la première liste déroulante dans cet exemple c’est : Homme; Femme (Homme, Femme pour les anciennes versions)ou bien vous pouvez le faire en sélectionnant les cellules comme illustré ci-dessous.

Cliquez sur OK. Cela créera le menu « Sexe ».

Sélectionnez l'ensemble de données complet (A1: B7 dans cet exemple).Allez à Formules -> Noms définis -> depuis sélection.

Dans la boîte de dialogue « Créer des noms à partir de la sélection », cochez l’option Ligne du haut et décochez toutes les autres. Cela crée 2 plages de noms (« Homme » et « Femme »). La rangé des hommes se rapportent à tous les noms des hommes de la liste et celle des femmes se rapportent à tous les noms des femmes de la liste.

Cliquez sur OK.

Après sélectionnez la cellule dans laquelle vous souhaitez que la liste déroulante Dépendante ou Conditionnelle soit affichée (E2 dans cet exemple).

Allez dans Données -> Validation des données et dans la boîte de dialogue « Validation des données », dans l'onglet « Paramètres », assurez-vous que l'option « Liste » est sélectionnée puis dans le champ « Source », entrez la formule =INDIRECT(D2). Ici, D2 est la cellule qui contient la liste déroulante principale « Sexe ».

Cliquez sur OK.

Vous allez voir que lorsque vous effectuez la sélection dans le menu « Sexe », les options répertoriées dans la liste « Nom » sont automatiquement mises à jour.

La liste déroulante conditionnelle (dans la cellule E2) fait référence à =INDIRECT(D2). Cela signifie que lorsque vous sélectionnez "Homme" dans la cellule D2, la liste déroulante de E2 fait référence à la plage nommée "Homme" (via la fonction INDIRECT) et répertorie par conséquent tous les éléments de cette catégorie.

Remarque : Si la cellule D2 est actuellement vide, vous obtiendrez le message d'erreur "La source est reconnu comme erronée. Voulez-vous continuer ?" Cliquez sur Oui. Dès que vous sélectionnez un élément dans le premier menu déroulant, les entrées correspondantes s'affichent dans la deuxième liste déroulante, dépendante.

Créer un menu déroulant en cascade 

Dans cette partie on va essayer de créer un menu déroulant en cascade en utilisant les listes déroulantes conditionnelles que nous avons vu.

1. Saisissez les entrées pour les listes déroulantes.

Tout d'abord, tapez les entrées que vous souhaitez voir apparaître dans les listes déroulantes, chaque liste dans une colonne séparée. Par exemple, je crée une liste déroulante en cascade des villes de régions et la colonne A de ma feuille comprend les éléments de la première liste déroulante et 3 autres colonnes répertorient les éléments des listes déroulantes dépendantes.

2. Nommer les plages crées :

Vous devez maintenant créer des noms pour votre liste principale et pour chacune des listes dépendantes. Vous pouvez le faire en ajoutant un nouveau nom dans la fenêtre Gestionnaire de noms (onglet Formules>Définir un nom)

Ou en tapant le nom directement dans la zone Nom et cliquez sur la touche clavier « entrer ».

Faites attention que si votre première ligne est une sorte d'en-tête de colonne comme vous le voyez dans la capture d'écran ci-dessus, vous ne devez pas l'inclure dans la plage nommée.

Pour voir la liste des noms, cliquez sur « gestionnaire de noms » dans l’onglet « formules »

Remarque : si les éléments de votre liste contiennent des espaces il vaut mieux de les séparer par des Under score « _ » sinon votre liste dépendante sera vide.

3. Créez la première liste déroulante (principale).

Dans la même ou dans une autre feuille de calcul, sélectionnez une ou plusieurs cellules dans lesquelles vous souhaitez voir apparaître votre liste déroulante principale.

Accédez à l'onglet Données, cliquez sur « Validation des données » et configurez une liste déroulante basée sur une plage nommée de la manière habituelle en sélectionnant « Liste » sous « Autoriser » et en entrant le nom de la plage dans la zone « Source ».

Dans cette exemple nous avons utilisé le nom de la plage « Region » au lieu de la fonction DECALER, c’est une autre méthode pour créer une liste déroulante.

4. Créez la liste déroulante dépendante.

Sélectionnez une ou plusieurs cellules pour le menu déroulant dépendant et appliquez à nouveau la validation des données Excel, comme décrit à l'étape précédente. Mais cette fois, au lieu du nom de la plage, vous entrez la formule suivante dans le champ « Source »: =INDIRECT(A18) où A18 est la cellule avec votre première liste déroulante (principale).