Comment Créer un Menu Déroulant Impeccable sur Excel (et Éviter les Erreurs)

Qu'est-ce qu'un Menu Déroulant sur Excel et Pourquoi l'Utiliser ?

Un menu déroulant, aussi appelé liste déroulante, est un contrôle qui permet à l'utilisateur de sélectionner une valeur parmi une liste prédéfinie. Au lieu de saisir manuellement des données dans une cellule, l'utilisateur choisit simplement une option dans le menu, ce qui réduit considérablement les risques d'erreurs de frappe et assure la cohérence des données.

Avantages des Menus Déroulants

• Saisie de données simplifiée : Plus besoin de taper, un simple clic suffit.
• Réduction des erreurs : L'utilisateur ne peut choisir que parmi les options proposées, évitant ainsi les erreurs de frappe ou les valeurs non valides.
• Cohérence des données : Assure que les données saisies respectent un format uniforme.
• Gain de temps : Saisie plus rapide et moins de corrections nécessaires.
• Professionnalisme : Améliore l'apparence et la convivialité de vos feuilles de calcul.

Créer un Menu Déroulant Simple sur Excel : Étape par Étape

Voici la méthode de base pour créer un menu déroulant simple, basé sur une liste de valeurs que vous définissez.

Étape 1 : Préparer la Liste des Options

La première étape consiste à créer une liste des options qui apparaîtront dans votre menu déroulant. Vous pouvez placer cette liste n'importe où dans votre feuille de calcul, de préférence dans une zone discrète et facile à retrouver. Par exemple, vous pouvez créer une nouvelle feuille nommée "Listes" et y placer vos différentes listes.

Exemple : Imaginons que vous voulez créer un menu déroulant pour sélectionner un pays. Dans la feuille "Listes", vous pouvez entrer les noms des pays suivants dans les cellules A1 à A5 :

• A1 : France
• A2 : Allemagne
• A3 : Espagne
• A4 : Italie
• A5 : Royaume-Uni

Étape 2 : Sélectionner la Cellule de Destination

Sélectionnez la cellule où vous souhaitez insérer le menu déroulant. C'est dans cette cellule que l'utilisateur pourra choisir une option.

Étape 3 : Accéder à la Validation des Données

1. Allez dans l'onglet Données du ruban Excel.
2. Dans le groupe Outils de données, cliquez sur Validation des données.

Une fenêtre "Validation des données" s'ouvre.

Étape 4 : Définir les Critères de Validation

1. Dans l'onglet Options, sous Autoriser, sélectionnez Liste dans le menu déroulant.
2. Dans le champ Source, entrez la plage de cellules contenant votre liste d'options. Dans notre exemple, ce serait =$Listes!$A$1:$A$5. Assurez-vous d'utiliser des références absolues (avec le signe $) pour que la plage ne change pas si vous copiez le menu déroulant dans d'autres cellules.

Explication des références absolues : L'utilisation des signes $ fixe la colonne et la ligne. Par exemple, $A$1 fait toujours référence à la cellule A1, même si vous copiez la formule. Si vous utilisez A1 (référence relative), la référence changera en fonction de la cellule où vous copiez la formule.

Étape 5 : Personnaliser les Messages (Facultatif)

Vous pouvez personnaliser les messages qui s'affichent lorsque l'utilisateur sélectionne la cellule ou saisit une valeur non valide.

• Onglet Message de saisie : Définissez un titre et un message qui s'afficheront lorsque l'utilisateur sélectionne la cellule. Cela peut être utile pour donner des instructions claires.
• Onglet Alerte d'erreur : Définissez un style d'alerte (Arrêt, Avertissement, Information) et un message qui s'afficheront si l'utilisateur saisit une valeur qui n'est pas dans la liste.

Étape 6 : Valider la Création du Menu Déroulant

Cliquez sur OK pour valider la création du menu déroulant. Vous devriez maintenant voir une petite flèche à droite de la cellule sélectionnée. Cliquez sur cette flèche pour afficher la liste des options et choisir celle qui convient.

Utiliser une Plage Nommée pour un Menu Déroulant Plus Dynamique

L'utilisation d'une plage nommée rend votre menu déroulant plus dynamique et plus facile à gérer, surtout si votre liste d'options est susceptible de changer.

Étape 1 : Définir la Plage Nommée

1. Sélectionnez la plage de cellules contenant votre liste d'options (par exemple, A1:A5 dans la feuille "Listes").
2. Allez dans l'onglet Formules du ruban Excel.
3. Dans le groupe Noms définis, cliquez sur Définir un nom.
4. Dans la fenêtre "Nouveau nom", entrez un nom pour votre plage (par exemple, "ListePays"). Assurez-vous que le champ Étendue est défini sur le nom de votre classeur.
5. Cliquez sur OK.

Étape 2 : Créer le Menu Déroulant avec la Plage Nommée

Suivez les étapes 2 à 4 de la section "Créer un Menu Déroulant Simple sur Excel", mais au lieu d'entrer la plage de cellules dans le champ Source, entrez simplement le nom de votre plage nommée (par exemple, =ListePays).

L'avantage d'utiliser une plage nommée est que si vous ajoutez ou supprimez des éléments dans votre liste, le menu déroulant se mettra automatiquement à jour, sans que vous ayez besoin de modifier la source dans la validation des données.

Menus Déroulants Dépendants : Créer des Listes en Cascade

Un menu déroulant dépendant (ou en cascade) est un menu déroulant dont les options disponibles dépendent de la sélection faite dans un autre menu déroulant. C'est utile pour affiner les choix et guider l'utilisateur vers la bonne option.

Exemple : Vous voulez créer un menu déroulant pour sélectionner une ville, mais vous voulez d'abord que l'utilisateur sélectionne la région. Le menu déroulant des villes ne devra afficher que les villes de la région sélectionnée.

Étape 1 : Préparer les Données

Organisez vos données de manière à ce que chaque région soit associée à une liste de villes.

Exemple :

Étape 2 : Créer les Plages Nommées Dynamiques

C'est l'étape la plus complexe. Vous devez créer des plages nommées dynamiques qui se réfèrent aux listes de villes correspondant à chaque région. Pour cela, vous allez utiliser la fonction DECALER (OFFSET) combinée à NBVAL (COUNTA).

1. Sélectionnez la première cellule de la liste des villes de la première région (par exemple, B2).
2. Allez dans l'onglet Formules et cliquez sur Définir un nom.
3. Entrez un nom pour cette plage (par exemple, Villes_Ile_de_France).
4. Dans le champ Fait référence à, entrez la formule suivante :

=DECALER(Feuil1!$B$2;;;NBVAL(Feuil1!$B:$B)-1;)

Explication de la formule :

• DECALER(Feuil1!$B$2;;;NBVAL(Feuil1!$B:$B)-1;) : Cette formule crée une plage dynamique qui commence à la cellule B2 (la première ville) et s'étend jusqu'à la dernière ville de la colonne B.
• Feuil1!$B$2 : La cellule de départ de la plage.
• ;;; : Les trois points-virgules indiquent que nous ne décalons ni la ligne, ni la colonne.

• NBVAL(Feuil1!$B:$B)-1 : Calcule le nombre de cellules non vides dans la colonne B (nombre total de villes) et soustrait 1 pour exclure l'en-tête (le titre de la colonne).

• Répétez cette opération pour chaque région, en créant une plage nommée pour chaque liste de villes (par exemple, Villes_Bretagne, Villes_Auvergne, etc.).

Étape 3 : Créer le Menu Déroulant des Régions

Créez un menu déroulant simple pour la liste des régions, comme expliqué dans la section "Créer un Menu Déroulant Simple sur Excel".

Étape 4 : Créer le Menu Déroulant des Villes Dépendant

1. Sélectionnez la cellule où vous voulez insérer le menu déroulant des villes.
2. Allez dans l'onglet Données et cliquez sur Validation des données.
3. Dans l'onglet Options, sélectionnez Liste.
4. Dans le champ Source, entrez la formule suivante :

=INDIRECT("Villes_"&A1)

Remplacez A1 par la cellule contenant le menu déroulant des régions.

Explication de la formule :

• INDIRECT("Villes_"&A1) : Cette formule construit dynamiquement le nom de la plage à utiliser pour le menu déroulant des villes. Elle concatène la chaîne de caractères "Villes_" avec la valeur de la cellule A1 (la région sélectionnée). Par exemple, si la cellule A1 contient "Île-de-France", la formule renverra "Villes_Île-de-France", qui est le nom de la plage que nous avons définie pour les villes d'Île-de-France.

Maintenant, le menu déroulant des villes affichera uniquement les villes de la région sélectionnée dans le menu déroulant des régions.

Astuces et Bonnes Pratiques pour les Menus Déroulants Excel

• Utiliser des tableaux Excel : Convertir vos listes d'options en tableaux Excel permet de gérer plus facilement les ajouts et suppressions d'éléments. Les plages nommées se mettront automatiquement à jour.
• Masquer la feuille contenant les listes : Pour éviter que les utilisateurs modifient accidentellement les listes d'options, vous pouvez masquer la feuille qui les contient (clic droit sur l'onglet de la feuille et sélectionner "Masquer").
• Utiliser la fonction SIERREUR : Si la cellule contenant le menu déroulant des régions est vide, le menu déroulant des villes affichera une erreur #REF!. Pour éviter cela, vous pouvez utiliser la fonction SIERREUR pour afficher un message plus convivial (par exemple, "Sélectionnez une région").
• Validation des données personnalisée : Explorez les autres options de validation des données, comme la validation des nombres, des dates ou des heures. Vous pouvez définir des critères spécifiques pour chaque cellule.

Erreurs Courantes à Éviter avec les Menus Déroulants

• Références incorrectes : Assurez-vous que les références aux listes d'options sont correctes et que les plages nommées sont bien définies.
• Oublier les références absolues : Si vous copiez un menu déroulant, vérifiez que les références à la liste d'options sont absolues (avec le signe $) pour qu'elles ne changent pas.
• Ne pas gérer les erreurs : Prévoyez les cas où la cellule contenant le menu déroulant principal est vide et affichez un message d'erreur clair.
• Listes trop longues : Si votre liste d'options est très longue, envisagez d'utiliser une autre méthode, comme une zone de liste ou une boîte de dialogue personnalisée.

En suivant ces conseils et en évitant ces erreurs courantes, vous serez en mesure de créer des menus déroulants Excel efficaces et professionnels qui amélioreront considérablement la qualité et la convivialité de vos feuilles de calcul.