Il n'est pas toujours simple de repérer des doublons dans un tableau, surtout quand il est grand. Mais Excel dispose de nombreuses fonctions pour les chercher, les afficher, les masquer, les compter et les supprimer si besoin.

S'il est normal que certains de vos tableaux comportent des données en double dans telle ou telle colonne –, si vous êtes dans le commerce, vous avez heureusement le droit de vendre une même référence de produit le vendredi puis le mardi et le jeudi suivants ! –, il est tout aussi normal de vouloir, à un moment ou à un autre, ne conserver qu'une liste de données uniques, ou réaliser des cumuls pour ces doublons, voire effectuer ces deux tâches à la fois.

Un simple tri sur la bonne colonne d'un tableau Excel suffit déjà à repérer les valeurs identiques ou approchantes, qu'il s'agisse de libellés de texte ou de dates, par exemple. Mais Excel étant né en 1985, au fil des versions, lui aussi a cumulé ! Il offre aujourd'hui de nombreuses fonctions, parfois redondantes ou aux particularités subtiles, et qu'on retrouve dans différents menus, pour vous aider à traiter ces doublons comme vous le souhaitez : les mettre à l'honneur, les masquer, les cumuler ou en calculer la moyenne, ou même les supprimer.

La présentation et le nom des options diffèrent un peu entre les versions d'Excel pour Windows, pour Mac et pour le Web, mais elles opèrent à l'identique.

Comment repérer les doublons dans une ou plusieurs colonnes dans Excel ?

Il existe plusieurs façons pour repérer d'un coup d'œil toutes les valeurs en double dans une ou plusieurs colonnes, sans pour autant les supprimer.

• Cliquez dans n'importe quelle cellule de votre tableau et, pour en sélectionner toutes les données, pressez la combinaison de touches Ctrl+A sur PC ou Cmd+A sur Mac. Que la première ligne d'en-tête (Élève, Matière) fasse ou non partie de la sélection n'a aucune importance pour la fonction que vous allez lancer ici. Sachez toutefois qu'en pressant à nouveau Ctrl+A, la sélection s'étendrait à ces titres de colonnes (si ces en-têtes ne sont pas déjà sélectionnés), et qu'une nouvelle pression sur Ctrl+A sélectionnerait la feuille entière.

• Vous pourriez aussi ne sélectionner qu'une seule colonne pour ne vous intéresser qu'aux doublons dans cette colonne : cliquez sur la première valeur (par exemple sur Pierre dans notre exemple) et pressez les trois touches Ctrl+Maj+Flèche vers le bas, sur PC comme sur Mac ou dans Excel pour le Web. Vous pouvez aussi cliquer, en haut, sur le A de la colonne A.
• Dans l'onglet Accueil, cliquez sur Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double.

• Dans la première liste déroulante, sélectionnez en double (l'autre critère possible est : uniques), et dans la deuxième liste, choisissez un formatage de cellule, par exemple Remplissage rouge clair ou précisez-en un autre en cliquant sur Format personnalisé…

• Si vous avez choisi un format personnalisé, la fenêtre de Format de cellule vous laisse choisir la mise en forme des doublons grâce aux onglets Nombre, Police, Bordure et Remplissage. Cliquez par exemple sur le bouton Motifs et textures, vous pourrez piocher deux couleurs dans la palette pour créer un dégradé.

• Pressez plusieurs fois OK pour valider vos choix, les cellules en double sont formatées comme vous l'aviez précisé pour être repérées plus facilement.

• Ce formatage n'écrase pas l'ancien formatage de vos cellules, il disparaîtra quand vous sélectionnerez votre tableau (Ctrl+A ou Cmd+A) et que, dans l'onglet Accueil, vous lancerez Mise en forme conditionnelle > Effacer les règles des cellules sélectionnées. Pour vous éviter d'avoir à sélectionner tout le tableau, vous pouvez aussi cliquer sur Effacer les règles de la feuille entière.

• Si une valeur, par exemple Emma, apparaît deux fois dans la colonne Élève, Excel marque deux fois cette valeur Emma. Nous le précisons car dans d'autres fonctions que l'on verra plus loin, Excel laisse intacte la première occurrence d'Emma, mais agit uniquement sur les suivantes, les doublons.
• Même si plusieurs colonnes sont sélectionnées (comme sur notre exemple), cette fonction d'Excel recherche les doublons dans chaque colonne, indépendamment des autres. Excel ne recherche donc pas dans le tableau toutes les lignes où apparaît le couple Emma+Histoire, il formate Emma si le prénom figure plusieurs fois dans la colonne Élève, et formate Histoire si ce libellé apparaît plusieurs fois dans la colonne Matière. Vous pourriez d'ailleurs lancer la commande sur la première colonne, puis sur la seconde, vous obtiendriez les mêmes résultats qu'en la lançant d'emblée sur les deux colonnes du tableau.
• Excel ne tient pas compte de la casse (des majuscules) : toto, Toto, ToTo et TotO sont mentionnés comme doublons.
• Le tableur ne tient pas compte non plus du format : si trois cellules stockent la date 11/11/2020, toutes seront mentionnées comme doublons même si, du fait de leur format d'affichage, la première affiche 11-nov, la deuxième 11/11 et la troisième 11/11/20.

Comment filtrer les données pour afficher les doublons dans Excel ?

Les filtres sont vraiment très pratiques pour n'afficher que certaines lignes d'un tableau. Excel masque les lignes qui ne correspondent pas à vos critères mais ne supprime aucune donnée – si vous utilisez un tableur compatible Excel, une fonction de filtrage vous est à coup sûr proposée. Avec les filtres, vous pouvez, par exemple, afficher dans une liste de produits, seulement ceux dont la marque est Sony – ce qui revient à trouver tous les doublons de Sony. Et créer tout aussi facilement des filtrages multicritères sur plusieurs colonnes, du type : Marque=Sony et Catégorie=Téléviseur.

• Les filtres d'Excel sont reconnaissables à la petite flèche déroulante qui s'affiche à droite de chaque en-tête de colonne, pour trier ou filtrer vos données sur des critères que vous allez préciser. Il est indispensable que vos colonnes commencent chacune par un en-tête (un titre de colonne).

• Pour activer les filtres, une première méthode consiste à cliquer sur une cellule non vide de votre plage de cellules et, dans l'onglet Accueil, à cliquer sur Trier et filtrer, puis en dessous sur Filtrer. Le raccourci clavier est Ctrl+Maj+L sur macOS et Windows (cette icône Filtrer est également disponible sous l'onglet Données). Les petites flèches déroulantes apparaissent sur la première ligne contenant les en-têtes.

• Autre méthode possible pour activer les filtres, plus spécifique à Excel et qui vous offre plus de possibilités (vous en verrez plus loin un usage pour calculer la somme des doublons) : créer un "tableau" à partir de vos données. Commencez par cliquer sur l'une de vos données. Sous l'onglet Accueil, cliquez maintenant sur Mettre sous forme de tableau, sélectionnez une mise en forme parmi celles proposées…

•  … Excel sélectionne tout le tableau et repère s'il comporte ou non des en-têtes. Si vos colonnes n'en ont pas, il en ajoute en insérant une ligne au-dessus, et les appelle Colonne 1, Colonne 2, etc. Mais il est préférable de nommer vous-même vos colonnes (Date, Client, Prestation…). Cliquez sur OK, le tableau est mis en forme et les filtres sont activés d'office.

• Quelle que soit la méthode par laquelle vous avez activé les filtres, cliquez sur une flèche déroulante d'un titre de colonne pour découvrir les options de tri et de filtrage. La présentation, quoiqu'un peu différente sur Mac, offre la même richesse.

• Si vous ne souhaitez voir, par exemple, que les lignes correspondant au client Dupond, le plus rapide consiste d'abord à décocher la case (Sélectionner tout) – ce qui va donc tout désélectionner –, puis à cocher uniquement la case Dupond (Excel considère que Dupond et dupond sont identiques).

• Seules les lignes correspondant à ce client restent affichées : vous en voyez donc les doublons. Les autres lignes sont simplement masquées, vous pouvez d'ailleurs le constater à gauche, sur notre illustration, les numéros de ligne indiquent : 1, 2, 4, 6, 11 (alors que les lignes 3, 5, 7, 8, 9, 10 sont cachées).

• Vous pourriez ajouter des critères de filtrage sur les autres colonnes. Pour une recherche multicritère, par exemple pour ne retenir que la prestation Article de blog du client Dupond, cliquez, en plus, sur la flèche déroulante Prestation pour ne cocher que ce libellé. Vous pourriez sinon demander que la cellule contienne (ou ne contienne pas) du texte que vous indiquez, etc.

• Pour supprimer un filtre sur une colonne, cliquez sur le bouton déroulant puis sur Effacer le filtre.

• Pour désactiver les filtres définitivement et supprimer les petites flèches déroulantes : sous l'onglet Accueil, cliquez sur Trier et filtrer, puis sur Filtrer.

Comment filtrer les données pour masquer les doublons dans Excel ?

Les filtres permettent de n'afficher que les doublons dans une feuille de calcul. Mais vous pouvez utiliser les filtres avancés d'Excel pour, au contraire, les cacher sans pour autant supprimer les données en double. Notez que cette fonction n'est disponible que dans les versions Windows et Mac d'Excel, et pas dans la version Web.

• Dans votre plage de données, sélectionnez uniquement la colonne susceptible de comporter des doublons. Cliquez par exemple sur sa première valeur et pressez les trois touches Ctrl+Maj+Flèche vers le bas.

• Cliquez, en haut, sur l'onglet Données, puis sur Avancé, dans la section Trier et filtrer.

• Dans la fenêtre Filtre avancé, vérifiez que seule la colonne comportant les doublons est sélectionnée.

• Cochez la case Extraction sans doublon et laissez vide le champ Zone de critères.
• Si vous souhaitez Filtrer la liste sur place, seule la première apparition d'un élément en double reste affichée, les lignes de votre tableau comportant des doublons sont cachées. Vous le constatez à gauche, en regardant les numéros de lignes : sur notre exemple, on passe de la ligne 3 à la ligne 6.

• Pour réafficher les lignes cachées, vous pouvez soit : dans l'onglet Données, cliquer sur Effacer…

• … ou, dans l'onglet Accueil, cliquer sur Trier et filtrer, puis sur Effacer.

• Une astuce sur PC : si vous souhaitez vraiment supprimer ces cellules cachées, cliquez sur l'onglet Fichier > Informations > bouton Vérifier l'absence de problèmes, choix Inspecter le document > bouton Inspecter… Faites défiler la liste des problèmes inspectés et, à la section Lignes et colonnes cachées, pressez le bouton Supprimer tout. Attention, cette action ne peut pas être annulée !

• Si vous préférez Copier vers un autre emplacement (c'est uniquement possible dans la feuille active), cliquez sur une cellule vide, ci-dessous nous avons choisi H1… Après avoir coché Extraction sans doublon et pressé le bouton OK, les éléments uniques sont recopiés de H1 à H6. Sans toucher à votre tableau d'origine, cette colonne pourrait vous servir à calculer le montant généré par chaque client, nous vous indiquons plus loin la formule.

Comment supprimer des doublons dans Excel ?

Vous voulez supprimer définitivement les doublons dans une feuille de calcul ? S'il s'agit d'un tableau à plusieurs colonnes, la ligne entière de votre tableau où apparaît le doublon sera supprimée. Si vos données se résument à une colonne, vous obtiendrez la liste des données uniques. Excel propose pour cela une fonction spéciale. Sa méthode : il garde le premier élément et supprime tous les doublons qu'il détecte ensuite, sans se soucier de la pertinence de conserver le premier plutôt que le dernier, et sans par exemple effectuer de cumul (apprenez ici à faire le cumul de doublons). Selon vos données, un tri préalable sera donc parfois utile…

• Même s'il est évidemment possible d'annuler l'action (Ctrl+Z sur PC ou Cmd+Z sur Mac), il est préférable de travailler sur une copie de vos données ou même de dupliquer la feuille de calcul avant d'effectuer la suppression. Un petit clic du bouton droit sur l'onglet de la feuille de calcul vous y aidera...

• Bien que ce ne soit pas obligatoire, mieux vaut que vos colonnes comportent une ligne d'en-tête.
• Cliquez sur une seule cellule, quelconque, de votre tableau.
• Dans l'onglet Données, cliquez sur Supprimer les doublons.

• Si vous avez transformé vos données en "tableau" Excel via l'onglet Accueil > Mettre sous forme de tableau, cette option Supprimer les doublons se retrouve également dans l'onglet Création de tableau sur PC, ou Tableau sur Mac, ou Création dans Excel pour le Web. Cet onglet n'est visible que lorsque vous sélectionnez l'une des cellules du tableau.
• Excel sélectionne tout le tableau et liste les en-têtes de colonnes qu'il a repérés.

• Rassurez-vous : que vous cochiez ou décochiez telle ou telle case, Excel ne risque pas de supprimer une valeur dans une colonne sans toucher aux autres sur la même ligne, ce qui créerait un décalage comme sur une chemise mal boutonnée ! S'il doit supprimer un doublon, dans tous les cas, il supprimera une ligne complète de la plage de données (les cellules situées en dehors de votre tableau ne sont pas impactées).
• Si vous laissez tous les noms de colonne cochés, Excel ne considérera comme doublon que les lignes où figurent, dans notre exemple : à la fois la même date, mais en plus le même nom de client, mais aussi le même type de prestation, et enfin obligatoirement le même montant. Si l'une des valeurs diffère dans les lignes en dessous, elles ne seront pas considérées comme doublon.
• Plus probablement, vous voudrez donc ne cocher que certaines colonnes. En cochant par exemple uniquement la colonne Client (illustration ci-dessous) : chaque nom de client n'apparaîtra plus qu'une seule fois dans votre tableau (3 Dupond sont supprimés, 1 Durand et 1 Matthieu).

• En cochant par exemple Client et Prestation (illustration ci-dessous) : seules les lignes ayant à la fois le même nom et la même prestation sont considérées comme des doublons. Sur notre exemple, trois lignes comportaient comme Client/Prestation les valeurs Dupond/Article de blog, les deux dernières lignes en double sont supprimées.

• Ci-dessus, nous avons envisagé le cas où vous souhaitez supprimer les lignes en doublon directement dans votre tableau. Mais vous pourriez par exemple copier-coller une seule colonne du tableau, par exemple copier les cellules B1:B11 de la colonne Client, pour les coller en cellule F1. Et en demandant la suppression des doublons en F1:F11…

• … vous obtiendrez la liste de vos clients uniques. Cette colonne pourra vous servir à calculer le chiffre d'affaires généré par chaque client, comme nous allons le voir maintenant.

Comment calculer la somme des doublons dans une feuille Excel ?

Savoir qu'il existe des doublons, c'est bien. Les supprimer pour ne garder que des éléments uniques, pourquoi pas ? Mais la plupart du temps, vous voudrez d'abord faire des calculs sur ces doublons, obtenir au moins le total, quitte à les supprimer ensuite ! Voici comment vous y prendre, selon la méthode que vous aviez choisie pour détecter les valeurs en double.

Calculer le total des valeurs filtrées

Si vous avez simplement filtré vos données, une petite formule suffira à calculer le total des cellules filtrées, et donc le total général si toutes les cellules sont affichées.

• Sur une plage de cellules filtrée via l'onglet Accueil > Trier et filtrer > Filtrer, la fonction SOUS.TOTAL d'Excel permet de calculer la somme des cellules affichées (ou la moyenne, ou le nombre de cellule, etc., selon les paramètres que vous indiquez).
• Sur l'exemple ci-dessous, en inscrivant en cellule G1 la formule :
   =SOUS.TOTAL(109;$D:$D)
  pour effectuer le total de la colonne D, on obtient le total général puisqu'aucune colonne n'est ici filtrée. La valeur 109 signifie : effectue la somme en ignorant les cellules masquées.

• Si vous ne souhaitez pas prendre en compte toute la colonne D, mais uniquement les cellules où sont stockées vos données, la formule pourrait s'écrire :
   =SOUS.TOTAL(109;D2:D11)
• Si l'on filtre la colonne Client pour ne retenir que le client Dupond, la fonction SOUS.TOTAL – qui exclut toujours les valeurs filtrées de ses calculs – vous indique le total des données affichées, donc du client Dupond.

Calculer le total d'un tableau Excel

Si vous avez transformé votre plage de cellules en un tableau Excel via l'onglet Accueil > Mettre sous forme de tableau, vous n'avez pas à écrire de formule, Excel la gère pour vous.

• Cliquez dans n'importe quelle cellule du tableau : un onglet vert Création de tableau sur PC (ou Tableau sur Mac, ou Création dans Excel pour le Web) s'ajoute aux onglets déjà présents (Fichier, Accueil, Insertion…), en haut de l'écran.
• Cliquez sur cet onglet Création de tableau puis, en dessous, sur Ligne Total. Excel ajoute une ligne de total au bas de votre tableau, pour les colonnes contenant des nombres.

• Si vous filtrez vos données, le total vous indique la somme des seules valeurs affichées. Une flèche déroulante vous sert à choisir la moyenne ou une autre fonction, au lieu de la somme.

Calculer le total de chaque doublon

Sans toucher à vos données initiales, vous avez créé une colonne débarrassée des doublons pour ne conserver que les éléments uniques ? S'il s'agit, par exemple, d'une liste de clients, une petite formule suffira pour calculer le chiffre d'affaires généré par chacun de ces clients.

• Sur notre exemple, la colonne G comporte la liste des clients uniques. La formule inscrite en H2 permet de calculer le chiffre d'affaires généré par le client inscrit en cellule G2, donc Dupond :
  =SOMME.SI($B:$B;G2;$D:$D)

• Avec cette formule, Excel compare le contenu de la cellule G2 (Dupond) à chaque valeur de la colonne B. S'il y a équivalence, il cumule la valeur inscrite sur la même ligne, mais en colonne D. Par exemple, s'il compare Dupond au contenu de la cellule B5, qui comporte elle aussi Dupond, il ajoute à son cumul la valeur inscrite en D5.
• Recopiez cette formule dans les cellules H3 à H6 pour connaître le chiffre d'affaires généré par chaque client. La formule recopiée en cellule H3 devient par exemple :
  =SOMME.SI($B:$B;G3;$D:$D)

Insérer des sous-totaux automatiques dans les tableaux

Dernière solution possible que nous vous proposons : les sous-totaux. Excel pour Windows et pour Mac (mais pas pour le Web) savent, en effet, insérer automatiquement des lignes de sous-totaux et de total général pour cumuler vos données. Le tableur peut calculer la somme, ou la moyenne, ou le min, le max, l'écart-type, etc. Avec quelques contraintes à connaître :

• Les sous-totaux ne sont pas pris en charge dans les "tableaux Excel". Quand vous cliquez sur une cellule de votre plage de données, si un onglet vert Création de tableau s'affiche en haut de l'écran (il s'appelle Tableau sur Mac, et Création dans Excel pour le Web), vous devez d'abord cliquer en dessous sur l'icône Convertir en plage afin de pouvoir demander à Excel d'insérer des sous-totaux.
• Dès que vous cliquez sur l'icône Sous-total, Excel vide son historique d'annulation. Vous ne pourrez donc plus annuler une ancienne commande en tapant Ctrl+Z, mais il reste possible de fermer le fichier et de le rouvrir pour revenir au dernier enregistrement du classeur Excel. Travaillez sur une copie de votre fichier si vous faites des essais.
• L'insertion des sous-totaux active le mode Plan, qui peut au départ impressionner les débutants. Il n'y a pas de raison !

Voici comment ajouter des sous-totaux dans vos colonnes de chiffres.

• Commencez par trier vos données pour que les doublons soient regroupés les uns à la suite des autres. S'il s'agit d'un tri sur une colonne, cliquez sur n'importe quelle valeur de cette colonne (sur une seule cellule seulement) puis, dans l'onglet Données, cliquez par exemple sur l'icône AZ pour un tri simple, croissant. Si vous devez trier sur deux colonnes ou plus, ou préciser d'autres critères de tri, cliquez plutôt sur l'icône Trier.

• Sur l'exemple ci-dessous, notre tableau, précédemment trié sur la colonne Date, est à présent trié sur la colonne Client, par ordre alphabétique, d'un clic sur l'icône AZ.

• Une fois vos données triées, sélectionnez n'importe quelle cellule de votre tableau (mais une seule cellule) et, toujours sous l'onglet Données, cliquez sur l'icône Sous-total.
• La fenêtre des options de Sous-total s'affiche. Excel propose d'effectuer un sous-total À chaque changement de Date. Déroulez cette liste et précisez plutôt À chaque changement de Client.

• La liste Utiliser la fonction indique Somme. Cliquez dessus si vous préférez une autre fonction (moyenne, min, max, écart-type, nombre de valeurs, etc.).
• Cochez ensuite les rubriques numériques (il peut s'agir d'une colonne de sommes facturées ou d'une colonne d'heures indiquant la durée de chaque prestation, par exemple) sur lesquelles doit s'effectuer le cumul.
• Si vous préférez que la ligne de total général figure en haut du tableau plutôt qu'en fin de liste, et que le sous-total de chaque client s'affiche au-dessus plutôt qu'en dessous de ses lignes de détail, décochez la case Synthèse sous les données.
• Notez la présence du bouton Supprimer tout, pour supprimer les lignes de sous-totaux et désactiver le monde Plan.
• Voici notre tableau avec, pour notre colonne Montant, les sous-totaux en gras insérés par Excel (Total Bertrand, Total Dupond, etc.), et le Total général en fin de liste. De votre côté, si votre tableau comporte plusieurs colonnes qui méritent un sous-total (des colonnes de nombres, de durées, etc.), cochez-en plusieurs.

• À gauche, si vous cliquez sur le niveau 2 du plan, vous n'affichez plus que les lignes correspondant aux sous-totaux et au total général.

• Si vous effectuez des calculs horaires et constatez que les totaux horaires sont faux car toujours ramenés à 24:00:00 (21h+5h=2h au lieu de 26h !), sélectionnez les cellules contenant vos données horaires, cliquez dessus avec le bouton droit de la souris, choisissez Format de cellule dans le menu contextuel. Sous l'onglet Nombre, cliquez à gauche sur Heure et, dans la liste Type, cliquez sur le format-exemple 37:30:55.

• Excel affiche à présent les bons totaux horaires ! Vous souhaitez créer un format horaire personnalisé ? Entourez par des crochets le [h] représentant les heures pour demander à Excel d'afficher des horaires supérieurs à 24 heures : [h]:mm:ss au lieu de h:mm:ss

Comment interdire la saisie de doublons dans une colonne Excel ?

Pour empêcher qu'une même valeur soit saisie plusieurs fois dans une colonne, il suffit d'utiliser la fonction de validation des données d'Excel.

• Sélectionnez la colonne entière ou la plage de cellules où vous devrez saisir vos données, ci-dessous la colonne B.

• Dans l'onglet Données, cliquez sur Validation des données.

• Dans la liste Autoriser, sélectionnez Personnalisé.
• Dans la zone de saisie Formule, puisque c'est la cellule B1 qui est actuellement sélectionnée, tapez la formule :
  =NB.SI(B:B;B1)=1

• Adaptez cette formule : si c'est par exemple la cellule C3 qui est sélectionnée, tapez =NB.SI(C:C;C3)=1
• Dans cette même fenêtre de dialogue de Validation des données, cliquez sur l'onglet Alerte d'erreur.

• Dans la zone Titre, indiquez un court message, ici Doublon.
• Dans la zone Message d'erreur, soyez plus explicite.
• Dans la zone Style : le choix Stop interdira la saisie de doublons ; les choix Avertissement ou Informations affichent votre message mais laissent l'utilisateur saisir un doublon s'il le souhaite.
• Sur cet exemple, quand on presse la touche Entrée, le Style Stop interdit de saisir deux fois Dupond, même en minuscules, et propose de Réessayer.

• Pour plus d'informations sur la validation des données et l'aide à la saisie, notamment grâce à des listes déroulantes, reportez-vous à notre fiche pratique Créer des listes déroulantes dans Excel.