Créer une liste déroulante dynamique sur Excel

Les listes déroulantes dynamiques 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.

Exemple de liste déroulante dynamique dans Excel
Exemple de liste déroulante dynamique dans 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.

Les modÚles Excel clé-en-main

TĂ©lĂ©chargez gratuitement les modĂšles Excel de Cleex. Vous y trouverez l’ensemble des modĂšles et calculs Ă©voquĂ©s dans nos articles.

C’est gratuit đŸ€©

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.

Liste déroulante dynamique dans Excel - Validation des données
Liste dĂ©roulante dynamique dans Excel – Validation des donnĂ©es

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.

Liste déroulante dynamique dans Excel - Téléchargez les modÚles de Cleex
Liste dĂ©roulante dynamique dans Excel – TĂ©lĂ©chargez les modĂšles de Cleex

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.

Nommer une plage de cellules

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.

Trouver le gestionnaire de noms
Nommer une plage de cellules via le gestionnaire de noms

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.

définir une liste déroulante dynamique sur Excel via une plage nommée

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 « = ».

Définir une liste déroulante dynamique sur Excel via la validation des données

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 ! 👋

4.7/5 - (23 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicitĂ©. Pas besoin d’acheter, un clic suffit.

Merci. đŸ€©

Annonces

ModĂšles Excel

+

Livret 

7 méthodes pour gagner 2H par semaine sur Excel

C’est gratuit đŸ€©

Les formations

Formations Excel

Parce que chacun devrait avoir les moyens de faire son travail sur Excel

DĂ©couvrez des formations accessibles, rapides et directement applicables.