Excel : mise en forme conditionnelle sur plusieurs feuilles — surligner les doublons de la colonne C de Feuille 1

Besoin de surligner, dans Feuille 1, toute valeur de la colonne C qui existe dans la colonne C d’autres feuilles Excel ? Voici une méthode robuste, extensible jusqu’à 20 feuilles, avec variantes Microsoft 365, tableaux structurés et VBA pour garder un classeur performant.

Sommaire

Mise en forme conditionnelle : détecter sur plusieurs feuilles les valeurs de la colonne C

Objectif : toute valeur saisie à partir de C2 dans Feuille 1 doit se mettre en évidence si cette même valeur existe dans la colonne C d’une ou plusieurs autres feuilles (Feuille 2 → Feuille 20 par exemple).

Problème : la mise en forme conditionnelle (MFC) d’Excel ne peut pas, à elle seule, « parcourir » plusieurs feuilles dans une seule règle. Elle autorise des formules, mais ces formules ne peuvent pas directement agréger des références multi-feuilles non contiguës. Il faut donc contourner la limitation.

La solution éprouvée (simple, fiable, compatible)

Nous allons : (1) nommer les plages à surveiller dans chaque feuille cible, (2) calculer sur Feuille 1 une colonne d’assistance qui teste la présence de la valeur dans ces plages, puis (3) brancher la MFC sur le résultat.

Étape	Action	Détails pratiques
1	Créer des plages nommées	Sur chaque feuille cible (Feuille 2 → Feuille 20), sélectionnez la zone `C2:Cn` qui contient réellement vos données (évitez toute colonne entière). Dans la zone Nom (à gauche de la barre de formule) donnez un nom court et parlant, p. ex. `sheet2`, `sheet3`, … Astuce performance : si la longueur de la liste varie, préférez un tableau structuré (voir plus loin) ou un nom dynamique non volatil via `INDEX` (ex. `=Feuille2!$C$2:INDEX(Feuille2!$C:$C;NBVAL(Feuille2!$C:$C))`).
2	Ajouter une colonne d’assistance sur Feuille 1	Choisissez une colonne « technique » masquable (ex. E). En E2, saisissez : `=SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";"sheet4";"sheet5"});C2))>0` Adaptez la liste entre accolades pour inclure tous vos noms (`sheet2` → `sheet20`). Recopiez ensuite la formule vers le bas. Remarque : selon vos paramètres régionaux, la liste dans les accolades peut se séparer par des virgules ou des points‑virgules. En français, l’exemple ci‑dessus utilise des points‑virgules pour empiler verticalement.
3	Appliquer la mise en forme conditionnelle	Sélectionnez la plage concernée en colonne C sur Feuille 1 (ex. `C2:C1000`). Accueil → Mise en forme conditionnelle → Nouvelle règle → « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ». Formule : `=$E2=VRAI` (ou plus concis : `=$E2`). Choisissez le format (remplissage, bordure, police) et validez.

Pourquoi ça marche

Limitation Excel : une formule de MFC ne peut pas agréger directement plusieurs feuilles non contiguës. La colonne d’assistance calcule le test, la MFC n’a plus qu’à lire un booléen.
INDIRECT + NB.SI : INDIRECT transforme des textes (noms de plages) en références réelles. NB.SI compte si la valeur de C2 existe dans chaque plage. SOMME(...) additionne ces comptes ; >0 renvoie VRAI si trouvé au moins une fois.
Indépendance : la colonne d’assistance peut être masquée, protégée ou déplacée sans affecter le résultat.

Exemples de formules prêtes à l’emploi

Version « 4 feuilles » (Feuilles 2 → 5)

=SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";"sheet4";"sheet5"});C2))&gt;0

Version « jusqu’à 20 feuilles »

Après avoir créé les noms sheet2 → sheet20 :

=SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";"sheet4";"sheet5";"sheet6";"sheet7";"sheet8";"sheet9";"sheet10";"sheet11";"sheet12";"sheet13";"sheet14";"sheet15";"sheet16";"sheet17";"sheet18";"sheet19";"sheet20"});C2))&gt;0

Version compacte avec un nom intermédiaire

Créez un nom de classeur (ex. ListePlages) défini comme :

={"sheet2";"sheet3";"sheet4";"sheet5"}

Puis, en E2 :

=SOMME(NB.SI(INDIRECT(ListePlages);C2))&gt;0

Avantage : vous modifiez la liste une seule fois dans le Gestionnaire de noms au lieu de toucher toutes les formules.

Variante Microsoft 365 (plus rapide et non volatile)

La fonction INDIRECT est volatile : au moindre recalcul, elle se réévalue, ce qui peut ralentir un gros classeur. Sous Microsoft 365, la meilleure pratique consiste à baser la détection sur des tableaux structurés (non volatils) et/ou sur une consolidation unique.

Option A : passer chaque plage en tableau structuré

Dans chaque feuille cible, convertissez C2:Cn en Tableau (Insertion → Tableau), renommez-les (TableF2, TableF3, …) et renommez la colonne concernée si besoin (ex. Valeur).
Sur une feuille Index (ou Feuille 1 si vous préférez), créez une liste consolidée via Power Query (Obtenir & Transformer) ou en copiant/empilant les tables. Cette liste ne change que si vos données changent.
Dans la colonne d’assistance, remplacez INDIRECT par un simple NB.SI sur la liste consolidée :
=NB.SI(Index!$A:$A;C2)>0 (ou mieux, limitez à la zone utile Index!$A$2:$A$50000).

Avantages : formules non volatiles, maintenance plus simple, évolutivité bien meilleure. Inconvénient : il faut une feuille « Index » (ou une requête Power Query) pour la consolidation.

Option B : RECHERCHEX/FILTRE sur une liste consolidée

Si vous avez une liste unique de toutes les valeurs colonne C (éventuellement dédupliquée : UNIQUE(Index!A:A)), vous pouvez remplacer le NB.SI par :

RECHERCHEX : =ESTNUM(RECHERCHEX(C2;Index!$A:$A;1)) → renvoie VRAI si la valeur existe.
FILTRE : =NB.SI(FILTRE(Index!$A:$A;Index!$A:$A<>"");C2)>0.

Ces alternatives 365 sont performantes, lisibles et faciles à auditer.

Bonnes pratiques de performance

Limitez la hauteur des plages (évitez $C:$C) : préférez $C$2:$C$5000 selon vos volumes.
Privilégiez les tableaux structurés : ils s’étendent automatiquement et ne sont pas volatils.
Centralisez la comparaison sur une liste unique (Index, Power Query) dès que vous avez plus de 5–6 feuilles ou des dizaines de milliers de lignes.
Nettoyez les données (espaces, caractères non imprimables) : utilisez SUPPRESPACE() et NETTOYER() dans la liste consolidée pour éviter les « faux négatifs ».
Types cohérents : évitez de comparer un nombre « stocké en texte » à un nombre réel. Si besoin, normalisez avec VALEUR() (côté comparaison) ou TEXTE() (côté recherche).

Checklist express

Nommer C2:Cn sur chaque feuille cible (sheet2, sheet3, …).
En Feuille 1!E2 : =SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";…});C2))>0, recopier.
Appliquer la MFC sur Feuille 1!C2:Cn avec la formule =$E2.
Masquer la colonne E (optionnel).

Cas d’usage complet (exemple)

Supposons 4 feuilles à contrôler. Vous créez :

sheet2 → Feuille 2!C2:C100
sheet3 → Feuille 3!C2:C120
sheet4 → Feuille 4!C2:C80
sheet5 → Feuille 5!C2:C60

Sur Feuille 1, en E2 :

=SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";"sheet4";"sheet5"});C2))&gt;0

Recopiez jusqu’à E1000 si votre colonne C va jusqu’à C1000. Puis appliquez la MFC sur C2:C1000 avec la formule =$E2. Dès que C2, C3… contiennent une valeur présente sur une autre feuille, la cellule se colore automatiquement.

Aller plus loin : exactitude, déduplication, surlignage partiel

Correspondance sensible à la casse

NB.SI n’est pas sensible à la casse. Si vous devez distinguer « ABC » de « abc », utilisez une approche par tableau et EXACT. Version simple (par feuille) :

=OU(SOMMEPROD(--EXACT(C2;sheet2))&gt;0;SOMMEPROD(--EXACT(C2;sheet3))&gt;0;SOMMEPROD(--EXACT(C2;sheet4))&gt;0;SOMMEPROD(--EXACT(C2;sheet5))&gt;0)

Cette formule teste chaque plage nommée avec EXACT (sensible à la casse) et renvoie VRAI si au moins une correspondance existe. Elle est plus coûteuse ; réservez-la aux cas nécessaires.

Ignorer les doublons au sein de Feuille 1

Pour ne surligner que si la valeur existe ailleurs (et ne pas tenir compte d’éventuels doublons internes à Feuille 1), la formule ci‑dessus suffit : elle ne regarde que les autres feuilles. Si vous souhaitez en plus empêcher le surlignage lorsque la valeur existe déjà plus haut dans Feuille 1, remplacez la MFC par :

=ET($E2;NB.SI($C$2:$C2;C2)=1)

Ici, seule la première occurrence de la valeur en colonne C est surlignée (et seulement si trouvée ailleurs).

Alternative robuste sans INDIRECT : consolidation Power Query

Power Query (Obtenir & Transformer) peut fusionner en quelques clics la colonne C de toutes les feuilles cibles :

Données → Obtenir des données → À partir d’un classeur (ou « À partir de ce classeur » via l’éditeur avancé).
Sélectionnez vos feuilles ; gardez uniquement la colonne C, renommez la colonne (ex. Valeur), puis utilisez « Regrouper les requêtes » ou « Ajouter des requêtes » pour empiler.
Supprimez les vides, optionnellement Supprimer les doublons.
Chargez le résultat sur une feuille Index (colonne A).
Utilisez alors, sur Feuille 1!E2, la formule non volatile : =NB.SI(Index!$A:$A;C2)>0.

Cette approche est idéale pour des volumes élevés ou une structure de feuilles qui change souvent ; il suffit d’actualiser la requête.

Macro VBA optionnelle (automatiser et fiabiliser)

Si vous manipulez souvent la liste des feuilles, une macro peut constituer la liste, mettre à jour la colonne d’assistance, voire poser la règle de MFC. Exemple minimaliste :

' Module standard
Option Explicit

Public Sub MarquerCorrespondances()
Dim ws As Worksheet, wsBase As Worksheet
Dim noms() As String, i As Long, r As Long, lastRow As Long
Dim val As Variant, trouve As Boolean

```
Set wsBase = ThisWorkbook.Worksheets("Feuille1")

' Construire la liste des feuilles cibles (toutes sauf Feuille1)
ReDim noms(1 To ThisWorkbook.Worksheets.Count - 1)
i = 0
For Each ws In ThisWorkbook.Worksheets
    If ws.Name &lt;&gt; wsBase.Name Then
        i = i + 1
        noms(i) = ws.Name
    End If
Next ws
ReDim Preserve noms(1 To i)

' Dernière ligne utile de la colonne C
lastRow = wsBase.Cells(wsBase.Rows.Count, "C").End(xlUp).Row
wsBase.Range("E2:E" &amp; lastRow).ClearContents

' Boucle sur Feuille1!C2:Cn
For r = 2 To lastRow
    val = wsBase.Cells(r, "C").Value
    If Len(val) &gt; 0 Then
        trouve = ExisteDansAutresFeuilles(val, noms)
        wsBase.Cells(r, "E").Value = trouve
    End If
Next r
```

End Sub

Private Function ExisteDansAutresFeuilles(ByVal v As Variant, ByRef noms() As String) As Boolean
Dim s As Variant, ws As Worksheet, lastRow As Long, rng As Range
For Each s In noms
Set ws = ThisWorkbook.Worksheets(CStr(s))
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set rng = ws.Range("C2:C" & lastRow)
If Application.WorksheetFunction.CountIf(rng, v) > 0 Then
ExisteDansAutresFeuilles = True
Exit Function
End If
Next s
End Function

La MFC peut alors utiliser la condition =$E2=VRAI comme précédemment. Avantages : aucune fonction volatile, pas de longue formule à maintenir. Inconvénient : il faut exécuter la macro (ou la déclencher à l’ouverture / à la modification).

Tableaux structurés : la version premium

Transformer vos listes en Tableaux simplifie tout :

Les plages se redimensionnent automatiquement.
Les références deviennent explicites : TableF2[Valeur].
La consolidation (Power Query) est plus simple et stable.

Exemple : si vous créez la feuille Index avec en colonne A la concaténation de TableF2[Valeur], TableF3[Valeur], … (via Power Query ou une copie ponctuelle), la formule d’assistance redevient triviale et performante :

=NB.SI(Index!$A$2:$A$50000;C2)&gt;0

Tableau comparatif des approches

Approche	Volatilité	Maintenance	Performance	Quand l’utiliser
Nom de plage + INDIRECT + NB.SI	Volatile (INDIRECT)	Moyenne (liste à maintenir)	Bonne → Moyenne	Jusqu’à ~20 feuilles, volume modéré
Tableaux structurés + liste Index	Non volatile	Facile (liste unique)	Excellente	Volumes importants, 365 ou 2019+
Power Query (consolidation)	Non volatile	Facile (actualisation)	Excellente	Données évolutives, nombreuses feuilles
VBA (mise à jour E2:E)	Non volatile	Faible à moyenne	Très bonne	Automatisation, contraintes de perf

Pièges fréquents et dépannage

Rien ne se surligne : vérifiez que la plage de la règle MFC commence bien à C2 (et non C1), que la formule est =$E2 (colonne absolue, ligne relative) et que la priorité/arrêt si vrai n’entre pas en conflit avec d’autres règles.
#NOM? / #VALEUR! : vos noms (sheet2, etc.) n’existent pas ou ont été supprimés. Ouvrez le Gestionnaire de noms pour corriger.
Comparaison chiffres vs texte : normalisez (ex. =NB.SI(Index!$A:$A;VALEUR(C2)) si Index contient des nombres).
Espaces invisibles : nettoyez la liste consolidée avec SUPPRESPACE(NETTOYER(A2)).
Plages trop grandes : évitez $C:$C dans NB.SI ; découpez.
Noms de feuilles modifiés : si vous utilisez des noms de plage indépendants (et non des références directes FeuilleX!C2:Cn), un changement de nom de feuille ne casse pas vos méthodes.

Sécurité et collaboration

Masquez et verrouillez la colonne d’assistance (Format → Masquer & afficher), et appliquez une protection de feuille si nécessaire.
Nommez clairement vos plages (Ventes_EUR_F2, Clients_F3) pour faciliter la relecture par un collègue.
Documentez la liste des feuilles contrôlées (dans une cellule de commentaire ou dans le Gestionnaire de noms via le champ « Commentaire »).

Recette « zéro défaut » (copier‑coller)

Créez les noms sheet2 → sheet20 qui pointent chacun vers la zone utile C2:Cn de la feuille correspondante.
En Feuille 1!E2 :
=SOMME(NB.SI(INDIRECT({"sheet2";"sheet3";"sheet4";"sheet5";"sheet6";"sheet7";"sheet8";"sheet9";"sheet10";"sheet11";"sheet12";"sheet13";"sheet14";"sheet15";"sheet16";"sheet17";"sheet18";"sheet19";"sheet20"});C2))>0
puis recopiez vers le bas.
Appliquez la MFC sur Feuille 1!C2:Cn avec la formule =$E2 et le format de votre choix.

Questions fréquentes

Q : Puis-je inclure certaines feuilles et en exclure d’autres ?
R : Oui. La liste entre accolades peut inclure uniquement les noms de plages des feuilles à auditer.

Q : Je dépasse 20 feuilles ; est‑ce encore viable ?
R : Techniquement oui, mais la formule devient lourde. Basculez vers une liste consolidée (Power Query) ou une macro VBA ; les performances seront meilleures et la maintenance plus simple.

Q : Puis-je mettre une couleur différente selon la feuille où la valeur a été trouvée ?
R : Créez plusieurs colonnes d’assistance (E, F, G…) et plusieurs règles de MFC, chacune testant une feuille ou un groupe de feuilles. Exemple : en F2 =NB.SI(sheet2;C2)>0 pour une couleur A, en G2 =NB.SI(sheet3;C2)>0 pour une couleur B, etc.

Q : Comment faire si les feuilles et leurs colonnes changent souvent ?
R : La consolidation Power Query + MFC sur liste Index est la plus robuste. En alternative, la macro VBA ci‑dessus reconstruit la détection à la volée.

Conclusion

Pour surligner dans Feuille 1 toute valeur de la colonne C présente sur d’autres feuilles, la combinaison « plages nommées + colonne d’assistance + MFC » est la méthode la plus simple et la plus sûre. Sur Microsoft 365 ou avec de gros volumes, migrez vers une liste consolidée (Power Query ou tableau Index) et remplacez INDIRECT par des références non volatiles : vous gagnerez en vitesse, en stabilité et en maintenabilité. Dans tous les cas, limitez la taille des plages, nettoyez vos données et documentez la configuration pour un résultat durable et professionnel.

Annexe : variantes utiles

NB.SI.ENS pour filtrer par condition supplémentaire

Si vos colonnes C contiennent des codes à comparer et que vous souhaitez ignorer certaines lignes (ex. statut « Inactif » en colonne D des feuilles cibles), substituez NB.SI par NB.SI.ENS dans la consolidation (via Index/Power Query) :

=NB.SI.ENS(Index!$A:$A;C2;Index!$B:$B;"&lt;&gt;Inactif")&gt;0

Normalisation rapide texte/numérique

=NB.SI(Index!$A:$A;SI(ESTNUM(C2);C2;VALEUR(C2)))&gt;0

Cette version transforme un « nombre en texte » lorsqu’il le faut.

Détection de correspondances partielles

Pour surligner si la valeur de Feuille 1!C2 contient un motif présent ailleurs (comparaison par sous-chaîne), utilisez une liste Index et une colonne auxiliaire :

=SOMMEPROD(--ESTNUM(TROUVE(Index!$A$2:$A$5000;C2)))&gt;0

Attention : TROUVE est sensible à la casse. Utilisez CHERCHE pour une recherche insensible à la casse.

En résumé : suivez la procédure standard (plages nommées → colonne d’assistance → MFC) pour une mise en forme conditionnelle multi‑feuilles efficace. Si vos données grossissent, consolidez et supprimez INDIRECT : vos utilisateurs et votre CPU vous remercieront.