Menu déroulant sans vide

Temps de lecture : 3 minutes

Si vous créer un menu déroulant avec des références bloquées, il est possible d'avoir des vides en cas de réactualisations. Pourtant il est facile de l'éviter.

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

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

RECHERCHE D’EMPLOI AVEC