Comment créer une liste déroulante Excel dynamique et intuitive ?

Qu'est-ce qu'une liste déroulante Excel et pourquoi l'utiliser ?

Une liste déroulante Excel, également appelée menu déroulant ou liste de validation, est un contrôle qui permet à l'utilisateur de sélectionner une valeur parmi une liste prédéfinie. Au lieu de saisir manuellement une information, l'utilisateur choisit simplement l'option souhaitée dans la liste.

Avantages de l'utilisation des listes déroulantes :

• Réduction des erreurs de saisie : En limitant les options possibles, vous évitez les fautes de frappe et les incohérences dans vos données.
• Uniformisation des données : Les listes déroulantes garantissent que les données sont saisies de manière uniforme, ce qui facilite l'analyse et le traitement des informations.
• Gain de temps : La sélection d'une option dans une liste est plus rapide que la saisie manuelle, surtout pour les données répétitives.
• Amélioration de l'ergonomie : Les listes déroulantes rendent vos feuilles de calcul plus conviviales et intuitives pour les utilisateurs.

Créer une liste déroulante simple dans Excel

Voici les étapes pour créer une liste déroulante de base dans Excel :

1. Sélectionnez la cellule où vous souhaitez insérer la liste déroulante. Par exemple, la cellule A1.
2. Accédez à l'onglet "Données" dans le ruban Excel.
3. Cliquez sur "Validation des données" dans le groupe "Outils de données". Une nouvelle fenêtre s'ouvre.
4. Dans la fenêtre "Validation des données", sélectionnez "Liste" dans le menu déroulant "Autoriser".
5. Dans le champ "Source", saisissez les éléments de votre liste, séparés par des virgules. Par exemple : Oui,Non,Peut-être. Vous pouvez également sélectionner une plage de cellules contenant les éléments de votre liste. Pour cela, cliquez sur l'icône à droite du champ "Source" et sélectionnez la plage de cellules souhaitée.
6. Cliquez sur "OK" pour valider la création de la liste déroulante.

Maintenant, la cellule A1 contient une liste déroulante avec les options que vous avez définies. Lorsque vous cliquez sur la cellule, une petite flèche apparaît, vous permettant de sélectionner une option dans la liste.

Personnaliser le message d'entrée et le message d'erreur

Pour rendre votre liste déroulante encore plus conviviale, vous pouvez personnaliser le message d'entrée et le message d'erreur :

• Message d'entrée : Ce message s'affiche lorsque l'utilisateur sélectionne la cellule contenant la liste déroulante. Il peut contenir des instructions ou des informations sur les options disponibles.
• Message d'erreur : Ce message s'affiche lorsque l'utilisateur tente de saisir une valeur non autorisée dans la cellule.

Pour personnaliser ces messages, retournez dans la fenêtre "Validation des données" (Onglet "Données" > "Validation des données").

• Onglet "Message de saisie" : Cochez la case "Afficher le message de saisie quand la cellule est sélectionnée". Saisissez un titre et un message dans les champs correspondants.
• Onglet "Alerte d'erreur" : Cochez la case "Afficher l'alerte d'erreur si des données non valides sont saisies". Choisissez un style d'alerte (Arrêt, Avertissement, Information) et saisissez un titre et un message dans les champs correspondants.

Créer une liste déroulante dynamique avec la fonction DECALER

Une liste déroulante dynamique s'adapte automatiquement lorsque vous ajoutez ou supprimez des éléments dans la plage de cellules source. Pour créer une liste déroulante dynamique, vous pouvez utiliser la fonction DECALER combinée à la fonction NBVAL.

1. Définissez une plage de cellules contenant les éléments de votre liste. Par exemple, les cellules B1 à B10 contiennent les noms de vos produits.
2. Nommez la plage de cellules. Sélectionnez la plage B1:B10 et dans la zone "Nom" (en haut à gauche de la feuille, à côté de la barre de formule), tapez un nom, par exemple "Produits". Appuyez sur Entrée pour valider.
3. Accédez au Gestionnaire de noms. Dans l'onglet "Formules", cliquez sur "Gestionnaire de noms".
4. Cliquez sur "Nouveau...".
5. Dans la fenêtre "Nouveau nom", saisissez un nom pour votre liste dynamique, par exemple "ListeProduits".
6. Dans le champ "Fait référence à", saisissez la formule suivante :

=DECALER(Produits;0;0;NBVAL(Produits);1)

• DECALER(Produits;0;0;NBVAL(Produits);1) : Cette formule crée une plage dynamique qui commence à la première cellule de la plage nommée "Produits", ne décale ni les lignes ni les colonnes (0;0), et s'étend sur le nombre de cellules non vides dans la plage "Produits" (NBVAL(Produits)) et sur une seule colonne (1).
• Cliquez sur "OK" pour enregistrer le nom.
• Créez la liste déroulante. Sélectionnez la cellule où vous souhaitez insérer la liste déroulante (par exemple, A1). Accédez à l'onglet "Données" > "Validation des données".
• Dans la fenêtre "Validation des données", sélectionnez "Liste" dans le menu déroulant "Autoriser".
• Dans le champ "Source", saisissez la formule suivante : =ListeProduits
• Cliquez sur "OK".

Maintenant, votre liste déroulante se mettra automatiquement à jour lorsque vous ajouterez ou supprimerez des éléments dans la plage de cellules B1:B10.

Explication détaillée de la formule DECALER et NBVAL

• DECALER(référence; lignes; colonnes; hauteur; largeur) : La fonction DECALER renvoie une référence à une plage de cellules qui est décalée d'un certain nombre de lignes et de colonnes par rapport à une référence de départ.
  • référence : La cellule ou la plage de cellules de départ.
  • lignes : Le nombre de lignes à décaler (positif pour descendre, négatif pour monter).
  • colonnes : Le nombre de colonnes à décaler (positif pour aller à droite, négatif pour aller à gauche).
  • hauteur : La hauteur de la plage résultante (en nombre de lignes).
  • largeur : La largeur de la plage résultante (en nombre de colonnes).
• NBVAL(valeur1; [valeur2]; ...) : La fonction NBVAL compte le nombre de cellules non vides dans une plage.

En combinant ces deux fonctions, nous créons une plage dynamique dont la hauteur s'adapte automatiquement au nombre d'éléments dans la liste.

Créer une liste déroulante dépendante

Une liste déroulante dépendante est une liste dont les options disponibles dépendent de la sélection faite dans une autre liste déroulante. Par exemple, vous pouvez avoir une première liste déroulante avec les régions, et une deuxième liste déroulante qui affiche les villes de la région sélectionnée.

Pour créer une liste déroulante dépendante, vous pouvez utiliser la fonction INDIRECT combinée à des plages nommées.

1. Créez un tableau avec les données pour vos listes déroulantes. Par exemple :

1. Nommez les plages de cellules correspondant à chaque région. Sélectionnez la plage de cellules contenant les villes de la Bretagne (Rennes, Brest, Nantes) et nommez-la "Bretagne" (comme la valeur dans la colonne Région). Faites de même pour la Normandie (Caen, Rouen, Le Havre) et nommez-la "Normandie". Assurez-vous que les noms des plages correspondent exactement aux valeurs de la première liste déroulante (Région).
2. Créez la première liste déroulante (Région). Sélectionnez la cellule où vous souhaitez insérer la première liste déroulante (par exemple, A1). Accédez à l'onglet "Données" > "Validation des données". Dans la fenêtre "Validation des données", sélectionnez "Liste" et saisissez les régions (Bretagne, Normandie) comme source, séparées par des virgules.
3. Créez la deuxième liste déroulante (Ville). Sélectionnez la cellule où vous souhaitez insérer la deuxième liste déroulante (par exemple, B1). Accédez à l'onglet "Données" > "Validation des données". Dans la fenêtre "Validation des données", sélectionnez "Liste" et saisissez la formule suivante dans le champ "Source" :

=INDIRECT(A1)

• INDIRECT(A1) : Cette formule renvoie la référence à la plage nommée dont le nom est égal à la valeur de la cellule A1 (la région sélectionnée dans la première liste déroulante).
• Cliquez sur "OK".

Maintenant, lorsque vous sélectionnez une région dans la première liste déroulante (A1), la deuxième liste déroulante (B1) affichera uniquement les villes de cette région.

Erreurs courantes et comment les éviter

• Erreur #REF! : Cette erreur se produit si le nom de la plage utilisé dans la fonction INDIRECT n'existe pas ou est mal orthographié. Vérifiez que les noms des plages correspondent exactement aux valeurs de la première liste déroulante.
• Listes déroulantes vides : Si la deuxième liste déroulante est vide, vérifiez que la plage nommée correspondant à la valeur sélectionnée dans la première liste déroulante contient bien des données.
• Problèmes de mise à jour : Si vous modifiez les données dans les plages nommées, assurez-vous que les listes déroulantes se mettent à jour correctement. Vous pouvez forcer la mise à jour en appuyant sur la touche F9.

Astuces et bonnes pratiques pour les listes déroulantes Excel

• Utilisez des noms de plages descriptifs : Choisissez des noms de plages clairs et faciles à comprendre pour faciliter la maintenance de vos feuilles de calcul.
• Protégez vos feuilles de calcul : Pour éviter que les utilisateurs ne modifient accidentellement les listes déroulantes ou les plages de données sources, protégez vos feuilles de calcul en définissant un mot de passe.
• Utilisez la validation des données pour d'autres types de données : La validation des données peut également être utilisée pour limiter la saisie de nombres, de dates ou de textes à des critères spécifiques.
• Combinez les listes déroulantes avec d'autres fonctions Excel : Vous pouvez utiliser les listes déroulantes comme point de départ pour des calculs complexes ou des analyses de données. Par exemple, vous pouvez utiliser la fonction RECHERCHEV pour rechercher des informations en fonction de la sélection faite dans une liste déroulante.

Conclusion

Les listes déroulantes Excel sont un outil indispensable pour simplifier la saisie de données, réduire les erreurs et uniformiser vos feuilles de calcul. En maîtrisant les différentes techniques présentées dans cet article, vous serez en mesure de créer des listes déroulantes efficaces, dynamiques et intuitives pour optimiser vos projets et gagner en productivité. N'hésitez pas à expérimenter et à adapter ces techniques à vos besoins spécifiques pour tirer le meilleur parti des listes déroulantes Excel.