Excel : surligner automatiquement les tâches en retard avec la mise en forme conditionnelle

Vous tenez une liste de tâches dans Excel et vous voulez qu’elles se colorent automatiquement dès que la date d’échéance est dépassée si la tâche n’est pas terminée ? Voici une méthode robuste, rapide à mettre en œuvre et facile à maintenir.

Sommaire

Vue d’ensemble de la question

Dans votre feuille Excel :

La colonne A contient la date de demande.
La colonne B calcule automatiquement la date d’échéance 14 jours plus tard.
La colonne C indique si la tâche est terminée (YES) ou reste vide.

Objectif : mettre en évidence (couleur) les dates d’échéance de la colonne B déjà dépassées uniquement lorsque la colonne C est vide (donc tâche non terminée).

Solution proposée

Préparer les colonnes

En B2, calculez l’échéance à J+14 : =SI(A2="";""; A2+14) Recopiez ensuite la formule jusqu’à la dernière ligne utile (ou convertissez la plage en Tableau : Accueil > Mettre sous forme de tableau pour que la formule s’étende automatiquement).
Laissez la colonne C vide par défaut et saisissez YES quand la tâche est terminée. Pour éviter les variantes (Yes, oui, espaces…), vous pouvez ajouter une validation des données (onglet Données > Validation des données) avec une liste contenant YES.

Créer la mise en forme conditionnelle

Sélectionnez la plage d’échéances, par exemple B2:B3500.
Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Saisissez la formule : =ET($C2=""; $B2<AUJOURDHUI())
Cliquez sur Format… et choisissez une couleur de remplissage (par exemple, rouge pâle) ou une couleur de police, éventuellement en gras.
Validez avec OK. Toutes les échéances passées dont la tâche n’est pas marquée comme terminée seront maintenant mises en surbrillance.

Pourquoi cette formule fonctionne

$C2="" : la cellule C de la même ligne est vide → la tâche n’est pas marquée comme terminée.
$B2<AUJOURDHUI() : l’échéance est antérieure à la date du jour → elle est dépassée.
Les $ figent la colonne (B et C) tandis que la ligne reste relative (2, 3, 4…) : la règle se déplace correctement pour chaque ligne.

Variante sur la date de demande

Si vous préférez raisonner sur la colonne A (demande) plutôt que sur B (échéance), utilisez :

=ET($C2=""; $A2<AUJOURDHUI()-14)

C’est équivalent puisque B = A + 14. Choisissez l’approche la plus intuitive pour votre équipe.

Points clés à retenir

AUJOURDHUI() doit comporter des parenthèses, même sans argument.
Dans une règle de MFC, n’écrivez la condition que pour la première ligne de la plage (ici la ligne 2). Excel la réplique automatiquement aux autres lignes.
Avec une configuration française d’Excel, les arguments de formule sont séparés par des point-virgules (;), pas des virgules (,).
Ne référencez pas toute la plage C2:C3500 dans la formule : un seul test sur $C2 suffit, Excel « glisse » la condition ligne par ligne.
Pour des listes très longues, préférez une plage raisonnable (ex. B2:B10000) plutôt que B:B, plus coûteux en performance.

Étapes de vérification

Colonne B au format date : appliquez un format Date courte pour lever toute ambiguïté.
Formule propagée : =SI(A2="";""; A2+14) doit être recopiée jusqu’en bas.
Aucune « date » en texte dans B (par exemple 2025/09/17 stocké comme texte). Si besoin : Données > Convertir ou Collage spécial > Multiplier par 1 pour forcer l’interprétation en date.
Test rapide : saisissez une date passée en A2 puis laissez C2 vide ; B2 doit se colorer. Si vous mettez YES en C2, la couleur doit disparaître.

Exemple de données

A – Date de demande	B – Échéance (A+14)	C – Terminée ?	D – Jours de retard (optionnel)
01/09/2025	15/09/2025		2
08/09/2025	22/09/2025
20/08/2025	03/09/2025	YES

Si la date du jour est le 17/09/2025, seule la ligne 1 est en retard et doit se colorer (C vide et B < 17/09/2025). La ligne 3 n’est pas colorée car C = YES.

Informations complémentaires utiles

Couleurs personnalisées

Dans la boîte de dialogue de la règle, choisissez un remplissage marquant (rouge pâle, texte blanc). Vous pouvez aussi appliquer un barré ou un gras pour renforcer le signal visuel. Évitez toutefois de multiplier les effets : trop d’alertes tuent l’alerte.

Filtrer uniquement les tâches en retard

Après application de la MFC, activez le filtre sur la colonne B (Données > Filtrer) puis utilisez Filtrer par couleur pour afficher les échéances dépassées d’un coup d’œil.

Rappels dynamiques

Ajoutez une colonne D « Jours de retard » :

=SI($C2=""; AUJOURDHUI()-$B2; "")

Créez ensuite une MFC sur cette colonne pour colorer en rouge les valeurs > 0. Vous verrez immédiatement de combien de jours chaque tâche dépasse la date limite.

Variantes avancées (opérationnelles)

Ignorer les week-ends dans l’échéance

Si l’échéance « 14 jours » doit être calculée en jours ouvrés (hors samedi/dimanche), remplacez la formule de B2 par :

=SI(A2="";""; SERIE.JOUR.OUVRE(A2; 14))

La règle de MFC ne change pas (=ET($C2=""; $B2<AUJOURDHUI())) puisque vous comparez toujours la date d’échéance réelle à aujourd’hui.

Prendre en compte les jours fériés

Créez une petite liste de jours fériés sur une autre feuille, nommez la plage Feries (Formules > Définir un nom).
En B2 : =SI(A2="";""; SERIE.JOUR.OUVRE(A2; 14; Feries))
En D2 (jours de retard ouvrés) : =SI($C2=""; NB.JOURS.OUVRES($B2; AUJOURDHUI(); Feries); "")

Paramétrer le délai dans une cellule

Vous voulez parfois 10 jours, parfois 21 jours ? Placez la durée en E1 (ex. 14) et utilisez :

En B2 (jours calendaires) : =SI(A2="";""; A2 + $E$1)
En B2 (jours ouvrés) : =SI(A2="";""; SERIE.JOUR.OUVRE(A2; $E$1))
La MFC reste identique, ou si vous raisonnez depuis A : =ET($C2=""; $A2<AUJOURDHUI()-$E$1)

Neutraliser l’heure dans les dates

Certaines dates importées contiennent une heure cachée (ex. 15/09/2025 13:45). Si la MFC « semble » se déclencher trop tôt ou trop tard, forcez la date à minuit :

=SI(A2="";""; DATE(ANNEE(A2); MOIS(A2); JOUR(A2)) + 14)

De même, pour comparer proprement en MFC : =ET($C2=""; ENT($B2)<AUJOURDHUI()) (la fonction ENT supprime la partie heure).

Avec un Tableau Excel (structuré)

Si vos données sont sous forme de Tableau nommé Taches avec les colonnes Date de demande, Échéance, Terminée ? :

En Échéance : =[@[Date de demande]] + 14
MFC : sélectionnez la colonne Échéance du tableau (pas l’en-tête), puis utilisez la même formule relative à la première ligne visible du tableau (équivalente à B2) : =ET($C2=""; $B2<AUJOURDHUI()) Excel répliquera la règle automatiquement aux nouvelles lignes ajoutées au Tableau.

Multi-niveaux d’alerte

Créez plusieurs règles pour distinguer « en retard », « échéance aujourd’hui », « échéance proche » :

En retard : =ET($C2=""; $B2<AUJOURDHUI())
Aujourd’hui : =ET($C2=""; $B2=AUJOURDHUI())
Proche (J+3) : =ET($C2=""; $B2>AUJOURDHUI(); $B2<=AUJOURDHUI()+3)

Dans Gestionnaire de règles, placez l’ordre du plus critique au moins critique et cochez Arrêter si vrai pour éviter qu’une même cellule prenne plusieurs formats.

Bonnes pratiques et pièges fréquents

Respectez les séparateurs : en français, c’est ; (point-virgule). Si votre Excel attend des , (virgules), adaptez la formule.
Ordonnez les règles : une autre MFC plus « forte » pourrait masquer votre couleur. Gérez la priorité et testez avec Afficher les règles pour cette feuille.
C cohérente : la règle testant vide, la présence d’un espace «  » en C empêche la coloration. Évitez les espaces involontaires ou utilisez une validation.
Dates au format texte : un triangle vert indique souvent une « date » en texte. Convertissez (menu contextuel > Convertir en nombre) ou multipliez par 1.
Plage « Appliquer à » : vérifiez qu’elle correspond exactement aux cellules d’échéance (ex. $B$2:$B$3500).
Performance : évitez les plages sur toute la colonne si votre fichier est volumineux. Préférez une plage calculée ou un Tableau.
Protection de feuille : si la feuille est protégée, cochez « Autoriser : Utiliser la mise en forme conditionnelle » pour permettre la mise à jour des couleurs.

FAQ (questions courantes)

Puis-je utiliser « OUI » au lieu de « YES » en colonne C ?

Oui. Adaptez simplement la logique : notre règle se base sur « cellule vide » pour considérer la tâche non terminée. Dès que C n’est plus vide (OUI, YES, ✔), la mise en surbrillance s’arrête. Si vous voulez être strict, utilisez une seconde règle différente, ou modifiez la condition en =$C2<>"YES".

Et si je veux 10 jours ouvrés (et non calendaires) ?

Remplacez le calcul d’échéance par SERIE.JOUR.OUVRE(A2; 10) et laissez la MFC telle quelle (ET($C2=""; $B2<AUJOURDHUI())).

Comment faire si mes dates contiennent des heures ?

Normalisez la date dans B avec DATE(ANNEE(A2); MOIS(A2); JOUR(A2)) + 14 ou comparez ENT($B2) à AUJOURDHUI() dans la MFC.

La règle ne s’applique pas aux nouvelles lignes, pourquoi ?

Si vous n’utilisez pas un Tableau, la plage Appliquer à est fixe. Élargissez-la (par exemple B2:B10000) ou convertissez la liste en Tableau pour un élargissement automatique.

Puis-je afficher le nombre de jours restants avant l’échéance ?

Oui, ajoutez une colonne « Jours restants » :

=SI($C2=""; $B2 - AUJOURDHUI(); "")

Appliquez une MFC en vert si la valeur ≥ 0, en orange si <= 3, en rouge si < 0.

Guide express (récapitulatif)

En B2 : =SI(A2="";""; A2+14) → recopier.
Sélectionnez B2:B3500 → Nouvelle règle > Utiliser une formule.
Collez : =ET($C2=""; $B2<AUJOURDHUI()).
Choisissez un format (remplissage rouge pâle).
Validez → seules les échéances dépassées sur lignes non terminées se colorent.

Modèle prêt à l’emploi (structure conseillée)

A : Date de demande (Date)
B : Échéance (Formule) → =SI(A2="";""; A2+14)
C : Terminée ? (Vide ou YES via validation)
D : Jours de retard (Optionnel) → =SI($C2=""; AUJOURDHUI()-$B2; "")

Optimisations pour la maintenance

Paramètre global de délai : conservez la durée (ex. 14) dans une cellule dédiée (E1) et faites référence à $E$1 partout.
Nommer vos plages (Formules > Définir un nom) : par exemple P_Echeances pour $B$2:$B$3500; si la plage évolue, vous ne modifiez qu’un seul endroit.
Commentaires de cellule : ajoutez un commentaire dans B1 pour documenter la logique (J+14 calendaires / ouvrés…)
Contrôle qualité mensuel : insérez un filtre « n’est pas vide » sur B et C pour repérer les incohérences (échéance vide, terminée vide, etc.).

Cheat‑sheet des formules utiles (français)

Besoin	Formule	Commentaires
Échéance J+14 (calendaire)	`=SI(A2="";""; A2+14)`	Simple et lisible
Échéance à J+N (cellule E1)	`=SI(A2="";""; A2+$E$1)`	Paramétrable
Échéance J+N ouvrés	`=SI(A2="";""; SERIE.JOUR.OUVRE(A2; $E$1))`	Hors week‑end
Échéance J+N ouvrés avec fériés	`=SI(A2="";""; SERIE.JOUR.OUVRE(A2; $E$1; Feries))`	Inclut jours fériés nommés
MFC « En retard »	`=ET($C2=""; $B2<AUJOURDHUI())`	Colorer B si non terminé et échu
MFC « Aujourd’hui »	`=ET($C2=""; $B2=AUJOURDHUI())`	Échéance le jour J
MFC « Proche » (3 jours)	`=ET($C2=""; $B2>AUJOURDHUI(); $B2<=AUJOURDHUI()+3)`	Anticipation
Jours de retard calendaire	`=SI($C2=""; AUJOURDHUI()-$B2; "")`	Négatifs si en avance
Jours de retard ouvrés	`=SI($C2=""; NB.JOURS.OUVRES($B2; AUJOURDHUI(); Feries); "")`	Plus proche du réel

Dépannage pas à pas

La MFC ne déclenche rien : mettez temporairement $B2<>"" dans la formule pour vérifier que la plage est bien ciblée. Si ça réagit, le problème vient des dates (texte…)
La mauvaise ligne se colore : assurez‑vous que la formule est écrite depuis la première cellule de la plage (B2) et que la ligne n’est pas figée (pas de $2).
Tout est coloré : vous avez peut‑être mis =$C2="" entre guillemets doubles ? Vérifiez aussi que la colonne C est vraiment vide (pas d’espace).
Dates « fantômes » : si B affiche une date mais que la MFC ne réagit pas, il s’agit probablement d’un texte formaté comme date. Convertissez‑la (multiplier par 1 ou Texte en colonnes).
Conflit de règles : ouvrez le Gestionnaire de règles et cochez « Arrêter si vrai » sur l’alerte la plus prioritaire.

Aller plus loin

Icônes de statut : en plus de la couleur d’arrière‑plan, vous pouvez afficher un ⚠️ (via une colonne auxiliaire) si =ET($C2=""; $B2<AUJOURDHUI()) renvoie VRAI.
Tableau croisé dynamique : synthétisez le nombre de tâches en retard par service ou responsable en filtrant sur C vide et B < aujourd’hui.
Fonction AUJOURDHUI() et recalcul : AUJOURDHUI() est volatile. La coloration se mettra à jour à chaque recalcul (F9) ou ouverture du fichier.

Récapitulatif minimal à copier-coller

B2 (échéance) : =SI(A2="";""; A2+14)
MFC (Appliquer à : B2:B3500) : =ET($C2=""; $B2&lt;AUJOURDHUI())
Option D2 (jours de retard) : =SI($C2=""; AUJOURDHUI()-$B2; "")

En appliquant cette méthode, vous obtenez un tableau qui s’auto‑surveille : il marque en rouge les tâches réellement en souffrance, sans bruit, et vous permet de prioriser rapidement ce qui doit être traité en premier.

Excel : surligner automatiquement les tâches en retard avec la mise en forme conditionnelle