Vous pouvez créer une liste déroulante Excel pour faciliter la saisie des données, éviter les erreurs, ou encore pour limiter cette saisie à une liste de valeurs prédéfinie.
Par exemple, pour retreindre le contenu des cellules aux valeurs “Produit A; Produit B; Produit C…”, ou bien “Nom 1; Nom 2; Nom 3…”, ou encore “Service A; Service B; Service C…”, etc.
Dans ce qui suit, on va voir qu’on peut restreindre vraiment la saisie à des valeurs particulières, ou bien juste faciliter cette saisie avec une liste déroulante, tout en laissant le choix à l’utilisateur de saisir une valeur autre que celles prédéfinies.
Sommaire
1- Créer une liste déroulante dans Excel
2- Afficher une infobulle sur une liste déroulante
3- Empêcher la saisie d’une valeur non valide dans la liste déroulante
4- Modifier les paramètres d’une liste déroulante ou la supprimer
5- Mettre à jour les éléments d’une liste déroulante
1- Créer une liste déroulante dans Excel
- Sélectionnez les cellules dans lesquelles vous voulez placer une liste déroulante.
- Allez dans l’onglet «Données».
- Dans le groupe «Outils de données», cliquez sur «Validation des données».
- La fenêtre «Validation des données» s’ouvre.
- Dans son onglet «Options», déroulez la liste «Autoriser» et choisissez comme critère de validation «Liste».
- Dans la zone «Source», saisissez les éléments que vous voulez faire afficher dans votre liste déroulante.
Il y a deux manières de faire suivant les situations:
- Si votre liste sera petite (quelques éléments seulement), optez pour la saisie directe dans la zone «Source» :
Donc, dans la zone «Source», saisissez les éléments que vous voulez afficher dans la liste, séparés par des point-virgules(;). Par exemple : Produit A; Produit B; Produit C
Voir l’image ci-dessous :
Notez que l’espace après le point-virgule n’est pas obligatoire, mais ça rend le contenu de votre source plus lisible et donc facile à consulter ou à modifier ultérieurement.
Cliquez sur Ok pour appliquer votre liste déroulante.
Vous vous retrouvez avec le résultat suivant (Une flèche apparaît à droite de la cellule lorsque sélectionnée, et vous permettra de dérouler la liste) :
- Si votre liste est longue (beaucoup d’éléments), optez plutôt pour l’utilisation d’une «source externe» :
Notez que cette source externe sera une plage de cellules de la même feuille ou mieux d’une autre feuille du même classeur (la référence à une feuille d’un autre classeur n’est pas permise).
Si la liste d’éléments n’est pas encore existante, préparez-la (le mieux, dans une autre feuille du classeur).
⇒ Référence à la plage de cellules source future de la liste déroulante :
Pour faire référence à votre plage de cellules, vous avez 2 possibilités :
♦♦ Soit, cliquer sur la flèche en regard de la zone «Source» de la fenêtre «Validation des données» (comme illustré ci-dessous), allez sélectionner ladite plage puis re-cliquer sur la flèche pour revenir vers la fenêtre «Validation des données» :
Notez que cette méthode ne permet de faire référence qu’à une plage de cellules sur la même feuille.
♦♦ Soit, saisir la référence ou le nom de la plage de cellules dans la zone «Source». Voir les exemples illustrés ci-dessous :
– Exemple 1 (en indiquant la référence d’une plage de cellules) :
On a un tableau dans la feuille 1 dans lequel on veut mettre des listes déroulantes au niveau de la colonne B, colonne Produit (Voir volet gauche de l’image ci-dessous), et notre source de données est une plage de cellules A2:A21 de la feuille 2 (Voir volet droit de l’image ci-dessous).
Sélectionnez les cellules dans lesquelles vous voulez placer une liste déroulante (Dans notre exemple, les cellules B2 à B14 de la feuille 1) → Onglet «Données» → Dans le groupe «Outils de données», cliquez sur «Validation des données». La fenêtre «Validation des données» s’ouvre. Dans son onglet «Options», déroulez la liste «Autoriser» et choisissez comme critère de validation «Liste».
Dans la zone «Source», on va saisir la formule suivante :=Feuil2!A2:A21
Détaillons notre formule :
– Le signe “=” indique à Excel que c’est une formule à calculer et non juste un texte à afficher.
– Feuil2! fait référence à la feuille 2 (n’oubliez pas le signe “!” après le nom de la feuille).
– A2:A21 fait référence à la plage de cellules A2 jusqu’à A21.
Dans cet exemple, on a utilisé le nom par défaut donné par Excel à la feuille 2. Si vous avez renommé la feuille que vous voulez utiliser comme source, saisissez le nouveau nom dans votre formule comme suit :=NouveauNom!A2:A21 (en prenant toujours l’exemple de la plage de cellules A2:A21).
– Notez que si vous modifiez le nom de la feuille source après avoir appliqué la liste déroulante, celle-ci sera mise à jour automatiquement. Heureusement d’ailleurs.
– Aussi, si le nom de la feuille Excel source est composé de plusieurs mots (séparés par des espaces), vous devez utilisez des guillemets simples:
- Par exemple, si votre feuille source s’appelle “Liste des Produits”, la formule sera :
='Liste des Produits'!A2:A21 - Alors que si la feuille se nomme “ListedesProduits” (considéré comme un seul mot), la formule deviendra :
=ListedesProduits!A2:A21
– Exemple 2 (en indiquant le nom d’une plage de cellules) :
Pour ce faire, il faudrait d’abord nommer la plage de données sources : Sélectionnez la plage de données sources → Entrez un nom pour cette plage dans la “Zone Nom” (comme illustré sur le volet droit de l’image ci-dessous) → Appuyez sur Entrée.
Notez que le nom d’une plage de cellules ne doit pas contenir d’espaces. Si vous voulez utilisez un nom composé, mettez-y des tirets “-” ou soulignés “_”. Par exemple, dans notre cas illustré ci-dessous, on a utilisé le nom “Liste_Produits”.
Pour la liste déroulante, on n’aura donc qu’à saisir dans la zone “Source” la formule :=Nom_de_la_plage_de_cellules (Dans notre exemple, la formule est : =Liste_Produits).
Comme vous le constatez, l’utilisation du nom de la plage de cellules comme source de la liste déroulante est plus simple que les autres méthodes, mais vous utiliserez bien sûr celle qui vous convient le mieux.
2- Afficher une infobulle sur une liste déroulante
Vous pouvez afficher un message ou infobulle lorsque votre liste déroulante est cliquée; Ça peut être une directive à l’utilisateur, ou juste une information sur le contenu de la cellule.
Bien sûr, c’est tout à fait optionnel.
- Sélectionnez donc votre cellule contenant la liste déroulante.
- Allez dans l’onglet «Données».
- Dans le groupe «Outils de données», cliquez sur «Validation des données».
- La fenêtre «Validation des données» s’ouvre.
- Dans son onglet «Message de saisie», saisissez un titre et un message dans leurs zones dédiées. Le titre est optionnel.
3- Empêcher la saisie d’une valeur non valide dans la liste déroulante
Vous pouvez soit empêcher catégoriquement l’utilisateur de saisir une valeur non présente dans la liste déroulante, soit juste lui afficher un message d’erreur pour l’alerter tout en lui laissant le choix de valider la nouvelle valeur:
- Sélectionnez la cellule contenant la liste déroulante.
- Allez dans l’onglet «Données».
- Dans le groupe «Outils de données», cliquez sur «Validation des données».
- La fenêtre «Validation des données» s’ouvre.
- Dans son onglet «Alerte d’erreur», choisissez un style et saisissez un titre et un message d’erreur dans leurs zones dédiées.
Le titre et le message d’erreur sont optionnels; Si vous ne définissez pas un titre et un message vous-même, un message par défaut sera affiché par Excel selon le style choisi.
Il y a 3 styles de messages d’erreurs (chacun avec un message et une icône particuliers, et un résultat différent):
- Arrêt : Message bloquant, qui empêche la saisie de valeurs non valides dans la cellule contenant la liste déroulante.
- Avertissement : Message à titre d’information, et ne bloque donc pas une saisie non valide dans la cellule contenant la liste déroulante.
- Informations : Message à titre d’information, et ne bloque donc pas une saisie non valide dans la cellule contenant la liste déroulante.
4- Modifier les paramètres d’une liste déroulante ou la supprimer
- Pour modifier les paramètres d’une liste déroulante dans Excel :
- Sélectionnez la ou les cellule(s) dans lesquelles vous voulez modifier les paramètres de la liste déroulante.
- Allez dans l’onglet «Données».
- Dans le groupe «Outils de données», cliquez sur «Validation des données».
- La fenêtre «Validation des données» s’ouvre.
- Selon votre objectif, cliquez sur l’un des onglets «Options», «Message de saisie» ou «Alerte d’erreur» et modifiez le paramètre voulu.
- Pour supprimer une liste déroulante dans Excel :
- Sélectionnez la ou les cellule(s) dans lesquelles vous voulez supprimer la liste déroulante.
- Allez dans l’onglet «Données».
- Dans le groupe «Outils de données», cliquez sur «Validation des données».
- La fenêtre «Validation des données» s’ouvre.
- Cliquez tout en bas sur le bouton «Effacer tout».
- Validez par Ok.
5- Mettre à jour les éléments d’une liste déroulante
Après la création d’une liste déroulante dans Excel, on pourra toujours modifier, ajouter ou supprimer un ou plusieurs de ses éléments. Cette mise à jour des éléments de la liste déroulante dans Excel dépendra de la méthode adoptée pour la définition de sa source lors de sa création.
Pour savoir comment a été définie la source de la liste déroulante, on regarde dans la zone «Source» de la fenêtre «Validation des données» (Sélectionnez la ou les cellule(s) concernée(s) → Onglet «Données» → Dans le groupe «Outils de données», cliquez sur «Validation des données» → La fenêtre «Validation des données» s’ouvre).
- Si une série d’éléments séparés par point-virgule y est inscrite, juste appliquez directement vos modifications dans cette zone.
- Si la source de la liste déroulante est une référence à une plage de cellules, comme dans l’exemple 1 vu tout en haut, modifiez tout d’abord cette plage puis changez en cas de besoin la référence dans la zone «Source». Pourquoi je dis «en cas de besoin», car la modification ou la suppression d’une ou plusieurs valeurs, ainsi que l’ajout à l’intérieur de la plage se répercutera automatiquement dans la liste déroulante. Si par contre, vous avez ajouté une ou plusieurs valeurs à la fin de la plage de cellules sources, vous devez intégrer cet ajout à la référence dans la zone «Source» de la fenêtre «Validation des données».
Reprenons l’exemple 1 vu tout en haut : Si vous avez ajouté par exemple 2 valeurs en fin de la plage de données sources situées dans la feuille 2, changez juste la formule=Feuil2!A2:A21en=Feuil2!A2:A23.
- Si la source de la liste déroulante est un nom de plage de cellules, comme dans l’exemple 2 vu tout en haut, faîtes les modifications voulues dans ladite plage, genre modification, ajout ou suppression de valeurs; puis, en cas de besoin, redéfinissez-là en utilisant le “Gestionnaire de noms“, comme expliqué ci-après.
Là aussi, en cas de modification ou suppression d’une ou plusieurs valeurs, ou en cas d’ajout à l’intérieur de la plage, la liste déroulante équivalente sera mise à jour automatiquement.
Si par contre, vous avez ajouté une ou plusieurs valeurs à la fin de la plage de cellules sources, vous devez la redéfinir comme suit :
- Sélectionnez la nouvelle plage de cellules (plage source).
- Allez dans l’onglet «Formules»..
- Dans le groupe de commandes «Noms définis», cliquez sur «Gestionnaire de noms».
- Sélectionnez dans la fenêtre qui s’affiche, le nom de la plage de cellules concernée.
- Modifiez en bas la référence en incluant les cellules contenant vos nouvelles valeurs.
Reprenons l’exemple 2 vu tout en haut : Si vous avez ajouté par exemple 2 valeurs en fin de la plage de données sources situées dans la feuille 2, changez dans le gestionnaire de noms, la formule de référence ='Liste des Produits'!A2:A20 en ='Liste des Produits'!A2:A22.
Voilà; c’est tout pour ce tutoriel sur les listes déroulantes dans Excel.
Fabrice Lambert est un spécialiste de l’informatique avec plus de 18 ans d’expérience dans le domaine.
Il a une connaissance approfondie des diverses applications Office, que ce soit Word, Excel, ou autres, et a animé, depuis 2005, plusieurs ateliers et formations dans le domaine informatique, notamment sur l’utilisation du système Windows et des applications Office.
Bonjour
Je connaissais cette technique, mais je trouve cet article très bien rédigé, ce qui n’est pas toujours le cas dans d’autres présentation de ce sujet
Cordialement
Yves GERAULT
Merci Yves pour votre appréciation.
Très bien expliqué
c’est agréable à suivre et facile à comprendre
merci
Merci Brinon.
Ravi que vous ayez trouvé utile ce petit tutoriel.
Très bien expliqué . J’ai apprécié. Bravo.
Merci Hachani pour votre appréciation.
Bonjour,
Effectivement, clair, simple à comprendre…
Pourriez-vous m’aider et me dire comment je peux additionner des chiffres de plusieurs listes déroulantes sans comptabiliser la valeur de la cellule de référence c’est à dire la cellule où est située la liste déroulante ? J’espère être claire…
Merci beaucoup
Bonjour Florence.
J’ai pas très bien compris la question ? Qu’est ce que vous voulez dire par “sans comptabiliser la valeur de la cellule de référence” ?
Est ce que vous pouvez détailler avec un exemple si possible ?
Bonjour
Merci pour votre tutoriel
J’aimerais savoir s’il est possible de faire un choix alphabétique, ma liste est longue, exemple pour trouver toto, taper le T et la liste apparaitrait à la première ligne commençant par un T Cordialement
Bonjour Goupil.
Oui, c’est tout à fait possible, mais ça sera un peu difficile de l’expliquer ici dans un commentaire!
En tous cas, c’est ce qu’on appelle une liste déroulante avec saisie semi-automatique.
Pour la mettre en oeuvre, vous devriez utiliser les fonctions “DECALER” et “SI”.
Mais, si jamais vous éprouvez des difficultés à le faire, je pourrai peut être faire un petit tuto sur le sujet prochainement.
Bonjour,
Très intéressant comme article.
Petite précision : Comment mettre PLUSIEURS choix dans une cellule à partir du menu déroulant?
C’est à dire, ma liste contient par exemple “produit A” “Produit B” “Produit C ” etc ..
Comment faire pour choisir “Produit A” et Produit B” ou “Produit A” “Produit C” etc….
Merci d’avance de votre réponse !!!
Salut Tremblé.
En fait, Excel ne propose pas directement les listes déroulantes à sélection multiple.
Donc, vous devriez insérer un contrôle liste déroulante ou zone de liste à partir de l’onglet “Développeur” et surtout utiliser du code VBA pour gérer tout ça.
Bonjour,
Merci bien, super.
Fabra AMURI
Bonjour Fabra.
Bienvenue sur le site, et Merci pour votre commentaire.
Bonjour,
Super le tutoriel, c’est très clair. J’ai une question:
Je voudrais fair un tableau, avec plusieurs colonnes comportant les mêmes options de réponses (par ex: colonne A/viande, colonne B/légumes. Réponses: tous le jours/ deux fois par semaine / jamais).
Je saisis d’abord mes options de réponses sur la feuille 2. Faut-il créer une colonne, dans cette feuille 2, pour chaque colonne de réponse (A et B), ou puis-je utiliser la même plage pour la colonne A et la colonne B? C’est à dire que je nomme la même à chaque fois dans la source =fréquenceplats. Ou bien faut-il créer une plage à chaque fois, =fréquenceviande, =fréquencelégumes…
J’espère que ma question est assez claire…
Merci d’avance!
Bonjour KHazp.
Oui, votre question est assez claire.
Pour la plage de données source, il suffit d’utiliser une seule, la même pour vos 2 colonnes puisque les éléments de la liste déroulante résultat seront les mêmes.
Donc, pour votre exemple, sélectionnez la totalité des cellules cibles des 2 colonnes, et affectez-leur la même source de liste déroulante “=fréquenceplats”.
Bonjour
Comme Tremblé je serai intéressé par le choix multiple et l’insertion dans une cellule. Comment utilise t on du code VBA pour cela ?
Bonjour Simipi.
Plus simple que la solution proposée plus haut, et si les valeurs de la liste déroulante sont alphabétiques ou alphanumériques (et pas numériques), insérer ce code dans la feuille concernée (Remplacer le nom de la colonne “C” par celui de votre colonne contenant la liste déroulante pour choix multiple):
Exemple de code à adapter:
Private Sub Worksheet_Change(ByVal Target As Range)Dim ValeurAjoutee
Dim P As Integer
If Not Intersect(Columns("C"), Target) Is Nothing And Target.Count = 1 And Target.Row > 1 Then
Application.EnableEvents = False
ValeurAjoutee = Target
Application.Undo
P = InStr(Target, ValeurAjoutee)
If P > 0 Then
Target = Left(Target, P - 1) & Mid(Target, P + Len(ValeurAjoutee) + 1)
If Right(Target, 1) = "," Then
Target = Left(Target, Len(Target) - 1)
End If
Else
If Target = "" Then
Target = ValeurAjoutee
Else
Target = Target & ";" & " " & ValeurAjoutee
End If
End If
Application.EnableEvents = True
End If
End Sub
Code à télécharger ici: officepourtous.com/downloads/Code-liste-deroulante-selection-multiple.txt
Génial, j’adore votre site
Merci Lamine pour votre appréciation.
Bonjour,
Je bloque sur un documents que je viens de créer.
Il faudrait que je mette en relation plusieurs feuilles avec un menu déroulant dans la première.
Par exemple, des numéros de factures avec comme onglet chaque mois.
Cela est-il possible ? Si oui, comment procéder ?
Merci d’avance
Bonjour Koska.
Donnez-moi, s’il vous plaît, plus de détails sur ce que vous voulez faire?
Je dois créer un menu déroulant en feuille 1 en A5 par exemple, en incluant les données de mes tableaux se trouvant en feuille 2 et feuille 3.
Cela ne fonctionne qu’avec la feuille 2.
Oui Bélinda. Si j’ai bien compris ta question (ce dont je suis pas très sûr), voici une solution de contournement:
– Crée une nouvelle feuille qui s’appellera par exemple ‘Liste’, qui contiendra dans une même colonne toutes les données des 2 feuilles (Feuil2 et Feuil3 dans notre exemple).
Les différentes cellules de cette colonne auront comme contenu (Exemple pour 6 cellules):
=Feuil2!A1
=Feuil2!A2
=Feuil2!A3
=Feuil3!A1
=Feuil3!A2
=Feuil3!A3
– Puis, dans la feuille 1, la source de ta liste déroulante sera: =Liste!A1:A6
Bonjour,
J’ai une première liste déroulante et une seconde restreinte (les choix de la 2nde liste dépendent du choix effectué dans la première). De fait, la seconde liste déroulante ne propose parfois qu’un seul choix. Est-il alors possible d’afficher directement ce choix dans la cellule au lieu de devoir cliquer sur la cellule puis dans la liste déroulante pour qu’il s’affiche?
Si possible sans passer par une macro (le fichier doit être distribué sur différents postes n’ayant pas tous la même version d’Excel).
D’avance merci
Désolé Jorge, mais je ne vois pas comment faire.
Je vous remercie pour ce guide très bien fait, simple et efficace accessible à tous. Un grand MERCI !
Je voulais savoir si je peux attribuer un code couleur aux éléments de ma liste déroulante ? exemple: Produit A (couleur de remplissage des colonnes Produit A en Vert) ; Produit B en Jaune …etc
En vous souhaitant bcp de succès
Bonjour, Bougherfa.
Si vous voulez qu’une couleur soit appliquée en fonction de l’élément sélectionné de la liste déroulante, il suffit d’appliquer une mise en forme conditionnelle aux cellules contenant la liste déroulante.
Voir l’article Excel – Mise en forme conditionnelle.