Comment créer une liste déroulante dans Excel (le seul guide dont vous avez besoin)

Une liste déroulante est un excellent moyen de donner à l'utilisateur la possibilité de choisir parmi une liste prédéfinie.

Il peut être utilisé pour demander à un utilisateur de remplir un formulaire ou lors de la création de 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.

Regarder la vidéo - Création d'une liste déroulante dans Excel

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

Comment créer une liste déroulante dans Excel

Dans cette section, vous apprendrez les étapes exactes pour créer une liste déroulante Excel :

1. Utilisation des données des cellules.
2. Saisie manuelle des données.
3. Utilisation de la formule OFFSET.

#1 Utiliser les données des cellules

Supposons que vous ayez une liste d'éléments comme indiqué ci-dessous :

Voici les étapes pour créer une liste déroulante Excel :

1. Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante.
2. Accédez à Données -> Outils de données -> Validation des données.
3. 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.
4. Dans le champ source, saisissez =$A$2:$A$6, ou cliquez simplement dans le champ Source et sélectionnez les cellules à l'aide de la souris et cliquez sur OK. Cela insérera une liste déroulante dans la cellule C2.
   • Assurez-vous que l'option déroulante Dans la cellule est cochée (qui est cochée par défaut). Si cette option n'est pas cochée, la cellule n'affiche pas de liste déroulante, cependant, vous pouvez saisir manuellement les valeurs dans la liste.

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

#2 En saisissant les données manuellement

Dans l'exemple ci-dessus, les références de cellule sont utilisées dans le champ Source. Vous pouvez également ajouter des éléments directement en le saisissant manuellement dans le champ source.

Par exemple, supposons que vous souhaitiez afficher deux options, Oui et Non, dans la liste déroulante d'une cellule. Voici comment vous pouvez le saisir directement dans le champ source de validation des données :

• Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante (cellule C2 dans cet exemple).
• Accédez à 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, saisissez Oui, Non
  • Assurez-vous que l'option de liste déroulante dans la cellule est cochée.
• 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 une virgule, sont répertoriés sur différentes lignes dans le menu déroulant.

Tous les éléments saisis dans le champ source, séparés par une virgule, sont affichés sur différentes lignes dans la liste déroulante.

Noter: Si vous souhaitez créer des listes déroulantes dans plusieurs cellules à la fois, sélectionnez toutes les cellules dans lesquelles vous souhaitez les créer, puis suivez les étapes ci-dessus.

#3 Utiliser 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 l'ensemble de données comme indiqué ci-dessous :

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

• Sélectionnez une cellule dans laquelle vous souhaitez créer la liste déroulante (cellule C2 dans cet exemple).
• Accédez à 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 la formule suivante : =OFFSET($A$2,0,0,5)
  • Assurez-vous que l'option de liste déroulante dans la cellule est cochée.
• Cliquez sur OK.

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

Noter: Si vous souhaitez créer une liste déroulante 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 (comme $A$2) et non relatives (comme A2, ou A$2 ou $A2).

Comment fonctionne cette formule ??

Dans le cas ci-dessus, nous avons utilisé une fonction OFFSET pour créer la liste déroulante. Il renvoie une liste d'éléments de la ra

Il renvoie une liste d'éléments de la plage A2:A6.

Voici la syntaxe de la fonction OFFSET : =OFFSET(reference, rows, cols, [height], [width])

Il prend cinq arguments, où nous avons spécifié la référence comme A2 (le point de départ de la liste). Rows/Cols sont spécifiés comme 0 car nous ne voulons pas décaler la cellule de référence. La hauteur est spécifiée comme 5 car il y a cinq éléments dans la liste.

Maintenant, lorsque vous utilisez cette formule, elle renvoie un tableau contenant la liste des cinq fruits dans A2:A6. 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 fruits.

Création d'une liste déroulante dynamique dans Excel (à l'aide d'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 OFFSET, comme indiqué ci-dessus, même si vous ajoutez d'autres éléments à la liste, la liste déroulante ne se mettra pas à jour automatiquement. Vous devrez le mettre à jour manuellement à chaque fois que vous modifiez la liste.

Voici 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 (cellule C2 dans cet exemple).
• Accédez à 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, saisissez la formule suivante : =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,""))
• Assurez-vous que l'option déroulante Dans la cellule est cochée.
• Cliquez sur OK.

Dans cette formule, j'ai remplacé l'argument 5 par COUNTIF($A$2:$A$100,"").

La fonction COUNTIF compte les cellules non vides dans la plage A2:A100. Par conséquent, la fonction OFFSET s'ajuste pour inclure toutes les cellules non vides.

Noter:

• Pour que cela fonctionne, il ne doit PAS y avoir de cellules vides entre les cellules remplies.
• Si vous souhaitez créer une liste déroulante 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 (comme $A$2) et non relatives (comme A2, ou A$2 ou $A2).

Copier coller des listes déroulantes dans Excel

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

Par exemple, si vous avez une liste déroulante dans la cellule C2 et que vous souhaitez également l'appliquer à C3:C6, copiez simplement la cellule C2 et collez-la dans C3:C6. Cela copiera la liste déroulante et la rendra disponible dans C3:C6 (avec la liste déroulante, il copiera également le formatage).

Si vous souhaitez uniquement copier la liste déroulante et non la mise en forme, voici les étapes :

• Copiez la cellule qui a la liste déroulante.
• Sélectionnez les cellules dans lesquelles vous souhaitez copier la liste déroulante.
• Allez dans Accueil -> Coller -> Collage spécial.
• Dans la boîte de dialogue Collage spécial, sélectionnez Validation dans les options de collage.
• Cliquez sur OK.

Cela ne copiera que la liste déroulante et non le formatage de la cellule copiée.

Attention lors de l'utilisation de la liste déroulante Excel

Vous devez faire attention lorsque vous travaillez avec des listes déroulantes dans Excel.

Lorsque vous copiez une cellule (qui ne contient pas de liste déroulante) sur une cellule qui contient une liste déroulante, la liste déroulante est perdue.

Le pire dans tout cela est qu'Excel n'affichera aucune alerte ou invite pour informer l'utilisateur qu'une liste déroulante sera écrasée.

Comment sélectionner toutes les cellules contenant une liste déroulante

Parfois, il est difficile de savoir quelles cellules contiennent la liste déroulante.

Par conséquent, il est logique de marquer ces cellules en leur donnant une bordure distincte ou une couleur d'arrière-plan.

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).

• Allez dans Accueil -> Rechercher et sélectionner -> Aller à spécial.
• Dans la boîte de dialogue Aller à spécial, sélectionnez Validation des données
  • La validation des données a deux options : Tout et Même. All sélectionnerait toutes les cellules auxquelles une règle de validation des données est appliquée. Idem sélectionne uniquement les cellules qui ont la même règle de validation des 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 des données est appliquée (cela inclut également les listes déroulantes).

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

Voici une autre technique de Jon Acampora que vous pouvez utiliser pour toujours garder l'icône de la flèche déroulante visible. Vous pouvez également voir quelques façons de le faire dans cette vidéo de M. Excel.

Création d'une liste déroulante Excel dépendante / conditionnelle

Voici une vidéo sur la façon de créer une liste déroulante dépendante dans Excel.

Si vous préférez lire plutôt que regarder une vidéo, continuez à lire.

Parfois, vous pouvez avoir plusieurs listes déroulantes et vous voulez 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 liste déroulante.

Celles-ci sont appelées listes déroulantes dépendantes ou conditionnelles.

Voici un exemple de liste déroulante conditionnelle/dépendante :

Dans l'exemple ci-dessus, lorsque les éléments répertoriés dans « Drop Down 2 » dépendent de la sélection effectuée dans « Drop Down 1 ».

Voyons maintenant comment créer cela.

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

• Sélectionnez la cellule où vous voulez la première liste déroulante (principale).
• 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.
• Dans le champ Source, spécifiez la plage qui contient les éléments qui doivent être affichés dans la première liste déroulante.
• Cliquez sur OK. Cela créera le menu déroulant 1.
• Sélectionnez l'ensemble des données (A1:B6 dans cet exemple).
• Allez dans Formules -> Noms définis -> Créer à partir de la sélection (ou vous pouvez utiliser le raccourci clavier Ctrl + Maj + F3).
• Dans la boîte de dialogue « Créer un nom à partir de la sélection », cochez l'option Ligne du haut et décochez toutes les autres. Cela crée 2 plages de noms (« Fruits » et « Légumes »). La plage de fruits nommés fait référence à tous les fruits de la liste et la plage de légumes nommés fait référence à tous les légumes de la liste.
• Cliquez sur OK.
• Sélectionnez la cellule dans laquelle vous voulez la liste déroulante Dépendante/Conditionnelle (E3 dans cet exemple).
• Allez dans Données -> Validation des données.
• Dans la boîte de dialogue Validation des données, dans l'onglet Paramètres, assurez-vous que Liste est sélectionné.
• Dans le champ Source, saisissez la formule =INDIRECT(D3). Ici, D3 est la cellule qui contient la liste déroulante principale.
• Cliquez sur OK.

Désormais, lorsque vous effectuez la sélection dans la liste déroulante 1, les options répertoriées dans la liste déroulante 2 sont automatiquement mises à jour.

Télécharger le fichier exemple

Comment cela marche-t-il? - La liste déroulante conditionnelle (dans la cellule E3) fait référence à =INDIRECT(D3). Cela signifie que lorsque vous sélectionnez « Fruits » dans la cellule D3, la liste déroulante dans E3 fait référence à la plage nommée « Fruits » (via la fonction INDIRECT) et répertorie donc tous les éléments de cette catégorie.

Remarque importante lors de l'utilisation de listes déroulantes conditionnelles dans Excel :

• Lorsque vous avez effectué la sélection, puis que vous modifiez la liste déroulante parent, la liste déroulante dépendante ne changerait pas et serait donc une entrée erronée. Par exemple, si vous sélectionnez les États-Unis comme pays, puis la Floride comme état, puis revenez en arrière et remplacez le pays par l'Inde, l'état restera la Floride. Voici un excellent tutoriel de Debra sur l'effacement des listes déroulantes dépendantes (conditionnelles) dans Excel lorsque la sélection est modifiée.
• Si la catégorie principale est constituée de plusieurs mots (par exemple, « Fruits de saison » au lieu de « Fruits »), alors vous devez utiliser la formule =INDIRECT(SUBSTITUT(D3,” “,”_”)), au lieu de la fonction INDIRECT simple illustrée ci-dessus. La raison en est qu'Excel n'autorise pas les espaces dans les plages nommées. Ainsi, lorsque vous créez une plage nommée utilisant plusieurs mots, Excel insère automatiquement un trait de soulignement entre les mots. Ainsi, la gamme nommée « Fruits de saison » serait « Fruits_de_saison ». L'utilisation de la fonction SUBSTITUT dans la fonction INDIRECT garantit que les espaces sommes convertis en traits de soulignement.