Le problème vient rarement des lignes. Si Excel échoue, c'est presque toujours parce que les deux listes n'utilisent pas la même clé, pas le même type de données, ou pas la même définition de ce qui constitue un même enregistrement.
C'est pour ça que la mise en forme conditionnelle peut colorer la moitié de la feuille sans vous donner la moindre réponse. Pour comparer deux listes Excel et repérer les écarts, commencez par trancher : cherchez-vous des éléments manquants, des valeurs qui divergent, ou des enregistrements qui ne correspondent pas ? Ce sont trois travaux différents. Forcés dans une seule formule, ils donnent de mauvais résultats.
Avant de construire quoi que ce soit, identifiez le type d'écart auquel vous avez réellement affaire.
À quoi ressemble l'écart avant même d'ouvrir le classeur
| Ce que vous voyez à l'écran | Comment la liste A le stocke | Comment la liste B le stocke | Ce qui se passe au moment de la correspondance |
|---|---|---|---|
001245 dans les deux feuilles | Texte avec des zéros initiaux | Nombre 1245 | Les valeurs vous semblent identiques, mais pas à Excel : la correspondance échoue |
01/05/2026 dans les deux feuilles | Date au format texte en jj/mm/aaaa | Vraie date Excel (numéro de série) | Un côté est du texte, l'autre une vraie date : les recherches et les comparaisons échouent |
INV-8842 apparaît deux fois | Même numéro de facture réparti sur deux lignes scindées | Même numéro de facture sur une seule ligne, avec un total regroupé | Une recherche un à un renvoie la mauvaise ligne ou masque un doublon |
| Les mêmes clients figurent dans les deux listes, mais dans un ordre différent | Une liste triée par date | L'autre triée par nom | La comparaison ligne par ligne signale comme différentes des lignes pourtant correctes |
1,250.00 paraît identique dans les deux fichiers | Texte contenant un espace insécable invisible | Vrai nombre | Le montant a l'air propre, mais Excel traite un côté comme du texte et l'autre comme un nombre |
Bank fee est absent d'une des listes | Un export inclut des lignes de contrôle ou d'ajustement | L'autre export les exclut | L'écart est structurel, pas dû à une formule erronée |
Si vous ne savez pas dans quel cas vous êtes, saisissez =TYPE(A2) sur une cellule suspecte de chaque liste. TYPE() renvoie 1 pour un nombre et 2 pour du texte. Vous savez aussitôt si l'écart est une vraie différence de contenu ou une simple différence de stockage qu'Excel transforme en faux écart.
Déterminez le type de comparaison que vous faites vraiment
La plupart des résultats de recherche sur le sujet partent du principe qu'il s'agit d'une colonne propre comparée à une autre colonne propre. Parfois, c'est le cas. Souvent, non.
| Votre vrai travail | Meilleure méthode | Ce qui compte comme un écart |
|---|---|---|
| Vérifier si les valeurs d'une colonne existent aussi dans une autre | NB.SI, EQUIV ou FILTRE | Élément manquant |
| Comparer les mêmes enregistrements entre deux listes à plusieurs colonnes | Rapprocher par une clé stable, puis comparer les champs | Ligne manquante ou différence de valeur |
| Comparer des enregistrements quand aucun identifiant unique n'existe | Construire une clé composite à partir de deux ou trois champs | Faux rapprochements, doublons potentiels ou enregistrements manquants |
| Comparer deux exports d'ordre, d'en-têtes ou de structure différents | Associer les colonnes par rôle avant de rapprocher | Écart structurel, et non écart de lignes |
Cette distinction compte parce que la position de la ligne n'est presque jamais la bonne clé de comparaison. Si une liste est triée par date et l'autre par client, la ligne 84 d'une feuille n'a aucune raison de correspondre à la ligne 84 de l'autre. Là, une formule ligne par ligne ne repère pas les écarts. Elle en fabrique.
Si les deux listes sont vraiment simples, utilisez la formule courte et arrêtez-vous là
Si chaque liste tient dans une seule colonne propre et que votre seule question est « qu'est-ce qui existe dans A mais pas dans B ? », la réponse rapide suffit.
Pour les versions récentes d'Excel :
=FILTRE(A2:A5000;NB.SI($D$2:$D$5000;A2:A5000)=0)Cette formule renvoie les valeurs de la liste A absentes de la liste B.
Pour l'inverse :
=FILTRE(D2:D5000;NB.SI($A$2:$A$5000;D2:D5000)=0)Pour les versions plus anciennes, utilisez une colonne de résultat intermédiaire :
=SI(NB.SI($D$2:$D$5000;A2)=0;A2;"")C'est suffisant quand :
- chaque liste a une seule colonne de comparaison
- les doublons sont sans importance
- l'ordre des lignes est sans importance
- seule compte la présence ou l'absence
Dès que le même enregistrement s'étale sur plusieurs colonnes, ou que la même valeur peut apparaître plus d'une fois, cette formule simple cesse de répondre à toute la question. Si votre travail est déjà devenu « quelles lignes exportées sont manquantes ou non rapprochées entre deux fichiers », repérer les lignes manquantes entre deux fichiers CSV dans Excel décrit un cas plus proche.
Choisissez une clé de rapprochement avant de comparer les valeurs
Un rapport d'écarts ne vaut que ce que vaut la clé utilisée pour aligner les enregistrements.
| Clé candidate | À utiliser quand | Là où elle échoue |
|---|---|---|
| Numéro de facture, identifiant de transaction, référence de commande | Les deux listes conservent le même identifiant unique | Un côté supprime l'identifiant, le tronque ou le duplique |
| Date + montant | Chaque opération est unique sur ces deux champs | Des montants identiques le même jour créent de faux appariements |
| Client + numéro de facture | Un seul champ n'est pas unique, mais la paire l'est | Les noms de clients varient ou les numéros de facture sont incohérents |
| Libellé seul | Il n'existe pas de meilleur champ | Troncatures, espaces et formulations différentes le rendent peu fiable |
Si un champ n'est pas assez unique, construisez une clé composite. Un schéma courant : référence, puis date, puis montant :
=A2&"|"&TEXTE(C2;"aaaa-mm-jj")&"|"&TEXTE(D2;"0,00")Ça fonctionne parce que chaque partie joue un rôle distinct :
- la référence identifie la transaction
- la date sépare les références réutilisées d'une période à l'autre
- le montant sépare les écritures scindées ou partielles
Une fois que les deux listes partagent la même clé, vous pouvez comparer le reste de l'enregistrement par rapport à elle. Par exemple, si la colonne H est votre clé composite dans les deux feuilles :
=RECHERCHEX($H2;Feuil2!$H$2:$H$5000;Feuil2!$J$2:$J$5000;"Manquant")Cette formule récupère le champ de la liste B associé à la même clé dans la liste A. Vous pouvez alors comparer la valeur renvoyée au champ d'origine, au lieu de comparer des positions de lignes.
Vérifiez les doublons dans la clé avant de vous fier au résultat :
=NB.SI($H$2:$H$5000;H2)Si le résultat dépasse 1, votre clé n'est pas encore un vrai un à un. RECHERCHEX renvoie la première correspondance et masque les autres. Ce n'est pas une réussite de la formule. C'est un échec de comparaison déguisé en résultat.
Normalisez les valeurs qui paraissent égales mais sont stockées différemment
C'est là que la plupart des guides de comparaison de tableurs s'arrêtent trop tôt. Ils montrent la formule de rapprochement, mais pas le nettoyage des données dont elle dépend.
Du texte d'un côté, un nombre de l'autre
Si la liste A stocke 1245 comme du texte et la liste B comme un nombre, confirmez-le d'abord avec TYPE() :
=TYPE(A2)Si A renvoie 2 et que la cellule correspondante de la liste B renvoie 1, convertissez directement dans la recherche, avant de créer une colonne intermédiaire.
Si la valeur doit être numérique :
=RECHERCHEX(CNUM(A2);Feuil2!$F$2:$F$5000;Feuil2!$G$2:$G$5000;"Manquant")Si la valeur doit rester du texte à largeur fixe avec des zéros initiaux, forcez les deux côtés au même format texte :
=RECHERCHEX(TEXTE(A2;"000000");TEXTE(Feuil2!$F$2:$F$5000;"000000");Feuil2!$G$2:$G$5000;"Manquant")L'essentiel n'est pas le masque exact. C'est que les deux côtés soient comparés comme un même type.
Espaces invisibles et CAR(160)
Les exports CSV et les données copiées depuis le web contiennent souvent un espace insécable, qu'Excel représente par CAR(160). À l'écran, il ressemble à un espace normal, mais SUPPRESPACE() seul ne le supprime pas toujours.
Normalisez-le directement dans la comparaison :
=RECHERCHEX(SUPPRESPACE(SUBSTITUE(A2;CAR(160);" "));SUPPRESPACE(SUBSTITUE(Feuil2!$F$2:$F$5000;CAR(160);" "));Feuil2!$G$2:$G$5000;"Manquant")Cette formule remplace d'abord le CAR(160) invisible par un espace normal, puis supprime les espaces de début et de fin.
Dates au format texte ambiguës
Si une liste stocke les dates comme du texte, ne passez pas directement par DATEVAL(). Une date au format texte comme 01/05/2026 est ambiguë. Un système peut vouloir dire le 5 janvier. Un autre, le 1er mai.
Pour du texte au format jj/mm/aaaa, extrayez les éléments explicitement :
=DATE(DROITE(A2;4);STXT(A2;4;2);GAUCHE(A2;2))Pour du texte au format mm/jj/aaaa, utilisez :
=DATE(DROITE(A2;4);GAUCHE(A2;2);STXT(A2;4;2))Une fois la date texte convertie en vraie date Excel, comparez-la comme une date, ou normalisez-la dans une clé avec TEXTE(date_réelle;"aaaa-mm-jj").
L'ambiguïté compte, car une formule peut convertir le texte sans erreur et vous donner quand même un mauvais résultat si elle suppose le mauvais ordre des éléments de la date.
Comparez des enregistrements entiers, pas des cellules isolées
Une fois la clé de rapprochement stable et les types de données normalisés, comparez les champs de l'enregistrement un par un.
Supposons que la liste A contienne :
- colonne A : identifiant de transaction
- colonne B : date
- colonne C : montant
- colonne D : statut
Et que la liste B contienne les mêmes champs, mais dans d'autres colonnes.
La marche à suivre :
- Rapprochez la liste A de la liste B avec la clé stable.
- Récupérez la valeur correspondante dans la liste B.
- Classez le résultat : rapproché, différent ou manquant.
Par exemple, si la colonne K de la liste A renvoie le montant de la liste B :
=RECHERCHEX($H2;Feuil2!$H$2:$H$5000;Feuil2!$C$2:$C$5000;"Manquant")Puis classez le résultat :
=SI(K2="Manquant";"Absent de la liste B";SI(C2<>K2;"Écart de montant";"Rapproché"))Faites de même pour la date, le statut ou tout autre champ qui compte.
Vous obtenez un rapport d'écarts utile, car il distingue trois situations très différentes :
- l'enregistrement existe dans les deux listes et concorde
- l'enregistrement existe dans les deux listes, mais un champ diffère
- l'enregistrement n'existe pas du tout de l'autre côté
C'est à ce niveau que le résultat devient exploitable.
Classez les écarts au lieu de colorer la feuille
Une bonne comparaison ne se termine pas par des cellules colorées. Elle se termine par des types d'exceptions nommés.
| Type d'écart | Ce que ça signifie en général | Quoi faire ensuite |
|---|---|---|
| Absent de la liste A | L'enregistrement n'existe que dans la liste B | Vérifiez les filtres, la plage de dates et l'exhaustivité de l'export |
| Absent de la liste B | L'enregistrement n'existe que dans la liste A | Cherchez des lignes oubliées, des enregistrements supprimés ou une mauvaise clé |
| Différence de valeur | Même clé, montant ou statut différent | Examinez le champ source et déterminez quelle liste fait foi |
| Clé en double | Une des listes, ou les deux, a réutilisé la même clé | Renforcez la clé avec la date, le montant ou un autre champ |
| Incompatibilité de type | Les valeurs paraissent égales, mais un côté est du texte | Normalisez avec CNUM, TEXTE ou une conversion de date |
| Écart structurel | L'export contient des en-têtes, des frais, des soldes ou des lignes de synthèse que l'autre liste n'a pas | Excluez ou réassociez ces lignes avant de comparer |
C'est aussi le moment où la mise en forme conditionnelle passe au second plan. Elle sert au contrôle visuel une fois les catégories d'écarts définies. Ce n'est pas elle qui les définit.
Si vos listes sont en réalité des exports de transactions avec des en-têtes décalés, des noms de colonnes différents et des écarts de date ou de signe, comparer deux relevés bancaires CSV sans formules traite le même problème au niveau du fichier plutôt qu'au niveau d'une seule feuille.
Quand Excel crée le travail au lieu de le réduire
Excel reste tout à fait adapté aux petites comparaisons. Il devient une charge dès que la routine ressemble à ça à chaque fois :
- importer deux nouvelles listes
- revérifier la clé
- normaliser à nouveau les dates
- supprimer encore les espaces invisibles
- tester encore les doublons
- réparer la comparaison parce qu'un export a changé de forme
À ce stade, le problème n'est pas que vous ne savez pas comparer des listes. Le problème, c'est que les listes arrivent sans cesse sous une forme qu'il faut réparer avant même de pouvoir lancer la comparaison.
C'est pour ça que tant de méthodes « comparer deux listes Excel et voir les différences » paraissent instables. La formule est rarement le plus dur. Ce qui est instable, c'est le nettoyage des fichiers, le choix de la clé et le tri des exceptions à refaire chaque fois autour de la formule.
