Mise en forme conditionnelle par formule dans Excel

La mise en forme conditionnelle change l'apparence d'une cellule selon une règle : une couleur de fond pour les retards, une police rouge pour les montants négatifs, une ligne grisée pour les lignes déjà traitées. La règle la plus souple est fondée sur une formule. Si la formule renvoie VRAI, la mise en forme s'applique. Si elle renvoie FAUX, elle ne s'applique pas.

Les étapes dans Excel pour Windows

La page Microsoft décrit la procédure ainsi : dans l'onglet Accueil, groupe Styles, ouvrez la liste Mise en forme conditionnelle et choisissez Gérer les règles. Cliquez sur Nouvelle règle. Sous le type de règle, sélectionnez « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ». Saisissez la formule dans la case « Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie ». Cliquez sur Format, choisissez la couleur ou la police, puis OK.

La page précise deux règles. La formule doit commencer par le signe égal (=) et renvoyer une valeur logique, VRAI ou FAUX. Et si la formule renvoie une erreur, la mise en forme conditionnelle n'est pas appliquée à ces cellules. Pour éviter ce cas, la page propose d'entourer la formule d'une fonction de type SIERREUR.

Un exemple : repérer les doublons

La page donne la formule =NB.SI($A$2:$A$400;A2)>1 pour repérer les valeurs qui apparaissent plus d'une fois dans la plage A2:A400. Les signes $ fixent la plage, tandis que A2 varie avec la ligne. La règle s'applique à la plage A2:A400, et la couleur signale chaque valeur répétée. Cela ne supprime rien : c'est un repérage, à vérifier avant toute suppression.

Prompt 1 : écrire la règle à partir d'une consigne

Je veux une règle de mise en forme conditionnelle dans la plage [plage, par exemple B2:H500] de la feuille [nom]. Elle doit colorer la ligne entière quand [condition, par exemple : la colonne E (date limite) est antérieure à aujourd'hui et la colonne F (statut) n'est pas Terminé].
Structure : colonne E = date (format JJ/MM/AAAA), colonne F = statut (texte : À faire, En cours, Terminé). Donne la formule qui commence par le signe égal, avec les références correctes pour la première ligne de la plage, et explique les $ utilisés.

Prompt 2 : tester la règle avant de l'étendre

Voici la formule de ma règle : [formule]. Propose trois lignes de test que je peux saisir dans ma feuille : une qui doit être colorée, une qui ne doit pas l'être, une cas limite (cellule vide ou valeur juste à la limite). Pour chacune, indique le résultat attendu.

Prompt 3 : contrôler les erreurs dans la règle

Ma règle de mise en forme ne colore pas certaines cellules. La formule est : [formule]. Les cellules concernées contiennent [type de valeur, par exemple : des montants calculés à partir d'une recherche]. Dis-moi si une erreur dans la formule peut empêcher la mise en forme, et propose une version qui renvoie une valeur explicite à la place de l'erreur.

Exemple concret

Un suivi de relances clients : la colonne E contient la date limite de paiement, la colonne F le statut. Vous voulez colorer en rouge les lignes en retard qui ne sont pas réglées. La formule, écrite pour la première ligne B2 : =ET($E2<AUJOURDHUI();$F2<>"Terminé"). Le $ devant la colonne fixe la colonne quand la règle est copiée. Le test : une ligne avec une date passée et un statut « En cours » doit être colorée ; une ligne « Terminé » ne l'est pas.

À vérifier

• Le signe égal : sans lui, la règle ne fonctionne pas.
• Le retour VRAI ou FAUX : une formule qui renvoie du texte ne déclenche pas la mise en forme comme attendu.
• Les erreurs : une cellule en erreur ne reçoit pas la mise en forme.
• Les références : la première ligne de la plage doit être écrite correctement, avec les $ au bon endroit.
• Les dates : une date saisie en texte ne se compare pas à AUJOURDHUI().
• L'ordre des règles : si plusieurs règles s'appliquent, vérifiez laquelle prime dans le gestionnaire de règles.
• La version : les menus peuvent différer sur Mac ou sur le web.

Éviter la surcharge

Une feuille avec dix règles de couleur devient illisible. Gardez trois couleurs au plus, une par signification : retard, attention, terminé. Une légende en haut de la feuille, écrite en quelques mots, évite les questions. Si une règle sert à décider, ajoutez une colonne de texte qui dit la même chose : une couleur ne se lit pas au filtre ni à l'impression en noir et blanc.

Pour une règle qui s'appuie sur une somme par catégorie, voyez la page SOMME.SI.ENS. Pour comprendre une formule déjà présente dans la feuille, voyez expliquer une formule.

Résumé

Une règle fondée sur une formule est simple à écrire si elle commence par le signe égal, renvoie VRAI ou FAUX, et utilise les bonnes références. Testez-la sur trois lignes connues, vérifiez les erreurs, puis étendez-la à la plage. Gardez peu de couleurs, avec une signification claire.