Tuto Excel : créer une liste déroulante dynamique

Dans ce tutoriel, nous vous montrons comment créer une liste déroulante dynamique dans une feuille de calcul Excel.

Comme vous le savez, la fonction de validation des données améliore l'efficacité de la saisie des données dans Excel. En utilisant cette fonctionnalité puissante d’Excel, vous réduisez les erreurs de saisie et de frappe. La validation de données offre différents types d’options que vous pouvez appliquer, mais l'une des options les plus populaires est la liste déroulante. Une liste déroulante affiche les éléments de votre choix et oblige l'utilisateur de sélectionner un élément dans la liste au lieu de taper n’importe quoi dans une cellule. Cela signifie que les données entrant dans la cellule sont contrôlées et exemptes d'interférences ou de fautes d'orthographe ! Mais, parfois besoin d'une liste déroulante liée ou dynamique.

Dans Microsoft Excel, les listes déroulantes dynamique sont un moyen très pratique de sélectionner des données sans apporter de modifications à la source. Créer une simple liste déroulante dans Excel est facile. Cependant, créer une liste déroulante dynamique a toujours été un défi. Lisez le reste du tutoriel pour vois comment faire.

Table des matières

Comment créer une liste déroulante dynamique

Etape 1 : obtenir des éléments pour la liste déroulante principale

Etape 2 : créer la liste déroulante dynamique

Méthode 1 : utiliser le nom de tableau avec la fonction « INDIRECT »

Méthode 2 : utiliser la fonction de plage nommée

Méthode 3 : utiliser les fonction DECALER et OFFSET pour définir la source

Méthode 4 : utiliser les fonction DECALER et OFFSET pour définir la plage nommée

Comment créer une liste déroulante dynamique

Par défaut, les listes déroulantes de validation des données ne sont pas dynamiques. Cela signifie qu’à chaque fois que vous obtenez un nouvel élément, vous devrais créer à nouveau votre liste déroulante de validation des données pour inclure la nouvelle entrée. Cette méthode prend du temps et n’est pas très pratique.

C'est là que vient l’utilité d’une liste déroulante de validation de données dynamique. Si vous transformez une liste simple en liste dynamique, chaque fois que vous ajoutez un nouvel élément, la liste déroulante sera automatiquement mise à jour pour l’inclure.

Pour expliquer comment créer une liste déroulante dynamique, prenons un exemple et comprenons. Comme vous le voyez dans la capture ci-dessous, nous avons une liste de produits dans la colonne A, et nous souhaitons avoir la liste déroulante dynamique des produits dans la cellule « D2 » :

Pour créer une liste déroulante dynamique à partir de cette liste de produits, suivez le processus ci-dessous.

Etape 1 : obtenir des éléments pour la liste déroulante principale

Pour commencer, vous devez d’abord extraire tous les différents noms de produits de la colonne « A ». Cela peut être fait en utilisant la fonction de filtre avancé. Le processus est très simple, suivez les étapes ci-dessous :

  1. Cliquez sur la cellule contenant l’en-tête de la colonne contenant les éléments à filtrer (la cellule « A1» dans notre exemple) :
  1. Accédez ensuite à l’onglet « Données» et cliquez sur la commande « Avancé » dans le groupe « Trier et filtrer » :
  1. La boite de dialogue « Filtre avancé» s’ouvre. Sélectionnez le radio bouton « Copier vers un autre emplacement » :
  1. Vérifiez que la plage de cellules à filtrer est correcte. Dans notre exemple, le plage est « A1:A13» :
  1. Cliquez à l’intérieur de la zone « Copier dans» et puis choisissez la cellule dans laquelle vous souhaitez effectuer la copie (la cellule « C4 » dans notre exemple) :
  1. Cochez la case en regard de l’option « Extraction sans doublon» et cliquez sur « Ok » pour valider :

Et voilà, la liste des éléments est prête, nous pouvons maintenant commencer à créer la liste déroulante.

Etape 2 : créer la liste déroulante dynamique

Pour créer la liste déroulante dynamique, utilisez l’une des méthodes qui suivent.

Méthode 1 : utiliser le nom de tableau avec la fonction « INDIRECT »

Cette méthode utilise les tableaux Excel en combinaison avec la fonction « INDIRECT ».

  1. Tout d'abord, nous allons créer une table. Sélectionnez la plage de cellule contenant les éléments à utiliser comme entrées dans la liste déroulante. Dans cet exemple, nous sélectionnons la plage des produits uniques « C4:C10»
  2. Accédez ensuite à l'onglet « Insertion» puis cliquez sur le bouton de commande « Tableau ». Alternativement, appuyez sur le raccourci clavier « Ctrl + L » :
  1. Après avoir cliqué sur le bouton « Tableau», la boite de dialogue « Créer un tableau » apparaît. Assurez-vous que la plage pour laquelle vous voulons insérer le tableau est correcte :
  1. Cliquez sur « OK»
  2. Vous pouvez voir dans la capture ci-dessous que la plage « C4:C10» a été convertie en tableau, et l'en-tête de ce tableau « Produits » a également une option de liste déroulante de filtre :

L’avantage des tableau Excel est qu’à chaque fois que vous ajoutiez un produit ou un article au bas de la liste, le tableau se développera automatiquement pour inclure ce nouveau produit.

  1. Une fois le tableau prêt, nous pouvons l’utiliser pour créer la liste déroulante dynamique. Sélectionnez la cellule dans laquelle vous souhaitez placer la liste déroulante. Nous sélectionnons la cellule « D2».
  2. Ouvrir la boîte de dialogue de validation des données, en cliquant sur la commande « Validation de données» dans le groupe « Outils de données » de l’onglet « Données » :
  1. La boite de dialogue « Validation des données ».Dans la liste déroulante « Autoriser », sélectionnez « Liste » :
  1. Ensuite entrez la formule suivante =INDIRECT("Tableau1")dans la zone de texte « Source » :

Remarque : dans la formule « Tableau1 » est le nom du tableau que nous venons de créer. Généralement lorsque vous créez un premier tableau dans le classeur, son nom est « Tableau1 », mais, si vous avez déjà créé d’autres tableaux, le nom sera différent.

  1. Cliquez sur « OK» pour valider. La capture ci-dessous montre la liste déroulante résultante :
  1. Maintenant, lorsque vous ajoutiez de nouveaux produits dans la liste des produits, la liste déroulante sera mise à jour automatiquement pour l’incorporer. Vous pouvez voir dans la capture d’écran ci-dessous que le nouveau produit ajouté apparaît dans la liste déroulante :

Méthode 2 : utiliser la fonction de plage nommée

Dans cette deuxième méthode, nous fournirons le nom de la table en tant que plage nommée. Notre tableau possède déjà un nom, à savoir « Tableau1 », mais ici nous devons définir le nom de cette table pour obtenir la liste déroulante dynamique ; suivez les étapes ci-dessous :

  1. Pour commencer, sélectionnez la plage contenant les éléments à inclure dans la liste du premier produit au dernier produit. Ne sélectionnez pas l’entête.

Dans cet exemple, le tableau n'a qu'une seule colonne, mais vous pouvez avoir un tableau qui contient plusieurs colonnes. Si c’est le cas, vous devrez spécifier quelle colonne du tableau contient les éléments à utiliser comme entrées dans la liste déroulante en nommant la plage de cellules.

  1. Ensuite, supprimez le texte dans la zone de nom et tapez le nom court que vous souhaitez attribuer à la plage. Par exemple « Produits».
  2. Appuyez sur « Enter» une fois terminer. Après avoir appuyé sur « Enter », nous voyons que rien n'a changé dans la zone de nom comme le montre la capture ci-dessous :

Même si le nom que vous venez d’affecter à la plage n’apparait pas, il est pris en considération par Excel. Pour vous assurer, cliquez sur l'option de liste déroulante pour voir toutes les plages nommées disponibles. En faisant ainsi, vous verrez que le nom que nous venons de définir pour cette table, apparaît également :

  1. Vous pouvez maintenant créer la liste déroulante. Cliquez sur la cellule « D2».
  2. Accédez à l’onglet « Données» et cliquez sur le bouton « Validation de données ».
  3. Sélectionnez « Liste» dans la zone « Autoriser » et puis, dans la zone « Source », tapez le signe « = » suivi du nom que vous avez attribuer au tableau :
  1. [Facultatif], vous pouvez spécifier un message d’entrée et message d’alerte à partir de l'onglet « Message de saisie» et « Alerte d’erreur ».
  2. Cliquez sur « OK» pour appliquer la validation de données.
  3. Maintenant, la cellule « D2» contient une liste déroulante dynamique. Lorsque nous ajoutons un produit dans la liste, il apparaîtra automatiquement dans la liste déroulante

Bien que ces deux premières méthodes soient faciles et pratiques, elles représentent des inconvénients. Par exemple, supposons que vous sautiez une cellule après la dernière cellule du tableau, puis ajoutiez un nouveau produit. Malheureusement, vous verrez que la plage de tableaux ne s’étend pas ! En fait, le produit nouvellement ajouté est au format général. Alors, le nouvel élément ne sera pas affiché dans la liste déroulante. Cela est dû au fait que la plage de tableau n'a rien trouvé après la toute dernière cellule et que la plage n'a donc pas été dépensée.

Vous pouvez surmonter cette imitation en utilisant des foncions plus avancées. Dans les deux méthodes suivantes, nous allons apprendre comment rendre notre liste déroulante plus dynamique en utilisant les fonctions DECALER et NBVAL.

Méthode 3 : utiliser les fonction DECALER et OFFSET pour définir la source

Pour que la liste des éléments soit dynamique, nous utiliseront les fonction DECALER et OFFSET :

  1. Sélectionnez la cellule « D2» et cliquez sur le bouton « Validation de données » dans l’onglet « Données ».
  2. La boîte de dialogue de validation des données s'ouvre. Sélectionnez « Liste» dans l'option « Autoriser ».
  3. Tapez ensuite la formule suivante dans la zone « Source» :

=DECALER($C$5;0;0; NBVAL($A:$A); 1)

  1. Une fois vous cliquez sur « OK», vous verrez une liste déroulante dans la cellule D2. Cette liste montre les éléments, y compris le blanc, puis les produits que nous avons ajoutés :

Expliquons comment fonctionne cette formule :

  • Nous fournissons comme premier argument de la fonction DECALER, la cellule « C5 », qui est le premier produit dans notre plage ;
  • Dans le deuxième argument nous tapons « 0 » car nous ne voulons pas déplacer la ligne à partir du point de départ ;
  • Nous tapons un autre «  » dans le troisième argument car nous voulons garder dans le nombre de colonnes fixe ;
  • Dans le quatrième argument, nous utilisons la fonction NBVAL. Le premier argument de cette fonction vérifiera la hauteur en nombre de lignes de la colonne « C » pour renvoyer le nombre de cellules non vide. Cela permet d’élargir la plage lorsque des modifications sont apportées à la plage.
  • Le dernier argument définit la largeur en nombre de colonnes. Vous pouvez laisser cet argument vide et Excel utilisera la largeur de la plage retournée que nous avons fournie dans l'argument. Dans l’exemple, nous avons tapé 1.

Méthode 4 : utiliser les fonction DECALER et OFFSET pour définir la plage nommée

Dans cet exemple, nous utiliserons les mêmes fonctions pour définir le nom de la plage.

  1. Accédez à l’onglet « Formules» et cliquez sur le bouton « Gestionnaire de noms » dans le groupe « Noms définis » :
  1. La boîte de dialogue « Gestionnaire de noms» apparaîtra. Cliquez sur le bouton « Nouveau » :
  1. Tapez le nom que vous souhaitez attribuer à la plage, par exemple « NomsProduits».
  2. Entrez ensuite la formule ci-dessous dans la zone « Fait référence à» :

=DECALER($C$5; 0; 0; NBVAL($A:$A))

  1. Cliquez sur « OK» pour terminer.
  2. Cliquez sur la cellule D2, accédez à l’onglet « Données » et cliquez sur le bouton « Validation des données».
  3. Dans la boite de dialogue, sélectionnez « Liste» dans la liste déroulante « Autoriser ».
  4. Tapez ensuite la formule « =NomsProduits» dans la zone de texte « Source » :
  1. Cliquez sur « OK». La liste déroulante apparaitra dans la cellule D2 et si vous ajoutez de nouveaux éléments dans la plage, la liste déroulante de met à jour pour les afficher.

Conclusion

C'est ainsi que vous pouvez créer une liste déroulante dynamique de tout produit ou article avec différentes méthodes en utilisant la validation des données. Vous pouvez utiliser la méthode qui convient le plus à vos besoins et à vos préférences.

Article publié le 05 Avril 2021par Hanane Mouqqadim