- La mise en forme conditionnelle colore, souligne ou ajoute une icône à une cellule selon son contenu : les anomalies se voient sans lire une seule valeur.
- Les règles prédéfinies (supérieur à, doublons, 10 premiers, barres, icônes) couvrent les cas simples ; la règle par formule, avec une colonne figée (=$F2>30), colore une ligne entière.
- L’ordre des règles compte : la première qui s’applique l’emporte quand elles se contredisent ; le gestionnaire de règles permet de réordonner et de limiter la portée.
- Ne reposez jamais sur la couleur seule : doublez-la d’un texte (« Relance forte ») ou d’une icône, pour les impressions en noir et blanc et les daltoniens.
Un tableau de gestion qu’on doit relire ligne à ligne pour trouver les anomalies est un tableau qui ne sert pas. La mise en forme conditionnelle demande à Excel de changer l’apparence d’une cellule en fonction de sa valeur : une facture échue depuis plus de 30 jours passe en rouge, un écart défavorable reçoit une icône, un doublon est surligné. L’œil fait le reste.
Ce que vous saurez faire à la fin de l’épisode
- appliquer les règles prédéfinies (surbrillance, haut/bas, barres, icônes) ;
- écrire une règle par formule qui colore une ligne entière ;
- gérer les priorités, la portée et la suppression des règles ;
- adapter les icônes au sens de l’indicateur (produit ou charge) ;
- éviter les pièges de lisibilité et de maintenance.
Où se trouve la commande ?
Accueil › Mise en forme conditionnelle. Les familles de règles :
| Famille | Exemples | Usage |
|---|---|---|
| Règles de mise en surbrillance | Supérieur à, entre, texte contenant, date, valeurs en double | Alertes simples |
| Règles des valeurs plus/moins élevées | 10 premiers éléments, 10 %, supérieur à la moyenne | Repérer les extrêmes |
| Barres de données | Barre proportionnelle dans la cellule | Comparer des montants d’un coup d’œil |
| Nuances de couleurs | Dégradé du vert au rouge | Cartes de chaleur (balance âgée, matrice) |
| Jeux d’icônes | Feux, flèches, drapeaux | Statut d’un indicateur |
| Nouvelle règle | Utiliser une formule pour déterminer… | Tout ce que les autres ne couvrent pas |
Le cas : un échéancier de factures clients
Atlas Négoce suit huit factures de vente au 15 mars 2026. La colonne Retard (j) est calculée par =MAX(0;$J$2-D2) (épisode 17), et la colonne Statut par un SI : « Relance forte » au-delà de 30 jours de retard, « En retard » de 1 à 30 jours, « À échéance » sinon.
Figure 1 : l’échéancier. Repère 1 : la ligne en rouge (36 jours) ; repère 2 : les lignes en orange (de 1 à 30 jours) ; repère 3 : les barres de données de la colonne Montant.
Résultats du statut au 15/03/2026 :
| Facture | Client | Échéance | Montant | Retard | Statut |
|---|---|---|---|---|---|
| FV-0103 | Atlas Export | 07/02/2026 | 15 000 | 36 j | Relance forte |
| FV-0106 | Sud Négoce | 26/02/2026 | 9 500 | 17 j | En retard |
| FV-0101 | Maroc Équipements | 06/03/2026 | 12 000 | 9 j | En retard |
| FV-0108 | Garage Mekki | 13/03/2026 | 7 800 | 2 j | En retard |
| FV-0102, 0104, 0105, 0107 | du 15/03 au 26/04 | 40 800 au total | 0 | À échéance |
L’encours échu s’élève à 15 000 (plus de 30 jours) + 29 300 (1 à 30 jours) = 44 300, soit 52 % des 85 100 de factures ouvertes ; 40 800 ne sont pas encore échus.
Règle 1 : colorer toute la ligne avec une formule
- Sélectionnez la plage de données sans l’en-tête, de
A2àG9. La cellule active estA2(la première). Mise en forme conditionnelle › Nouvelle règle › Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.- Saisissez la formule, écrite pour la première cellule de la sélection :
=$F2>30. Format…: remplissage rouge clair, police rouge foncé. Validez.
Pourquoi $F2 ? Le dollar fige la colonne (on regarde toujours la colonne F, le retard) et laisse la ligne relative : pour la ligne 4, Excel évalue $F4>30. Sans le dollar, la formule se déplacerait de colonne en colonne et ne colorerait qu’une partie de la ligne. C’est exactement la référence mixte de l’épisode 4.
Règle 2 : =ET($F2>=1;$F2<=30) en orange, pour les retards de 1 à 30 jours. Le gestionnaire de règles doit montrer la règle rouge avant l’orange ; ici les conditions sont exclusives, l’ordre est sans conséquence, mais cocher Interrompre si Vrai sur la première évite les chevauchements quand les conditions ne s’excluent pas.
Barres de données : sélectionnez E2:E9, Mise en forme conditionnelle › Barres de données › Remplissage dégradé ou uni. Les 22 000 de FV-0105 produisent la barre la plus longue, 4 100 la plus courte : on repère les gros montants sans les lire. Pour des barres dont la longueur se compare d’une colonne à l’autre, fixez le minimum et le maximum dans Gérer les règles › Modifier › Minimum/Maximum.
Règle 3 : les icônes, selon le sens de l’indicateur
Les jeux d’icônes (feux, flèches) conviennent aux tableaux d’écarts, à condition d’en choisir le sens : un écart de −14,7 % sur le chiffre d’affaires est mauvais, le même écart sur les achats est favorable.
Figure 2 : cinq lignes de budget et réel. Le sens des couleurs dépend de la nature de la ligne.
| Ligne | Budget | Réel | Écart % | Lecture |
|---|---|---|---|---|
| Chiffre d’affaires | 430 000 | 366 900 | −14,7 % | Rouge : défavorable (produit sous le budget) |
| Achats | 260 000 | 214 000 | −17,7 % | Vert : favorable (charge sous le budget) |
| Charges externes | 52 000 | 58 300 | +12,1 % | Rouge : défavorable (charge au-dessus) |
| Personnel | 96 000 | 96 000 | 0,0 % | Vert : conforme |
| Résultat | 22 000 | −2 300 | −110,5 % | Rouge : défavorable |
Un jeu d’icônes unique appliqué à toute la colonne se tromperait sur les charges. Deux solutions : appliquer une règle par type de ligne (règles de formule avec un test sur la nature de la ligne), ou calculer d’abord un écart favorable/défavorable (positif quand c’est bon, négatif sinon) et appliquer l’icône à cet écart. La seconde est plus simple à maintenir : =SI(nature="Charge";budget-réel;réel-budget).
D’autres règles par formule utiles
| Besoin | Formule (première cellule en A2) |
|---|---|
| Échéance dépassée | =$D2<AUJOURDHUI() (ou la date de situation $J$2) |
| Doublon de numéro de pièce | =NB.SI($A$2:$A$100;$A2)>1 |
| Week-end | =JOURSEM($C2;2)>5 |
| Cellule vide dans une colonne obligatoire | =ET($A2<>"";$E2="") |
| Contrôle : écart non nul | =ARRONDI($G2;2)<>0 |
| Lignes alternées | =MOD(LIGNE();2)=0 |
| Total de la ligne en gras | =$A2="Total" |
Gérer les règles
Mise en forme conditionnelle › Gérer les règles, avec Afficher les règles de mise en forme pour : Cette feuille. On y trouve :
- l’ordre (flèches haut/bas) : la règle du haut a la priorité en cas de conflit ;
- S’applique à : la portée de chaque règle, modifiable ;
- Interrompre si Vrai : arrête l’évaluation des règles suivantes ;
- Supprimer la règle, ou
Effacer les règles(de la sélection, de la feuille).
Piège de copier-coller. Chaque copier-coller de cellules mises en forme découpe et multiplie les règles : un classeur utilisé pendant deux ans peut accumuler des centaines de règles fragmentées. Ouvrez de temps en temps le gestionnaire de règles de la feuille, supprimez les règles inutiles et fusionnez les doublons.
Les bonnes pratiques
- Peu de couleurs, un sens constant : rouge pour l’alerte, orange pour l’attention, vert pour l’absence de problème. Pas de dégradés arc-en-ciel.
- Doublez la couleur : la colonne Statut dit ce que la couleur signifie ; sur une impression en noir et blanc, le message passe.
- Une légende dans l’onglet Lisez-moi : « rouge : plus de 30 jours de retard ».
- Des paramètres, pas des constantes :
=$F2>SeuilRelance(nom défini, épisode 10) plutôt que>30en dur. - Pas de logique cachée : une règle de mise en forme ne doit jamais être la seule trace d’un contrôle ; une colonne de statut doit aussi porter l’information.
À vous de jouer
Dans le classeur d’exercice :
- Onglet Échéancier : créez les deux règles par formule (
=$F2>30rouge,=ET($F2>=1;$F2<=30)orange) surA2:G9. Combien de lignes sont rouges ? orange ? - Ajoutez les barres de données sur
E2:E9. - Ajoutez la règle de doublons sur la colonne Facture. Saisissez deux fois
FV-0103pour la tester, puis annulez. - Onglet Écarts : calculez un écart favorable/défavorable selon la nature de la ligne et appliquez des icônes à cette colonne.
- Ouvrez le gestionnaire de règles et lisez la portée de chaque règle.
Correction. Une ligne est rouge (FV-0103, 36 jours) et trois sont orange (FV-0101, FV-0106 et FV-0108). Les lignes à échéance restent sans couleur. Pour les écarts, la colonne favorable/défavorable donne +17,7 % favorable pour les achats, −14,7 % pour le chiffre d’affaires, −12,1 % pour les charges externes, 0 % pour le personnel, −110,5 % pour le résultat : trois icônes rouges, deux vertes.
À retenir
- La mise en forme conditionnelle fait signaler les anomalies par le tableau lui-même.
- Règle par formule : écrite pour la première cellule, avec la colonne figée (
=$F2>30) pour colorer une ligne. - Le sens d’une icône dépend de la nature de la ligne (produit ou charge) : calculez un écart favorable/défavorable.
- Gérez les règles : ordre, portée, nettoyage après copier-coller.
- La couleur ne suffit pas : doublez-la d’un texte ou d’une icône.
Épisode 30, dernier du module 5 : assembler un tableau de bord financier d’une page.
Questions fréquentes
Comment colorer toute une ligne selon la valeur d’une cellule ?
Sélectionnez la plage entière (A2:G9), créez une règle par formule : =$F2>30, avec la colonne F figée par le dollar et la ligne relative. Excel évalue la formule pour chaque ligne et colore la ligne entière quand elle est vraie.
Pourquoi ma règle s’applique-t-elle à la mauvaise ligne ?
La formule est écrite par rapport à la première cellule de la plage sélectionnée. Si vous sélectionnez A2:G9 mais écrivez la formule pour la ligne 4, tout est décalé. Vérifiez la cellule active au moment de créer la règle.
Comment voir et modifier toutes les règles d’une feuille ?
Accueil › Mise en forme conditionnelle › Gérer les règles, puis choisissez « Cette feuille » dans la liste déroulante. On y réordonne les règles, on modifie leur portée (« S’applique à ») et on les supprime.
La mise en forme conditionnelle ralentit-elle le classeur ?
Quand elle est appliquée à de très grandes plages avec des formules complexes, oui. Limitez la portée aux lignes utiles (ou à un tableau Excel) et évitez les fonctions volatiles dans les règles.
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.