Comment mettre en évidence les cellules et les lignes en double dans Excel

Dans ce tutoriel, vous apprendrez à montrer les doublons dans Excel. Nous allons examiner de près différentes méthodes pour ombrer les cellules dupliquées, les lignes entières ou les doublons consécutifs à l’aide de la mise en forme conditionnelle. De plus, vous verrez comment mettre en évidence les doublons avec différentes couleurs en utilisant un outil spécialisé.

La semaine dernière, nous avons exploré différentes façons d’identifier les doublons dans Excel. Sans aucun doute, les formules de duplication sont très utiles, mais la mise en évidence des entrées dupliquées avec une couleur définie pourrait rendre l’analyse des données encore plus facile.

La façon la plus rapide de trouver et de mettre en évidence les doublons dans Excel est d’utiliser le formatage conditionnel. Le plus grand avantage de cette méthode est qu’elle ne vous montre pas seulement les doublons existants, mais détecte et colorie les nouveaux doublons lorsque vous saisissez, modifiez ou écrasez vos données.

Plus loin dans ce tutoriel, vous trouverez un certain nombre de façons de mettre en évidence les enregistrements en double en fonction de votre tâche spécifique. Ces techniques fonctionnent dans toutes les versions d’Excel 2016, Excel 2013, Excel 2010 et inférieures.

• Mise en évidence des doublons dans Excel avec les 1ères occurrences (règle intégrée).dans la règle)
• Comment mettre en évidence les doublons dans Excel sauf les 1ères occurrences
• Comment mettre en évidence les doublons dans une plage (plusieurs colonnes)
• Comment mettre en évidence le Nième enregistrement et les doublons suivants. suivants
• Transformer des lignes entières en fonction des valeurs en double dans une colonne
• Comment mettre en évidence les lignes en double dans Excel
• Comment visualiser les cellules en double consécutives
• Duplicate Remover – le moyen le plus rapide de mettre en évidence les doublons dans Excel

Comment mettre en évidence les doublons dans Excel en utilisant la règle intégrée (avec les 1ères occurrences)

Pour commencer, dans toutes les versions d’Excel, il existe une règle prédéfinie pour mettre en évidence les cellules en double. Pour utiliser cette règle dans vos feuilles de calcul, effectuez les étapes suivantes :

1. Sélectionnez les données que vous voulez vérifier pour les doublons. Il peut s’agir d’une colonne, d’une ligne ou d’une plage de cellules.
2. Sur l’onglet Accueil, dans le groupe Styles, cliquez sur Mise en forme conditionnelle >Règles de mise en évidence des cellules >Valeurs dupliquées…
   
3. La fenêtre de dialogue Valeurs dupliquées s’ouvre avec le format de remplissage rouge clair et de texte rouge foncé sélectionné par défaut. Pour appliquer le format par défaut, il suffit de cliquer sur OK.

En dehors du remplissage rouge et du formatage du texte, une poignée d’autres formats prédéfinis sont disponibles dans la liste déroulante. Pour mettre en évidence les doublons en utilisant une autre couleur, cliquez sur Format personnalisé… (le dernier élément de la liste déroulante) et sélectionnez la couleur de remplissage et/ou de police de votre choix.

En utilisant la règle intégrée, vous pouvez mettre en évidence les doublons dans une colonne ou dans plusieurs colonnes comme le montre la capture d’écran suivante :

Lorsque vous utilisez la règle intégrée d’Excel pour mettre en évidence les valeurs en double, veuillez garder à l’esprit les deux choses suivantes :

• Elle ne fonctionne que pour les cellules individuelles. Pour mettre en évidence les lignes en double, vous auriez besoin de créer vos propres règles soit basées sur les valeurs d’une colonne spécifique, soit en comparant les valeurs de plusieurs colonnes.
• Elle met en évidence les cellules en double, y compris leurs premières occurrences. Pour mettre en évidence tous les doublons à l’exception des premières occurrences, créez une règle de formatage conditionnel basée sur cette formule.

Comment mettre en évidence les doublons sans les 1ères occurrences

Pour mettre en évidence les 2èmes occurrences et toutes les occurrences ultérieures de doublons, sélectionnez les cellules que vous voulez colorer, et créez une règle basée sur une formule de cette manière :

1. Sur l’onglet Accueil, dans le groupe Styles, cliquez sur Mise en forme conditionnelle > Nouvelle règle >Utiliser une formule pour déterminer les cellules à mettre en forme.
2. Dans la case Formater les valeurs où cette formule est vraie, entrez une formule similaire à celle-ci :
   =COUNTIF($A:$A2,$A2)>1

   Où A2 est la cellule la plus haute de la plage sélectionnée.

3. Cliquez sur le bouton Format… et sélectionnez la couleur de remplissage et/ou de police que vous souhaitez.
4. Enfin, cliquez sur OK pour enregistrer et appliquer la règle.

Si vous n’avez pas beaucoup d’expérience avec le formatage conditionnel d’Excel, vous trouverez les étapes détaillées pour créer une règle basée sur une formule dans le tutoriel suivant : Mise en forme conditionnelle Excel basée sur la valeur d’une autre cellule.

Comme résultat, les cellules dupliquées à l’exception des premières instances seront mises en évidence avec la couleur de votre choix:

Comment afficher les 3e, 4e et tous les enregistrements en double suivants

Pour afficher les doublons à partir de la Nième occurrence, créez une règle de formatage conditionnel basée sur la formule comme dans l’exemple précédent, avec la seule différence que vous remplacez >1 à la fin de la formule par le nombre requis. Par exemple :

Pour mettre en évidence la 3e instance et toutes les instances en double suivantes, créez une règle de formatage conditionnel basée sur cette formule :

=COUNTIF($A:$A2,$A2)>=3

Pour mettre en évidence la 4e instance et tous les enregistrements en double suivants, utilisez cette formule :

=COUNTIF($A:$A2,$A2)>=4

Pour mettre en évidence uniquement des occurrences spécifiques, utilisez l’opérateur égal à (=). Par exemple, pour ne mettre en évidence que les 2èmes instances, vous iriez avec cette formule:

=COUNTIF($A:$A2,$A2)=2

Comment mettre en évidence les doublons dans une plage (colonnes multiples)

Lorsque vous voulez vérifier les doublons sur plusieurs colonnes, non pas en comparant les colonnes entre elles, mais trouver toutes les instances du même élément dans toutes les colonnes, utilisez l’une des solutions suivantes.

Mettre en évidence les doublons sur plusieurs colonnes, y compris les 1ères occurrences

Si la première instance d’un élément qui apparaît plus d’une fois dans l’ensemble de données est considérée comme un doublon, le plus simple est d’utiliser la règle intégrée d’Excel pour les doublons.

Ou, créer une règle de formatage conditionnel avec cette formule :

Par exemple, pour mettre en évidence les doublons dans la plage A2 :C8, la formule est la suivante :

=COUNTIF($A:$C, A2)>1

Veuillez noter l’utilisation de références absolues de cellules pour la plage ($A$2:$C$8), et de références relatives pour la cellule supérieure (A2).

Mettre en évidence les doublons dans plusieurs colonnes sans les 1ères occurrences

La solution pour ce scénario est beaucoup plus délicate, pas étonnant qu’Excel n’ait pas de règle intégrée pour cela 🙂

Pour mettre en évidence les doublons dans plusieurs colonnes en ignorant les 1ères occurrences, vous devrez créer 2 règles avec les formules suivantes :

Règle 1. S’applique à la première colonne

Ici, vous utilisez exactement la même formule que celle que nous avons utilisée pour mettre en évidence les doublons sans les 1ères occurrences dans une colonne (les étapes détaillées se trouvent ici).

Dans cet exemple, nous créons une règle pour A2:A8 avec cette formule:

=COUNTIF($A:$A2,$A2)>1

Comme résultat, les éléments en double sans 1ère occurrence sont mis en évidence dans la colonne la plus à gauche de la plage (il n’y a qu’un seul élément de ce type dans notre cas) :

Règle 2. S’applique à toutes les colonnes suivantes

Pour mettre en évidence les doublons dans les colonnes restantes (B2:C8), utilisez cette formule:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

Dans la formule ci-dessus, la première fonction COUNTIF compte les occurrences d’un élément donné dans la première colonne, et la seconde fonction COUNTIF fait de même pour toutes les colonnes suivantes. Et ensuite, on additionne ces nombres et on vérifie si la somme est supérieure à 1.

Comme résultat, tous les éléments dupliqués à l’exclusion de leurs 1ères occurrences sont trouvés et mis en évidence :

Mise en évidence des doublons dans toutes les colonnes avec une seule règle

Une autre solution possible est d’ajouter une colonne vide à gauche de votre ensemble de données, et de combiner les formules ci-dessus en une seule formule comme ceci:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Où B2 est la cellule supérieure avec des données dans la 2e colonne de la plage cible.

Pour mieux comprendre la formule, décomposons-la en 2 parties principales :

• Pour la première colonne (B), la condition IF n’est jamais remplie, donc seule la deuxième fonction COUNTIF est appliquée, qui est la formule régulière que nous avons déjà utilisée pour trouver les doublons sans 1ère occurrence dans une colonne.
• Pour toutes les colonnes suivantes (C2:D8), le point clé est l’utilisation astucieuse des références absolues et relatives dans les deux fonctions COUNTIF. Pour faciliter la compréhension, je l’ai copié dans la colonne G, afin que vous puissiez voir comment la formule change lorsqu’elle est appliquée à d’autres cellules:
  

Parce que la condition IF est toujours VRAIE pour toutes les colonnes autres que la première (le nombre de colonnes est supérieur à 1), la formule se déroule de cette façon :

• Compte le nombre d’occurrences d’un élément donné (D5 dans la capture d’écran ci-dessus) dans toutes les colonnes à gauche de la colonne donnée : COUNTIF(B:$C,D5)
• Compte le nombre d’occurrences d’un élément donné dans la colonne de l’élément, jusqu’à la cellule de l’élément : COUNTIF(D:D5,D5)
• Enfin, la formule additionne les résultats des deux fonctions COUNTIF. Si le nombre total est supérieur à 1, c’est-à-dire s’il y a plus d’une occurrence de l’élément, la règle est appliquée et l’élément est mis en évidence.

Mise en évidence de lignes entières sur la base de valeurs en double dans une colonne

Si votre tableau contient plusieurs colonnes, vous pouvez vouloir mettre en évidence des lignes entières sur la base d’enregistrements en double dans une colonne spécifique.

Comme vous le savez déjà, la règle intégrée d’Excel pour les doublons ne fonctionne qu’au niveau de la cellule. Mais une règle personnalisée basée sur une formule n’a aucun problème pour ombrer les rangées. Le point clé est de sélectionner les lignes entières, puis de créer une règle avec l’une des formules suivantes:

• Pour mettre en évidence les lignes en double excluant les 1ères occurrences:
  =COUNTIF($A:$A2, $A2)>1
• Pour mettre en évidence les lignes en double incluant les 1ères occurrences:
  =COUNTIF($A:$A, $A2)>1

Où A2 est la première cellule et A15 est la dernière cellule utilisée dans la colonne que vous voulez vérifier pour les doubles. Comme vous le voyez, l’utilisation astucieuse des références de cellules absolues et relatives est ce qui fait la différence.
La capture d’écran suivante démontre les deux règles en action:

Comment mettre en évidence les lignes en double dans Excel

L’exemple précédent a démontré comment colorer des lignes entières en fonction des valeurs en double dans une colonne spécifique. Mais que se passe-t-il si vous voulez visualiser des lignes qui ont des valeurs identiques dans plusieurs colonnes ? Ou, comment mettre en évidence les lignes de doublons absolus, celles qui ont des valeurs complètement égales dans toutes les colonnes ?

Pour cela, employez la fonction COUNTIFS qui permet de comparer les cellules par plusieurs critères. Par exemple, pour mettre en évidence les lignes en double qui ont des valeurs identiques dans les colonnes A et B, utilisez l’une des formules suivantes :

• Pour mettre en évidence les lignes en double sauf les 1ères occurrences :
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Pour mettre en évidence les lignes en double avec les 1ères occurrences :
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

La capture d’écran suivante démontre le résultat :

Comme vous le comprenez, l’exemple ci-dessus est uniquement à des fins de démonstration. Lorsque vous mettez en évidence les lignes en double dans vos feuilles de la vie réelle, vous n’êtes naturellement pas limité à la comparaison des valeurs uniquement dans 2 colonnes, la fonction COUNTIFS peut traiter jusqu’à 127 paires plage/critères.

Mise en évidence des cellules en double consécutives dans Excel

Parfois, vous n’avez pas besoin de mettre en évidence tous les doublons dans une colonne, mais plutôt de montrer seulement les cellules en double consécutives, c’est-à-dire celles qui sont à côté les unes des autres. Pour ce faire, sélectionnez les cellules contenant des données (sans inclure l’en-tête de colonne) et créez une règle de formatage conditionnel avec l’une des formules suivantes :

• Pour mettre en évidence les doublons consécutifs sans 1ère occurrence :
  =$A1=$A2
• Pour mettre en évidence les doublons consécutifs avec les 1ères occurrences:
  =OR($A1=$A2, $A2=$A3)

La capture d’écran suivante démontre la mise en évidence des doublons consécutifs de textes, mais ces règles fonctionneront également pour les doublons consécutifs de chiffres et de dates :

Si votre feuille Excel peut avoir des lignes vides et que vous ne voulez pas que les cellules vides consécutives soient mises en évidence, apportez les améliorations suivantes aux formules :

• Pour mettre en évidence les cellules dupliquées consécutives sans les 1ères occurrences et ignorer les cellules vides:
  =AND($A2<>"", $A1=$A2)
• Pour mettre en évidence les cellules dupliquées consécutives avec les 1ères occurrences et ignorer les cellules vides:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Comme vous le voyez, ce n’est pas un gros problème de mettre en évidence les doublons dans Excel en utilisant la mise en forme conditionnelle. Cependant, il existe même un moyen plus rapide et plus simple. Pour le découvrir, lisez la section suivante de ce tutoriel.

Comment mettre en évidence les doublons dans Excel avec Duplicate Remover

Le complément Duplicate Remover est la solution tout-en-un pour traiter les enregistrements en double dans Excel. Il peut trouver, mettre en évidence, sélectionner, copier ou déplacer des cellules dupliquées ou des lignes entières dupliquées.

Malgré son nom, le complément peut rapidement mettre en évidence les doublons dans différentes couleurs sans les supprimer.

Le Duplicate Remover ajoute 3 nouvelles fonctionnalités à votre ruban Excel:

• Tableau de dédoublonnage – pour trouver et mettre en évidence immédiatement les doublons dans un tableau.
• Suppression des doublons – assistant étape par étape avec des options avancées pour identifier et mettre en évidence les doublons ou les valeurs uniques dans 1 tableau.
• Comparer 2 tableaux – trouver et mettre en évidence les doublons en comparant deux colonnes ou deux tableaux distincts.

Après avoir installé Ultimate Suite pour Excel, vous trouverez ces outils sur l’onglet Ablebits Data dans le groupe Déduplication :

Mise en évidence des doublons dans Excel en quelques clics

Pour cet exemple, j’ai créé le tableau suivant avec quelques centaines de lignes. Et notre objectif est de mettre en évidence les lignes en double qui ont des valeurs égales dans les trois colonnes:

Croyez-le ou non, vous pouvez obtenir le résultat souhaité en seulement 2 clics de souris 🙂

1. Avec n’importe quelle cellule de votre tableau sélectionnée, cliquez sur le bouton Dedupe Table, et l’astucieux add-in ramassera tout le tableau.
2. La fenêtre de dialogue Dedupe Table s’ouvrira avec toutes les colonnes sélectionnées automatiquement, et l’option Color duplicates choisie par défaut. Ainsi, tout ce que vous avez à faire est de cliquer sur OK :). C’est fait!

Conseil. Si vous voulez détecter les lignes en double par une ou plusieurs colonnes, décochez toutes les colonnes non pertinentes et ne laissez que la ou les colonnes clés sélectionnées.

Et le résultat ressemblerait à ceci :

Comme vous le voyez dans l’image ci-dessus, l’outil Tableau des doublons a mis en évidence les lignes en double sans première occurrence.

Si vous voulez mettre en évidence les doublons y compris les premières occurrences, ou si vous voulez colorer les enregistrements uniques plutôt que les doublons, ou si vous n’aimez pas la couleur rouge par défaut, alors utilisez l’assistant de suppression des doublons qui a toutes ces fonctionnalités et bien d’autres encore.

Mettez en évidence les doublons dans Excel à l’aide d’un assistant avancé étape par étape

Par rapport à l’outil rapide Dedupe Table, l’assistant de suppression des doublons nécessite quelques clics de plus, mais il compense cela par un certain nombre d’options supplémentaires. Laissez-moi vous le montrer en action :

1. Sélectionnez n’importe quelle cellule de votre tableau où vous voulez mettre en évidence les doublons, et cliquez sur le bouton Suppression des doublons sur le ruban. L’assistant s’exécutera et l’ensemble du tableau sera sélectionné. Le module complémentaire vous suggère également de créer une copie de sauvegarde de votre tableau, au cas où. Si vous n’en avez pas besoin, décochez cette case.

   Vérifiez que la table a été sélectionnée correctement et cliquez sur Suivant.

   

2. Sélectionnez l’un des types de données suivants que vous souhaitez trouver :
   • Duplicats sauf les 1ères occurrences
   • Duplicats avec les 1ères occurrences
   • Valeurs uniques
   • Valeurs uniques et 1ères occurrences de doublons

   Pour cet exemple, trouvons les doublons + 1ères occurrences :
   

3. Maintenant, sélectionnez les colonnes à vérifier pour les doublons. Parce que nous voulons mettre en évidence les lignes complètes de doublons, j’ai sélectionné les 3 colonnes.
   

   En outre, l’add-in vous permet de spécifier si votre tableau a des en-têtes et si vous voulez sauter les cellules vides. Ces deux options sont sélectionnées par défaut.

4. Enfin, choisissez l’action à effectuer sur les doublons. Vous avez un certain nombre d’options telles que la sélection, la suppression, la copie, le déplacement des doublons ou l’ajout d’une colonne d’état pour identifier les doublons.

Puisqu’aujourd’hui nous explorons différentes façons de mettre en évidence les doublons dans Excel, notre choix est évident :). Donc, sélectionnez Remplir avec une couleur et choisissez l’une des couleurs standard du thème, ou cliquez sur Plus de couleurs… et choisissez n’importe quelle couleur RVB ou HSL personnalisée.

Cliquez sur le bouton Terminer et appréciez le résultat 🙂

C’est ainsi que vous surlignez les doublons dans Excel en utilisant notre complément Duplicate Remover. Si vous êtes curieux d’essayer cet outil sur vos propres feuilles de calcul, vous êtes le bienvenu pour télécharger une version d’essai entièrement fonctionnelle de la suite Ultimate qui comprend tous nos outils de gain de temps pour Excel. Et vos commentaires dans les commentaires seront grandement appréciés !

Si vous êtes satisfait des performances de Duplicate Remover, nous sommes heureux de vous offrir cette opportunité très spéciale :

Vous pourriez également être intéressé par

• Comment identifier les doublons dans Excel (dans 1 tableau)
• Comment comparer 2 colonnes ou tableaux pour les correspondances et les différences
• Comment supprimer les lignes en double dans Excel

.