Ces tableaux sont régulièrement alimentés avec des données présentant souvent des redondances et une même valeur peut-être saisie différemment d'un utilisateur à un autre.

Voici un exemple :

Pour désigner l'état d'une commande en cours, les utilisateurs d'un même classeur utilisateurs pourraient saisir : "En cours", "En cours de traitement", "En cours de livraison"… pour désigner une même valeur.

Ce qui engendre non seulement une perte de temps lors de la saisie ou bien lors de l'analyse des données, mais aussi un risque d'erreur et de confusion.

La solution est de créer une liste déroulante permettant de choisir parmi plusieurs valeurs prédéfinies.

Ainsi on n'aura pas à saisir la même valeur plusieurs fois de différentes manières pour finalement dire la même chose.

En plus, grâce à cette méthode, il est possible d'imposer aux utilisateurs les valeurs à saisir pour limiter les erreurs de saisie et faciliter ainsi l'analyse des données.

Comment Créer une liste déroulante Excel

Méthode n°1: La Méthode Simple

Voici la liste déroulante que nous allons créer :

Voici comment procéder :

Étape n°1 - Créer les valeurs de la liste

Comme exemple, nous allons reprendre l'exemple que j'ai cité un peu plus haut.

Le but étant de créer une liste déroulante pour désigner l'état d'une commande en cours.

Les valeurs de la liste sont doivent être créées sous une autre feuille séparée qui peut être protégée pour des raisons de confidentialité ou d’ordre pratique.
Donc, pour commencer, créer une nouvelle puis sélectionnez la.

Définir toutes les valeurs possibles pour désigner l'état de la commande et les saisir sur la feuille de calcul:

Au besoin, le tri par ordre alphabétique pourrait faciliter aux utilisateurs le choix et la recherche des valeurs.
Pour cela, cliquez sur le menu "Données" puis sur l'option "Trier".

Étape n°2 - Créer la liste déroulante

Sélectionnez la première cellule de la feuille destinée à votre liste déroulante.

Sélectionnez la première cellule de la feuille destinée à votre liste déroulante. Et saisissez le libellé de la liste, soit "État de la commande".

Sélectionnez ensuite la cellule B1 (ou la cellule juste après le libellé) puis cliquez sur l’onglet "Données" et sur "Validation des données"

Sélectionnez « Liste » parmi les critères de validation au niveau de l’onglet option sans modifier les critères cochés par défaut.

Cliquez sur la partie "Source" de la fenêtre

Sélectionnez la liste de mots créée dans la deuxième feuille.

Fermez la petite fenêtre puis validez par le bouton "Ok".

Afin d’optimiser l’expérience utilisateur, vous pouvez paramétrer les onglets « Message » et « Alerte d’erreur » avec des aides à la saisie (cette étape est facultative).

Il s’agit tout simplement de créer un message d'aide qui apparaîtra lorsque l’utilisateur sélectionne la cellule.

Voici le résultat :

Les alertes suivent le même principe.

Il s'agit de créer un message qui alerte un utilisateur quand il saisit des données non valides.

Le résultat pour notre exemple :

Étape n°3 - Tester votre liste déroulante

C'est une étape déterminante.

Vous devez tester afin de vérifier si tout fonctionne correctement et si vous n'avez pas besoin de réajuster quelques critères avant de partager et diffuser votre feuille de calcul.

Méthode n°2: La Méthode Professionnelle

La deuxième méthode que je vais vous montrer est nettement plus rapide.

Mais le grand avantage de cette méthode est qu'elle permet de vraiment organiser son tableau de bord surtout en présence de plusieurs listes déroulantes.

Le concept est de donner un nom à la liste après avoir saisi ses différentes valeurs dans la feuille source et l'utiliser pour la liste.

Voici comment faire :

Sélectionner la liste des valeurs de la feuille source et cliquez avec le bouton droit de la souris pour activer l'option "Définir un nom"

Donnez un nom significatif et facile à retenir à votre liste (Choisissez toujours un libellé pertinent, surtout si vous avez plusieurs menus déroulants à créer).

Revenez vers la feuille initiale, et sélectionnez la cellule destinée à recevoir la liste déroulante. Et de la même manière que dans la première méthode, cliquez sur « validation des données » sur « liste ».
Au lieu de spécifier la plage de cellules source, il suffit juste d'écrire "= le nom de la liste". Donc, pour notre exemple, il suffit d'écrire "=état" ou d'appuyer sur la touche F3 pour choisir dans la liste des noms disponibles.

Le reste des étapes se déroule de la même façon que pour méthode n°1.

Méthode N°3 : Utiliser Des Formules Excel

Cette méthode à l'avantage de créer directement une liste déroulante sans passer par une feuille intermédiaire ou par un quelconque nom assigné à une plage de données.

Le but étant d'afficher sur une colonne B un menu déroulant en fonction des valeurs déjà saisies manuellement sous une colonne A .

Je vous l'explique grâce à cet exemple :

Voici la disposition des données que nous allons utiliser:

La manœuvre consiste à obtenir la liste déroulante faisant référence à la la cellule A2 avec le libellé "Etat" sur la cellule B2.

Et de même, la cellule B3 doit contenir la liste déroulante avec les références des commandes.

Commencez par sélectionner la cellule B2, de cliquer sur le menu "Données"puis "Validation de données".

La formule à insérer est donc "=SI($A$2="Etat";$C$2:$C$5;$D$2:$D$5) " (adaptez la formule selon les emplacements des valeurs).

La formule signifie que si la cellule A2 contient le mot "Etat" alors le menu déroulant contiendra les valeurs de la colonne C.

Donc, en toute logique, si vous saisissez une autre valeur dans la cellule "A2", la liste changera.

Et le deuxième menu :

Parmi les trois méthodes, c'est la deuxième qui est plus pratique d'un point vue organisation et référence.

Puisque le fait de nommer ses listes pour pouvoir les utiliser à tout moment est nettement plus efficace pour des tableaux de bord de grande envergure.

Comment Modifier Une Liste Déroulante Excel

Pour être optimisé, un tableau bord, doit s'adapter à chaque situation.

Il doit donc être en continuelle évolution.

Et une telle évolution implique des changements aussi fréquents que nécessaire.

Ajouter Un Élément

La Méthode Simple

Si vous voulez rajouter la valeur « annulée » à la liste de valeurs source, vous pouvez tout simplement la saisir sur la cellule qui suit le menu déjà créé.

Il suffit ensuite de recommencer la procédure de création, soit passer par le menu "Données" et "Validation de données" (méthode n°1 de la création d'une liste déroulante).

La Méthode Professionnelle

Comme je l'ai déjà mentionné, il vaut lieux attribuer un nom pour chaque liste.

C'est nettement plus pratique et ça facilite surtout la manipulation des listes déroulantes.

Référez vous à la partie "Création d'une liste déroulante" de ce tuto pour savoir comment donner un nom à une liste.

Commencez par ajouter une valeur à la liste en question :

Cliquez ensuite sur l'onglet "Formule" puis "Gestionnaire des noms" et sélectionnez le nom concerné "Etat"

Sélectionnez maintenant la nouvelle liste dans le champ « fait référence à »:

Cliquez enfin sur fermer et votre liste déroulante sera modifiée:

Modifier Un Élément

Pour changer l'une des valeurs de la source de la liste, il suffit de changer la valeur et la liste déroulante se mettra à jour automatiquement.

si par exemple, on devrait changer "En cours de traitement" par "En cours d'étude":

Sélectionnez la première cellule de la source.

Changez la valeur "En cours d'étude"

La liste déroulante se met à jour instantanément.

Modifier La Référence De La Liste Déroulante

Il s'agit maintenant de changer la liste déroulante elle même.

Comme exemple, nous allons changer la liste déroulante de l'état de la commande par la liste des numéros des commandes.

Pour cela, commencez par donner des noms à vos listes :

Nous allons changer la liste affichée par le Menu déroulant.

Sélectionnez la cellule cible (Celle qui contiendra la liste déroulante) et cliquez sur le menu "Données" puis sur l'option "Validation de données".

Changez maintenant le nom de liste en écrivant "=Numéro" qui est le nom de la deuxième liste.

Cochez "Appliquer ces modifications aux cellules de paramètres identiques" si vous avez plusieurs cellules qui pointent sur la liste et que vous voudriez éventuellement changer.

Supprimer Un Élément

Pour supprimer une valeur existante sur un menu défilant, il n’y a pas plus simple.

Il suffit de cliquer sur le bouton droit puis sur supprimer pour déplacer les cellules vers le haut et votre élément disparaîtra du menu défilant.

Comment Supprimer Une Liste Déroulante

Il suffit de sélectionner la ou les cellules contenant les menus déroulants à supprimer puis cliquer sur l’onglet "Données" ensuite "Validation des données" et enfin cliquer sur "Effacer tout"

Comment Créer Une Liste Déroulante Dynamique

Une liste déroulante dynamique à l'avantage de s'actualiser toute seule après chaque mise à jour sans pour autant refaire toute la procédure de création.

Pour être plus clair, je vous ai fait un petit comparatif entre une liste simple et une liste dynamique.

Voici la liste simple :

Et voici la liste dynamique :

Pour créer une liste dynamique :

Saisissez les données sources pour la liste et transformez la en Tableau.
Pour cela, sélectionnez la liste et cliquez sur le menu "Insertion" puis sur le raccourci "Tableau"

N'oubliez pas de cocher la case pour l'entête

Préparez la cellule qui va contenir la liste déroulante et cliquez sur le menu "Données" puis "Validation de données"

Activer l'option "Liste" et dans la source écrivez la commande suivante : =INDIRECT("Tableau1") où "Tableau1" est le nom de votre tableau.

Votre liste est créée et elle est totalement dynamique, ce qui veut dire qu'elle s'auto-actualise.

En ce qui concerne le nom du tableau, vous pouvez l'afficher ou le modifier en cliquant sur l'entête du tableau et en activant l'onglet "Création" :

Une liste dynamique est aussi appelée une liste conditionnelle ou encore une liste en cascade.

C'est essentiellement pour mettre en évidence l'utilité de cd type de liste.

Un Super Exemple Pratique

Pour vraiment comprendre l'importance des listes en cascade, voici un exemple pratique :

Comme vous le voyez, les listes sont dynamiques mais ce qui est vraiment pratique c'est que la liste déroulante des membres est totalement dépendante de la liste des agences.

Pour y arriver, la procédure est la suivante :

Saisissez les sources de listes avec des noms ne comportant ni des espaces ni des caractères spéciaux. Le seul séparateur est le tiret "_" .

Veillez bien à ce que les noms des colonnes soient les mêmes comme le montre l'image.
Sélectionnez ensuite les quatre listes en appuyant sur la touche "Ctrl" en même temps que la sélection.

Il faut maintenant donner un nome à chaque liste. Pour cela, cliquez sur le menu "Formule" puis dans "Gestionnaire de noms" cliquez sur "Depuis sélection".

Cochez ensuite la case "Ligne du haut" et validez.

Les noms sont créés automatiquement.

Sélectionnez ensuite la cellule qui va contenir la liste des agences, soit la cellule C9 pour notre exemple. Cliquez sur le menu "Données" puis sur "Validation des données"

Activer une liste puis cliquez dans la zone "Source" et appuyez sur la touche F3 pour afficher les noms des listes disponibles puis choisissez la liste "Traitement" .

Vous devez avoir l'expression =Traitement dans la zone "Source".

Si tout s'est bien passé, la première liste déroulante doit être fonctionnelle.

Sélectionnez maintenant la cellule qui doit contenir la deuxième liste déroulante, soit la cellule D9. Et de la même façon afficher la fenêtre de validation de données.

Activer l'option "Liste" et dans la zone "Source" écrivez la fonction =INDIRECT() . Cliquez entre les parenthèses puis cliquez sur la cellule C9. Au final, vous devez avoir cette formule "=INDIRECT($C$9)". Les parenthèses sont optionnelles mais il vaut mieux les mettre.

Cliquez sur "Oui" dans la fenêtre qui apparaît.

Votre liste en cascade est enfin prête. Vous pouvez ajouter autant de membres que vous voulez, ils seront toujours affichés en temps réel.

Mise en Forme Conditionnelle D’une Liste déroulante

La mise en forme est très simple.

Elle suit le même principe que tous les produits office avec les outils classiques.

Mais il existe une autre possibilité très pratique qu'on peut utiliser avec les listes déroulantes: La mise en forme conditionnelle.

Elle permet de personnaliser l’affichage des valeurs pour gagner en visibilité et surtout clarifier certains indicateurs clés afin de faciliter la création d'un bilan statistique éventuel.

Suivez ces étapes pour utiliser une mise en forme conditionnelle :

Nous allons utiliser la liste déroulante "Traitement" que nous avons créé au préalable.

Le but étant de créer une liste défilante avec des couleurs spécifiques pour chaque valeur. Donc, au final, chacune des valeurs de la liste aura une couleur propre à elle.
Sélectionnez la première cellule de la liste puis cliquez sur l'onglet "Accueil" puis sur "Mise en forme conditionnelle" et enfin sur "Gérer les règles"

Choisissez ensuite "Sélection actuelle" puis cliquez sur le bouton "Nouvelle règle"

Dans la liste qui s'affiche, choisissez "Utiliser une formule pour déterminer pour quelle cellule le format sera appliqué".

Cliquez dans la zone de la formule, cliquez sur la cellule de la liste déroulante pour afficher $A$2 puis finissez par écrire ="Agence_A" pour avoir la formule =$A$2="Agence_A". Si vous prévoyez de reproduire la même liste ou de la copier, il vaudrait mieux écrire la formule sans référence absolue, soit =A2="Agence_A".
Cliquez ensuite sur le bouton "Format" pour fixer une mise en forme.

formule-mise-en-forme-conditionnelle-liste

Pour la mise en forme, il suffit de choisir les couleurs. Par exemple, si la liste affiche pour valeur "Agence_A", je veux qu'elle se présente avec un remplissage rouge et une écriture blanche.

format-mise-en-forme-conditionnelle-liste

Recommencez les mêmes étapes pour les autres valeurs de la liste.

Cette mise en forme attribue la couleur définie en fonction de l’élément sélectionné de notre menu défilant:

Comment Créer Un Filtre Dans Une Liste Déroulante

Pour filtrer les données d'une liste déroulante, il suffit de sélectionner la colonne du menu déroulant et de cliquer sur l'option "Filtrer" du menu "Données".

Le filtre permet d'appliquer plusieurs options pour ordonner les données.

Utiliser La Valeur D’Une Liste Dans Une Formule

Notre liste dépendante est créée, mise en forme et filtrée, Excel nous permet également d’utiliser les données de cette liste dans des formules.

Il serait, par exemple, intéressant de ressortir le nombre de commandes en cours de traitement par Agence à l’aide de la fonction "NB.Si".

La fonction "NB.Si" permet d'afficher le nombre de cellules répondant à un critère précis.

Pour appliquer la formule, nous allons commencer par créer cette feuille :

Pour copier la liste, il suffit de la sélectionner et de faire glisser (même un copier/coller fonctionne).

Pour appliquer la fonction :

Sélectionnez la cellule cible, soit C6 et introduisez la formule en écrivant =NB.SI(A2:A21;"Agence_A")

Le résultat est instantané.

Recommencez avec les Agences B et C mais en modifiant la valeur du critère pour chaque agence.
=NB.SI(A2:A21;"Agence_B") pour l'Agence_B
=NB.SI(A2:A21;"Agence_C") pour l'Agence_C

La Super Astuce : Une Liste Déroulante Avec Des Images

Pour finir ce tuto, je vais vous montrer comment associer des images à une liste déroulante Excel.

C'est une fonctionnalité sans égal pour les adeptes du graphique.

Et le résultat est vraiment spectaculaire.

Comme exemple pratique, nous allons combiner les logos des agences à leurs noms.

Ce qui veut dire que lorsqu'une agence est sélectionnée le logo correspondant s'affiche également comme ceci:

Pour réaliser ce type de liste déroulante :

Créez la liste des agences et donnez lui le nom "Les_agences"

Insérez les images grâce à l'onglet "Insertion" puis "Illustration" et enfin "Images". Spécifiez que la source est votre appareil.

Ajustez les cellules de façon à ce qu'elles soient chacune dans une cellule à part.

Dans la cellule C3, soit celle qui est juste après la première image, écrivez =CELLULE("adresse";B2). Cette fonction a pour rôle d'attacher une image à une adresse.

Appliquez la formule sur les deux autres cellules pour référencer les images.

Créez maintenant la liste déroulante des agences.

Dans le gestionnaire de noms, cliquez sur "Nouveau nom".
Dans la fenêtre qui apparaît, donnez "images" comme nouveau nom et dans la zone de source écrivez cette fonction: =INDIRECT(RECHERCHEV(Feuil1!$G$7;Feuil1!$A$2:$C$4;3;0))

La fonction INDIRECT permet de récupérer le contenu de l'adresse indiquée.

La fonction RECHERCHEV permet d'effecteur une recherche dans un tableau donné et de renvoyer le contenu désiré.

Voici la syntaxe de base: =RECHERCHEV(cellule_source ; Tableau ; Colonne,Précision)

En l'appliquant sur notre exemple, nous avons spécifié la Cellule_source, soit la liste déroulante des agences.

Puis nous avons indiqué le tableau concerné qui est celui composé des trois colonnes: Les_agences, images et adresses des images.

Ensuite, nous avons sélectionné la colonne du contenu concerné, soit la colonne numéro 3 qui contient les images.

Le dernier paramètre demande à Excel d'afficher le contenu exact.

Pour continuer, il est nécessaire d'ajouter l'outil "Appareil photo" au ruban principal. Cliquez donc sur le ruban avec le bouton droit de la souris puis sur "Personnaliser le ruban"

Dans la liste qui s'affiche, choisissez "Toutes les commandes" puis cherchez la commande "Appareil photo" grâce à l'ordre alphabétique des commandes.

Pour ajouter la commande, créez un nouveau groupe de commande en appuyant sur le bouton "Nouveau groupe" puis cliquez sur "Ajouter". N'oubliez pas de spécifier dans quel onglet vous voulez ajouter votre groupe de commandes.

Cliquez sur la commande "Appareil photo" et écrivez =images Soit le nom de la liste des images que vous avez créé au tout début.

Il ne vous reste plus qu'à personnaliser votre affichage avec les outils de mise en forme classique.

Conclusion

Pour conclure, j'ai essayé de clarifier au maximum cette notion de liste déroulante Excel.

Il est évident qu'un seul article ne pourra jamais venir à bout de ce monstre qu'est Excel mais espérons que vous trouverez dans cet article une lueur d'espoir.

Et si vous avez une contribution quelconque pour encore enrichir cet article, partagez le savoir.

Devenez Un Expert Incontournable De La Liste Déroulante Excel

L'Utilité D’Une Liste Déroulante Excel