Liste déroulante à choix multiple dans Excel
Une macro gratuite à coller dans ta feuille pour garder plusieurs choix dans la même cellule, puis de quoi compter, filtrer et colorer ces choix.
Si tu veux choisir plusieurs valeurs dans une liste déroulante, Excel n’en garde qu’une par cellule. Dans Microsoft 365, des cases à cocher réunies par JOINDRE.TEXTE donnent un résultat proche. Pour une vraie liste à choix multiple, copie-colle notre macro VBA, qui ajoute chaque choix aux précédents et retire celui que tu choisis une seconde fois.
Mettre plusieurs choix dans une même cellule avec une liste déroulante
Une liste déroulante ordinaire garde plusieurs choix dans une même cellule dès que la macro est installée dans sa feuille. Dans le tableau de l’exemple, c’est la colonne Équipes qui porte cette liste. Tu y choisis Achats, puis Logistique, et la cellule affiche « Achats, Logistique » au lieu de ne garder que le dernier choix. Si tu choisis encore Achats, la macro le retire, et il ne reste plus que « Logistique » dans la cellule.
Chaque valeur choisie s’ajoute aux précédentes, dans la même cellule.
Une valeur choisie une seconde fois est retirée, et les autres gardent leur ordre.
La touche Suppr vide la cellule, comme d’habitude.
Seules les cellules de la plage réglée qui portent une liste déroulante sont concernées.
| A | B | |
|---|---|---|
| 1 | Tâche | Équipes |
| 2 | Relancer le fournisseur | Achats, Logistique |
| 3 | Préparer l'inventaire | Achats |
Créer la liste déroulante de choix avec la validation des données
Tout commence par une liste déroulante ordinaire, que tu crées avec la validation des données, puisque la macro s’appuie sur elle sans jamais la créer à ta place. Si ta colonne en porte déjà une, passe directement à l’installation de la macro, un peu plus bas. Sinon, range d’abord tes options dans un tableau, sur un onglet à part, puis laisse la liste y prendre ses valeurs par une formule INDIRECT. Chaque option que tu ajoutes au tableau entre alors dans la liste, sans que tu rouvres la validation des données.
- 01
Sur un onglet à part, par exemple un onglet Listes, écris le titre Option, puis tes options en dessous, une par ligne.
- 02
Clique dans cette colonne, appuie sur Ctrl+L, vérifie que la case « Mon tableau comporte des en-têtes » est cochée, puis valide par OK.
- 03
Nomme ensuite le tableau Options dans la case Nom du tableau de l’onglet Création de tableau.
- 04
Sélectionne les cellules qui recevront les choix, par exemple B2:B200, puis clique sur le bouton Validation des données de l’onglet Données.
- 05
Dans la zone Autoriser, choisis Liste, puis tape la formule suivante dans la zone Source et valide par OK.
=INDIRECT("Options[Option]")Désigne la colonne Option du tableau Options, qui s’allonge avec lui. Si ton tableau ou sa colonne portent un autre nom, remplace-les entre les guillemets, et garde les guillemets.
Tu pourrais aussi sélectionner les cellules du tableau dans Source, mais seule la formule INDIRECT propose à chaque fois l’option ajoutée, qu’elle arrive en bas ou au milieu du tableau. Pour quelques options qui ne bougeront pas, tu peux enfin les taper directement dans Source, séparées par des points-virgules (Achats;Compta;Logistique). Avec des virgules, un Excel réglé en français n’en ferait qu’une seule option. La fiche Liste déroulante du lexique détaille les autres réglages de la fenêtre, dont le message de saisie et l’alerte d’erreur.
Appliquer une liste déroulante à toute une colonne
Pour appliquer la liste déroulante à toute une colonne, sélectionne toute la plage avant d’ouvrir la validation des données, par exemple B2:B1000. En partant de la ligne 2, tu laisses le titre de la colonne sans liste.
Si ton suivi est lui-même un tableau créé par Ctrl+L, sélectionne plutôt les cellules de sa colonne, et chaque ligne ajoutée au tableau recevra la liste toute seule. Une liste déjà posée se recopie aussi avec la poignée de recopie. Dans les deux cas, pourtant, la macro ne cumule les choix que dans B2:B200. Elle ne suit ta nouvelle plage qu’une fois celle-ci écrite dans sa ligne PLAGE_LISTES, comme l’explique la partie sur la plage et le séparateur.
Installer la macro VBA de choix multiple dans la feuille
La macro de choix multiple s’installe dans le module de la feuille qui porte la liste, et non dans un module ordinaire. Ce module est en effet le seul endroit d’où Excel la lance à chaque choix que tu fais dans la liste.
Fais un clic droit sur l’onglet de la feuille, choisis Visualiser le code, colle le code dans la page blanche qui s’ouvre, puis enregistre le classeur au format « Classeur Excel (prenant en charge les macros) ».
Pour vérifier l’installation, reviens sur la feuille et choisis deux valeurs dans la même cellule. La liste continue d’afficher toutes les options, même celles que tu as déjà retenues, puisque c’est la cellule, et non la liste, qui cumule les choix.
Si la seconde valeur remplace la première, vérifie d’abord que les macros sont activées. À l’ouverture du classeur, clique sur le bouton Activer le contenu dans la barre jaune, comme l’explique l’article Activer les macros sur Excel sans casser la sécurité.
Modifier la plage et le séparateur de la liste à choix multiple
Pour modifier la plage et le séparateur, retourne dans le code de la feuille, où ils se règlent dans deux lignes en tête, marquées À MODIFIER. Tu n’y changes que la valeur écrite après le signe égal. La plage peut d’ailleurs se trouver sur une feuille protégée, puisque la macro y marche tant que les cellules de la liste ne sont pas verrouillées.
PLAGE_LISTESLes cellules où la macro cumule les choix, "B2:B200" dans le code. Une cellule de cette plage sans liste déroulante reste ignorée.SEPARATEURCe qui sépare deux choix dans la cellule, ", " dans le code, soit une virgule suivie d’une espace.
Imagine une liste posée en C2:C500, avec un point-virgule plutôt qu’une virgule entre deux choix. Il suffit alors de réécrire ces deux lignes du code, qui deviennent les suivantes.
Private Const PLAGE_LISTES As String = "C2:C500"Private Const SEPARATEUR As String = "; "
Étendre le choix multiple à plusieurs colonnes
Pour étendre le choix multiple à plusieurs colonnes, écris leurs plages à la suite dans PLAGE_LISTES, séparées par une virgule, à l’intérieur des mêmes guillemets. La macro cumule alors les choix dans chacune d’elles, ici dans les colonnes B et D.
Private Const PLAGE_LISTES As String = "B2:B200,D2:D200"
Pour une colonne entière, écris simplement "B:B", puisque la ligne de titre reste ignorée tant qu’elle ne porte pas de liste. Et si ton suivi est un tableau, donne plutôt le nom de sa colonne, comme "Tableau1[Équipes]", pour que la macro suive les lignes que tu ajoutes.
Afficher un choix par ligne dans la même cellule
Pour afficher chaque choix sur sa propre ligne dans la cellule, remplace le séparateur par vbLf, écrit sans guillemets. Excel active alors de lui-même le renvoi à la ligne de la cellule, et rechoisir une valeur la retire toujours, comme avec la virgule.
Private Const SEPARATEUR As String = vbLf
Les formules de la partie « Exploiter les cellules à choix multiples » s’appuient sur la virgule, si bien qu’il faut y remplacer leur séparateur (", " ou ",") par CAR(10). Celle qui compte les choix d’une cellule devient ainsi la suivante.
=SI(B2="";0;NBCAR(B2)-NBCAR(SUBSTITUE(B2;CAR(10);""))+1)Comment ajouter un choix à une liste déroulante Excel
Pour ajouter un choix à la liste déroulante, c’est-à-dire une nouvelle option, passe par le tableau de l’onglet Listes, où la liste prend ses valeurs. C’est là aussi qu’une option se renomme, alors qu’un choix déjà posé dans une cellule se retire par la liste elle-même, comme il s’y est ajouté.
Pour retirer un choix d’une cellule, choisis-le de nouveau dans la liste. Les autres restent dans leur ordre.
Pour vider la cellule, appuie sur Suppr.
Pour ajouter une option à la liste, écris-la juste sous le tableau de l’onglet Listes. Le tableau s’agrandit, et la liste déroulante la propose aussitôt si sa source est la formule INDIRECT vue plus haut.
Pour renommer une option, écris d’abord le nouveau nom sous le tableau, sans effacer l’ancien. Dans chaque cellule concernée, choisis l’ancien nom pour le retirer, puis le nouveau. Supprime enfin la ligne de l’ancien nom dans le tableau.
Rechercher et remplacer (Ctrl+H) semble plus rapide pour renommer une option dans la colonne des choix, mais évite-le tant que la macro est installée. Le remplacement la déclenche en effet une fois par cellule modifiée, et comme elle annule le dernier geste pour relire l’ancien contenu, elle risque de mélanger l’ancien et le nouveau nom.
Si ta liste ne s’appuie pas encore sur un tableau, la fiche Tableau Excel montre comment en créer un. La partie sur la création de la liste, plus haut, explique ensuite comment en faire la source de la liste avec la formule INDIRECT.
Exploiter les cellules à choix multiples
Pour exploiter une cellule à choix multiples, garde en tête qu’elle reste un texte, même quand elle affiche « Achats, Logistique ». Les formules et les outils qui s’en servent y cherchent donc un choix, ou bien la découpent sur le séparateur. Les exemples suivants partent de choix posés en B2:B200 avec le séparateur de la macro, une virgule suivie d’une espace. Avec un autre séparateur, comme "; ", remplace-le de la même façon dans les formules.
Compter les lignes qui contiennent un choix avec NB.SI
Pour savoir combien de tâches concernent l’équipe Achats, NB.SI compte les lignes où ce choix apparaît, grâce à deux astérisques qui acceptent n’importe quel texte avant et après le mot.
=NB.SI(B2:B200;"*Achats*")Compte les cellules où Achats apparaît, seul ou avec d’autres choix.
Ces astérisques ont pourtant leur limite, car ils trouvent Achats à l’intérieur d’une autre option, comme « Achats groupés », ou même dans « Rachats », puisque NB.SI ne distingue pas les majuscules des minuscules. Quand deux options se ressemblent ainsi, la formule suivante entoure chaque cellule et le choix cherché du séparateur, si bien que seul le choix entier est compté.
=SOMMEPROD(--ESTNUM(CHERCHE(", Achats, ";", "&B2:B200&", ")))Compte les cellules qui contiennent exactement le choix Achats.
Connaître le nombre de choix d’une cellule
Pour savoir combien d’équipes se partagent une même tâche, la formule suivante compte les virgules de la cellule, puis ajoute un.
=SI(B2="";0;NBCAR(B2)-NBCAR(SUBSTITUE(B2;",";""))+1)Rend 2 pour « Achats, Logistique » et 0 pour une cellule vide.
Elle se trompe donc dès qu’une option contient elle-même une virgule. Dans Microsoft 365, FRACTIONNER.TEXTE donne le même résultat en découpant la cellule sur le séparateur, mais le test de la cellule vide y reste nécessaire, sans quoi une cellule vide compterait pour un choix.
=SI(B2="";0;NBVAL(FRACTIONNER.TEXTE(B2;", ")))Filtrer le tableau sur l’un des choix
Pour filtrer le tableau sur l’un des choix, le filtre textuel Contient garde les lignes dont la cellule contient le mot tapé, où qu’il se trouve dans le texte. Toutes les lignes où Achats fait partie des choix restent ainsi affichées, qu’il y soit seul ou au milieu d’autres choix.
- 01
Si les flèches de filtre ne sont pas déjà dans les titres, clique dans ton tableau, puis sur le bouton Filtrer de l’onglet Données.
- 02
Ouvre la flèche de la colonne des choix, choisis Filtres textuels, puis clique sur Contient.
- 03
Tape Achats et valide.
Comme les astérisques de NB.SI, ce filtre garde pourtant aussi les lignes où figure « Achats groupés ». Si tu veux une liste filtrée qui se met à jour seule, FILTRE recopie ailleurs les lignes concernées, ici les colonnes A et B, avec le même défaut.
=FILTRE(A2:B200;ESTNUM(CHERCHE("Achats";B2:B200));"")Le dernier argument évite l’erreur #CALC! quand aucune ligne ne contient le choix. FILTRE n’existe pas dans les versions anciennes d’Excel, comme 2016 ou 2019.
Mettre en couleur une cellule ou une ligne selon le choix
Une mise en forme conditionnelle met en couleur la cellule où un choix apparaît, avec la règle Texte qui contient, ou toute sa ligne, avec une règle écrite en formule. Les deux règles cherchent le mot dans le texte de la cellule des choix, et y trouvent Achats seul comme au milieu d’autres choix.
- 01
Sélectionne la colonne des choix, par exemple B2:B200.
- 02
Dans l’onglet Accueil, clique sur le bouton Mise en forme conditionnelle, choisis Règles de mise en surbrillance des cellules, puis Texte qui contient.
- 03
Tape Achats, choisis une couleur et valide.
Pour colorer toute la ligne de la tâche, sélectionne les lignes du tableau à partir de la ligne 2, par exemple A2:F200. Clique ensuite sur le bouton Mise en forme conditionnelle, puis sur Nouvelle règle, choisis Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué, et écris la formule suivante.
=ESTNUM(CHERCHE("Achats";$B2))Choisis enfin une couleur avec le bouton Format, puis valide la règle. Le dollar devant B garde la colonne des choix pour toutes les cellules de la ligne, qui se colorent donc ensemble.
Pour une couleur par équipe, pose une règle par option, en sachant qu’une cellule qui porte deux choix prend la couleur de la règle placée le plus haut dans la fenêtre Gérer les règles. La fiche Mise en forme conditionnelle montre comment changer cet ordre.
Séparer les choix multiples en colonnes
Pour séparer les choix d’une cellule en colonnes, FRACTIONNER.TEXTE la découpe sur son séparateur en une seule formule.
=FRACTIONNER.TEXTE(B2;", ")Dans Microsoft 365, place chaque choix de B2 dans sa propre colonne, à partir de la cellule de la formule.
Et si tu préfères ranger tous les choix de la colonne l’un sous l’autre, un par ligne, réunis-les d’abord avec JOINDRE.TEXTE, puis découpe le tout.
=FRACTIONNER.TEXTE(JOINDRE.TEXTE(", ";VRAI;B2:B200);;", ")Sans ces fonctions, l’outil Convertir de l’onglet Données fait le découpage une fois pour toutes. Choisis Délimité, puis, à l’étape suivante, coche seulement la case Virgule, et chaque choix après le premier gardera l’espace qui suivait la virgule. Garde-toi de cocher aussi la case Espace pour effacer cette espace, car elle couperait en deux une option comme « Achats groupés ».
Faire une liste déroulante à choix multiple sans VBA
Sans VBA, une liste déroulante garde toujours une seule valeur, puisque la validation des données d’Excel n’a aucune option de choix multiple. Quand une macro n’est pas possible, les choix vivent donc dans plusieurs cellules, qu’une formule réunit ensuite dans une seule.
Réunir des cases à cocher avec JOINDRE.TEXTE
Avec des cases à cocher, chaque option reçoit sa propre colonne, et une formule réunit dans une dernière colonne les titres des options cochées sur la ligne. Ces cases posées dans la cellule demandent une version à jour d’Excel pour Microsoft 365, comme l’explique la fiche Case à cocher.
- 01
Écris une option par colonne, en titre, par exemple Achats en C1, Compta en D1, Logistique en E1 et Ventes en F1.
- 02
Sélectionne les cellules sous ces titres, par exemple C2:F200, puis clique sur le bouton Case à cocher de l’onglet Insertion.
La case à cocher elle-même, posée dans la cellule. Ici, une case par tâche, alors que la méthode en pose une par option. - 03
En G2, écris la formule suivante, puis recopie-la vers le bas.
=JOINDRE.TEXTE(", ";VRAI;FILTRE($C$1:$F$1;C2:F2;""))Affiche « Achats, Logistique » quand ces deux cases sont cochées, et rien quand aucune ne l’est.
Comme une case cochée vaut VRAI et une case décochée FAUX, FILTRE s’en sert directement pour choisir les titres à garder. Sans son dernier argument, une ligne où aucune case n’est cochée afficherait #CALC!.
Taper un x sous chaque option retenue
Si ta version d’Excel n’a pas encore les cases à cocher, le même tableau marche avec un x tapé sous chaque option retenue. Seule change alors la condition de FILTRE, qui teste si chaque cellule de la ligne vaut x, là où les cases valaient déjà VRAI ou FAUX.
=JOINDRE.TEXTE(", ";VRAI;FILTRE($C$1:$F$1;C2:F2="x";""))Les deux méthodes partagent pourtant le même défaut, celui d’une colonne par option. Chaque option ajoutée demande ainsi une colonne de plus au tableau et une formule élargie.
Choix multiple dans Excel pour le web et Google Sheets
Dans Excel pour le web, le choix multiple par macro ne marche pas, puisque l’aide de Microsoft indique qu’on ne peut y créer, y exécuter ni y modifier une macro VBA. Un classeur qui en contient s’y ouvre bien, mais la macro n’y réagit pas, et la liste y garde un seul choix.
Google Sheets, de son côté, écrit ses macros en Apps Script. Google propose bien un outil qui convertit le code VBA d’un fichier Excel en Apps Script, mais il est réservé aux offres Workspace Enterprise Plus et Education Plus. Pour choisir plusieurs valeurs sans macro, l’aide de Google décrit une option Autoriser les sélections multiples dans les réglages d’un menu déroulant, réservée aux menus déroulants « au format de chip ». La même aide précise que, sur mobile, on ne peut pas choisir plusieurs valeurs, même quand la sélection multiple est activée. Cette page s’en tient à ce que décrit l’aide de Google, sans couvrir davantage Google Sheets.
Pourquoi ce code VBA est plus long que ceux des forums
Ce code VBA est plus long que ceux des forums d’abord parce qu’il évite deux erreurs que font beaucoup de codes qui circulent. Ces codes cherchent le choix dans la cellule avec la fonction InStr, puis le retirent avec Replace, alors qu’InStr trouve un texte à l’intérieur d’un autre. Une cellule qui porte « Achats groupés » refuse alors le choix Achats, qu’ils prennent pour un doublon. Replace, de son côté, abîme une autre option, puisque choisir Achats dans une cellule qui porte « Compta, Achats groupés » y laisse « Compta groupés ».
Notre code, lui, découpe d’abord la cellule sur le séparateur, puis compare chaque choix en entier. Il ne touche par ailleurs qu’aux cellules qui portent une liste, laisse vide une cellule vidée par Suppr, et garde son type à une date ou à un nombre choisi seul. Il se méfie enfin des dates, qu’Excel relit à l’américaine quand une macro les écrit sous forme de texte, le 1er février devenant alors le 2 janvier.
Savoir ce que la macro de choix multiple ne fait pas
La macro ne vaut que pour la feuille où tu la colles. Pour une autre feuille, colle-la aussi dans le module de cette feuille.
Si le module de la feuille porte déjà une procédure Worksheet_Change ou Worksheet_SelectionChange, Excel refuse le code et affiche « Nom ambigu détecté ». Il faut alors réunir les deux procédures du même nom en une seule.
Ctrl+Z n’annule pas un choix fait avec la macro. Pour retirer une valeur, choisis-la de nouveau dans la liste.
La macro traite une cellule à la fois. Un collage ou une recopie sur plusieurs cellules remplace leur contenu, sans rien cumuler.
Une option qui contient le séparateur, une virgule suivie d’une espace par défaut, ne peut pas être retirée. La choisir de nouveau l’ajoute une seconde fois. Choisis alors un autre séparateur.
Une cellule qui réunit plusieurs choix ne respecte plus la validation des données. Avec l’alerte d’erreur par défaut, F2 puis Entrée sur cette cellule est refusé, et la commande Entourer les données non valides la signale.
Une date ou un nombre choisi seul garde son type. Dès que la cellule réunit plusieurs choix, elle devient un texte, que les calculs ne lisent plus comme une date ou un nombre. Une date restée seule après le retrait des autres choix reste le plus souvent en texte.
Le classeur doit être enregistré au format « Classeur Excel (prenant en charge les macros) », sinon le code disparaît à la fermeture.
Tu as peut-être encore une question
Crée d’abord une liste déroulante classique avec Données puis Validation des données, fais un clic droit sur l’onglet de la feuille, choisis Visualiser le code et colle la macro de cette page, qui ajoute chaque valeur choisie aux précédentes dans la même cellule et retire celle que tu choisis une seconde fois. Enregistre ensuite le classeur au format qui prend en charge les macros.
Pour remplir une liste déroulante, écris ses options en colonne sous le titre Option, dans un tableau nommé Options posé sur un onglet à part, puis tape =INDIRECT("Options[Option]") dans la zone Source de la validation des données, pour que la liste propose aussi chaque option que tu ajouteras au tableau. Pour quelques options qui ne bougeront pas, tu peux aussi les taper directement dans Source, séparées par des points-virgules, comme Achats;Compta;Logistique.
Pour supprimer un choix d’une cellule, choisis-le de nouveau dans la liste déroulante, et la macro le retire en laissant les autres choix dans leur ordre, alors que la touche Suppr vide la cellule entière et que Ctrl+Z, lui, n’annule pas un choix fait avec la macro. Une option qui contient le séparateur fait exception, puisque la choisir de nouveau l’ajoute une seconde fois au lieu de la retirer.
Non, la liste déroulante elle-même garde une seule valeur par cellule sans macro, mais Excel pour Microsoft 365 pose des cases à cocher dans les cellules, une colonne par option, et une formule JOINDRE.TEXTE réunit ensuite les options cochées de chaque ligne dans une seule cellule, comme « Achats, Logistique ». Ces cases demandent une version à jour d’Excel pour Microsoft 365, et la partie sur la liste à choix multiple sans VBA donne la formule.
Excel pour Mac devrait la faire tourner, puisqu’il exécute les macros VBA et qu’elle n’emploie rien de propre à Windows, alors qu’Excel pour le web ne permet pas d’exécuter une macro VBA et que Google Sheets écrit ses macros en Apps Script. Google propose, pour certaines offres Workspace, un outil qui convertit le code VBA en Apps Script, et son aide décrit une option Autoriser les sélections multiples dans ses menus déroulants, que cette page ne détaille pas.
Pour aller plus loin
Tout ce dont tu as besoin pour faire tourner tes macros et progresser sur Excel se trouve ici.
Télécharge notre cours Excel complet en PDF
Télécharger
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Entraîne-toi sur nos 63 exercices corrigés
S’entraîner
Les macros Excel et le VBA expliqués simplement
Lire le guide
Activer les macros quand Excel les bloque
Voir comment