Repérer d’un coup d’œil les factures en retard, sans relire ligne par ligne un tableau de cent échéances : voilà ce que permet la mise en forme conditionnelle. Cet outil colore automatiquement vos cellules selon des règles que vous définissez. Bien réglée, votre feuille de suivi clignote en rouge sur les retards et en orange sur les échéances proches. Je vous montre comment la paramétrer pour vos factures.
Le principe de la mise en forme conditionnelle
La mise en forme conditionnelle applique un format, couleur de fond, de texte ou icône, uniquement lorsqu’une condition est remplie. Au lieu de surligner manuellement chaque retard, vous écrivez une règle une fois pour toutes, et Excel l’applique en continu. Quand une date passe, la couleur se met à jour seule à l’ouverture du fichier.
Pour le suivi d’échéances, nous allons combiner trois fonctions clés : AUJOURDHUI (TODAY en anglais) qui renvoie la date du jour, et ET (AND en anglais) qui combine plusieurs conditions. Tout repose sur une formule personnalisée.
Préparer le tableau des factures
Construisez d’abord une table claire avec au minimum la date d’échéance, le montant TTC et le statut de paiement. Voici un exemple :
| Facture | Client | Date d’échéance | Montant TTC | Statut |
|---|---|---|---|---|
| F-2026-014 | Dupont SARL | 10/01/2026 | 3 000,00 € | Impayée |
| F-2026-015 | Martin & Fils | 05/02/2026 | 1 440,00 € | Impayée |
| F-2026-016 | Atelier Léa | 28/02/2026 | 960,00 € | Payée |
| F-2026-017 | Boutique Verte | 15/03/2026 | 3 720,00 € | Impayée |
Les montants TTC intègrent la TVA à 20 %. Veillez à ce que la colonne « Date d’échéance » contienne de vraies dates au format jj/mm/aaaa, et non du texte, sinon les comparaisons de dates échoueront.
Règle 1 : colorer les factures en retard en rouge
Une facture est en retard si sa date d’échéance est dépassée et qu’elle n’est pas payée. Procédez ainsi :
- Sélectionnez la plage des dates d’échéance, par exemple C2:C100.
- Allez dans Accueil, puis Mise en forme conditionnelle, puis Nouvelle règle.
- Choisissez « Utiliser une formule pour déterminer les cellules à mettre en forme ».
- Saisissez la formule
=AND(C2<TODAY(),E2="Impayée"). - Cliquez sur Format, onglet Remplissage, et choisissez un rouge clair. Validez.
La fonction ET combine deux conditions : la date d’échéance est antérieure à aujourd’hui, et le statut est « Impayée ». Seules les factures réellement en retard se colorent, les factures déjà payées restent neutres.
Règle 2 : signaler les échéances à venir en orange
Pour anticiper, signalez en orange les factures dont l’échéance tombe dans les sept prochains jours. Créez une nouvelle règle sur la même plage avec la formule =AND(C2>=TODAY(),C2<=TODAY()+7,E2="Impayée"). Cette règle teste trois conditions à la fois : l’échéance n’est pas encore passée, elle survient dans la semaine, et la facture reste impayée. Choisissez un remplissage orange clair.
L’ordre des règles a son importance. La règle « en retard » doit primer sur la règle « à venir » pour éviter qu’une cellule réponde aux deux. Dans le gestionnaire de règles, placez la règle rouge au-dessus de la règle orange.
Règle 3 : griser les factures payées
Pour clarifier visuellement votre suivi, grisez les lignes payées. Sélectionnez toute la table, créez une règle avec la formule =$E2="Payée" et appliquez un gris léger. Notez le dollar devant la colonne E : il fige la colonne tout en laissant la ligne varier, ce qui colore la ligne entière en fonction de son statut.
Cette technique du dollar partiel est la clé pour mettre en forme une ligne complète à partir d’une seule colonne de référence.
Ajouter des icônes et des barres de données
Au-delà des couleurs de remplissage, Excel propose des jeux d’icônes et des barres de données qui enrichissent votre suivi. Sur une colonne « Jours de retard », vous pouvez appliquer un jeu d’icônes à trois symboles : un point vert pour les échéances lointaines, un orange pour les échéances proches, un rouge pour les retards. Sélectionnez la colonne, ouvrez Mise en forme conditionnelle, puis Jeux d’icônes, et réglez les seuils.
Les barres de données, elles, transforment chaque cellule de montant en une mini-jauge proportionnelle. Sur une colonne « Montant TTC », elles font ressortir d’un coup d’œil les factures les plus lourdes. Combinées aux règles de couleur sur les dates, elles offrent un tableau de bord visuel complet, sans le moindre graphique à construire.
Tester et déboguer vos règles
Une règle qui ne se déclenche pas est souvent source de frustration. Avant de conclure à un bug, testez votre formule dans une cellule libre. Recopiez par exemple =AND(C2<TODAY(),E2="Impayée") dans une cellule à côté de votre table : si elle renvoie VRAI ou FAUX cohérent, votre règle est correcte et le problème vient du format des dates ou de la plage d’application. Si elle renvoie une erreur, corrigez d’abord la formule avant de la replacer dans la règle.
Pensez aussi à vérifier la priorité dans le gestionnaire de règles, car une règle bien écrite mais mal classée peut être masquée par une autre. Cette méthode de test isolé est valable pour toute formule personnalisée de mise en forme conditionnelle.
Combiner avec une colonne de relance
Vous pouvez ajouter une colonne calculée indiquant le nombre de jours de retard. La formule =IF(E2="Payée","",TODAY()-C2) renvoie le nombre de jours écoulés depuis l’échéance pour les factures impayées, et reste vide pour les factures réglées. Pour éviter toute erreur si une cellule de date est vide, encadrez-la : =IFERROR(IF(E2="Payée","",TODAY()-C2),""). Combinée à la mise en forme conditionnelle, cette colonne hiérarchise vos relances : on attaque d’abord les retards les plus anciens.
Pour démarrer rapidement, partez de notre modèle de suivi des créances clients et complétez votre pilotage avec le tableau de suivi des devis.
Erreurs courantes
- Des dates stockées en texte : si l’échéance n’est pas une vraie date, la comparaison avec AUJOURDHUI ne fonctionne pas et aucune couleur n’apparaît.
- Oublier la référence relative : la formule doit pointer vers la première cellule de la sélection (C2) pour que la règle s’adapte à chaque ligne. Une référence figée colorerait tout en bloc.
- Mauvais ordre des règles : si la règle orange passe avant la rouge, une facture en retord pourrait s’afficher en orange. Placez le retard en priorité.
- Plage de la règle trop courte : si vous ajoutez des lignes au-delà de la plage définie, elles ne seront pas colorées. Étendez la plage ou utilisez un tableau structuré.
Questions fréquentes
Pourquoi ma mise en forme ne se déclenche-t-elle pas ?
Dans la majorité des cas, vos dates sont stockées en texte et non comme de vraies dates. Vérifiez le format jj/mm/aaaa et assurez-vous que la formule pointe vers la bonne cellule de référence de la plage.
La couleur se met-elle à jour automatiquement chaque jour ?
Oui, car la fonction AUJOURDHUI se recalcule à chaque ouverture du fichier. Une facture qui dépasse son échéance pendant la nuit apparaîtra en rouge dès la prochaine ouverture du classeur.
Puis-je colorer la ligne entière et pas seulement la date ?
Oui. Sélectionnez toute la plage de la table et utilisez une référence avec un dollar sur la colonne, comme =$E2="Payée". Le dollar fige la colonne et la règle s’applique à la ligne complète.
Comment retirer une règle de mise en forme ?
Allez dans Mise en forme conditionnelle, puis Gérer les règles. Sélectionnez la règle concernée et cliquez sur Supprimer la règle. Vous pouvez aussi y modifier l’ordre de priorité.
