Comment mettre en surbrillance les doublons dans Excel : Un guide complet étape par étape

Méthode 1 : Créer une liste déroulante simple à partir d'une liste saisie

Idéal pour : Listes courtes et fixes qui changent rarement.

Pour créer une liste déroulante dans Excel, sélectionnez la cellule où vous voulez la liste, allez à l'onglet données, cliquez sur validation des données, choisissez Liste dans la boîte Permettre, et spécifiez la plage source ou saisissez manuellement les éléments séparés par des virgules. Il est possible de saisir les éléments manuellement ou de faire référence à une plage de cellules pour les listes déroulantes dans Excel.

Étapes :

1. Sélectionnez la cellule ou la plage de cellules où vous voulez la liste déroulante.
2. Cliquez sur l'onglet données dans le ruban.
3. Dans le groupe outils de données, cliquez sur validation des données. La boîte de dialogue de validation des données s'ouvre.
4. Dans la boîte Permettre, sélectionnez Liste dans le menu déroulant.
5. La case à cocher déroulant dans la cellule apparaît. L'option déroulant dans la cellule doit être cochée pour activer un menu déroulant dans une cellule.
6. Cliquez sur la zone source et tapez vos options sous forme de liste séparée par des virgules, par exemple : Électronique,Meubles,Logiciels,Services
7. Cliquez sur OK.

Une petite flèche apparaît maintenant dans la cellule. En cliquant dessus, le menu déroulant s'ouvre affichant toutes les options de la liste déroulante. Les utilisateurs peuvent copier une cellule avec une liste déroulante et la coller ailleurs, en conservant la fonctionnalité de la liste déroulante, ce qui est un moyen rapide d'appliquer la même liste à d'autres cellules.

Évitez les espaces après les virgules dans la liste séparée par des virgules, sauf si vous souhaitez un espace d'initialisation dans le libellé. "Amérique du Nord, EMEA" affichera " EMEA" avec un espace d'initialisation.

Suggestion de capture d'écran : Afficher la boîte de dialogue de validation des données avec l'onglet paramètres actif, Liste dans la boîte Permettre, et une liste séparée par des virgules dans la boîte source. La cellule cible devrait être sélectionnée en arrière-plan en utilisant le fichier de démonstration de bon de commande.

Méthode 2 : Créer une liste déroulante à partir d'une plage de cellules

Idéal pour : Listes avec de nombreux éléments, ou lorsque les mêmes données sources sont réutilisées à travers plusieurs cellules.

Faire référence à une plage de cellules comme source de données est plus flexible que de taper les éléments. Lorsque vous mettez à jour la plage source, les options de la liste déroulante dans toutes les cellules connectées se mettent à jour également.

1. Entrez vos éléments de liste dans une colonne, idéalement sur une feuille distincte (par exemple une feuille nommée "Listes").
2. Sélectionnez la cellule où vous voulez la liste déroulante.
3. Allez à onglet données > groupe outils de données > sélectionnez validation des données.
4. Dans la fenêtre de validation des données, sur l'onglet paramètres, sélectionnez Liste dans la boîte Permettre.
5. Cliquez sur la boîte source et sélectionnez la plage de cellules sur votre feuille de listes. Excel entre automatiquement la référence de plage, par exemple =Listes!$A$3:$A$7.
6. Cliquez sur OK.

Les critères de validation limitent maintenant les saisies aux seules données valides de cette plage source. La boîte de nom en haut à gauche montre l'adresse actuelle de la cellule et vous aide à vérifier que la bonne référence est sélectionnée.

Utilisez toujours des références absolues avec des signes $ dans la plage source. Utiliser =A3:A7 sans signes dollar peut bouger si des lignes sont insérées au-dessus.

Suggestion de capture d'écran : Afficher la boîte de dialogue de validation des données avec une référence de plage dans la boîte source et l'onglet feuille Listes visible en bas. Utilisez le fichier de démonstration de bon de commande.

Méthode 3 : Utiliser une plage nommée comme source

Idéal pour : Listes déroulantes utilisées à travers plusieurs emplacements, ou lorsque les données sources sont sur une feuille différente.

Une plage nommée attribue un nom mémorable à un groupe de cellules, que vous pouvez ensuite utiliser dans la boîte source au lieu d'une adresse de cellule.

1. Sélectionnez vos éléments de liste, puis cliquez sur la boîte de nom et tapez un nom (par exemple CatégoriesProduits), puis appuyez sur entrée.
2. Sélectionnez la cellule où vous voulez la liste déroulante, allez à l'onglet données > validation des données.
3. Dans l'onglet paramètres, sélectionnez Liste dans la boîte Permettre, puis tapez =CatégoriesProduits dans la boîte source.
4. Cliquez sur OK.

Cela rend la liste de validation plus facile à gérer. Les plages nommées permettent également des listes déroulantes dépendantes lorsqu'elles sont combinées avec la fonction INDIRECT (vu dans la Méthode 5).

Suggestion de capture d'écran : Afficher la boîte de nom avec un nom de plage saisi, et la boîte source de validation des données montrant =CatégoriesProduits.

Méthode 4 : Ajouter des messages d'entrée et des alertes d'erreur

Idéal pour : Classeur partagé ou formulaires remplis par d'autres personnes.

Les utilisateurs peuvent ajouter des messages d'entrée à afficher lorsqu'un utilisateur clique sur la cellule avec une liste déroulante pour les guider sur ce qu'ils doivent sélectionner. Excel permet la configuration de messages d'erreur personnalisés dans la boîte de dialogue de validation des données.

Message d'entrée :

1. Ouvrez l'onglet données > validation des données.
2. Cliquez sur l'onglet message d'entrée dans la boîte de dialogue de validation des données.
3. Cochez Afficher le message d'entrée lorsque la cellule est sélectionnée et tapez un titre plus un message dans les champs ci-dessous.
4. Cliquez sur OK.

Quand quelqu'un clique sur cette cellule, le message d'entrée apparaît comme un tooltip guidant leur sélection.

Alerte d'erreur :

1. Cliquez sur l'onglet alerte d'erreur dans la boîte de dialogue de validation des données.
2. Cochez la case afficher l'alerte d'erreur (aussi étiqueté alerte d'erreur après que des données non valides sont entrées, ou alerte après des données non valides).
3. Choisissez un Style : Arrêt bloque les données non valides, Avertissement les permet avec une invite, Information affiche seulement une note.
4. Entrez un titre et un message d'erreur.
5. Cliquez sur OK.

Lorsque des données non valides sont saisies en dehors de la liste autorisée, l'alerte d'erreur se déclenche. Pour des formulaires stricts, utilisez Arrêt afin que seules les données valides puissent être acceptées. Le paramètre d'affichage de l'alerte d'erreur et l'onglet message d'entrée sont contrôlés indépendamment, vous pouvez donc activer l'un sans l'autre.

Si vous voulez permettre des cellules vides, cochez la case Ignorer les blancs dans l'onglet paramètres. Cela empêche l'alerte d'erreur de se déclencher sur des cellules vides.

Suggestion de capture d'écran : Afficher l'onglet alerte d'erreur de la boîte de dialogue de validation des données avec Arrêt sélectionné et un message d'erreur personnalisé saisi. Utilisez le fichier de démonstration de formulaire RH.

Méthode 5 : Construire des listes déroulantes dépendantes

Idéal pour : Sélection multi-niveaux où la seconde liste dépend de la première.

Les listes déroulantes dépendantes dans Excel lient deux ou plusieurs listes déroulantes ensemble, où la sélection dans la première liste contrôle les options disponibles dans la seconde liste. Par exemple, si un utilisateur sélectionne Pizza dans la première liste déroulante, la seconde liste déroulante peut être peuplée avec des articles spécifiques de pizza, tandis que sélectionner Chinois montrerait des plats chinois dans la seconde liste. Créer des listes déroulantes dépendantes peut améliorer l'efficacité et la précision de la saisie de données en assurant que les utilisateurs ne voient que les options pertinentes en fonction de leurs sélections précédentes.

Mise en place :

1. Créez vos données sources dans des colonnes, une colonne par catégorie. La ligne d'en-têtes de chaque colonne doit correspondre exactement à ce qui apparaît dans la première liste déroulante.
2. Nommez chaque colonne d'éléments en utilisant la boite de nom, en correspondant exactement à l'option de la première liste (par exemple nommez la colonne Pizza "Pizza", la colonne Chinois "Chinois").
3. Créez la première liste déroulante dans la colonne A en utilisant la Méthode 1 ou Méthode 2.
4. Sélectionnez la cellule pour la seconde liste déroulante (colonne B). Allez à onglet données > sélectionnez liste validation des données dans l'onglet paramètres, choisissez Liste dans la boîte Permettre, et dans la boîte source entrez =INDIRECT(A4).
5. Cliquez sur OK.

Lorsque qu'une valeur est choisie dans la première liste déroulante, INDIRECT cherche la plage nommée avec le même nom et l'utilise comme liste de sélection pour la deuxième liste déroulante. Les critères de validation pour la deuxième cellule se mettent à jour dynamiquement en fonction de la première sélection.

Suggestion de capture d'écran : Afficher le fichier de démonstration de liste dépendante avec Pizza sélectionné dans la colonne A et les articles de menu de pizza visibles dans le menu déroulant de la colonne B. La feuille Menu Listes devrait être visible en arrière-plan.

Méthode 6 : Créer une liste déroulante dynamique

Idéal pour : Listes qui se développent avec le temps et qui doivent se mettre à jour automatiquement lorsque de nouveaux éléments sont ajoutés.

Vous pouvez créer une liste déroulante dynamique dans Excel en utilisant la fonction DECALER, ce qui permet à la liste de se mettre à jour automatiquement lorsque de nouveaux éléments sont ajoutés à la plage source. Utiliser un tableau Excel comme source pour une liste déroulante lui permet de s'étendre automatiquement lorsque de nouvelles lignes sont ajoutées, ce qui en fait une excellente option pour les listes dynamiques.

Option A : Tableau Excel

1. Sélectionnez vos éléments de source et appuyez sur Ctrl + T (depuis l'onglet insertion) pour créer un tableau Excel. Choisissez un style de tableau et confirmez l'option ligne d'en-tête.
2. Référez-vous à la colonne de tableau dans la boite source de la validation des données : =Tableau1[Catégorie].

Lorsque qu'une nouvelle ligne est ajoutée au tableau Excel, les options de liste déroulante se mettent à jour automatiquement sans changement nécessaire à la règle de validation.

Option B : formule DECALER

Pour créer une liste déroulante dynamique, vous pouvez utiliser la formule =DECALER(Feuille2!$A$1,0,0,NBCAR(Feuille2!$A:$A),1), qui ajuste la plage en fonction du nombre de cellules non-vides dans la colonne source.

Entrez ceci dans la boîte de source de la fenêtre de validation des données. À mesure que des éléments sont ajoutés à la colonne source, la plage source s'étend et les options de liste déroulante se mettent à jour automatiquement.

Suggestion de capture d'écran : Afficher la boîte de source de validation des données avec la formule DECALER saisie et la feuille Listes visible. Utilisez le fichier de démonstration de bon de commande.

Méthode 7 : Ajouter, éditer et supprimer des éléments de liste déroulante

Gestion des éléments dans la plage source :

Pour ajouter un élément à une liste déroulante existante, allez à la feuille originale contenant la liste, faites un clic droit sur la cellule en dessous de l'endroit où vous voulez insérer, sélectionnez Insérer, choisissez décaler les cellules vers le bas, et entrez le nouvel élément. Les options de la liste déroulante se mettent à jour automatiquement. Sur la même feuille de calcul ou à travers les feuilles, les mêmes étapes s'appliquent tant que la plage source couvre la nouvelle cellule.

Suppression de la liste déroulante :

Pour supprimer une liste déroulante dans Excel, sélectionnez la cellule avec la liste déroulante, allez à l'onglet données, cliquez sur validation des données, puis cliquez sur supprimer (Effacer tout) pour supprimer la liste. Pour effacer seulement la valeur de la cellule sans supprimer la règle de validation, sélectionnez la cellule et appuyez sur supprimer. Pour supprimer complètement la règle, utilisez Effacer tout dans la fenêtre de validation des données.

Notez que si vous voulez appuyer sur supprimer uniquement sur le contenu de la cellule et garder la validation, vous pouvez le faire en toute sécurité. La liste de validation reste sur la cellule même après que le contenu ait été effacé.

Protection avec mot de passe des cellules validées :

Pour empêcher les autres de modifier votre configuration de liste déroulante, allez à Révision > Protéger la feuille et entrez un mot de passe (protéger par mot de passe la feuille de calcul). Les utilisateurs peuvent toujours utiliser les listes déroulantes pour saisir des données, mais ne peuvent pas modifier les critères de validation ou supprimer les règles.

Suggestion de capture d'écran : Afficher la boîte de dialogue de validation des données avec le bouton Effacer tout visible, prêt à supprimer la règle. Afficher également la boîte de dialogue Protéger la feuille avec le champ de mot de passe actif.

Problèmes courants et dépannage

La flèche de la liste déroulante n'apparaît pas

Confirmez que la case boîte déroulante dans la cellule est cochée dans l'onglet paramètres. Si elle n'est pas cochée, la validation fonctionne toujours, mais aucune flèche n'apparaît dans la cellule. C'est le paramètre le plus souvent manqué lorsque la liste déroulante semble avoir disparu.

L'alerte d'erreur se déclenche sur des données valides

C'est généralement un problème de correspondance de formattage. Vérifiez la boîte source pour des espaces égarés autour des virgules dans l'entrée de liste séparée par des virgules, ou inspectez les données sources pour des cellules vides ou des capitalisations incohérentes. Les critères de validation comparent exactement les valeurs.

La liste dynamique ne se met pas à jour automatiquement

Si la source est une plage de cellules statique, les nouveaux éléments ajoutés en dehors de cette plage n'apparaîtront pas. Passez à un tableau Excel ou à la formule DECALER pour que la plage source s'étende automatiquement. La liste de validation des données doit faire référence à une plage incluant tous les éléments actuels et futurs.

La liste dépendante montre une erreur de référence

Cela signifie que la plage nommée ne correspond pas exactement à la première sélection de liste déroulante. Ouvrez Formules > Gestionnaire de noms et confirmez que les noms correspondent exactement aux options dans la première liste déroulante, y compris la capitalisation. Toute discordance fait qu'INDIRECT retourne une cellule blanche ou une erreur dans toutes les cellules utilisant la règle dépendante.

Référence rapide : Méthodes de liste déroulante

Pour les développeurs : Ajouter la validation des données avec IronXL

Si votre application .NET génère des classeurs Excel avec des formulaires ou des modèles de saisie de données, IronXL vous permet d'appliquer des listes déroulantes de validation des données par programmation en C# sans Microsoft Office sur le serveur. L'approche recommandée est AddFormulaListRule(), qui fait référence à une plage de cellules comme source et évite la limite de 255 caractères qui s'applique lors du passage des éléments de liste directement comme une chaîne.

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim formSheet As WorkSheet = workBook.DefaultWorkSheet
formSheet.Name = "Order Form"

' Headers
formSheet("A1").Value = "Order ID"
formSheet("B1").Value = "Product Category"
formSheet("C1").Value = "Region"

' Write source list values to a Lists sheet
Dim listSheet As WorkSheet = workBook.CreateWorkSheet("Lists")
Dim categories As String() = {"Electronics", "Furniture", "Software", "Office Supplies", "Services"}
Dim regions As String() = {"North America", "EMEA", "APAC", "Latin America", "Middle East"}

For i As Integer = 0 To categories.Length - 1
    listSheet($"A{i + 1}").Value = categories(i)
    listSheet($"B{i + 1}").Value = regions(i)
Next

' Apply drop-down validation referencing the Lists sheet source range
Dim categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           ' cell range to validate
    "Lists!$A$1:$A$5"   ' source range on Lists sheet
)
categoryRule.ShowErrorBox = True
categoryRule.ErrorBoxTitle = "Invalid Category"
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list."
categoryRule.ShowPromptBox = True
categoryRule.PromptBoxTitle = "Product Category"
categoryRule.PromptBoxText = "Choose a product category from the drop-down."

Dim regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
)
regionRule.ShowErrorBox = True
regionRule.ErrorBoxTitle = "Invalid Region"
regionRule.ErrorBoxText = "Select a valid region from the drop-down list."

workBook.SaveAs("order-form-validated.xlsx")

IronXL fonctionne sur .NET 6 et versions ultérieures, compatible avec Windows, Linux, macOS, Docker et Azure. Pour des détails complets, voir la référence d'API DataValidation.

Pour commencer : Installez via NuGet avec Install-Package IronXL.Excel. Un essai gratuit est disponible avec 30 jours de fonctionnalité complète sans carte de crédit requise.

Lectures complémentaires :

• IronXL DataValidation API reference
• Écrire des fichiers Excel en .NET avec IronXL
• Guide de démarrage IronXL

Conclusion

Intégrer une liste déroulante dans des cellules Excel prend moins d'une minute une fois que vous savez où aller. Pour une liste déroulante rapide dans une cellule avec quelques choix fixes, taper une liste séparée par des virgules directement dans la boîte de source du dialogue de validation des données est la voie la plus rapide. Pour tout ce qui doit évoluer ou se mettre à jour avec le temps, faire référence à une plage nommée ou à un tableau Excel donne aux options de liste déroulante l'espace nécessaire pour croître. La liste de validation des données reste connectée à ses données sources, donc ajouter un nouvel élément à la plage source le rend automatiquement disponible dans chaque cellule connectée.

Quand plusieurs personnes remplissent la même feuille de calcul, le message d'entrée sur l'onglet message d'entrée les guide avant qu'ils ne fassent une sélection, et une alerte d'erreur de niveau Arrêt sur l'onglet alerte d'erreur garantit que les données non valides saisies ne se retrouvent jamais dans une cellule validée. Les listes déroulantes dépendantes construites avec INDIRECT et des plages nommées laissent la première liste déroulante contrôler automatiquement la seconde, gardant l'ensemble d'options pertinent sans travail supplémentaire de la part de l'utilisateur.

Pour les développeurs qui doivent générer des fichiers Excel validés à partir d'applications .NET, IronXL couvre l'ensemble du flux de travail de validation des données : plages sources, alertes d'erreur, invites d'entrée, et règles de liste de formules, le tout sans Office installé sur le serveur. Commencez avec une essai gratuit pour tester la validation des données dans votre propre projet.

Avez-vous un scénario de liste déroulante que ce guide n'a pas couvert ? Laissez un commentaire ci-dessous, ou visitez le blog Iron Software pour plus de guides sur la saisie de données Excel, la validation, et l'automatisation des feuilles de calcul.