Comment créer une liste déroulante Excel (et éviter les erreurs courantes)

Comprendre l'intérêt des listes déroulantes Excel

Les listes déroulantes, également appelées menus déroulants, sont un élément d'interface utilisateur qui permet de sélectionner une valeur parmi une liste d'options prédéfinies. Dans Excel, elles offrent plusieurs avantages significatifs :

• Réduction des erreurs de saisie : En limitant les options possibles, vous évitez les fautes de frappe et les incohérences.
• Gain de temps : La sélection d'une option est plus rapide que la saisie manuelle.
• Standardisation des données : Assure une uniformité dans la manière dont les données sont enregistrées.
• Amélioration de la clarté : Facilite la compréhension des données pour les utilisateurs.

Cas d'utilisation courants

Les listes déroulantes sont utiles dans de nombreux contextes :

• Formulaires : Pour collecter des informations standardisées (genre, pays, etc.).
• Rapports : Pour filtrer et analyser les données selon des critères prédéfinis.
• Tableaux de bord : Pour contrôler les paramètres et les visualisations.
• Gestion de projet : Pour suivre l'état d'avancement des tâches (à faire, en cours, terminé).

Créer une liste déroulante simple sur Excel

La manière la plus simple de créer une liste déroulante dans Excel consiste à utiliser la validation des données. Voici les étapes à suivre :

1. Sélectionnez la cellule dans laquelle vous souhaitez insérer la liste déroulante.
2. Allez dans l'onglet Données du ruban Excel.
3. Cliquez sur Validation des données (se trouve généralement dans le groupe "Outils de données").
4. Dans la fenêtre "Validation des données", sous l'onglet Options, choisissez Liste dans le menu déroulant "Autoriser".
5. Dans le champ Source, entrez les valeurs que vous souhaitez afficher dans la liste déroulante, séparées par des virgules (par exemple, Oui,Non,Peut-être).
6. Cliquez sur OK.

Vous devriez maintenant voir une petite flèche à côté de la cellule sélectionnée. Cliquez sur cette flèche pour afficher la liste déroulante et choisir une option.

Exemple pratique

Imaginez que vous gérez un inventaire de produits et que vous souhaitez indiquer si un produit est en stock ou non. Suivez les étapes ci-dessus et entrez En stock,Rupture de stock dans le champ Source. Vous aurez ainsi une liste déroulante dans la colonne "Statut" pour chaque produit.

Utiliser une plage de cellules comme source de données

Au lieu de taper les valeurs directement dans le champ Source, vous pouvez utiliser une plage de cellules existante comme source de données pour votre liste déroulante. Cette méthode est plus flexible car elle vous permet de modifier facilement les options de la liste en modifiant simplement les cellules sources.

1. Créez une liste de valeurs dans une colonne ou une ligne de votre feuille de calcul. Par exemple, vous pouvez lister les noms des départements de votre entreprise (Ventes, Marketing, RH, etc.) dans les cellules A1 à A4.
2. Sélectionnez la cellule où vous voulez la liste déroulante.
3. Allez dans Données > Validation des données.
4. Dans l'onglet Options, choisissez Liste dans "Autoriser".
5. Dans le champ Source, cliquez sur l'icône de sélection de plage (une petite icône de feuille de calcul avec une flèche rouge). Sélectionnez ensuite la plage de cellules contenant votre liste de valeurs (par exemple, =$A$1:$A$4). L'utilisation de références absolues ($A$1:$A$4) est recommandée pour que la plage ne change pas si vous copiez la cellule contenant la liste déroulante.
6. Cliquez sur OK.

Votre liste déroulante affichera maintenant les valeurs contenues dans la plage de cellules que vous avez sélectionnée.

Avantages de cette méthode

• Facilité de mise à jour : Il suffit de modifier les valeurs dans la plage de cellules pour mettre à jour la liste déroulante.
• Réutilisation : Vous pouvez utiliser la même plage de cellules comme source de données pour plusieurs listes déroulantes.
• Listes plus longues : Plus pratique pour les listes avec un grand nombre d'options.

Personnaliser votre liste déroulante Excel

Excel offre plusieurs options pour personnaliser l'apparence et le comportement de vos listes déroulantes.

Message de saisie

Vous pouvez afficher un message d'aide lorsque l'utilisateur sélectionne la cellule contenant la liste déroulante. Ce message peut fournir des instructions ou des informations supplémentaires.

1. Allez dans Données > Validation des données.
2. Sélectionnez l'onglet Message de saisie.
3. Cochez la case "Afficher le message de saisie lorsque la cellule est sélectionnée".
4. Entrez un Titre (par exemple, "Instructions") et un Message de saisie (par exemple, "Veuillez sélectionner une option dans la liste.").
5. Cliquez sur OK.

Alerte d'erreur

Vous pouvez afficher une alerte si l'utilisateur tente de saisir une valeur qui n'est pas dans la liste déroulante.

1. Allez dans Données > Validation des données.
2. Sélectionnez l'onglet Alerte d'erreur.
3. Cochez la case "Afficher l'alerte d'erreur après la saisie de données non valides".
4. Choisissez un Style d'alerte (Arrêt, Avertissement ou Information). L'alerte "Arrêt" empêche l'utilisateur de saisir une valeur non valide, tandis que les autres styles affichent simplement un message d'avertissement.
5. Entrez un Titre (par exemple, "Erreur") et un Message d'erreur (par exemple, "Veuillez sélectionner une option dans la liste déroulante.").
6. Cliquez sur OK.

Gérer les listes déroulantes dynamiques

Une liste déroulante dynamique est une liste dont les options se mettent à jour automatiquement en fonction de modifications apportées à la plage de cellules source. Pour créer une liste déroulante dynamique, vous devez utiliser une formule qui ajuste la taille de la plage source en fonction du nombre d'éléments présents.

Utiliser la fonction DECALER (OFFSET)

La fonction DECALER est idéale pour créer des plages dynamiques. Elle permet de définir une plage en fonction d'une cellule de départ, d'un nombre de lignes et de colonnes à décaler, et d'une hauteur et d'une largeur.

Voici la syntaxe de la fonction DECALER :

=DECALER(référence; lignes; colonnes; [hauteur]; [largeur])

• référence : La cellule de départ de la plage.
• lignes : Le nombre de lignes à décaler par rapport à la cellule de départ (positif pour descendre, négatif pour monter).
• colonnes : Le nombre de colonnes à décaler par rapport à la cellule de départ (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).

Pour créer une liste déroulante dynamique, vous pouvez combiner la fonction DECALER avec la fonction NBVAL (ou COUNTA en anglais), qui compte le nombre de cellules non vides dans une plage.

1. Créez une liste de valeurs dans une colonne (par exemple, en A1:A10). Laissez des cellules vides en dessous de la liste pour permettre l'ajout de nouvelles valeurs.
2. Sélectionnez la cellule où vous voulez la liste déroulante.
3. Allez dans Données > Validation des données.
4. Dans l'onglet Options, choisissez Liste dans "Autoriser".
5. Dans le champ Source, entrez la formule suivante (en adaptant la référence de cellule à votre situation) :

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

Cette formule signifie :

• $A$1 : La cellule de départ de la plage.
• 0;0 : Pas de décalage en lignes ni en colonnes.
• NBVAL($A:$A) : La hauteur de la plage est égale au nombre de cellules non vides dans la colonne A (c'est-à-dire le nombre d'éléments dans votre liste).

• 1 : La largeur de la plage est d'une colonne.

• Cliquez sur OK.

Maintenant, lorsque vous ajoutez de nouvelles valeurs à votre liste dans la colonne A, la liste déroulante se mettra automatiquement à jour pour inclure ces nouvelles valeurs.

Exemple concret : Liste de tâches dynamiques

Imaginez que vous gérez une liste de tâches dans une feuille Excel. Vous pouvez utiliser une liste déroulante dynamique pour attribuer chaque tâche à un responsable. Si vous ajoutez de nouveaux responsables à votre équipe, la liste déroulante se mettra automatiquement à jour.

Erreurs courantes et comment les éviter

Voici quelques erreurs courantes lors de la création et de l'utilisation de listes déroulantes Excel, ainsi que des conseils pour les éviter :

• Erreur : #REF! dans la liste déroulante : Cela signifie que la plage de cellules source est invalide ou a été supprimée. Vérifiez que la plage existe toujours et qu'elle est correctement référencée dans la validation des données.
• Erreur : La liste déroulante n'affiche pas toutes les valeurs : Vérifiez que la plage source est suffisamment grande pour contenir toutes les valeurs. Si vous utilisez une plage dynamique avec la fonction DECALER, assurez-vous que la formule est correcte et que la plage s'étend bien sur toutes les valeurs.
• Erreur : Les modifications apportées à la plage source ne se reflètent pas dans la liste déroulante : Vérifiez que vous avez bien utilisé des références absolues ($) dans la formule de la plage source. Sans références absolues, la plage peut se décaler lorsque vous copiez la cellule contenant la liste déroulante.
• Erreur : L'utilisateur peut saisir des valeurs non valides malgré l'alerte d'erreur : Assurez-vous que le style d'alerte est défini sur "Arrêt". Les styles "Avertissement" et "Information" permettent à l'utilisateur de saisir une valeur non valide après avoir affiché un message.

Astuces supplémentaires pour optimiser vos listes déroulantes

• Utiliser des noms définis : Au lieu d'utiliser des références de cellules directement dans la formule de la plage source, vous pouvez définir un nom pour la plage et utiliser ce nom dans la formule. Cela rend la formule plus lisible et plus facile à maintenir. Pour définir un nom, sélectionnez la plage de cellules, allez dans l'onglet Formules, cliquez sur Définir un nom et entrez un nom pour la plage.
• Combiner les listes déroulantes avec d'autres fonctions Excel : Vous pouvez utiliser les listes déroulantes comme critères de recherche dans des fonctions comme RECHERCHEV ou INDEX/EQUIV. Cela vous permet de créer des tableaux de bord interactifs où les résultats se mettent à jour automatiquement en fonction de la sélection dans la liste déroulante.
• Utiliser Power Query pour importer des données externes : Si votre liste de valeurs se trouve dans une autre source de données (par exemple, une base de données ou un fichier texte), vous pouvez utiliser Power Query pour importer les données dans Excel et les utiliser comme source pour votre liste déroulante. Cela vous permet de maintenir votre liste à jour automatiquement en fonction des modifications apportées à la source de données externe.

En suivant ces conseils et astuces, vous serez en mesure de créer des listes déroulantes Excel efficaces et personnalisées qui vous aideront à simplifier la saisie de données, à éviter les erreurs et à améliorer la clarté de vos feuilles de calcul.