Liste de valeurs excel : créer, gérer et utiliser une liste déroulante dynamique

Une liste déroulante Excel permet de sélectionner une valeur dans un menu plutôt que de la saisir au clavier. En apparence, c’est un petit détail. En pratique, c’est l’un des meilleurs moyens de fiabiliser un tableau, d’éviter les fautes de frappe et de rendre un fichier plus agréable à utiliser.

Le vrai défi commence lorsque la liste doit évoluer. Ajouter un nouveau produit, un collaborateur ou une catégorie ne devrait pas nécessiter de modifier manuellement toute la configuration. La solution : créer une liste déroulante dynamique, capable de s’adapter automatiquement aux nouvelles valeurs.

Dans ce tutoriel, nous allons voir comment créer, gérer et utiliser une liste de valeurs Excel, avec plusieurs méthodes adaptées aux versions récentes comme aux versions plus anciennes du logiciel.

Pourquoi utiliser une liste déroulante dans Excel ?

Imaginez un tableau de suivi des commandes. Dans la colonne « Statut », certains utilisateurs écrivent « En cours », d’autres « en cours », « En-cours » ou encore « À traiter ». Pour Excel, ces textes sont différents. Les filtres, les graphiques et les tableaux croisés dynamiques risquent alors de produire des résultats incohérents.

Avec une liste déroulante, les utilisateurs choisissent une valeur parmi une liste définie à l’avance. Vous obtenez ainsi :

• des données homogènes et plus faciles à analyser ;
• moins d’erreurs de saisie ;
• un formulaire plus rapide à remplir ;
• des tableaux de bord plus fiables ;
• une meilleure expérience pour les personnes qui utilisent votre fichier.

Une liste déroulante est donc particulièrement utile pour les statuts, les services, les villes, les produits, les niveaux de priorité ou encore les catégories comptables.

Créer une première liste déroulante avec la validation des données

Commençons par une méthode simple. Supposons que vous souhaitiez créer une liste de statuts dans les cellules de la colonne C.

Dans une zone dédiée de votre feuille, saisissez les valeurs suivantes :

• À traiter
• En cours
• Terminé
• Annulé

Vous pouvez placer ces valeurs dans les cellules H2 à H5. Il est préférable de les regrouper dans une zone clairement identifiée, voire dans une feuille appelée « Listes ». Cela évite de mélanger les données de référence avec les données saisies par les utilisateurs.

Sélectionnez ensuite les cellules dans lesquelles la liste doit apparaître, par exemple C2:C100. Dans le ruban Excel, ouvrez l’onglet Données, puis cliquez sur Validation des données.

Dans la fenêtre qui s’affiche :

• choisissez Liste dans le champ « Autoriser » ;
• dans le champ « Source », indiquez =$H$2:$H$5 ;
• vérifiez que l’option permettant d’afficher une liste déroulante dans la cellule est activée ;
• cliquez sur OK.

Chaque cellule sélectionnée contient désormais une flèche. Un clic suffit pour choisir un statut.

Cette méthode est efficace, mais elle possède une limite évidente : si vous ajoutez « En attente » en H6, cette nouvelle valeur n’apparaîtra pas automatiquement dans la liste. C’est ici que la liste dynamique devient intéressante.

La méthode la plus fiable : convertir la liste en tableau Excel

Pour créer une liste qui s’agrandit automatiquement, la solution la plus simple consiste à transformer la plage de valeurs en tableau Excel.

Sélectionnez vos valeurs, par exemple H1:H5, en incluant un en-tête comme « Statut ». Utilisez ensuite le raccourci Ctrl + T, ou cliquez sur Mettre sous forme de tableau dans l’onglet Accueil.

Dans la fenêtre de confirmation, vérifiez que la case « Mon tableau comporte des en-têtes » est bien cochée. Dans l’onglet de conception du tableau, donnez-lui un nom explicite, par exemple tblStatuts.

Vous pouvez maintenant utiliser la colonne du tableau comme source de votre liste déroulante. Selon la version d’Excel, la validation des données accepte plus ou moins directement les références structurées. La méthode la plus robuste consiste à créer un nom défini.

Créer un nom défini pour la liste déroulante

Un nom défini permet de donner un nom clair à une plage ou à une formule. Au lieu d’utiliser une référence difficile à relire comme $H$2:$H$5, vous pourrez utiliser un nom tel que ListeStatuts.

Pour créer ce nom :

• ouvrez l’onglet Formules ;
• cliquez sur Gestionnaire de noms ;
• sélectionnez Nouveau ;
• entrez ListeStatuts dans le champ « Nom » ;
• dans « Fait référence à », saisissez =tblStatuts[Statut] ;
• validez.

Retournez ensuite dans la validation des données et indiquez simplement =ListeStatuts dans le champ « Source ».

Lorsque vous saisissez une nouvelle valeur directement sous la dernière ligne du tableau, Excel agrandit automatiquement le tableau. La liste déroulante pourra alors utiliser cette nouvelle valeur sans que vous ayez à modifier sa source.

Cette approche est particulièrement adaptée aux fichiers partagés, car elle repose sur une structure claire et facilement maintenable. Le tableau devient la véritable source de référence, tandis que le nom défini sert de passerelle vers la validation des données.

Créer une liste dynamique avec la fonction DECALER

Si vous utilisez une version plus ancienne d’Excel ou si vous préférez travailler avec une plage classique, la fonction DECALER peut rendre la source dynamique.

Supposons que les valeurs se trouvent dans la colonne H, à partir de H2, et que H1 contient l’en-tête « Statut ». Vous pouvez créer un nom défini appelé ListeStatuts avec la formule suivante :

=DECALER(Feuil1!$H$2;0;0;NBVAL(Feuil1!$H:$H)-1;1)

Cette formule fonctionne de la manière suivante :

• DECALER définit une plage à partir d’une cellule de départ ;
• NBVAL compte le nombre de cellules non vides de la colonne H ;
• le nombre 1 retiré correspond à l’en-tête situé en H1 ;
• la hauteur de la plage s’adapte au nombre de valeurs présentes.

Dans la validation des données, utilisez ensuite =ListeStatuts comme source.

Cette technique est pratique, mais elle doit être utilisée avec attention. Une cellule remplie accidentellement au milieu de la colonne sera considérée comme une valeur valide. De plus, la fonction DECALER est volatile : elle peut être recalculée fréquemment et ralentir les classeurs très volumineux.

Créer une liste dynamique avec INDEX

Une alternative plus légère consiste à utiliser la fonction INDEX. Dans le Gestionnaire de noms, créez par exemple le nom ListeStatuts et utilisez la formule suivante :

=Feuil1!$H$2:INDEX(Feuil1!$H:$H;NBVAL(Feuil1!$H:$H))

Cette formule crée une plage allant de H2 jusqu’à la dernière cellule non vide de la colonne H. Elle évite le caractère volatile de DECALER et convient mieux aux classeurs importants.

Comme toujours, adaptez le nom de la feuille et la colonne à votre fichier. Si le nom de la feuille contient des espaces, placez-le entre apostrophes, par exemple :

='Listes de référence'!$H$2:INDEX('Listes de référence'!$H:$H;NBVAL('Listes de référence'!$H:$H))

Utiliser les fonctions FILTRER et UNIQUE dans les versions récentes

Microsoft 365 et les versions récentes d’Excel proposent les tableaux dynamiques. Ils permettent de générer automatiquement une liste sans doublons grâce à la fonction UNIQUE.

Supposons que votre liste de départ se trouve dans A2:A100. Dans une cellule libre, par exemple J2, saisissez :

=UNIQUE(FILTRE(A2:A100;A2:A100<>""))

La fonction FILTRE élimine les cellules vides, tandis que UNIQUE conserve une seule occurrence de chaque valeur. Excel « déverse » ensuite automatiquement le résultat dans les cellules situées sous J2.

Pour utiliser ce résultat dans une liste déroulante, créez un nom défini avec la référence :

=Feuil1!$J$2#

Le symbole # désigne toute la plage déversée à partir de J2. Dans la validation des données, indiquez ensuite =ListeSansDoublons.

Cette méthode est idéale lorsque la liste est issue d’une autre base de données et que vous souhaitez supprimer automatiquement les doublons. Par exemple, vous pouvez générer la liste des villes présentes dans un tableau de commandes, même si chaque ville apparaît plusieurs fois.

Gérer les cellules vides et les doublons

Une liste déroulante propre commence par une liste source propre. Les cellules vides peuvent créer des choix inutiles, tandis que les doublons rendent le menu plus long et moins lisible.

Pour éviter ces problèmes, vous pouvez appliquer quelques bonnes pratiques :

• placez un seul élément par cellule ;
• évitez les espaces en début ou en fin de texte ;
• utilisez SUPPRESPACE pour nettoyer les valeurs importées ;
• utilisez UNIQUE pour supprimer les doublons dans Microsoft 365 ;
• triez les valeurs avec TRIER afin de faciliter la sélection ;
• ne mélangez pas des valeurs de nature différente dans une même liste.

Pour générer une liste unique et triée, vous pouvez utiliser :

=TRIER(UNIQUE(FILTRE(A2:A100;A2:A100<>"")))

Le résultat sera plus agréable à parcourir et plus facile à maintenir. Un menu déroulant n’a pas vocation à devenir un inventaire interminable où l’on cherche une valeur pendant trois minutes.

Afficher un message d’aide et gérer les erreurs

La validation des données ne sert pas uniquement à afficher une flèche. Elle peut aussi guider l’utilisateur.

Dans la fenêtre de validation des données, ouvrez l’onglet Message de saisie. Vous pouvez afficher un titre et une indication, par exemple : « Choisissez un statut dans la liste ».

L’onglet Alerte d’erreur permet ensuite de définir le comportement d’Excel lorsqu’une valeur non autorisée est saisie. Le style Arrêt bloque la saisie. Le style Avertissement laisse l’utilisateur confirmer son choix. Le style Information affiche simplement un message.

Pour un tableau qui doit rester parfaitement fiable, le style « Arrêt » est généralement le meilleur choix. Dans un fichier plus souple, un avertissement peut être préférable.

Créer une liste déroulante dépendante

Une liste dépendante change en fonction d’un premier choix. Par exemple, l’utilisateur sélectionne une région, puis Excel lui propose uniquement les villes correspondant à cette région.

Avec Microsoft 365, vous pouvez utiliser la fonction FILTRE. Si la région sélectionnée se trouve en B2, et si votre base contient les régions en colonne E et les villes en colonne F, saisissez dans une cellule auxiliaire :

=FILTRE($F$2:$F$100;$E$2:$E$100=B2;"Aucune ville")

La liste des villes correspondant à la région choisie se déverse automatiquement. Il ne reste plus qu’à utiliser la plage déversée comme source de la seconde liste déroulante.

Cette technique est très utile pour créer des formulaires de commande, des outils de réservation ou des fichiers de suivi structurés. Elle évite surtout de présenter à l’utilisateur une liste complète et peu pratique.

Conseils pour maintenir une liste de valeurs Excel

Une liste dynamique fonctionne bien lorsqu’elle est pensée comme une petite base de données. Donnez un nom explicite à la feuille qui contient les valeurs, protégez cette feuille si nécessaire et limitez les modifications aux personnes autorisées.

Évitez également de supprimer la colonne source sans vérifier les validations de données qui l’utilisent. Une liste déroulante peut continuer d’exister visuellement tout en renvoyant une erreur si sa source a été déplacée ou renommée.

Enfin, testez toujours les cas importants : ajout d’une valeur, suppression d’une valeur, cellule vide, doublon et saisie manuelle d’un élément non prévu. Quelques minutes de vérification peuvent éviter de longues recherches dans un fichier partagé.

Quelle méthode choisir ?

Pour un nouveau fichier, le tableau Excel associé à un nom défini est généralement le meilleur compromis entre simplicité, fiabilité et évolutivité.

• Liste classique : parfaite pour quelques valeurs qui ne changent jamais.
• Tableau Excel : recommandé pour une liste qui s’agrandit régulièrement.
• DECALER : utile pour assurer la compatibilité avec d’anciennes versions.
• INDEX : bonne alternative non volatile pour les listes dynamiques classiques.
• UNIQUE et FILTRE : idéales pour créer des listes automatiques, propres et sans doublons dans Microsoft 365.

Une liste déroulante bien conçue transforme un simple classeur en véritable outil de travail. Elle sécurise les saisies, facilite les analyses et évite les petites incohérences qui finissent par compliquer tout un fichier. Et comme souvent avec Excel, quelques minutes de préparation permettent d’économiser beaucoup de temps ensuite.