Comparer deux feuilles Excel et surligner les différences

Afficher deux feuilles du même classeur dans deux fenêtres

Une fenêtre d’Excel n’affiche qu’une feuille à la fois, si bien que deux feuilles du même classeur ne se voient ensemble qu’en ouvrant une seconde fenêtre du même fichier. Cette seconde fenêtre n’a pourtant rien d’une copie, puisqu’elle affiche le même classeur que la première. Tout ce que tu corriges dans l’une vaut donc aussi pour l’autre, sans aucun fichier en double à ranger ensuite.

1. 01

   Dans l’onglet Affichage, groupe Fenêtre, clique sur Nouvelle fenêtre. Chaque fenêtre porte alors le nom du classeur suivi d’un numéro, 1 pour la première et 2 pour la seconde.

2. 02

   Dans la première fenêtre, clique sur l’onglet de la feuille Comptage, puis, dans la seconde, sur celui de la feuille Stock.

Placer les deux feuilles côte à côte et les faire défiler ensemble

Le bouton Afficher côte à côte range les deux fenêtres à l’écran, et Défilement synchrone relie leurs barres de défilement pour que les deux feuilles avancent ensemble. Descends dans la fenêtre de Comptage, et celle de Stock suit d’autant, si bien que la ligne 3 de l’une reste en face de la ligne 3 de l’autre. D’après l’aide de Microsoft, ce défilement synchrone n’est disponible que lorsque Afficher côte à côte est activé.

1. 01

   Dans le même groupe Fenêtre, clique sur Afficher côte à côte. Si plus de deux fenêtres sont ouvertes, Excel te demande dans la boîte Comparer en côte à côte laquelle placer à côté de la fenêtre active.

2. 02

   Vérifie que le bouton Défilement synchrone, dans le même groupe, est activé, puis fais défiler l’une des deux fenêtres.

3. 03

   Pour choisir leur disposition, l’une à côté de l’autre par exemple, clique sur Réorganiser tout, choisis Vertical, coche Fenêtres du classeur actif, puis valide par OK.

   

4. 04

   Si tu redimensionnes l’une des fenêtres, Rétablir la position de la fenêtre, toujours dans le groupe Fenêtre, remet les deux à leur place de départ.

Une fois la comparaison finie, ferme la seconde fenêtre par sa croix, en haut à droite, pour revenir à une seule fenêtre. Le classeur reste ouvert dans la première, avec tout ce que tu y as saisi entre-temps.

Poser la règle qui colore les différences d’une feuille à l’autre

Toute la règle tient en une formule, que tu écris pour la première cellule de la plage, A1 ici. Excel la décale ensuite pour chaque cellule de la plage, comme une formule qu’on recopie, si bien qu’en B6 elle compare B6 à la cellule B6 de Stock. Chaque cellule prend alors la couleur choisie dès que sa formule renvoie VRAI.

1. 01

   Sur la feuille Comptage, sélectionne A1:C7 en partant de A1.

2. 02

   Dans l’onglet Accueil, clique sur Mise en forme conditionnelle, puis sur Nouvelle règle.

3. 03

   Choisis « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué », puis tape la formule ci-dessous dans la zone « Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie ».

   

4. 04

   Clique sur Format, choisis une couleur dans l’onglet Remplissage, puis valide par OK dans les deux fenêtres.

Sur l’exemple, la règle colore cinq cellules, la quantité 42 de REF-102, l’article Surligneurs et les trois cellules de REF-106, une ligne que Stock n’a pas. Elle réagit aussi à chaque saisie, et il suffit d’écrire 42 en C3 sur Stock pour que la couleur quitte aussitôt la cellule C3 de Comptage.

Pourquoi la formule s’écrit sans dollar

La formule s’écrit sans dollar parce qu’une référence sans dollar est relative, et c’est elle qui permet à la règle de suivre chaque cellule de la plage. Avec =$A$1<>Stock!$A$1, toutes les cellules comparent au contraire A1 à la cellule A1 de Stock. La règle colore alors tout ou rien, selon ces deux seules cellules. Sur l’exemple, où les deux A1 portent Référence, aucune cellule n’est colorée, alors qu’un écart en A1 les colorerait toutes. Si des dollars apparaissent dans ta formule, retire-les donc avant de valider.

Tenir compte des majuscules avec la fonction EXACT

Pour que la règle tienne compte des majuscules, c’est la fonction EXACT qu’il te faut, puisque le signe <> tient Stylo Bleu et Stylo bleu pour égaux. Elle compare deux textes lettre pour lettre, majuscules comprises, puis NON inverse son résultat, pour que la règle colore les cellules qui ne sont pas identiques.

Comme EXACT compare des textes, elle tient en revanche le nombre 30 et le texte 30 pour identiques, là où le signe <> les dit différents. La partie sur le choix de la méthode, plus bas, reprend ces cas un par un, avec la macro.

Surligner en jaune les cellules qui diffèrent sur la première feuille

La macro ne surligne que la première feuille, la version que tu veux contrôler, et laisse la seconde telle quelle. Lancée avec Comptage en première feuille et Stock en seconde, elle colore en jaune cinq cellules de Comptage, C3, B6, puis A7, B7 et C7. Elle annonce alors « 5 cellule(s) différente(s), surlignée(s) sur Comptage » dans une boîte de message, et aucune valeur n’a changé, ni sur Comptage ni sur Stock.

Nommer les deux feuilles à comparer

Tu nommes les deux feuilles à comparer sur les deux lignes « À MODIFIER », en tête du code, en recopiant le nom tel qu’il s’affiche sur leur onglet. La première des deux reçoit le surlignage, et c’est donc elle qui doit porter la version à contrôler. Pour l’exemple, écris "Comptage" à la place de "Feuil1" et "Stock" à la place de "Feuil2", en gardant les guillemets. Les majuscules du nom ne comptent pas, puisque la macro les ignore dans le nom d’un onglet, comme Excel.

• nomA La feuille qui reçoit le jaune, "Feuil1" dans le code. Mets-y la version à contrôler, Comptage dans l’exemple.
• nomB La feuille de référence, "Feuil2" dans le code, qui reste intacte. C’est Stock dans l’exemple.

Installer la macro qui surligne les différences

Connaître les limites de la macro avant de comparer

• Elle compare les cellules par leur position, comme les deux règles. Une ligne insérée sous les en-têtes de Comptage décale toutes celles du dessous, et la macro surligne alors 21 cellules au lieu de 5. La partie « Comment croiser les données de deux feuilles Excel » compare par la référence.

• Elle écrase le fond des cellules qui diffèrent, et Ctrl+Z ne le rend pas. Sur un tableau dont les couleurs ont un sens, lance-la sur une copie de la feuille.

• Relancée après une correction, elle retire d’abord de la première feuille le jaune qu’elle y avait posé, et ce jaune seulement, si bien qu’une cellule que tu aurais remplie de ce même jaune le perd aussi.

• Elle ne compare que les valeurs, jamais les formules ni les formats. Une formule remplacée par son résultat ou un format qui change ne sont pas surlignés, et une date passe pour identique à son numéro de série.

• Elle tient compte des majuscules, mais lit le nombre 30 et le texte 30 comme identiques, à la manière de la règle avec EXACT.

• Les deux feuilles doivent être dans le classeur actif au moment où tu la lances. Pour deux fichiers, copie d’abord l’une des feuilles dans l’autre classeur.

• Sur une première feuille protégée, elle s’arrête sur l’erreur 1004 sans rien surligner, si bien qu’il faut en ôter la protection le temps de la comparaison.

• Au-delà de deux millions de cellules dans le rectangle comparé, elle s’arrête sans rien surligner et le dit. Une seule cellule mise en forme loin sous les données suffit à gonfler ce rectangle.

Copier une feuille d’un fichier Excel dans l’autre

La commande Déplacer ou copier recopie un onglet entier d’un fichier Excel dans l’autre, pourvu que les deux classeurs soient ouverts. Quand tu demandes une copie, elle laisse l’original à sa place. Les deux feuilles se retrouvent alors dans le même classeur, où la règle ou la macro les compare comme dans l’exemple.

1. 01

   Ouvre les deux classeurs.

2. 02

   Fais un clic droit sur l’onglet de la feuille à copier, Stock par exemple, puis choisis Déplacer ou copier.

3. 03

   Dans la liste Dans le classeur, choisis le classeur de l’autre version, puis la place du nouvel onglet dans la liste Avant la feuille.

4. 04

   Coche Créer une copie, pour que la feuille reste aussi dans son fichier, puis valide par OK.

Si le classeur de destination a déjà une feuille du même nom, Excel appelle la copie Stock (2). C’est alors ce nom qu’il faut écrire dans la règle comme dans la macro.

Comparer deux classeurs entiers avec Spreadsheet Compare

Microsoft livre avec certaines éditions d’Office une application à part qui compare deux classeurs entiers, Comparer les feuilles de calcul, appelée Spreadsheet Compare en anglais. D’après l’aide de Microsoft, elle n’existe qu’avec Office Professionnel Plus 2013, 2016 et 2019 et avec les Applications Microsoft 365 pour les grandes entreprises, sur Windows. Cette liste exclut par exemple Microsoft 365 Personnel ou Famille.

Selon la même aide, elle s’ouvre depuis le menu Démarrer de Windows, sous le nom Comparer les feuilles de calcul. On y choisit l’ancienne version par l’icône de dossier bleue de la zone Comparer, puis la nouvelle par l’icône verte de la zone À. Les deux classeurs s’affichent alors dans une grille à deux volets, où chaque type de changement a sa couleur, le vert pour une valeur saisie.

Dans ces mêmes éditions, le complément Inquire ajoute à Excel un onglet Inquire, dont le bouton Compare Files lance l’application. Pour l’activer, l’aide de Microsoft indique d’ouvrir Fichier, Options, Compléments, de choisir Compléments COM dans la liste Gérer et de cliquer sur Atteindre, puis de cocher Inquire. Si Inquire ne figure pas dans la liste, c’est que ton Excel ne l’inclut pas, ou que l’administrateur de ton organisation l’a rendu indisponible.

Comparer deux tableaux par référence avec RECHERCHEX

RECHERCHEX compare deux tableaux par référence, en allant chercher sur Stock la ligne de chaque référence, où qu’elle se trouve, pour en renvoyer la quantité. Écrite en face de chaque ligne de Comptage, elle place les deux quantités côte à côte, dans l’ordre de Comptage. Il ne reste alors qu’à lire les lignes où les deux chiffres diffèrent.

Si ton Excel est un 2016 ou un 2019, RECHERCHEX n’y existe pas, d’après l’aide de Microsoft, qui la réserve à Microsoft 365, Excel 2021 et Excel 2024. Sur ces versions, RECHERCHEV rend le même service, comme le montre la question qui lui est consacrée plus bas.

Mettre en évidence les différences quel que soit l’ordre des lignes

La même recherche, posée en règle de mise en forme conditionnelle, met en évidence les différences quel que soit l’ordre des lignes. Sélectionne A2:C7 sur Comptage en partant de A2, puis pose la règle ci-dessous comme dans la partie sur la mise en forme conditionnelle.

Le dollar de $A2 garde la recherche sur la colonne des références, pendant que Stock!A:A, écrit sans dollar, devient Stock!B:B puis Stock!C:C d’une colonne à l’autre. Une référence absente de Stock renvoie un texte vide, qui diffère des valeurs de la ligne, si bien que la ligne entière de REF-106 est colorée.

Trouver les lignes absentes de l’autre feuille avec NB.SI

NB.SI trouve les lignes absentes de l’autre feuille, puisqu’elle compte les lignes qui portent une référence donnée. Pour une référence que l’autre feuille n’a pas, elle renvoie donc 0. Écrite sur Comptage, la formule ci-dessous renvoie VRAI en face de REF-106, la gomme que le logiciel ne connaît pas. La même, écrite sur Stock en visant Comptage, trouve à l’inverse les lignes que le comptage a oubliées.