Liste déroulante sans vide dans Excel

Dernière mise à jour le 09/09/2024
Temps de lecture : 3 minutes

Comment créer une liste déroulante sans vide dans Excel ? L'astuce expliquée ici.

Pourquoi j'ai des valeurs vides dans mes menus déroulants ?

De base, la façon de créer un menu déroulant, c'est

  1. Ouvrir le menu Données > Validation de données
Menu Validation de donnees M365
  1. Choisir Liste et indiquer les références d'une plage de cellules
Menu déroulant avec références fixes

Et le résultat est tout simplement l'ajout d'un menu déroulant à l'écran

Menu déroulant dans Excel

Maintenant, si je retire une valeur de cette liste, la zone de liste =$A$2:$A$6 n'a pas été modifiée. Donc, le menu déroulant continue de récupérer les données dans cette plage, y compris les cellules vides

Menu déroulant avec vide

Techniques pour éviter les cellules vides

La solution pour éviter d'avoir des menus déroulants avec des vides c'est de rendre dynamique la zone de sélection des cellules. Et pour cela, il y a 2 solutions.

Utilisation d'un Tableau

Outre ses couleurs, l'avantage d'un Tableau c'est d'avoir des références dynamiques. En clair, les références s'adaptent à la dimension d'un Tableau.

Ainsi, en appelant dans la zone de texte les références d'un Tableau, nous pouvons créer des menus déroulants dynamiques. Ainsi, il n'y a plus de vide dans un menu déroulant.

Menu déroulant dynamique
Menu déroulant dynamique

Utilisation de la fonction UNIQUE et FILTRE

Si votre liste de données contient déjà des valeurs vides, il est préférable d'utiliser la technique pour extraire une liste de valeurs distinctes et sans vide.

=UNIQUE(FILTRE(A2:A15;NON(ESTVIDE(A2:A15))))

Liste distincte de valeurs sans vides

Intégrer le résultat dans un menu déroulant

Avec les fonctions matricielles dynamiques, il y a une astuce pour "lire" toutes les cellules retournées. Il faut utiliser les références étendues avec le #

Référence étendue pour intégrer une formule matricielle dans un menu déroulant

En effet, on ne sait pas combien de cellules la fonction matricielle va retourner. Donc, le # va faire le travail de retourner toutes les cellules à partir de la première (ici C2).

Et tout simplement, avec l'une de ces formules et une référence étendue, nous pouvons construire un menu déroulant à partir d'une liste de valeur distinctes et sans vide.

Menu déroulant avec une liste de valeurs disctinctes sans vide

Articles liés

9 Comments

  1. BLAISE
    27/10/2022 @ 10:01

    Bonjour je voudrais appliquer cette validation sur un fichier à plusieurs liste déroulante, mais cela ne fonctionne que pour une seule (utilisation du tableau). Ne peut-on pas sur une même feuille avoir plusieurs listes déroulantes qui font appel à des données différentes ?

    Reply

  2. Turquoise
    06/05/2022 @ 13:28

    Merci beaucoup pour la fonction UNIQUE et FILTRE !!!

    Reply

  3. Sauveur
    05/07/2021 @ 20:54

    Mois j'ai une autre préoccupation. J'ai une liste de personnes appartenant à plusieurs régions. Cependant, je chercher comment numéro mes individues conformément à leurs appartenances régionale.

    Par exemple si l'individu 1 (i1) est de la région 1 (R1) il devient le numéro 1 et si l'i2 est de la région r2 il devient aussi numéro 1 pour cette région. Néanmoins, si l'i3 est de la r1 il devient le numéro 2. Comment faire

    Reply

  4. Julien Kighelman
    20/05/2021 @ 16:14

    Bonjour, je rencontre le même soucis. Le problème est que la colonne sélectionnée n'est pas une liste mais le résultat d'une formule. donc les cellules vides sont équivalentes à une valeur "vide" ou "0" et apparait donc après le filtre ....

    Reply

  5. Bernard
    04/03/2021 @ 16:14

    Bonjour,
    En effet cela répond à des soucis que j'avais, cependant moi aussi j'ai une cellule avec "0" qui s'affiche, et je pense que c'est parce que la cellule comporte une formule "SI" dont le résultat est "".
    Y a-t-il une possibilité de considérer la cellule comme vraiment vide ?

    Reply

  6. dave
    03/02/2021 @ 04:11

    =UNIQUE(FILTRE(A2:A15;NON(ESTVIDE(A2:A15))))
    office 2019 fonction invalid

    Reply

    • Frédéric LE GUEN
      04/02/2021 @ 09:06

      UNIQUE et les autres fonctions matricielles dynamiques ne sont accessible que pour Office 365

      Reply

  7. Sebastien
    17/11/2020 @ 16:44

    Bonjour,

    Merci beaucoup pour cette fonction au top.
    Je viens de l'utiliser, et cela fonctionne super, sauf sur une colonne ou j'ai quand même une cellule vide qui apparait, et je ne comprends pas pourquoi

    Reply

    • Frédéric LE GUEN
      17/11/2020 @ 17:24

      C'est étonnant que ça fonctionne partout sauf pour une cellule. Il doit y avoir un espace dans la cellule qui explique le soucis. Sinon, il n'y a pas de raison que ça ne fonctionne pas

      Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Microsoft MVP 2024

Newsletter

1 fois par mois :
Astuces et quiz

    Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

    Liste déroulante sans vide dans Excel

    Reading time: 3 minutes
    Dernière mise à jour le 09/09/2024

    Comment créer une liste déroulante sans vide dans Excel ? L'astuce expliquée ici.

    Pourquoi j'ai des valeurs vides dans mes menus déroulants ?

    De base, la façon de créer un menu déroulant, c'est

    1. Ouvrir le menu Données > Validation de données
    Menu Validation de donnees M365
    1. Choisir Liste et indiquer les références d'une plage de cellules
    Menu déroulant avec références fixes

    Et le résultat est tout simplement l'ajout d'un menu déroulant à l'écran

    Menu déroulant dans Excel

    Maintenant, si je retire une valeur de cette liste, la zone de liste =$A$2:$A$6 n'a pas été modifiée. Donc, le menu déroulant continue de récupérer les données dans cette plage, y compris les cellules vides

    Menu déroulant avec vide

    Techniques pour éviter les cellules vides

    La solution pour éviter d'avoir des menus déroulants avec des vides c'est de rendre dynamique la zone de sélection des cellules. Et pour cela, il y a 2 solutions.

    Utilisation d'un Tableau

    Outre ses couleurs, l'avantage d'un Tableau c'est d'avoir des références dynamiques. En clair, les références s'adaptent à la dimension d'un Tableau.

    Ainsi, en appelant dans la zone de texte les références d'un Tableau, nous pouvons créer des menus déroulants dynamiques. Ainsi, il n'y a plus de vide dans un menu déroulant.

    Menu déroulant dynamique
    Menu déroulant dynamique

    Utilisation de la fonction UNIQUE et FILTRE

    Si votre liste de données contient déjà des valeurs vides, il est préférable d'utiliser la technique pour extraire une liste de valeurs distinctes et sans vide.

    =UNIQUE(FILTRE(A2:A15;NON(ESTVIDE(A2:A15))))

    Liste distincte de valeurs sans vides

    Intégrer le résultat dans un menu déroulant

    Avec les fonctions matricielles dynamiques, il y a une astuce pour "lire" toutes les cellules retournées. Il faut utiliser les références étendues avec le #

    Référence étendue pour intégrer une formule matricielle dans un menu déroulant

    En effet, on ne sait pas combien de cellules la fonction matricielle va retourner. Donc, le # va faire le travail de retourner toutes les cellules à partir de la première (ici C2).

    Et tout simplement, avec l'une de ces formules et une référence étendue, nous pouvons construire un menu déroulant à partir d'une liste de valeur distinctes et sans vide.

    Menu déroulant avec une liste de valeurs disctinctes sans vide

    Articles liés

    Newsletter

    1 fois par mois :
    Astuces et quiz

      Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

      9 Comments

      1. BLAISE
        27/10/2022 @ 10:01

        Bonjour je voudrais appliquer cette validation sur un fichier à plusieurs liste déroulante, mais cela ne fonctionne que pour une seule (utilisation du tableau). Ne peut-on pas sur une même feuille avoir plusieurs listes déroulantes qui font appel à des données différentes ?

        Reply

      2. Turquoise
        06/05/2022 @ 13:28

        Merci beaucoup pour la fonction UNIQUE et FILTRE !!!

        Reply

      3. Sauveur
        05/07/2021 @ 20:54

        Mois j'ai une autre préoccupation. J'ai une liste de personnes appartenant à plusieurs régions. Cependant, je chercher comment numéro mes individues conformément à leurs appartenances régionale.

        Par exemple si l'individu 1 (i1) est de la région 1 (R1) il devient le numéro 1 et si l'i2 est de la région r2 il devient aussi numéro 1 pour cette région. Néanmoins, si l'i3 est de la r1 il devient le numéro 2. Comment faire

        Reply

      4. Julien Kighelman
        20/05/2021 @ 16:14

        Bonjour, je rencontre le même soucis. Le problème est que la colonne sélectionnée n'est pas une liste mais le résultat d'une formule. donc les cellules vides sont équivalentes à une valeur "vide" ou "0" et apparait donc après le filtre ....

        Reply

      5. Bernard
        04/03/2021 @ 16:14

        Bonjour,
        En effet cela répond à des soucis que j'avais, cependant moi aussi j'ai une cellule avec "0" qui s'affiche, et je pense que c'est parce que la cellule comporte une formule "SI" dont le résultat est "".
        Y a-t-il une possibilité de considérer la cellule comme vraiment vide ?

        Reply

      6. dave
        03/02/2021 @ 04:11

        =UNIQUE(FILTRE(A2:A15;NON(ESTVIDE(A2:A15))))
        office 2019 fonction invalid

        Reply

        • Frédéric LE GUEN
          04/02/2021 @ 09:06

          UNIQUE et les autres fonctions matricielles dynamiques ne sont accessible que pour Office 365

          Reply

      7. Sebastien
        17/11/2020 @ 16:44

        Bonjour,

        Merci beaucoup pour cette fonction au top.
        Je viens de l'utiliser, et cela fonctionne super, sauf sur une colonne ou j'ai quand même une cellule vide qui apparait, et je ne comprends pas pourquoi

        Reply

        • Frédéric LE GUEN
          17/11/2020 @ 17:24

          C'est étonnant que ça fonctionne partout sauf pour une cellule. Il doit y avoir un espace dans la cellule qui explique le soucis. Sinon, il n'y a pas de raison que ça ne fonctionne pas

          Reply

      Laisser un commentaire

      Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

      Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.