Créer une liste déroulante dynamique sur Excel

Une liste déroulante dynamique sur Excel est une liste déroulante qui pourra s’adapter aux valeurs contenues dans la plage de données source. Pour l’instant, pour que vous ajoutiez des valeurs à votre liste, vous devez modifier vos plages de cellules sources de votre feuille de calcul Excel dans la barre d’outils.

Les listes déroulantes dynamiques permettent d’ajouter ou de supprimer automatiquement de la liste de choix des valeurs en fonction de la source de données Excel.

Les listes de choix via la validation des données augmente la qualité de vos reportings Excel. En définissant des choix possibles dans des cellules Excel de vos onglets, vous limitez les risques d’erreur de saisie. En obligeant l’utilisateur à sélectionner une valeur parmi une liste de choix, vous vous affranchissez des conséquences d’une saisie libre.

Les listes déroulantes dynamiques ne sont pas à confondre avec les listes déroulantes en cascade. Les listes déroulantes en cascade sont des listes de choix dont les choix possibles des listes inférieures seront conditionnées par les choix effectués dans les listes supérieures.

Dans ce tutoriel Excel, je vais vous expliquer comment créer facilement une liste de choix dynamique dans vos feuilles de calcul. Pour vous simplifier la tâche, sachez que vous pouvez télécharger un modèle Excel dans lequel vous trouverez un mini formulaire à remplir pour générer la formule à intégrer.

Comment créer une liste déroulante dynamique sur Excel ?

Pour créer une liste déroulante dynamique sur Excel il y a deux méthodes :

• Créer la liste dynamique à partir d’une plage nommée.
• Créer la liste dynamique à partir d’une formule.

Dans les deux cas, il vous faudra entrer une formule qui viendra indiquer à Excel de façon dynamique le bloc de cellules à intégrer dans la liste de choix.

La formule de calcul Excel aura une synthaxe de la forme suivante :

=INDIRECT(ADRESSE(29;11)&":"&ADRESSE(NBVAL($K:$K)+28;11))

La formule sera à intégrer dans le nommage d’une plage ou directement dans les options de la liste de choix (validation des données) comme dans l’exemple ci-dessous.

Pour vous simplifier la tâche, j’ai mis à disposition dans le fichier Excel disponible en téléchargement un calcul automatique de la formule à intégrer dans votre feuille de calcul Excel. Vous pourrez créer facilement votre liste déroulante dynamique.

Voyons ensemble le fonctionnement de cette formule Excel et des fonctions qui y sont présentes.

La formule qui génère une liste de choix dynamique

Il y a plusieurs formules de calcul possibles pour générer la liste déroulante dynamique. Dans ce tutoriel Excel, je vais me concentrer sur celle qui fonctionne dans toutes les situations et qui ne dépend pas d’Office 365. Cette formule sera également très utile si vous souhaitez faire une liste de choix en cascade.

Pour celles et ceux qui seraient intéressé(e)s par découvrir les autres formules, voici les fonctions qui permettent d’arriver à un résultat similaire :

• Fonction UNIQUE
• Fonctions DECALER + NBVAL

Dans le fichier disponible en téléchargement, j’ai utilisé la formule suivante :

=INDIRECT(ADRESSE(29;11)&":"&ADRESSE(NBVAL($K:$K)+28;11))

Plusieurs fonctions sont présentes. L’objectif de cette formule de calcul est d’indiquer à Excel que nous voulons lui donner les coordonnées d’une plage de cellules. Le bloc de cellules ainsi défini alimentera la liste déroulante dynamique via divers choix possibles. Par la synthaxe transmise précédemment, Excel comprend que nous voulons la plage de cellules K29:K35.

La formule ADRESSE permet de convertir deux nombres en coordonnées d’une cellule. Ainsi, ADRESSE(29;11) devient K29. En alliant la fonction NBVAL et la fonction ADRESSE comme dans ADRESSE(NBVAL($K:$K)+28;11), j’indique à Excel que je veux la dernière cellule ayant une valeur dans mon bloc de cellules.

La fonction Excel INDIRECT convertit un texte en coordonnées. Les fonctions ADRESSE ont indiqué les coordonnées des cellules en format texte, il fallait faire comprendre à Excel que le format n’était pas du texte mais des coordonnées. C’est chose faite avec la formule INDIRECT.

Où placer la formule

Comme évoqué en préambule, vous avez deux possibilités quant à la méthode à appliquer :

• Vous pouvez vous servir de la formule pour définir une plage de cellules nommée ;
• Ou inscrire la formule en tant que critère de validation des données.

Si vous hésitez entre les deux méthodes, voici mon critère de sélection. Si votre liste de choix doit vous servir à plusieurs endroits de votre document, passez par une plage de cellules nommée. Dans le cas contraire, passez par la validation des données.

Liste déroulante dynamique Excel via plage de données nommée

Une plage de cellules nommée est le fait de définir un nom à une sélection de plusieurs cellules. Ainsi, dans vos formules et vos manipulations, vous pourrez faire référence à ces cellules par un nom comme “liste_villes” à la place des cordonnées (ex: ‘paramètres’!B4:B11). C’est quand même plus parlant non ? 😉 Attention, il faut que vos cellules soient groupées. Vous ne pouvez pas définir un nom pour une sélection regroupant A1 et B12 et C264.

Normalement, pour nommer une plage de cellules, sélectionnez vos cellules puis rendez vous dans la barre à gauche de la barre des formules. Vous devriez y voir les coordonnées de la 1ère cellule. Remplacez cette valeur par le nom souhaité pour votre plage.

Excel a attribué un nom à votre sélection de cellules. Vous pouvez retrouver la liste des noms attribués dans le menu Formules > Gestionnaire des noms. Dans cette fenêtre il vous sera possible d’ajouter, de modifier ou de supprimer les noms et les cellules attribuées.

Dans notre cas, nous allons passer directement par le gestionnaire de nom pour nommer une plage de cellules définies via une formule. Cliquez simplement sur nouveau puis inscrivez le nom que vous voulez attribuer à votre plage. Dans la cellule fait référence à inscrivez y la formule qui sert à définir la liste déroulante dynamique Excel.

Une fois notre plage nommée définie, rendez vous dans Données > Validation des données, puis sélectionnez Autoriser et Liste. Dans la partie Source, vous allez y indiquer le nom défini pour votre plage. Il faudra mettre auparavant un “=” comme dans l’exemple ci-dessous.

La liste déroulante qui apparaitra sur nos cellules sera la liste des choix définis par notre formule.

Liste déroulante dynamique Excel via validation des données

Si vous ne souhaitez pas passer par une plage de cellules nommées, il est possible de saisir directement la formule dans la partie validation des données.

Pour définir une formule en tant que source de la liste déroulante, rendez vous dans Données > Validation des données , puis sélectionnez Autoriser et Liste. Dans la partie Source, vous allez y indiquer votre formule en commençant par “=”.

La formule utilisée dans l’exemple ci-dessous va permettre de prendre en compte automatiquement les valeurs qui seraient ajoutées ou supprimées dans la plage de cellules. Elle doit trouver identifier l’ensemble des cellules de façon à renvoyer à Excel les coordonnées suivantes “A2:B9”.

Astuce de pro : créer un onglet pour gérer vos listes déroulantes.

Avant de finir cet article, je souhaite vous donner une astuce de pro. En tant que contrôleur de gestion, j’ai eu à mettre en place beaucoup de fichiers pour mes collègues et clients. A la fin, la problématique qui se pose est la maintenabilité du fichier. On a tous entendu parler de ce fichier trop lourd, de cette usine à gaz,… L’une des bonnes pratiques que je vous conseille est la création d’un onglet Paramètres. Inscrivez-y toutes vos listes et définissez des noms à vos plages en utilisant des formules.

Conclusion

Dans ce tutoriel Excel, je vous ai expliqué comment mettre en place une liste déroulante dynamique dans votre classeur Excel. J’ai volontairement simplifié les explications afin de faciliter votre mise en oeuvre. Il existe d’autres formules Excel qui vous permettent d’arriver au même résultat. Personnellement, je les trouve moins intéressantes.

La formule qui a été abordée et que vous devez intégrer dans votre classeur Excel pour obtenir une liste déroulante dynamique a un autre avantage majeur : sa synthaxe est similaire à celle qui faut utiliser pour obtenir une liste déroulante en cascade. Si vous ne savez pas faire, je vous invite à consulter l’article.

A bientôt ! 👋