Modifier une liste déroulante Excel sans la recréer

Retrouver la source d’une liste déroulante existante

Rien dans la cellule ne dit d’où sortent ses choix, et c’est ce qui bloque en premier quand on reprend le fichier de quelqu’un d’autre. La flèche apparaît au clic, le menu s’ouvre, mais la source peut aussi bien être une poignée de mots enfermés dans la règle qu’une plage rangée sur une autre feuille. Une seule cellule suffit à trancher, puisque la boîte de validation affiche la source en clair.

Reconnaître les trois formes du champ Source

Le contenu de ce champ se lit d’un coup d’œil, et il désigne à lui seul le geste à faire ensuite. La colonne Statut du fichier d’exemple affiche =Listes!$A$2:$A$6, donc ses choix vivent dans des cellules, alors que la colonne Priorité affiche Haute;Moyenne;Basse et n’existe nulle part ailleurs que dans la règle. La colonne Service, elle, affiche un simple =Services, qui renvoie à une plage nommée.

Ces trois formes couvrent l’essentiel des fichiers qui circulent, et le tableau Excel en ajoute une quatrième, décrite plus bas. Voici les deux feuilles du classeur d’exemple, avec les trois colonnes à listes et les cellules qui les alimentent.

Changer le contenu d’une liste déroulante tapée à la main

Une liste tapée à la main vit entièrement dans la règle de validation, donc aucune cellule de la feuille ne la contient. C’est le cas de la colonne Priorité, dont le champ Source affiche Haute;Moyenne;Basse, et la correction se fait dans la boîte elle-même.

Le menu affiche les valeurs dans l’ordre où elles sont écrites, sans les trier, donc la Source décide aussi de leur ordre d’apparition. La correction ne se répercute en revanche nulle part, et une autre colonne portant la même liste garde l’ancienne version. Dès qu’une liste dépasse quelques valeurs, ou qu’elle sert à plusieurs endroits, elle gagne à déménager dans une plage de cellules.

Ajouter un élément à une liste déroulante assise sur une plage

Quand la Source pointe vers une plage, les choix vivent dans les cellules, donc la liste se modifie en écrivant dedans. Le piège tient à la plage elle-même, qui reste figée sur les lignes qu’on lui a données une fois pour toutes. Une valeur tapée juste en dessous, en A7 quand la source s’arrête en A6, n’entre jamais dans le menu et se fait refuser.

Insérer une ligne à l’intérieur de la plage source

Le geste le plus sûr consiste à insérer une ligne à l’intérieur de la plage, parce qu’Excel déplace alors lui-même la référence de la règle. Une ligne insérée sur la dernière valeur, celle de A6, la repousse d’un cran et la Source devient =Listes!$A$2:$A$7 toute seule.

La même insertion faite une ligne plus bas, donc juste sous la plage, n’étend rien du tout. Excel ne suit que ce qui se passe à l’intérieur des bornes, et la nouvelle ligne se retrouve alors en dehors, exactement comme une valeur tapée sous la dernière cellule.

Étendre la plage source jusqu’à la nouvelle ligne

Une valeur déjà écrite sous la plage demande l’autre geste, qui consiste à réécrire la référence dans le champ Source. Sélectionne d’abord toutes les cellules qui portent la liste, sans quoi la correction ne touchera qu’une ligne, puis rouvre la validation des données.

Prévoir quelques lignes vides au bout de la plage paraît tentant pour s’épargner cette reprise, sauf que le menu affiche alors autant de lignes blanches qu’il reste de cellules libres. Une liste qui bouge souvent mérite plutôt le montage du tableau, décrit deux sections plus bas, qui règle la question une fois pour toutes.

Mettre à jour une liste déroulante qui repose sur une plage nommée

Un champ Source qui n’affiche qu’un mot, comme =Services, renvoie à une plage nommée, c’est-à-dire à une étiquette collée sur un groupe de cellules. La règle ne connaît que cette étiquette et ne change donc jamais, si bien que c’est le nom qu’on élargit quand la liste s’allonge. Le gestionnaire de noms, dans l’onglet Formules, montre vers quelles cellules il pointe.

La validation n’a pas bougé d’un caractère, elle affiche toujours =Services, et pourtant le menu propose la nouvelle valeur. L’intérêt se voit surtout quand plusieurs colonnes ou plusieurs feuilles partagent la même liste, puisqu’un seul nom corrigé les met toutes à jour d’un coup. Comme pour une plage ordinaire, une ligne insérée à l’intérieur du nom l’étend toute seule, alors qu’une valeur ajoutée en dessous reste dehors.

Rendre une liste déroulante dynamique avec un tableau Excel

Une liste dynamique est simplement une liste dont la source grandit toute seule, quand les deux méthodes précédentes obligent à revenir sur la plage. Un tableau Excel s’en charge, puisqu’il absorbe la ligne tapée juste en dessous de lui. Le raccourci se heurte pourtant à un obstacle, car le champ Source refuse une référence structurée du genre =Tableau1[Statut] et renvoie un message d’erreur. Un nom posé sur la colonne du tableau contourne ce refus, et c’est le montage le plus solide pour une liste qui bouge souvent.

INDIRECT obtient le même résultat en une ligne, en faisant lire la référence structurée comme du texte, et évite de créer un nom. Le nom reste pourtant plus lisible dans la boîte de validation, où =Statuts se comprend d’un coup d’œil quand la chaîne entre guillemets se relit deux fois. Convertir la plage en tableau ne change d’ailleurs rien aux règles déjà posées, qui continuent de citer les cellules. C’est donc bien la source qu’il faut reprendre pour profiter de l’extension automatique.

Appliquer la correction à toutes les cellules qui portent la même liste

Une correction faite sur une seule cellule ne descend pas dans les autres, d’où ces colonnes dont le menu change d’une ligne à l’autre. Corrige la règle en B2 seule, et B3 à B11 gardent l’ancienne liste, le groupe des cellules aux réglages identiques se scindant aussitôt en deux. La boîte propose une case pour l’éviter, tout en bas de l’onglet Options, intitulée « Appliquer ces modifications aux cellules de paramètres identiques ».

Coche-la avant de valider, et la nouvelle source part vers toutes les cellules qui portaient exactement les mêmes réglages. Le réflexe le plus sûr reste pourtant de sélectionner la plage entière avant d’ouvrir la boîte. Une case oubliée ne laisse en effet aucune trace, et le défaut n’apparaît qu’au moment où une ligne refuse ce que la précédente accepte.

Sélectionner d’un coup les cellules qui portent la liste

Sur un fichier hérité, ces cellules sont rarement une colonne bien nette, et Excel sait les désigner lui-même. La boîte Atteindre garde une option pour cela, sous son bouton Cellules, et elle balaie la feuille entière.

L’option Toutes, juste au-dessus, sélectionne au contraire toutes les cellules de la feuille qui portent une règle de validation, quelle qu’elle soit. Elle sert à faire l’inventaire d’un classeur qu’on découvre, avant même de savoir combien de listes il cache.

Recopier la règle sur d’autres cellules

Pour étendre une liste à des cellules qui n’en ont pas, copie une cellule qui la porte et sélectionne la zone d’arrivée. Fais ensuite un collage spécial en ne gardant que la validation, si bien que ni la valeur ni la mise en forme de la cellule copiée n’écrasent ce qui est déjà en place.

Supprimer une liste déroulante sans perdre les valeurs saisies

Enlever une liste déroulante revient à retirer sa règle, une opération qui ne touche pas au contenu des cellules. Les choix déjà faits redeviennent une saisie comme une autre, et c’est le bouton Effacer tout, en bas à gauche de la boîte, qui s’en charge.

La flèche s’en va, la colonne accepte dès lors n’importe quelle saisie, et Haute reste écrit en C2 comme avant l’opération. Vider les cellules relève d’un geste séparé, une sélection de la plage suivie d’un appui sur Suppr, que rien n’oblige à faire en même temps. Quand les cellules à libérer sont éparpillées sur la feuille, la sélection décrite plus haut évite de les chercher une par une.

Pourquoi la liste déroulante ne se met pas à jour

Une liste qui ignore la valeur qu’on vient d’ajouter a presque toujours l’une de ces trois causes, et le champ Source les départage en quelques secondes. Commence donc par ouvrir la validation sur la cellule qui refuse, avant de toucher à quoi que ce soit.

La valeur a été écrite sous la plage

C’est le cas le plus fréquent, puisque rien n’indique où s’arrête la source quand on regarde la feuille des listes. Une plage =Listes!$A$2:$A$6 ignore la valeur tapée en A7, et la cellule du Suivi la rejette avec un message d’erreur, alors même qu’elle est bien écrite au bon endroit. Étends la plage jusqu’à la nouvelle ligne, ou insère plutôt la ligne à l’intérieur comme plus haut.

La correction n’a touché qu’une partie des cellules

Quand le menu propose la nouvelle valeur sur une ligne et pas sur la suivante, la règle a été reprise cellule par cellule. Sélectionne toute la colonne et repose la source d’un seul coup, ou coche la case des cellules de paramètres identiques avant de valider.

La source affiche #REF!

Un champ Source qui montre =Listes!#REF! signifie que les cellules de la source ont été supprimées, et un =#REF!$A$2:$A$6 que c’est la feuille entière qui a disparu. La règle survit à cette disparition sans rien afficher, et les valeurs déjà choisies restent en place, donc le défaut passe inaperçu jusqu’au premier clic sur la flèche. Recrée la plage des choix, puis repose la source dessus.

Une liste dont les choix doivent dépendre d’un premier menu relève d’un tout autre montage, celui de la cascade, que notre article sur la liste déroulante conditionnelle détaille avec INDIRECT et les plages nommées. Pour la création depuis zéro et le vocabulaire de la validation, la fiche du lexique sur la liste déroulante reprend les réglages un par un.