Créez une liste déroulante dynamique sur Excel

#Formules

#Listes

Publié le 14 oct. 2023 par

Vanessa

Dernière mise à jour le 21 déc. 2024

Partagez cet article

Vous connaissez surement les listes déroulantes sur Excel, mais saviez-vous qu’elles peuvent être dynamiques ?Voici comment automatiser vos listes déroulantes sur Excel, en quelques étapes simples et rapides !

🤩 LES SUPERS AVANTAGES

Les listes déroulantes sont mises à jour automatiquement lors d'ajouts ou de suppressions de données dans la source
Plus besoin de penser à modifier manuellement la configuration à chaque mise à jour de la base de donnée source
Lorsque vous supprimez des valeurs, plus de ligne blanche inutile dans les listes déroulantes

Commençons par créer deux feuilles. Dans la Feuil1 on affichera la liste déroulante, dans la Feuil2 on intègrera la base de données, qui servira de source.

La liste déroulante et ses limites

Voyons déjà comment faire une liste déroulante sur Excel. Il vous suffit de :

Vous placer sur la cellule dans laquelle vous souhaitez afficher la liste déroulante (ici dans l’exemple, dans la Feuil1), puis cliquer sur Données dans la Barre d’Outils, puis sur Validation des données.
Une pop-up s’affiche alors, et dans l’onglet Options, en dessous d’Autoriser, sélectionnez Liste, en laissant les deux cases à droite cochées.
Toujours dans la pop-up, en dessous de Source, cliquez sur la petite flèche pour pouvoir sélectionner la plage de données qui vous intéresse. Ici dans l’exemple, les données qui serviront de source se trouve dans la Feuil2, des cellules A2 à A10 (on ne prend pas en compte la cellule A1 puisqu’il s’agit du titre de la liste). Puis, cliquez sur OK.

⚠️ En retournant sur la Feuil1, vous verrez que la liste déroulante s’affiche bien MAIS vous aurez deux problèmes :1. Si vous effacez des valeurs en fin de colonne dans la base de données source (ici dans l’exemple, la plage A1:A10 de la Feuil2), alors dans la liste déroulante vous aurez à la place de ces valeurs une ligne blanche qui apparaitra, correspondant à un vide.🤔 Pourquoi ? Car, si on prend l’exemple, la liste est configurée à partir des données de la plage A2:A10, or les cellules A8, A9 et A10 sont vides !❌ Résultat : on obtient une liste déroulante se terminant avec une ligne blanche inutile.2. Plus embêtant, si vous souhaitez rajouter des valeurs dans la base de données source, elles ne seront pas prises en compte ni rajoutées automatiquement dans la liste déroulante.🤔 Pourquoi ? Car, si on prend l’exemple, la liste étant configurée à partir des données de la plage A2:A10, elle ne prend pas en compte les cellules non comprises entre A2 et A10.❌ Résultat : iI faut modifier la base de données source en se plaçant sur la cellule de la liste déroulante (ici dans l’exemple, D3 dans la Feuil1), puis en cliquant sur Validation des données depuis la Barre d’Outils d’Excel, et en modifiant la plage de données sélectionnée afin d’intégrer les nouvelles valeurs. Bref, vous devez repasser par les 3 étapes listées plus haut.

La solution simple pour automatiser vos listes déroulantes sur Excel ? La formule DÉCALER, qui permet d’indiquer où se trouve une matrice de données à utiliser et quelle taille elle a.

La solution ? La liste déroulante dynamique

ici donc comment créer une liste déroulante dynamique sur Excel :

Les étapes 1 et 2 de la liste déroulante simple listées plus haut dans l’article restent les mêmes.
C’est à l’étape 3 que ça change : en dessous de Source, au lieu de cliquer sur la flèche pour sélectionner une plage de données, entrez la formule DÉCALER, en fonction des données source à prendre en compte. Ici dans l’exemple, les données qui serviront de source se trouvent dans la Feuil2, des cellules A2 à A10 (on ne prend pas en compte la cellule A1 puisqu’il s’agit du titre de la liste).

Dans notre exemple, la cellule de référence est A2, dans la Feuil2.

La matrice de données à utiliser commence à partir de la cellule A2, sans décalage de ligne ou de colonne.
Pour déterminer la hauteur de la matrice, on utilise la formule NBVAL qui va nous donner le nombre de valeurs non vides d’une plage de données. Ce qui correspond à la colonne A. Nous devons penser à soustraire une valeur car le titre de la liste (« Villes ») ne doit pas apparaître dans la liste déroulante.
La largeur de la matrice correspond au nombre de colonnes inclues, ici il n’y en a qu’une.

🥁 On obtient donc la formule :=DÉCALER(Feuil2!A2;;;NBVAL(Feuil2!A:A)-1;1)

Conclusion

Grâce à la formule DÉCALER sur Excel, vous obtenez donc une liste déroulante dynamique. Si des valeurs sont supprimées ou ajoutées dans la base de données source, plus de ligne blanche inutile en fin de liste et tout est automatiquement mis à jour dans la liste déroulante !

Dans le même thème