La validation des données reste le seul mécanisme natif d’Excel capable de bloquer un doublon avant qu’il n’atteigne la cellule. La mise en forme conditionnelle ou le bouton « Supprimer les doublons » interviennent trop tard : le doublon existe déjà, il faut le repérer puis le traiter. Avec une règle de validation personnalisée, la saisie est tout simplement refusée. Nous détaillons ici la méthode la plus fiable, ses pièges de paramétrage et les cas où elle ne suffit plus.
Normaliser les données avant de poser la validation des doublons Excel
Une règle de validation fondée sur NB.SI compare des chaînes brutes. Si la colonne contient « Dupont », « dupont » et » Dupont » (avec espace), Excel considère ces trois valeurs comme distinctes. La détection échoue silencieusement.
A voir aussi : Comment expliquer les raccourcis pour imprimer à un débutant en quelques minutes ?
Nous recommandons de traiter la colonne cible avec une colonne auxiliaire de nettoyage, ou d’appliquer la normalisation directement dans la formule de validation. La combinaison SUPPRESPACE(MAJUSCULE()) couvre la majorité des cas : elle supprime les espaces superflus en début, fin et milieu de chaîne, puis uniformise la casse.
Si la clé de doublon porte sur un identifiant alphanumérique (référence produit, numéro de facture), vérifiez aussi les zéros non significatifs. Excel convertit parfois « 007 » en 7 lorsqu’il interprète la cellule comme un nombre. Forcer le format texte sur la colonne avant toute saisie évite ce décalage.
Lire également : Envoyer SMS Multiples : Comment Envoyer à Plusieurs Personnes en Même Temps ?
Validation des données avec NB.SI : formule et paramétrage exact
Sélectionnez la plage de saisie, par exemple A2:A500 (excluez l’en-tête). Ouvrez Données, puis Validation des données. Dans l’onglet « Options », choisissez « Personnalisé » et entrez la formule suivante :
=NB.SI($A$2:$A$500;A2)=1
Cette formule compte le nombre d’occurrences de la valeur saisie dans la plage. Si le résultat dépasse 1, la condition n’est plus remplie et Excel refuse l’entrée.
Pourquoi la référence mixte est critique
La plage doit être verrouillée en absolu ($A$2:$A$500) pour que chaque cellule vérifie l’ensemble de la colonne. La référence de critère (A2) reste relative : elle s’adapte à la ligne en cours de saisie. Inverser ces références est l’erreur la plus courante, et elle rend la règle inopérante sans aucun message d’alerte.
Configurer le message d’erreur
Dans l’onglet « Alerte d’erreur » de la boîte de validation, sélectionnez le style « Arrêt ». Ce style est le seul qui empêche réellement la saisie. Les styles « Avertissement » et « Information » affichent un message mais laissent l’utilisateur confirmer la valeur, ce qui annule toute la protection.
Rédigez un message explicite, par exemple : « Cette valeur existe déjà dans la colonne. Vérifiez la référence. » Un message générique (« Valeur non valide ») génère des appels support inutiles.
Doublons multi-colonnes : passer de NB.SI à NB.SI.ENS
Un doublon métier ne se limite pas toujours à une seule colonne. Un même nom de client peut apparaître plusieurs fois légitimement s’il correspond à des commandes différentes. Le vrai doublon est la combinaison nom + date + référence.
Pour gérer ce cas, remplacez NB.SI par NB.SI.ENS. La formule de validation devient :
=NB.SI.ENS($A$2:$A$500;A2;$B$2:$B$500;B2;$C$2:$C$500;C2)=1
Chaque paire plage/critère ajoute une dimension au contrôle. La saisie n’est refusée que si l’ensemble des colonnes spécifiées présente une combinaison identique.
- Appliquez cette formule à la dernière colonne du groupe (ici C), car c’est lors de la saisie du dernier champ que la combinaison complète peut être évaluée.
- Les plages de chaque critère doivent avoir exactement la même taille, sinon NB.SI.ENS renvoie une erreur.
- Si vos colonnes ne sont pas contiguës (A, G, K par exemple), la syntaxe reste identique : vous référencez chaque colonne séparément dans la formule.
Limites de la validation et alternatives pour les fichiers volumineux
La validation des données a un défaut structurel : elle ne se déclenche qu’à la saisie manuelle. Un copier-coller, un import CSV ou une macro VBA contournent la règle sans aucun avertissement. C’est un comportement documenté par Microsoft, pas un bug.
Pour les fichiers partagés ou alimentés par plusieurs sources, nous combinons la validation avec une colonne de contrôle. Cette colonne utilise une formule NB.SI classique et renvoie « DOUBLON » lorsque le compte dépasse 1. Un filtre ou une mise en forme conditionnelle sur cette colonne permet ensuite de repérer ce qui a échappé à la validation.
Power Query pour un nettoyage en amont
Quand le volume de données dépasse quelques milliers de lignes, Power Query offre une approche plus robuste. L’étape « Supprimer les doublons » dans l’éditeur fonctionne sur une ou plusieurs colonnes, et le traitement s’exécute à chaque actualisation de la requête. L’avantage principal : Power Query travaille sur une copie des données sources, ce qui préserve le fichier d’origine.
Intégrer la normalisation dans la formule de validation
Pour éviter de dépendre d’une colonne auxiliaire de nettoyage, vous pouvez encapsuler la normalisation directement dans la règle de validation. La formule devient plus longue, mais elle élimine les faux négatifs liés à la casse ou aux espaces :
=NB.SI.ENS($A$2:$A$500;SUPPRESPACE(MAJUSCULE(A2)))=1
Cette approche a une limite : NB.SI compare le critère normalisé aux valeurs brutes de la plage. Si les données existantes contiennent elles-mêmes des incohérences de casse, le comptage sera faussé. La normalisation dans la formule de validation ne fonctionne correctement que si les données déjà présentes ont été nettoyées au préalable.
- Nettoyez la colonne existante avant d’activer la validation (chercher-remplacer ou formule SUPPRESPACE/MAJUSCULE dans une colonne temporaire, puis coller en valeurs).
- Appliquez ensuite la validation avec la formule normalisée.
- Protégez la feuille pour empêcher la suppression accidentelle de la règle de validation par un utilisateur.
La protection à la saisie est la couche la plus efficace contre les doublons, mais elle ne remplace pas un contrôle périodique sur l’ensemble du fichier. Un audit mensuel via Power Query ou une simple formule NB.SI en colonne annexe reste le filet de sécurité que nous posons systématiquement sur les fichiers critiques.
