- La mise en forme conditionnelle change l’aspect d’une cellule selon sa valeur : couleur, icône, barre de données, sans intervention manuelle.
- Trois familles de règles : règles de cellule (supérieur à, entre, contient), règles de haut/bas, et règles par formule, les plus puissantes.
- Une règle par formule s’écrit pour la première cellule de la plage, avec des références mixtes ($D2) pour colorer toute la ligne.
- Limitez-vous à 3 couleurs signifiantes (rouge, orange, vert) et à des seuils documentés pour que le tableau reste lisible.
Un tableau de suivi qui demande une lecture ligne à ligne n’est pas un tableau de pilotage. La mise en forme conditionnelle d’Excel colore automatiquement ce qui doit retenir l’attention : un client en retard, un poste de budget dépassé, une échéance fiscale proche. Voici comment l’utiliser efficacement, sur un cas de balance âgée.
Le cas
Une balance âgée liste les clients, leur solde, leur échéance et leurs jours de retard. Objectif : afficher en rouge les retards de plus de 90 jours, en orange ceux de 31 à 90 jours, en vert les créances non échues.
Étape 1 : préparer la colonne de calcul
Les jours de retard se calculent à partir de l’échéance et de la date du jour :
=MAX(0;AUJOURDHUI()-C2)Pour des tableaux figés à une date d’arrêté, remplacez AUJOURDHUI() par une cellule contenant la date d’arrêté (par exemple $B$1) : sinon les résultats changent à chaque ouverture.
Étape 2 : première règle par comparaison
- Sélectionnez la plage D2:D7 (jours de retard).
- Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Supérieur à…
- Saisissez 90, choisissez « Remplissage rouge clair avec texte rouge foncé ».
Pour les autres seuils, ajoutez une règle Entre… 31 et 90 (orange) et une règle Égal à 0 (vert).
Étape 3 : la règle par formule pour colorer toute la ligne
Colorer seulement la cellule D laisse le reste du tableau terne. La règle par formule colore toute la ligne :
- Sélectionnez A2:E7.
- Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer les cellules pour lesquelles le format sera appliqué.
- Formule :
=$D2>90 - Format : remplissage rouge.
Deux points techniques essentiels :
- la formule est écrite pour la première cellule de la sélection (ligne 2) ;
- la colonne est verrouillée par $ (
$D) mais la ligne est relative (2sans $), pour que la règle évalue chaque ligne avec sa propre valeur en D.
Figure 1 : résultat de la mise en forme. Repère 1 : la colonne testée ; repère 2 : une cellule dépassant le seuil de 90 jours.
Étape 4 : ordonner les règles
Gérer les règles > Afficher les règles de mise en forme pour : Cette feuille. L’ordre compte : la règle du haut a priorité. Placez la règle la plus restrictive (> 90) en premier, cochez Arrêter si vrai pour que la ligne ne reçoive pas aussi la couleur orange.
Autres usages utiles en comptabilité et finance
| Besoin | Règle |
|---|---|
| Budget dépassé de plus de 10 % | =$E2>$D2*1,1 sur la ligne (E = réalisé, D = budget) |
| Échéance fiscale dans moins de 30 jours | =ET($B2>=AUJOURDHUI();$B2-AUJOURDHUI()<=30) |
| Doublons de facture | Règle prédéfinie « Valeurs en double » sur la colonne des numéros |
| Écart de rapprochement non nul | =ARRONDI($F2;2)<>0 (rouge) |
| Trésorerie négative | « Inférieur à 0 » (rouge gras) sur la ligne de trésorerie du plan |
| Variation mensuelle forte | Jeu d’icônes (flèches) selon le pourcentage de variation |
| Poids relatif des postes | Barres de données dans la colonne des montants |
Jeux d’icônes et barres de données
- Barres de données : une barre proportionnelle dans la cellule ; adaptées aux classements (charges par poste).
- Jeux d’icônes : feux tricolores, flèches ; réglez les seuils en pourcentage ou en nombre selon le sens métier, car les seuils par défaut (33 % / 67 %) sont rarement pertinents.
- Échelles de couleurs : dégradé du vert au rouge sur une matrice (marge par produit et par mois).
Six règles prêtes à l’emploi
Dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer… les cellules pour lesquelles le format sera appliqué, voici six formules à adapter. Elles s’écrivent pour la première ligne de la plage (ici la ligne 2) ; les références de colonne sont bloquées par un signe dollar pour colorer toute la ligne.
| Besoin | Formule | Couleur suggérée |
|---|---|---|
| Facture échue et non payée | =ET($D2<AUJOURDHUI();$E2="") |
Rouge |
| Échéance dans les 7 prochains jours | =ET($D2>=AUJOURDHUI();$D2<=AUJOURDHUI()+7;$E2="") |
Orange |
| Numéro de facture en double | =NB.SI($A:$A;$A2)>1 |
Jaune |
| Écart de rapprochement non nul | =ABS($H2)>0,005 |
Rouge |
| Écriture saisie un samedi ou un dimanche | =JOURSEM($B2;2)>5 |
Gris |
| Facture payée | =$E2<>"" |
Vert clair |
Exemple sur un échéancier
| N° | Client | Échéance | Date de paiement | Mise en forme |
|---|---|---|---|---|
| F-101 | Client A | 05/03 | 04/03 | Vert clair (payée) |
| F-102 | Client B | 10/03 | Rouge si aujourd’hui est postérieur au 10/03 | |
| F-103 | Client C | 18/03 | Orange si aujourd’hui est entre le 11/03 et le 18/03 | |
| F-103 | Client D | 25/03 | Jaune : numéro F-103 en double |
Le signe dollar est la clé : $D2 fige la colonne D mais laisse la ligne varier ; sans lui, la règle se décale colonne par colonne et ne colore que la cellule.
Trois pièges à connaître
- L’ordre des règles compte : la première règle vraie l’emporte si l’option « Arrêter si vrai » est cochée. Mettez en tête les alertes les plus graves (échu non payé).
- Les règles s’appliquent à une plage : si vous insérez des lignes, la plage peut se fragmenter. Gérer les règles > « Afficher les règles de mise en forme pour : cette feuille » permet de contrôler et de réajuster la plage.
- La date du jour se recalcule :
AUJOURDHUI()fait changer les couleurs d’un jour à l’autre sans que le fichier soit modifié, ce qui est le but, mais rend toute capture d’écran périssable ; imprimez ou enregistrez en PDF le jour de la revue.
Mesurer l’effet
Une règle d’alerte bien conçue réduit les retards : dans un suivi d’échéances de vingt clients, quelques minutes de revue hebdomadaire sur les lignes rouges et orange suffisent à relancer à temps. Le tableau de relance du suivi des délais clients donne un calendrier d’actions.
Un budget avec écarts : feux tricolores et seuils
Pour un suivi budgétaire mensuel, une colonne Écart % = (Réel − Budget) ÷ Budget permet de colorer les lignes selon l’importance de l’écart. Trois règles par formule, appliquées à la plage des écarts (par exemple F2:F40), dans cet ordre :
| Ordre | Formule | Format | Signification |
|---|---|---|---|
| 1 | =ABS($F2)>0,15 |
Rouge | Écart supérieur à 15 % : analyse obligatoire |
| 2 | =ABS($F2)>0,05 |
Orange | Écart de 5 à 15 % : à surveiller |
| 3 | =ABS($F2)<=0,05 |
Vert | Écart toléré |
Cochez Arrêter si vrai pour les deux premières règles afin d’éviter qu’une cellule rouge soit aussi orange. Pour un poste de charges, un écart favorable (réel inférieur au budget) peut mériter une couleur différente de l’écart défavorable : =$F2>0,05 (dépassement) et =$F2<-0,05 (économie).
Détecter les doublons dans une liste de factures
Pour repérer deux factures identiques (même fournisseur, même numéro, même montant), colorez les doublons : sélectionnez la plage, puis Mise en forme conditionnelle > Règles de mise en surbrillance > Valeurs en double. Pour un doublon sur deux colonnes (fournisseur et numéro), utilisez une règle par formule :
=NB.SI.ENS($A$2:$A$500;$A2;$B$2:$B$500;$B2)>1Les lignes en doublon se colorent entièrement ; vous les contrôlez avant paiement, ce qui évite le paiement en double, l’une des anomalies les plus coûteuses en comptabilité fournisseurs.
Un échéancier de paiements avec alertes de date
Pour un tableau de factures fournisseurs avec une colonne d’échéance (D) et une colonne de paiement (F, vide si non payé) :
| Situation | Règle (formule) | Couleur |
|---|---|---|
| Échue et impayée | =ET($D2<AUJOURDHUI();$F2="") |
Rouge |
| Échéance dans les 7 jours | =ET($D2>=AUJOURDHUI();$D2<=AUJOURDHUI()+7;$F2="") |
Orange |
| Échéance dans les 30 jours | =ET($D2>AUJOURDHUI()+7;$D2<=AUJOURDHUI()+30;$F2="") |
Jaune |
| Payée | =$F2<>"" |
Gris |
Appliquer ces règles à la plage A2:H200 colore toute la ligne selon l’état. Chaque jour, à l’ouverture du fichier, le tableau se met à jour avec la date du jour. Avec un tri par échéance, c’est un outil de pilotage de trésorerie.
Contrôler la cohérence d’un classeur : cellules de contrôle
Un bon classeur comptable comporte des cellules de contrôle qui passent au rouge si un total ne correspond pas.
| Contrôle | Formule | Règle de mise en forme |
|---|---|---|
| Équilibre de la balance | =SOMME(Débit)-SOMME(Crédit) |
Rouge si ≠ 0 : =ABS($B$2)>0,005 |
| Total TCD = total source | =TCD_Total-SOMME(Source[Montant]) |
Rouge si ≠ 0 |
| Clés non trouvées | =NB.SI(Résultats;"Compte inconnu") |
Rouge si > 0 |
| Écart de rapprochement | =Solde_banque_ajuste-Solde_compta_ajuste |
Rouge si ≠ 0 |
Placez ces cellules en haut de chaque feuille : le lecteur voit immédiatement si le classeur est fiable.
Ne pas abuser des couleurs
La mise en forme conditionnelle perd son intérêt si tout est coloré. Quelques principes :
- Trois couleurs au maximum et une légende visible.
- Alerter sur l’exception, pas sur la normalité : mieux vaut colorer les 5 % de lignes à traiter.
- Accessibilité : combiner couleur et symbole (icône, texte) pour les lecteurs daltoniens et l’impression en noir et blanc.
- Performance : limiter les plages (A2:H500 plutôt que des colonnes entières) et le nombre de règles ; des centaines de règles par feuille ralentissent le classeur.
- Documentation : décrire les règles dans un onglet « Mode d’emploi », avec les seuils et leur justification.
Gérer, copier et nettoyer les règles
Mise en forme conditionnelle > Gérer les règles liste toutes les règles de la feuille, dans l’ordre de priorité. On peut modifier la plage d’application, changer l’ordre, supprimer une règle. Pour dupliquer la mise en forme d’une plage vers une autre, utilisez le Pinceau ou Collage spécial > Formats. Après de nombreuses modifications, des règles en doublon apparaissent : nettoyez-les périodiquement. Avant de transmettre un classeur à un tiers, vérifiez que les règles ne dépendent pas de cellules ou de feuilles masquées.
Bonnes pratiques
- Documenter les seuils dans une cellule de paramètres (
=$D2>$K$1) pour que l’utilisateur puisse les modifier sans toucher aux règles. - Limiter les couleurs : rouge pour l’action urgente, orange pour la surveillance, vert pour la conformité.
- Toujours doubler d’un texte (colonne Statut) pour l’impression et l’accessibilité.
- Éviter les plages entières (colonnes A:Z) : cela ralentit le fichier.
- Nettoyer les règles lors des copier-coller de feuilles (elles se dupliquent).
- Tester avec des valeurs limites (89, 90, 91).
Erreurs fréquentes
- Règle écrite pour la mauvaise cellule de départ : décalage d’une ligne.
- Références absolues partout (
$D$2) : toutes les lignes prennent la couleur de la première. - Plages fusionnées : les règles s’appliquent mal.
- Dates en texte : les comparaisons échouent ; contrôler avec
=ESTNUM(C2).
Cas particuliers et situations limites
La règle ne s’applique qu’à la première ligne. La formule est écrite avec une référence relative incorrecte : il faut verrouiller la colonne ($D2) mais laisser la ligne relative pour que la règle s’étende à toute la plage. Tester la règle sur trois lignes avant de la généraliser.
Le classeur devient lent. Des centaines de règles sur des colonnes entières ralentissent le recalcul. Limiter les plages aux lignes utiles (A2:H500) ou convertir les données en tableau Excel et appliquer la règle à la colonne du tableau.
Les couleurs disparaissent à l’impression en noir et blanc. Associer la couleur à un symbole (jeu d’icônes, texte « ALERTE ») ou à une police en gras ; vérifier l’aperçu avant impression.
La règle dépend d’une autre feuille. Excel accepte les références à d’autres feuilles dans les règles par formule depuis les versions récentes ; pour la compatibilité, utiliser un nom défini (Date_analyse, Seuil_alerte) plutôt qu’une référence directe.
Un destinataire n’a pas les mêmes règles. Les règles voyagent avec le classeur, mais la copie de cellules vers un autre fichier peut dupliquer ou perdre des règles. Nettoyer les règles via Gérer les règles avant la diffusion.
À retenir
La mise en forme conditionnelle transforme un tableau en système d’alerte : règles par formule pour colorer des lignes entières, trois couleurs au maximum, légende visible, cellules de contrôle en haut de feuille. Elle gagne à être documentée et limitée aux plages utiles.
Questions fréquentes
Où se trouve la mise en forme conditionnelle ?
Onglet Accueil, groupe Style, bouton Mise en forme conditionnelle. Les règles existantes se gèrent par Gérer les règles.
Pourquoi ma règle ne s’applique-t-elle pas à toute la ligne ?
Parce que la plage d’application ne couvre qu’une colonne ou que la formule utilise une référence absolue ($D$2). Pour colorer la ligne selon la colonne D, appliquez la règle à A2:E50 avec la formule =$D2>90.
Les couleurs apparaissent-elles à l’impression ?
Oui, mais elles peuvent être illisibles en noir et blanc : doublez les couleurs par une colonne de texte (Alerte, Action) ou par des icônes.
Combien de règles peut-on empiler ?
Excel en accepte beaucoup, mais au-delà de trois ou quatre règles par plage la lisibilité chute. Les règles s’appliquent dans l’ordre de priorité ; cochez Arrêter si vrai quand nécessaire.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.