Une facture en retard, un doublon dans une liste de commandes, un objectif non atteint : la mise en forme conditionnelle les fait apparaître en couleur, sans que vous ayez à parcourir le tableau ligne par ligne. Excel applique le format tout seul et le met à jour à chaque modification. Voici sept règles que l’on retrouve dans la plupart des fichiers de gestion, avec les menus exacts et les formules à copier.
Les exemples s’appuient sur un suivi de factures fictif de 40 lignes : N° facture en colonne A, Client en B, Date de facture en C, Échéance en D, Montant TTC en E et Statut en F (Payée, En attente ou Impayée). Les en-têtes sont en ligne 1 et les données de A2 à F41. La règle 7 utilise un planning d’équipe.
Trouver les commandes dans le ruban
Toutes les règles partent du même bouton : Accueil › Mise en forme conditionnelle, dans le groupe Styles. Sélectionnez toujours les cellules à mettre en forme avant d’ouvrir le menu.
| Menu | Ce qu’il fait | Règle de cet article |
|---|---|---|
| Règles de mise en surbrillance des cellules | Comparer à une valeur, un texte ou une date, repérer les doublons | Règles 1 et 2 |
| Règles des valeurs plus/moins élevées | Les N premiers, les N derniers, au-dessus de la moyenne | Règle 3 |
| Barres de données, Nuances de couleurs, Jeux d’icônes | Visualiser des écarts dans une plage de nombres | Règle 6 |
| Nouvelle règle › Utiliser une formule… | Toute condition exprimable par une formule | Règles 4, 5 et 7 |
| Gérer les règles | Modifier, ordonner et supprimer les règles existantes | Toutes |
La documentation Microsoft sur la mise en forme conditionnelle dans Excel détaille chaque type de règle. Les intitulés peuvent légèrement varier selon votre version.
Règle 1 : mettre en évidence les montants au-dessus d’un seuil
- Sélectionnez E2:E41, la colonne Montant TTC.
- Ouvrez Accueil › Mise en forme conditionnelle › Règles de mise en surbrillance des cellules › Supérieur à…
- Saisissez le seuil, par exemple 5000, et choisissez un format dans la liste, comme Remplissage rouge clair avec texte rouge foncé.
- Validez avec OK : toute facture supérieure à 5 000 € se colore, y compris celles que vous saisirez plus tard dans la plage.
Plutôt que de taper 5000 dans la règle, vous pouvez cliquer sur une cellule qui contient le seuil, par exemple H1. Modifier H1 suffit alors à changer le seuil de toute la colonne, sans rouvrir la règle. Les autres commandes du même menu, Inférieur à, Entre et Égal à, fonctionnent de la même façon ; Texte qui contient colore par exemple tous les clients dont le nom contient « SARL ».
Règle 2 : repérer les doublons dans une liste
Un numéro de facture saisi deux fois fausse tous les totaux. Sélectionnez A2:A41, puis Règles de mise en surbrillance des cellules › Valeurs en double. La liste déroulante de la boîte de dialogue propose aussi Unique, pour faire ressortir à l’inverse les valeurs qui n’apparaissent qu’une fois.
Cette règle signale toutes les occurrences, l’original comme la copie. Pour ne colorer qu’à partir de la deuxième apparition, utilisez une règle par formule (voir règle 4) avec la formule suivante, appliquée à A2:A41 :
=NB.SI($A$2:$A2;$A2)>1La plage qui s’agrandit ($A$2:$A2) compte les occurrences depuis le début de la liste jusqu’à la ligne en cours : seules les répétitions dépassent 1. Une fois les doublons repérés, Données › Supprimer les doublons les retire, ou Power Query si la liste est réimportée chaque mois.
Règle 3 : faire ressortir les meilleures et les moins bonnes valeurs
Le menu Règles des valeurs plus/moins élevées compare chaque cellule au reste de la plage. Sur E2:E41, choisissez 10 valeurs les plus élevées et remplacez 10 par 5 dans la boîte de dialogue : les cinq plus grosses factures se colorent. La commande Valeurs supérieures à la moyenne colore toutes les factures au-dessus du montant moyen, recalculé automatiquement.
Ces règles se recalculent dès qu’une valeur change, ce qui les rend utiles dans un classement de commerciaux ou un suivi d’objectifs. Une limite à connaître : en cas d’égalité, Excel colore toutes les valeurs ex æquo, et vous pouvez obtenir six cellules colorées pour un « top 5 ».
Règle 4 : colorer toute la ligne selon un statut
C’est la règle la plus demandée, et celle qui pose le plus de difficultés. Le but : colorer la ligne entière de chaque facture « Impayée », pas seulement la cellule du statut.
- Sélectionnez A2:F41 en commençant par A2 : la cellule active, en blanc dans la sélection, doit se trouver sur la première ligne de données.
- Choisissez Mise en forme conditionnelle › Nouvelle règle, puis le type Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Saisissez la formule ci-dessous dans le champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie.
- Cliquez sur Format, onglet Remplissage, choisissez une couleur claire, puis validez deux fois avec OK.
=$F2="Impayée"Le symbole $ devant F bloque la colonne : chaque cellule de la ligne, de A à F, regarde toujours le statut en colonne F. L’absence de $ devant 2 laisse la ligne varier : la ligne 3 regarde F3, la ligne 4 regarde F4. Avec F2 sans $, chaque colonne regarderait une cellule différente et les couleurs deviendraient incohérentes. La formule doit renvoyer VRAI ou FAUX, exactement comme le test logique d’une fonction SI dans Excel.
Pour plusieurs statuts, créez une règle par statut avec sa couleur, par exemple orange pour « En attente » et vert pour « Payée ». Pour combiner deux critères, utilisez ET ou OU dans la formule. Celle-ci ne colore que les impayés de plus de 5 000 € :
=ET($F2="Impayée";$E2>5000)Règle 5 : signaler les échéances dépassées et à venir
Avec la fonction AUJOURDHUI, la règle se met à jour chaque jour à l’ouverture du fichier. Sélectionnez A2:F41 et créez deux règles par formule, la première avec un remplissage rouge, la seconde en orange.
=ET($F2<>"Payée";$D2<AUJOURDHUI())=ET($F2<>"Payée";$D2>=AUJOURDHUI();$D2<=AUJOURDHUI()+7)La première colore les factures non réglées dont l’échéance est passée, la seconde celles qui arrivent à échéance dans les sept jours. La condition sur le statut évite de signaler une facture déjà payée.
Pour une condition portant uniquement sur une date, le menu Règles de mise en surbrillance des cellules › Une date se produisant propose sans formule des périodes comme Hier, Aujourd’hui, Cette semaine ou Le mois dernier. Il ne permet pas de combiner la date avec le statut, d’où l’intérêt de la formule. Ce type de suivi correspond au cas pratique « facturation et relances » de la formation Excel CPF de CNFN, réalisé d’abord en mode guidé, puis chronométré.
Règle 6 : visualiser les écarts avec des barres de données et des icônes
Barres de données dessine dans chaque cellule une barre proportionnelle à sa valeur : sur E2:E41, l’œil repère immédiatement les grosses factures. Nuances de couleurs applique un dégradé, utile sur un tableau de chiffres mensuels pour voir les mois forts et les mois faibles. Jeux d’icônes ajoute des flèches, des feux ou des symboles selon des seuils.
Par défaut, les jeux d’icônes découpent la plage en tiers selon des pourcentages, ce qui donne rarement le résultat attendu. Pour un taux d’atteinte d’objectif, ouvrez Gérer les règles › Modifier la règle, choisissez le type Nombre plutôt que Pourcentage, puis fixez les seuils : vert à partir de 1 (100 %), orange à partir de 0,8, rouge en dessous. La case Afficher l’icône uniquement, ou Afficher la barre uniquement pour les barres de données, masque le nombre quand seul le visuel compte.
Ces visuels s’appliquent aussi aux valeurs d’un tableau croisé dynamique : sélectionnez une cellule de valeurs avant de créer la règle, puis choisissez dans le petit bouton qui apparaît d’appliquer la règle à toutes les cellules du même champ, pour qu’elle suive le tableau quand il change de forme.
Règle 7 : griser les week-ends et les jours fériés d’un planning
Dans un planning d’équipe, les dates sont en ligne 1, de B1 à AF1, et les noms des salariés en colonne A. Sélectionnez B1:AF20 en commençant par B1, puis créez une règle par formule avec un remplissage gris :
=JOURSEM(B$1;2)>5Avec le type 2, JOURSEM renvoie 1 pour le lundi et 7 pour le dimanche : la condition est vraie le samedi et le dimanche. Le $ est cette fois devant le numéro de ligne, car toutes les cellules d’une colonne regardent la date en ligne 1. Pour les jours fériés, saisissez-les dans une plage nommée Feries (Formules › Définir un nom), puis ajoutez une seconde règle :
=NB.SI(Feries;B$1)>0Le même principe met en valeur la colonne du jour avec la formule =B$1=AUJOURDHUI(), pratique pour que chaque membre de l’équipe repère la date en ouvrant le fichier partagé.
Gérer l’ordre et la portée des règles
Dès qu’une plage porte plusieurs règles, ouvrez Accueil › Mise en forme conditionnelle › Gérer les règles. Dans la liste Afficher les règles de mise en forme pour, choisissez Cette feuille de calcul pour tout voir.
- L’ordre compte : les règles sont évaluées de haut en bas, et la règle placée plus haut l’emporte lorsque deux formats entrent en conflit. Les nouvelles règles s’ajoutent en haut ; les flèches Monter et Descendre changent l’ordre. Placez la règle « échéance dépassée » au-dessus de la règle « échéance dans 7 jours ».
- Interrompre si Vrai : cochée sur une règle, cette case empêche l’application des règles situées en dessous lorsque la condition est remplie.
- S’applique à : cette colonne indique la plage couverte. Corrigez-la ici plutôt que de recréer la règle, par exemple pour étendre $A$2:$F$41 à $A$2:$F$200.
- Modifier la règle : rouvre la formule et le format sans tout recommencer.
Corriger une mise en forme conditionnelle qui ne fonctionne pas
- Rien ne se colore : la formule ne commence pas par le signe égal, ou elle compare un nombre stocké en texte. Testez la formule dans une cellule vide de la ligne 2 : elle doit afficher VRAI ou FAUX.
- Les couleurs sont décalées d’une ligne : la cellule active n’était pas sur la première ligne au moment de créer la règle, ou un $ est mal placé. Vérifiez la formule et la plage dans Gérer les règles.
- Toute la plage se colore d’un coup : la référence est entièrement figée, comme $F$2, et toutes les cellules regardent la même cellule.
- Le fichier ralentit : les copier-coller successifs multiplient les règles fragmentées. Supprimez les doublons dans Gérer les règles, ou passez par Effacer les règles › Effacer les règles de la feuille entière, puis recréez une règle par plage.
- Les règles se propagent là où il ne faut pas : le pinceau Reproduire la mise en forme copie aussi les règles conditionnelles. Vérifiez ensuite la plage S’applique à.
Les cas pratiques métiers de la formation CNFN, comme le planning d’équipe partagé ou la facturation et les relances, mobilisent ce type de règles, avec des exercices corrigés en visioconférence. Pour savoir par quel niveau commencer, remplissez le formulaire de vérification des prérequis Excel.
Questions fréquentes sur la mise en forme conditionnelle
Comment faire une mise en forme conditionnelle avec une formule sur Excel ?
Sélectionnez la plage, puis Accueil › Mise en forme conditionnelle › Nouvelle règle › Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué. Saisissez une formule qui renvoie VRAI ou FAUX pour la première cellule de la sélection, choisissez un format avec le bouton Format, puis validez.
Comment colorer une ligne entière en fonction de la valeur d’une cellule ?
Sélectionnez toutes les colonnes du tableau à partir de la première ligne de données, puis créez une règle par formule du type =$F2="Impayée". Le $ devant la lettre fige la colonne testée, ce qui permet à toute la ligne de suivre la même cellule.
Comment appliquer une mise en forme conditionnelle en fonction d’une autre cellule ?
Utilisez une règle par formule qui fait référence à l’autre cellule, par exemple =B2>C2 pour colorer B2 lorsqu’elle dépasse C2. Pour comparer toute une plage à un seuil unique, la référence absolue $H$1 garde la même cellule de référence partout.
Pourquoi ma mise en forme conditionnelle ne fonctionne-t-elle pas ?
Les causes les plus fréquentes sont des $ mal placés, une cellule active qui n’était pas en haut de la sélection, ou des nombres stockés en texte. Ouvrez Gérer les règles pour vérifier la formule et la plage S’applique à, puis testez la formule dans une cellule vide.
Combien de règles de mise en forme conditionnelle peut-on appliquer ?
Les versions actuelles d’Excel ne limitent plus le nombre de règles à trois, comme le faisaient les très anciennes versions. Au-delà de quelques règles par plage, le fichier devient toutefois difficile à maintenir et peut ralentir : regroupez les conditions dans une formule avec ET ou OU quand c’est possible.
Retenez la méthode : sélectionner la plage depuis sa première ligne, choisir une règle prédéfinie quand elle suffit, et passer à la formule dès que la condition porte sur une autre colonne ou combine plusieurs critères. Ces règles prennent toute leur valeur dans les tableaux de suivi partagés, où elles orientent l’attention de chaque lecteur. Pour les travailler sur des cas métiers, découvrez le programme de la formation Excel CPF.
