Pour faciliter la saisie dans vos feuilles de calcul Excel, utilisez des listes déroulantes simples ou dynamiques qui s'adaptent à vos données. Vous éviterez les erreurs et vos données seront plus homogènes.

Vous voulez éviter de saisir tout le temps les mêmes données dans Excel ? Prévenir les fautes de frappe ? Uniformiser la saisie de libellés pour réaliser plus facilement des cumuls ? Il y a mille et une bonnes raisons d'utiliser des listes déroulantes pour saisir des données dans le tableur de Microsoft ! En deux clics dans la feuille de calcul, vous n'avez plus qu'à sélectionner la bonne valeur dans une liste.

Le principe de la liste déroulante est simple : il consiste à proposer des choix prêts à l'emploi dans un "menu déroulant", en préparant diverses options et en fixant quelques règles. C'est la solution la plus simple et la plus efficace, à la fois pour la saisie et pour la cohérence des données et des intitulés.

Excel fourmille ainsi de bonnes idées et d'aides à la saisie pour vous faciliter la vie. Voici plusieurs méthodes, très simples ou plus élaborées, pour afficher des listes déroulantes et valider les données saisies par vous-même ou par les utilisateurs de vos feuilles de calcul. Bonne nouvelle : ces techniques fonctionnent avec pratiquement toutes les versions d'Excel, même les plus anciennes, aussi bien sur Windows que sur Mac, parfois avec quelques petites nuances que nous précisons.

Vous disposez de la version la plus récente du tableur, comprise dans la version d'Office par abonnement mensuel/annuel, dite Microsoft 365 ? Tout est encore plus simple pour vous, nous vous indiquons une méthode spécifique à Excel 365. Mais tenez-vous-en aux autres méthodes proposées si le fichier Excel doit être ouvert par des personnes ne disposant pas forcément de la dernière version de l'application.

Autre précision importante : reportez-vous à cette autre fiche pratique si vous souhaitez créer des listes déroulantes en cascade dans Excel, appelées aussi listes déroulantes dépendantes ou listes déroulantes liées. Avec les listes déroulante en cascade, vous adaptez le contenu d'une liste déroulante au choix fait dans une première liste déroulante : par exemple, une première liste permet de sélectionner un pays, puis une deuxième liste présente uniquement des villes du pays choisi, puis une troisième liste affiche des sites touristiques de la ville sélectionnée.

Comment utiliser la liste de saisie semi-automatique d'Excel ?

Commençons par la méthode la plus simple, celle où Excel s'occupe de tout. Elle vous rend sans doute déjà service dans Windows et sur Mac ; il y a juste un raccourci clavier à connaître pour être sûr d'en profiter pleinement.

• Tapez quelques lettres dans une cellule d'une colonne comportant déjà des éléments : Excel vous suggère une ou des valeurs déjà saisies dans la même colonne. C'est la saisie semi-automatique.

• Si la liste ne s'affiche pas, tapez le raccourci clavier Alt+Flèche vers le bas (la flèche des quatre touches fléchées de déplacement). Cette combinaison de deux touches oblige Excel à afficher la liste pour vous permettre de sélectionner une valeur déjà saisie.

• Vous pouvez aussi cliquer avec le bouton droit de la souris sur la cellule pour choisir Liste déroulante de choix dans le menu contextuel, ce qui vous affichera la liste.

• Excel affiche une liste vide ou incomplète si une ou plusieurs cellules vides figurent au-dessus dans la colonne. Vous pouvez certes taper un espace dans ces cellules pour qu'elles ne soient plus vides, mais l'astuce peut avoir une incidence sur vos calculs, par exemple si vous utilisez la fonction NBVAL pour savoir combien votre colonne comporte d'éléments. À vous de voir…
• Cette liste déroulante ne fonctionne que pour du texte, pas pour des valeurs numériques ou des dates, et uniquement si les données sont en colonne. Excel supprime les doublons et trie les valeurs par ordre alphabétique avant de vous présenter la liste.
• Pour désactiver la saisie automatique dans Excel pour Windows, rendez-vous dans Fichier > Options > Options avancées (dans la colonne de gauche), et sous la section Options d'édition, décochez la case Saisie semi-automatique des valeurs de cellule.
• Pour paramétrer Excel pour Mac, rendez-vous dans menu Excel > Préférences > Saisie semi-automatique.

Comment créer une liste déroulante avec validation des données dans Excel ?

Grâce à la fonction de validation des données, Excel peut vérifier pour vous ce que saisit l'utilisateur dans une cellule, et n'autoriser; par exemple, que des nombres, ou seulement une date comprise entre deux bornes, ou un libellé  –de 15 caractères maximum – ou encore une valeur piochée dans une liste que vous allez lui préciser : c'est exactement ce qui nous intéresse ici.

Avec la validation des données, l'utilisateur garde le droit de taper une valeur dans la cellule plutôt que de la sélectionner dans votre liste déroulante. Mais il vous est possible de refuser toute valeur saisie non prévue, ou d'afficher une mise en garde avant d'accepter finalement la valeur tapée. Voici le principe pour créer une liste déroulante.

• Sélectionnez les cellules où l'utilisateur devra dérouler la liste pour saisir plus facilement une donnée (ici B3:B12).
• Dans le ruban d'Excel, cliquez sur l'onglet Données puis sur Validation des données.
• Dans la zone Autoriser, choisissez Liste.
• Laissez cochées les cases Ignorer si vide et Liste déroulante dans la cellule.

• Cliquez dans la zone Source puis sélectionnez dans votre feuille de calcul la plage de cellules contenant la liste à afficher (ici la plage de cellules A2:A9 de la feuille que nous avons appelée Paramètres).
• Pressez OK et testez votre liste déroulante en cliquant dans l'une des cellules qui doivent afficher cette liste.

• Dans la feuille de calcul, visuellement, rien n'indique qu'une liste déroulante est prévue pour la saisie. C'est uniquement lorsque vous sélectionnez la cellule qu'une petite icône apparaît à droite, sur laquelle il faut cliquer pour dérouler la liste.
• Pour dérouler la liste, vous pouvez aussi pressez la combinaison de deux touches Alt+Flèche vers le bas.
• Nous vous donnons plus loin quelques conseils pour préparer votre liste d'éléments à afficher.
• Quand vous sélectionnez la cellule ou la plage de cellules où devra apparaître votre liste déroulante, sélectionnez au besoin la colonne entière en cliquant sur sa lettre dans la grille, par exemple sur C pour sélectionner toute la colonne C (il sera facile d'en exclure ensuite certaines cellules seulement, par exemple C1 et C2, via le bouton Effacer tout de la boîte de dialogue de Validation des données).

La zone Source – qui indique où se trouve la liste d'éléments à afficher – accepte différents types d'information : du texte, une plage de cellules, une zone nommée, une formule… Nous allons découvrir toutes les subtilités de cette zone Source.

Une astuce dans Excel pour Windows : quand vous cliquez dans la zone Source et que vous voulez vous déplacer à l'intérieur pour modifier son contenu, pressez (une fois) la touche F2 avant d'utiliser les touches fléchées Gauche/Droite du clavier.

Comment créer une liste déroulante avec des valeurs fixes dans Excel ?

Cette méthode convient si la liste ne comporte qu'une poignée de valeurs, non susceptibles de changer.

• Après avoir choisi Données > Validation des données > Autoriser > Liste, dans la zone Source, tapez des valeurs séparées par des points-virgules (ou par des virgules si vous êtes dans une version anglaise d'Excel), sans espace entre les valeurs, par exemple les trois choix :Oui;Non;Ne sait pas
• Attention, avec ce type de Source, Excel fait la distinction entre majuscules et minuscules (on dit qu'il tient compte de "la casse"). Donc si l'utilisateur tape OUI dans la cellule au lieu de sélectionner Oui dans la liste déroulante, Excel renverra un message d'erreur (voir plus loin les Alertes d'erreur). Les autres méthodes expliquées ci-dessous ne tiennent pas compte de la casse.

Comment créer une liste déroulante avec Excel 365 ou Excel pour le Web ?

Cette méthode est intéressante si vous utilisez la version la plus récente d'Excel, celle comprise dans l'abonnement Microsoft 365 ou Excel pour le Web, qui est régulièrement mise à jour. C'est la solution la plus simple pour créer une liste déroulante débarrassée des doublons et éventuellement triée, et pour ajouter et supprimer des éléments à la liste.

Préférez les autres méthodes présentées dans cette fiche pratique si vos feuilles de calcul doivent être ouvertes ou retravaillées avec des versions antérieures à Excel 2021, ou avec des tableurs compatibles Excel conçus par d'autres éditeurs. Si vous possédez une version d'Excel 2007, 2010, 2013, 2016 ou 2019, tournez-vous en priorité vers la méthode utilisant les tableaux Excel.

Ici, vous allez non seulement profiter des tableaux d'Excel, mais aussi des nouvelles fonctions TRIER, UNIQUE et FILTRE (apparues avec Excel 2021) et de l'opérateur de plage renversée (signe #) d'Excel pour Microsoft 365.

Nous vous montrons ici comment créer une seule liste déroulante. Reportez-vous plutôt à cette autre fiche pratique si vous souhaitez créer plusieurs listes déroulantes en cascade.

Comment savoir si vous disposez d'Excel pour Windows 365 ?

Sur PC, une fenêtre verte "Excel pour Microsoft 365" apparaît brièvement au centre de l'écran quand vous ouvrez l'application, et une fois Excel lancé, l'info "Abonnement Microsoft 365" est visible via l'onglet Fichier > Compte.

Sur Mac, l'info "Abonnement Microsoft 365" est visible dans À propos de Microsoft Excel.

Créer une liste déroulante avec ou sans doublons dans Excel pour Microsoft 365

Ci-dessous, pour l'exemple, notre liste de départ contient des doublons, et les articles ne sont pas triés. Microsoft travaille sur la question des doublons... Au moment où vous lirez ces lignes, il se peut donc qu'Excel 365 se charge de supprimer automatiquement les doublons dans la Validation des données sous forme de Liste. Si une valeur apparaît plusieurs fois dans la liste de départ, vous n'aurez alors pas à vous en soucier pour vos listes déroulantes. Nous l'avons vérifié, c'est déjà le cas dans Excel Windows si vous avez choisi de faire partie du programme de bêta-test via Fichier > Compte > Office Insider. Sinon, vous utiliserez la nouvelle fonction UNIQUE d'Excel 365/2021. Voici comment faire dans les deux cas…

• Sélectionnez une cellule quelconque de vos données (ou alors la totalité de la plage, y compris l'en-tête appelé Articles ici en A4, donc A4:A20).

  

• Cliquez sur l'onglet Accueil > Mettre sous forme de tableau et sélectionnez un style.

  

• Confirmez l'étendue de la plage de données, et précisez que le tableau comporte des en-têtes si c'est le cas – ce qui est toujours préférable pour faciliter la compréhension des formules.

  

• Voilà votre tableau Excel créé, avec un style de ligne alterné…

  

• Quand une cellule d'un tel tableau est sélectionnée, un onglet vert Création de tableau (ou équivalent) s'affiche à droite du ruban : il vous permet notamment de modifier le style du tableau, de supprimer l'alternance de lignes colorées, les boutons de filtrage, etc.

  

•  Excel nomme toute la plage de cellules Tableau1 (ou Tableau2, Tableau3, etc.). Renommez le tableau si vous voulez rendre vos formules plus lisibles. (À savoir aussi : pour désactiver le mode tableau, cliquez sur Convertir en plage.)

  

• Utiliser des tableaux Excel n'a pas que des avantages cosmétiques. Ici, toute la plage de valeurs s'appelle Articles, du nom que vous avez inscrit en en-tête. Si vous ajoutez ou supprimez des données dans cette plage (en fin de liste ou ailleurs), Excel étend automatiquement la liste et applique le bon style à chaque ligne. Ci-dessous, nous ajoutons Accessoires en fin de plage : le tableau en tient compte et alterne la couleur de fond (le petit bouton déroulant en dessous sert à gérer les options).

  

• Dernier préparatif du contenu à afficher dans la liste déroulante : dans une cellule quelconque (mais il est important que les cellules en dessous soient vides), tapez le signe = et sélectionnez toutes les cellules du tableau, sauf la première contenant l'en-tête.
  Excel inscrit la formule :
  =Tableau1[Articles]
  puisque l'en-tête de colonne de notre Tableau1 s'appelle Articles.
  Cette formule reproduit donc le contenu de la colonne Articles du tableau. Seule la première cellule (ici C5) contient la formule, les autres cellules sont remplies automatiquement par Excel et encadrées d'un filet bleu. On y retrouve pour l'instant les doublons…

  

• Pour adapter cette formule (et toutes celles qui suivent) à vos propres besoins, il vous suffit de remplacer le nom Tableau1 par le nom de votre tableau, et Articles par le nom de votre en-tête de colonne. Si vous renommez plus tard le tableau et même cet en-tête Articles en Vêtements, Excel mettra automatiquement à jour vos formules.
• Dans une autre feuille de votre classeur Excel (ici nous restons dans la même feuille pour faciliter la démonstration), afin de créer votre liste déroulante, cliquez sur une cellule vide puis sur l'onglet Données > Validation des données > Validation des données.

  

• Commencez par indiquer que vous voulez une Liste.

  

• Cliquez juste en dessous dans la zone Source puis cliquez sur la cellule contenant la formule, C5 sur notre exemple. Excel inscrit la référence absolue de la cellule :
  =$C$5
  Mais ce n'est pas fini…
• Dans Excel pour Microsoft 365, c'est important : juste après cette référence =$C$5 ajoutez impérativement un signe # pour indiquer à Excel qu'il doit prendre en compte toute la plage de cellules renvoyées par la formule en C5, et pas seulement le contenu de C5. Ce signe # vous évite d'avoir à connaître l'étendue de la plage ($C$5:$C$21 dans l'exemple ci-dessous), il suffit d'indiquer à Excel la référence de la première cellule suivie d'un signe #.
  Dans la zone Source, la formule complète est donc :
  =$C$5#

  

• Si la plage de valeurs est située dans une autre feuille de calcul du même classeur, appelée par exemple Données, la formule sera donc :
  =Données!$C$5#

  

• Si le signe # fait référence à une plage de cellules située dans un autre classeur (un autre fichier Excel), celui-ci doit être ouvert, sinon Excel renvoie une erreur.
• Testez la liste déroulante. Si elle n'affiche aucun doublon alors qu'il y en a dans la plage renvoyée par la formule, vous n'avez rien d'autre à faire, votre version d'Excel fait le travail pour vous (c'est déjà le cas dans Excel pour le Web d'après nos tests). Si la liste affiche des doublons comme dans l'exemple ci-dessous (Manteau et Veste sont en double), vous devez légèrement modifier la formule en C5…

  

• Cliquez en C5 pour modifier la formule. Vous utiliserez la nouvelle fonction UNIQUE, qui renvoie une liste de valeurs uniques à partir d'une liste qui peut contenir des doublons (aide en français et en anglais).
• La formule en C5 devient :
  =UNIQUE(Tableau1[Articles])
  Elle renvoie une liste de valeurs débarrassée de ses doublons.

  

• Testez la liste déroulante : elle ne comporte plus aucun doublon. Vous pouvez également essayer d'ajouter des éléments à la liste de départ (A5:A21 sur notre exemple) : grâce au tableau Excel, la plage Articles s'adapte et la liste déroulante se met à jour automatiquement.

  

• Votre liste déroulante peut, bien entendu, faire partie d'un autre tableau (onglet Accueil > Mettre sous forme de tableau). À chaque ligne du tableau que vous ajouterez (pressez la touche Tab dans la dernière cellule, en bas à droite du tableau, pour ajouter une ligne), la liste déroulante apparaîtra pour vous laisser sélectionner un élément dans la liste.

  

• Vous aimeriez aller plus loin ? Il sera très facile de récupérer, par exemple, un prix associé à l'élément sélectionné dans la liste déroulante. Nous avons ajouté une colonne Prix à Tableau1 pour le démontrer.

  

• Cas 1 : si votre tableau de départ ne contient pas de doublons. Grâce à la nouvelle fonction FILTRE d'Excel pour Microsoft 365, la formule ci-dessous nous permet de récupérer le prix de l'article sélectionné sur la ligne :
  =FILTRE(Tableau1[Prix];Tableau1[Articles]=[@Produit])

  

• Quelques explications sur cette formule pour vous aider à l'adapter :
  ► Prix et Articles sont les deux colonnes de notre tableau Tableau1
  ► @Produit fait référence à l'élément sélectionné dans la colonne nommée Produit du tableau en cours.
• Cas 2 : si le tableau de départ comporte des doublons. Utilisez par exemple la nouvelle fonction RECHERCHEX, spécifique à Microsoft 365. Elle vous permet ici d'obtenir la valeur associée à la première valeur trouvée (ci-dessous, la première occurrence du Manteau, à 110€) :
  =RECHERCHEX([@Produit];Tableau1[Articles];Tableau1[Prix])

  

Trier les données d'une liste déroulante avec Excel pour Microsoft 365

• Si vous voulez, en plus, que les données soient rangées par ordre alphabétique, la nouvelle fonction TRIER va se charger de trier une plage ou un tableau par ordre croissant ou décroissant, par ligne ou par colonne (aide en français et en anglais).
• Ci-dessous, nous avons complété la formule en C5, qui devient :
  =TRIER(UNIQUE(Tableau1[Articles]))

  

• Testez la liste déroulante : elle affiche des données triées et sans doublons.

  

Afficher une liste déroulante sans tenir compte des cellules vides

• Reste un dernier détail à régler, quand la liste de départ comporte des cellules vides : ci-dessous, la cellule A13 est vide, la formule en C5 renvoie la valeur 0 en fin de liste. Et notre liste déroulante aussi. Ce n'est certes pas insupportable, mais pas très élégant non plus…

  

• Vous allez vous aider cette fois de la nouvelle fonction FILTRE (aide en français et en anglais), et des classiques fonctions NON et ESTVIDE.
• La formule à inscrire en C5 devient :
  =TRIER(UNIQUE(FILTRE(Tableau1[Articles];NON(ESTVIDE(Tableau1[Articles])))))

  

• Copiez notre formule telle quelle dans votre feuille de calcul, il y a juste à remplacer les noms Tableau1 et Articles pour l'adapter à vos besoins.
• La fonction TRIER est évidemment facultative, simplifiez la formule si vous préférez conserver l'ordre d'apparition des valeurs dans la liste :
  = UNIQUE(FILTRE(Tableau1[Articles];NON(ESTVIDE(Tableau1[Articles]))))
• N'oubliez pas : dans la boîte de dialogue de validation des données d'Excel 365, dans la zone Source de votre liste, vous devez indiquer l'adresse de la cellule contenant la formule matricielle qui renvoie la première valeur de la liste (=$C$5 sur nos exemples), puis faire suivre cette référence du signe # qu'Excel appelle l'opérateur de plage renversée (aide en français et en anglais).
  Donc, inscrire la formule : =$C$5#

  

  
  Le signe # vous évite d'avoir à connaître et à inscrire l'étendue de la plage. Sur notre exemple, au lieu de =$C$5# vous pourriez taper =$C$5:$C$18 dans la zone Source.
• Nous vous indiquons souvent les pages d'aide d'Excel en français et en anglais, car Microsoft utilise un système de traduction automatique de l'anglais vers le français qui s'avère parfois médiocre et source d'erreurs, notamment dans les exemples et formules. Notez par exemple que dans la version française d'Excel : le séparateur d'argument est le point-virgule (comme dans =TRIER(A1:A5;;-1) ), et non la virgule comme en anglais ; et dans une formule, les guillemets entourant un texte sont toujours des "guillemets droits" et non des « chevrons », comme dans =A2&"-"&A3

Comment créer une liste déroulante dans Excel à partir d'une plage de cellules ?

Cette méthode est intéressante quand la liste peut contenir de nombreuses entrées et quand elle n'évolue pas ou rarement. Vous apprendrez ici à créer une seule liste. Reportez-vous plutôt à cette autre fiche pratique si vous souhaitez créer plusieurs listes déroulantes en cascade.

• Après avoir choisi Données > Validation des données > Autoriser > Liste, cliquez dans la zone Source puis cliquez dans une feuille pour sélectionner à la souris ou au clavier une plage de cellules, en ligne ou en colonne.

• Vous pouvez bien sûr aussi taper vous-même la plage de cellules dans la zone Source.
• Quand on tape ensuite une valeur au lieu de la choisir dans la liste déroulante, Excel ne tient pas compte des majuscules et minuscules. Si la valeur Lucie figure dans votre liste, l'utilisateur pourra taper lucie, LUCIE ou LuCie dans la cellule sans recevoir de message d'erreur.

• Nous vous expliquons plus loin comment ajouter ou comment supprimer des éléments à la liste source sans avoir à modifier la Validation des données pour cette liste déroulante.

Comment faire une liste déroulante dans Excel à partir d'une plage nommée ?

Cette méthode présente plusieurs intérêts : la liste peut être très longue ; les noms rendent toujours les formules plus compréhensibles, et en utilisant une formule pour définir l'étendue de la plage de cellules nommée, la liste déroulante s'adaptera automatiquement aux changements quand on ajoute ou supprime des éléments.

Vous apprendrez ici à créer une seule liste. Reportez-vous plutôt à cette autre fiche pratique si vous souhaitez créer plusieurs listes déroulantes en cascade.

• Pour nommer une plage de cellules dans Excel, sélectionnez d'abord ces cellules, puis cliquez dans la zone Nom, juste au-dessus de la colonne A, et tapez le nom : sur notre exemple, nous avons sélectionné la plage de cellules A4:A10 et avons tapé Auteurs, qui devient le nom de la plage. Un nom ne peut commencer que par les lettres A à Z ou par le signe _ et ne doit pas contenir d'espace (voir l'onglet Formules > Gestionnaire de noms pour d'autres options).

• Autre méthode encore plus simple pour nommer une plage quand la première cellule de cette plage comporte déjà un titre : sélectionnez les cellules avec cet en-tête de colonne, ici notre titre est Auteurs, donc sélectionnez les cellules A3:A10 et, dans l'onglet Formules, cliquez sur Depuis une sélection. Vérifiez que la case Ligne du haut (et seulement elle) est cochée et pressez OK. La plage A4:A10 (donc sans la première cellule de titre) est nommée avec le contenu de la première cellule sélectionnée (ici A3), la plage A4:A10 s'appelle donc ici Auteurs. Si la cellule A3 contenait le texte Auteurs français, le nom serait Auteurs_français car dans les plages nommées, les espaces et les traits d'union sont remplacés dans le nom par des signes _ par Excel.
• Sélectionnez les cellules où doit s'afficher la liste déroulante et cliquez sur l'onglet Données > Validation des données > Autoriser > Liste.
• Dans la fenêtre Validation des données, pour la Source de votre liste, tapez le signe = (égal) suivi du nom de la plage nommée, donc =Auteurs dans notre exemple.

• Au lieu de taper le nom de la plage, lorsque vous éditez le contenu de la zone Source, vous pouvez aussi presser la touche F3 dans Excel pour Windows : la petite fenêtre Coller un nom surgit, elle liste toutes les zones nommées de votre classeur. Cliquez sur le nom qui vous intéresse, ici Auteurs, Excel ajoute la formule =Auteurs dans la zone Source.

• Voici donc notre liste déroulante dans laquelle il suffit de piocher un auteur.

• Nous vous expliquons plus loin comment ajouter ou comment supprimer des éléments à votre liste source sans avoir à reparamétrer la liste déroulante.

Besoin d'approfondir vos connaissances sur Excel ?
Suivez notre formation sur CCM Benchmark Institut !

Découvrir la formation Excel sur CCM Benchmark Institut

Comment faire une liste déroulante dynamique à partir d'un tableau dans Excel ?

Avec cette méthode, quand vous ajoutez ou supprimez des données dans la liste source, la liste déroulante s'adapte automatiquement pour tenir compte des changements. D'autres méthodes le permettent, celle-là a le mérite de l'élégance et de la clarté, notamment car toutes les plages de cellules sur lesquelles vous travaillez sont nommées. Et les "tableaux" d'Excel ont bien d'autres atouts.
Vous apprendrez ici à créer une seule liste. Reportez-vous plutôt à cette autre fiche pratique si vous souhaitez créer plusieurs listes déroulantes en cascade.

• Cliquez sur l'une des cellules de données sources ou sélectionnez toute la plage de cellules avec ses noms de colonnes. Votre tableau peut comporter une ou plusieurs colonnes, la liste déroulante, elle, n'utilisera de toute façon qu'une seule colonne que vous préciserez plus tard par son nom.

• Sous l'onglet Insertion, cliquez sur Tableau.
• Si vous n'aviez pas sélectionné tout le tableau, Excel sélectionne les données qu'il estime devoir faire partie du tableau, titres (en-têtes) de colonne compris. Vérifiez que la sélection vous convient.
• Cochez la case Mon tableau comporte des en-têtes et pressez OK. Sur notre exemple, nos en-têtes sont Mammifères aquatiques et Taille.

• Excel nomme Tableau1 le premier tableau créé dans le classeur (ne tenez pas compte des listes déroulantes qu'il ajoute à droite des en-têtes). Vérifiez le nom du tableau en cliquant dans le coin supérieur gauche dans la zone Nom. Cliquez sur le nom pour sélectionner tout le tableau : Tableau1 ne tient pas compte des en-têtes (il couvre donc B3:C10 sur notre exemple), mais Excel les conserve précieusement en mémoire…
• Sélectionnez maintenant les cellules où doit apparaître la liste déroulante de saisie.
• Cliquez sur l'onglet Données > Validation des données > Autoriser > Liste.
• Dans la zone Source, tapez =INDIRECT("NomduTableau[en-tête de colonne]") c'est-à-dire =INDIRECT("Tableau1[Mammifères aquatiques]") dans notre exemple.
• Testez votre liste déroulante…

• À la fin de la liste source, ajoutez un ou plusieurs éléments dans les cellules vides. Ci-dessous, nous tapons deux nouveaux éléments, Baleine à bosse et Dauphin de Chine, en fin de liste…

• … Automatiquement, notre Tableau1, qui s'étendait sur B3:C10, couvre à présent la plage de cellules B3:C12. Et sans rien avoir à faire, la liste déroulante prend en compte les deux éléments ajoutés.

• Vous aimeriez afficher la taille de l'animal quand on en sélectionne un dans la liste ? Dans la feuille de saisie, inscrivez des en-têtes juste au-dessus de la liste déroulante (Cétacé et Taille, sur notre exemple) puis sélectionnez ce mini tableau (en-têtes et première ligne de données) et cliquez sur Insertion, Tableau pour créer cette fois un Tableau2.

• En G3, inscrivez la formule =RECHERCHEV([@Cétacé];Tableau1;2;FAUX)
• La taille de l'animal sélectionné juste à gauche dans la liste déroulante s'affiche automatiquement…

• Quand vous êtes à la fin de ce tableau, ici en cellule G3, pressez la touche Tab de votre clavier PC ou Mac pour ajouter une ligne au tableau Tableau2. En cellule F4, la liste déroulante est recopiée et à votre disposition pour sélectionner un animal, choisissez-en un dans la liste et sa taille s'inscrira d'office en cellule G4, puisque la formule inscrite en G3 s'est, elle aussi, automatiquement recopiée en G4.

• Pour adapter notre formule =RECHERCHEV([@Cétacé];Tableau1;2;FAUX) à vos données, voici quelques explications :
  ► la fonction RECHERCHEV cherche une valeur dans la première colonne à gauche d'un tableau (ici dans Tableau1), et renvoie une valeur sur la même ligne du tableau, mais d'une autre colonne
  ► @Cétacé est le texte que nous avons tapé en F2 et qui devient ici un en-tête du tableau Tableau2, il pointe sur le texte que vous recherchez dans Tableau1 (ici le nom de l'animal inscrit dans la cellule grâce à la liste déroulante)
  ► Tableau1 est le tableau dans lequel on effectue donc la recherche
  ► la valeur "2" correspond au numéro de colonne où se trouve la valeur qui doit être renvoyée (ici la deuxième colonne de Tableau1, qui comporte la taille des animaux)
• La valeur FAUX est importante, ne l'omettez pas, elle précise ici que vous voulez une recherche exacte du nom de l'animal, et non la valeur la plus proche (sinon indiquez la valeur VRAI, mais dans ce cas la liste des noms d'animaux doit être triée par ordre croissant).
• Enfin, quelques commandes à connaître pour gérer les tableaux… Si vous préférez supprimer les petites listes déroulantes qui s'affichent dans les en-têtes du tableau source : onglet Accueil > Trier et filtrer > Filtrer.

• Pour renommer un tableau, cliquez sur l'une de ses cellules, cliquez sur l'onglet Création de tableau qui surgit tout à droite du ruban (après Fichier, Accueil, Insertion, Mise en page…), puis renommez le tableau en haut à gauche de l'écran (ici Tableau1 est renommé TabloCétacés). Pour supprimer un tableau, cliquez sur Convertir en plage (le contenu des cellules est conservé).

Comment créer une liste déroulante à partir d'une formule Excel ?

Avec cette méthode, si vous ajoutez des éléments à la liste source, ils s'affichent automatiquement dans la liste déroulante. Nous proposons ici une formule relativement "simple", mais toute la puissance d'Excel est à votre disposition pour définir les éléments sources de votre liste. Et, on va le voir, un nom peut aussi être associé à une formule plutôt qu'à une plage de cellules…
Vous apprendrez ici à créer une seule liste. Reportez-vous plutôt à cette autre fiche pratique si vous souhaitez créer plusieurs listes déroulantes en cascade.

• Une fois vos données saisies en colonne, sélectionnez les cellules où doit s'afficher la liste déroulante et cliquez sur l'onglet Données > Validation des données > Autoriser > Liste.

• Dans la zone Source, avec les données de notre exemple ci-dessus, tapez la formule =DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
• Et voilà notre liste…

• Si vous ajoutez un nouvel élément en fin de liste source (ci-dessous Quiche au fromage), elle s'ajoute automatiquement à la liste déroulante.

• Cette formule =DECALER($A$5;0;0;NBVAL($A:$A)-1;1) sera facile à adapter à vos propres données :
  ► la fonction DECALER renvoie une référence à une plage de cellules (explications ici) qui contiennent la liste à afficher ;
  ► $A$5 fait référence à la première cellule de notre liste source ;
  ► laissez 0;0; pour n'appliquer aucun décalage horizontal ou vertical ;
  ► $A:$A correspond à la colonne (A) où se trouve la liste source ;
  ► NBVAL compte le nombre de cellules non vides dans cette colonne (ici le résultat est 11) ;
  ► valeur -1 : on enlève 1 au nombre renvoyé par NBVAL (11 dans notre exemple) car l'une de nos cellules contient un en-tête de liste (Plat du jour), dont il ne faut pas tenir compte ;
  ► le dernier 1 correspond au nombre de colonnes de la référence renvoyée par DÉCALER (laissez la valeur 1).
• Vous pourriez très bien associer cette formule à un nom. Voyons d'abord comment faire dans Windows…

• Dans Windows, peu importe la cellule actuellement sélectionnée : cliquez sur l'onglet Formules puis sur Gestionnaire de noms, puis sur le bouton Nouveau.
• Donnez un nouveau nom à votre liste, par exemple PlatsDuJour (sans espace, et la première lettre doit être une lettre de A à Z ou le signe _ de soulignement).

• Dans la zone Fait référence à, copiez-collez la formule vue plus haut, c'est-à-dire =DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
• Pressez le bouton OK et refermez la fenêtre du Gestionnaire des données.

Voyons à présent comment effectuer la même manipulation sur Mac.

• Pour associer une formule à un nom, peu importe la cellule sélectionnée, cliquez sur l'onglet Formules puis sur Définir un nom.
• Tapez le nouveau nom, ici PlatsDuJour (les espaces sont interdits et le nom doit commencer par une lettre de A à Z ou le signe _ de soulignement).

• Dans la zone Sélectionner la plage de cellules, copiez-collez la formule =DECALER($A$5;0;0;NBVAL($A:$A)-1;1)
• Pressez le bouton OK.
• Dans Windows ou macOS, sélectionnez maintenant les cellules où doit apparaître la liste déroulante. Cliquez sur l'onglet Données puis sur Validation des données.

• Choisissez Autoriser > Liste. Dans la zone Source, tapez = (signe égal) suivi du nom que vous venez de créer, donc =PlatsDuJour et pressez le bouton OK.
• La liste déroulante affiche tous les éléments. Elle se mettra à jour automatiquement si vous modifiez, ajoutez ou supprimez des éléments dans la liste source.

Comment préparer les données à afficher dans une liste déroulante Excel ?

Pour préparer les données à afficher dans une liste déroulante, il est préférable – mais pas obligatoire – de stocker les éléments sources de la liste dans une feuille à part de votre classeur Excel.

• Cliquez au bas de la fenêtre sur le signe + pur créer une nouvelle feuille dans votre classeur Excel. Double-cliquez ensuite sur l'onglet Feuil2 qui vient de s'ajouter, afin de renommer cette feuille en l'appelant, par exemple, Paramètres.

• Il est possible ensuite de masquer votre feuille Paramètres en cliquant dessus avec le bouton droit de la souris, choix Masquer. Pour la réafficher, cliquez avec le bouton droit sur n'importe quel autre nom de feuille (par exemple Feuil1) pour choisir Afficher… et voir la liste des feuilles masquées.

• En cliquant avec le bouton droit de la souris sur le nom d'une feuille, vous pouvez également Protéger la feuille, y compris par mot de passe.
• Si vous n'êtes pas très à l'aise avec la gestion de multiples feuilles et préférez travailler avec une seule feuille de calcul, vous pouvez saisir les éléments puis masquer la colonne. Cliquez avec le bouton droit de la souris sur la lettre de colonne, en haut de la feuille (sur la lettre A sur l'exemple ci-dessous), et choisissez Masquer dans le menu contextuel. Pour réafficher la colonne masquée, sélectionnez les colonnes qui l'entourent, cliquez dessus avec le bouton droit pour afficher le menu contextuel et choisissez Afficher.

• Les libellés qui apparaîtront dans la liste de Validation des données peuvent figurer soit dans une colonne, soit dans une ligne, au choix, mais les cellules doivent impérativement être adjacentes, donc se suivre.
• Les données de votre liste déroulante apparaîtront dans l'ordre où elles figurent ici, elles ne seront pas automatiquement triées par ordre alphabétique. Donc, triez-les vous-même si cela peut faciliter la saisie : sélectionnez la liste et cliquez sur l'onglet Accueil > Trier et filtrer > Trier de A à Z.

• Si la liste initiale comporte des doublons à supprimer, sélectionnez cette liste et cliquez sur l'onglet Données > Supprimer les doublons.

Comment ajouter un élément à une liste déroulante Excel ?

Nous avons vu que dans certains cas, avec les tableaux Excel notamment, il suffit de taper une nouvelle valeur en fin de liste source pour qu'elle s'ajoute à la liste déroulante. Sinon, selon la manière dont votre liste déroulante fait référence à la liste de cellules sources, voici comment ajouter un élément sans avoir à modifier les options de Validation des données…

Ajouter un élément à une liste déroulante définie à partir d'une plage de cellules

• Pour ajouter ultérieurement un élément à la liste déroulante, cliquez à l'intérieur de la liste avec le bouton droit de la souris, choisissez Insérer… puis Décaler les cellules vers le bas et inscrivez le nouvel élément dans la cellule qui s'ajoute. Retriez au besoin votre liste.

• Autre méthode : dans la zone Source de la Validation des données, si vous sélectionnez au départ une plage de cellules plus grande que nécessaire pour ajouter plus tard des cellules vides en fin de liste, cela vous permettra d'étoffer la liste sans avoir à redéfinir les listes déroulantes de validation des données qui s'y réfèrent. Mais…

• … Nous avons constaté que si les lignes contenant les cellules vides (lignes 17 à 21 sur l'exemple ci-dessus) n'ont jamais contenu ni données ni formatage (gras, italique, encadrement…), Excel ne fait pas apparaître de choix vides dans la liste déroulante, c'est parfait. Sinon, il ajoute des items vides en fin de liste déroulante : la liste reste utilisable mais ce n'est pas très élégant comme on le voit ci-dessous…

Ajouter un élément à une liste déroulante définie à partir d'une plage nommée

• Pour ajouter un élément à la liste déroulante qui affiche le contenu d'une plage nommée, faites comme pour une plage de cellules (voir ci-dessus) : cliquez avec le bouton droit de la souris sur l'une des cellules de la zone nommée (mais pas la première), sélectionnez Insérer > Décaler les cellules vers le bas puis tapez le nouvel élément dans la cellule vide. Triez à nouveau la liste si nécessaire.

• Dans Excel pour Windows, si vous souhaitez redéfinir l'étendue d'une plage de cellules nommée, cliquez dans le ruban sur l'onglet Formules, puis en dessous sur Gestionnaire de noms. Cliquez sur le nom de la plage, puis cliquez en dessous dans la zone Fait référence à afin de sélectionner une nouvelle plage dans la feuille. Cliquez enfin sur la coche verte pour valider et pressez le bouton Fermer. Notez que cette fenêtre sert aussi à Modifier (ajouter un commentaire, par exemple) ou Supprimer une plage nommée.

• Dans Excel pour Mac, pour redéfinir l'étendue d'une plage nommée, cliquez sur l'onglet Formules puis sur Définir un nom (ou dans le menu Insérer > Nom > Définir un nom). Cliquez sur le nom, redéfinissez la plage de cellules et pressez OK. Les boutons + et – vous permettent de créer ou de supprimer des noms.

Comment supprimer un élément dans une liste déroulante Excel ?

Pour éviter d'avoir à reparamétrer votre liste déroulante via la boîte de dialogue de Validation des données, voici comment procéder.

• Rendez-vous dans la feuille où se trouve la liste initiale.
• Cliquez avec le bouton droit de la souris sur la cellule à supprimer de la liste source.
• Cliquez sur Supprimer dans le menu contextuel.

• Dans la petite boîte de dialogue Supprimer, cochez la case Décaler les cellules vers le haut et pressez le bouton OK. Si les données sont disposées en ligne et non en colonne, cochez plutôt la case Décaler les cellules vers la gauche.

Comment supprimer une liste déroulante dans une feuille de calcul Excel ?

Il est bien sûr tout à fait possible de supprimer des listes déroulantes que nous n'utilisez plus dans une feuille de calcul, et ce, sans effacer le contenu des cellules remplies avec la liste.

• Sélectionnez au moins une cellule dans laquelle s'affiche la liste déroulante ou les seules cellules concernées.
• Cliquez sur l'onglet Données > Validation des données.

• Sous l'onglet Outils, si vous souhaitez que la toutes les cellules ayant les mêmes options de validation des données soient concernées, donc toutes les cellules affichant cette liste déroulante : cochez la case Appliquer ces modifications aux cellules de paramètres identiques.
• Cliquez sur le bouton Effacer tout.
• La liste déroulante n'apparaîtra plus, mais le contenu des cellules déjà remplies n'est pas supprimé.
• Si vous ne savez pas où se trouvent les cellules contenant une validation des données, sous l'onglet Accueil, cliquez tout à droite du ruban sur Rechercher et sélectionner, puis sur Validation des données.

• Avant de cliquer sur Rechercher et sélectionner, si vous avez sélectionné une cellule contenant une liste déroulante, cliquez sur Sélectionner les cellules > Validation des données > Identiques : Excel sélectionne toutes les cellules affichant la même liste déroulante. Il vous reste à vous rendre dans l'onglet Données > Validation des données > bouton Effacer tout.

Comment ajouter un message d'aide dans une liste déroulante Excel ?

Il est parfois utile – voire souhaitable ! – d'afficher un message sous forme de bulle pour aider les utilisateurs d'une feuille à employer correctement une liste déroulante.

• Dans la fenêtre Validation des données, onglet Message de saisie, dans les zones Titre (30 caractères maximum) et Message de saisie (250 caractères), indiquez les informations qui s'afficheront sous la forme d'une bulle d'aide.
• Décochez la case Quand la cellule est sélectionnée pour désactiver ponctuellement ou définitivement cette option.

Comment contrôler la saisie et afficher un message d'erreur dans une liste déroulante Excel ?

Toujours pour aider les utilisateurs de votre feuille, il est fortement conseillé d'effectuer un contrôle de la saisie et d'afficher un message d'alerte quand une erreur est détectée.

• Si vous ne souhaitez pas effectuer de vérification de la valeur saisie dans la cellule, ouvrez l'onglet Alerte d'erreur et décochez la case Quand des données non valides sont tapées.

• Si vous souhaitez au contraire effectuer une vérification de la saisie, ouvrez l'onglet Alerte d'erreur et laissez cochée la case Quand des données non valides sont tapées. 
• Dans la liste Style, le choix Stop (ou Arrêt, dans les anciennes versions d'Excel) affiche un message d'erreur et refuse toute valeur saisie incorrecte.

• Dans la liste Style, le choix Avertissement affiche un message d'erreur mais permet à l'utilisateur d'accepter quand même la valeur saisie même si elle ne figure pas dans la liste. C'est ce que nous avons indiqué dans les champs Titre et Message d'erreur.

• Dans la liste Style, le choix Informations est assez similaire au choix Avertissement : il affiche un message et propose cette fois un bouton OK pour valider une valeur non prévue dans la liste. Nous avons donc adapté le message à afficher dans les champs Titre et Message d'erreur.