Vous avez une liste de commandes, un suivi de budget ou un tableau de notes ? La mise en forme conditionnelle peut faire ressortir automatiquement les informations importantes : retards en rouge, objectifs atteints en vert, valeurs à surveiller en orange. Et avec la formule SI, vous pouvez adapter ces règles à vos propres critères.
Dans ce tutoriel, nous allons voir comment créer une règle de mise en forme conditionnelle avec SI, comment l’appliquer à une ligne entière et comment éviter les erreurs de références les plus fréquentes. Pas besoin d’être un pro des formules : on avance étape par étape.
À quoi sert la mise en forme conditionnelle avec SI ?
La mise en forme conditionnelle modifie l’apparence d’une cellule lorsque certaines conditions sont remplies. Vous pouvez, par exemple, colorer en rouge les commandes en retard ou mettre en évidence les chiffres qui dépassent un objectif.
La fonction SI permet de vérifier une condition et de renvoyer un résultat si elle est vraie, puis un autre si elle est fausse. Sa structure est la suivante :
=SI(condition; valeur_si_vrai; valeur_si_faux)
Dans une règle de mise en forme conditionnelle, Excel a surtout besoin de savoir si la condition est vraie ou fausse. Une formule qui renvoie VRAI déclenche donc le format choisi ; une formule qui renvoie FAUX ne le déclenche pas.
Par exemple, cette formule renvoie VRAI si la valeur de A2 est supérieure à 100 :
=SI(A2>100;VRAI;FAUX)
En pratique, elle revient au même que =A2>100. La fonction SI est donc souvent facultative pour une règle simple. Elle devient utile lorsque vous souhaitez rendre la logique plus explicite ou combiner plusieurs résultats.
Créer une première règle avec SI
Imaginons un tableau de ventes dont les montants se trouvent dans la colonne B, à partir de la cellule B2. Vous voulez colorer en vert chaque montant supérieur ou égal à 1 000 €.
- Sélectionnez la plage concernée, par exemple
B2:B50. - Dans le ruban, ouvrez Accueil, puis Mise en forme conditionnelle.
- Choisissez Nouvelle règle, puis l’option permettant d’utiliser une formule pour déterminer les cellules à mettre en forme.
- Saisissez la formule
=SI(B2>=1000;VRAI;FAUX). - Cliquez sur Format, choisissez un remplissage vert, puis validez.
Excel teste la formule pour chaque cellule de la plage. Pour B2, il vérifie si B2 atteint 1 000. Pour B3, il vérifie B3, et ainsi de suite. C’est pour cela que la formule commence par B2 : elle doit faire référence à la première cellule de la plage sélectionnée.
Vous pouvez aussi écrire simplement =B2>=1000. Le résultat sera identique, avec une formule plus courte. Gardez cette version en tête pour les règles directes ; utilisez SI lorsque votre logique comporte plusieurs cas ou que sa lecture vous semble plus claire.
Colorer toute une ligne selon son statut
La mise en forme devient encore plus utile quand elle porte sur une ligne complète. Prenons un tableau de suivi avec les colonnes suivantes : numéro de commande, client, date prévue et statut. Le statut se trouve en colonne D, et les données commencent à la ligne 2.
Pour colorer en rouge toute commande dont le statut est « En retard » :
- Sélectionnez toute la zone du tableau, par exemple
A2:D100. - Créez une nouvelle règle fondée sur une formule.
- Entrez
=$D2="En retard". - Choisissez un remplissage rouge clair et validez.
La formule n’utilise pas SI, car la comparaison renvoie déjà VRAI ou FAUX. Si vous souhaitez malgré tout l’écrire avec cette fonction, vous pouvez utiliser =SI($D2="En retard";VRAI;FAUX).
Le signe dollar devant D est important : $D bloque la colonne du statut, tandis que le numéro de ligne reste variable. Excel vérifie donc toujours la colonne D, mais adapte le numéro de ligne à chaque commande. Sans ce dollar, la référence pourrait se décaler lorsqu’Excel applique la règle aux autres colonnes de la ligne.
Utiliser SI avec plusieurs critères
Vous pouvez aussi combiner plusieurs conditions. Supposons qu’un tableau contienne une date d’échéance en B et un statut en C. Vous voulez mettre en évidence les lignes dont la date est dépassée, à condition que le dossier ne soit pas déjà terminé.
La formule suivante vérifie ces deux critères :
=SI(ET($B2<AUJOURDHUI();$C2<>"Terminé");VRAI;FAUX)
ET signifie que les deux conditions doivent être vraies : la date en B doit être antérieure à aujourd’hui et le statut en C doit être différent de « Terminé ». La fonction AUJOURDHUI met la comparaison à jour automatiquement à l’ouverture du fichier. Une échéance dépassée aujourd’hui pourra donc être signalée demain sans que vous ayez à modifier la règle.
Si vous préférez ne pas utiliser SI, la formule équivalente est =ET($B2<AUJOURDHUI();$C2<>"Terminé"). Dans les deux cas, appliquez la règle à la plage de lignes souhaitée, par exemple A2:C100.
Pour vérifier si au moins une condition est vraie, utilisez OU. Par exemple, =OU($C2="Urgent";$C2="Prioritaire") mettra en évidence les lignes dont le statut est « Urgent » ou « Prioritaire ».
Éviter les cellules vides et les faux positifs
Une règle basée sur une date peut parfois colorer une ligne vide de façon inattendue. Pour éviter cela, ajoutez un test qui vérifie que la cellule de date n’est pas vide :
=ET($B2<>"";$B2<AUJOURDHUI();$C2<>"Terminé")
La première condition, $B2<>"", demande à Excel de poursuivre uniquement si la cellule B2 contient une valeur. C’est un petit ajout qui rend la règle plus fiable, surtout dans un tableau que vous complétez au fil du temps.
Vous pouvez également utiliser SI pour distinguer plusieurs résultats dans une cellule, mais ce n’est pas toujours la meilleure méthode pour colorer. Une formule conditionnelle doit finalement indiquer si la règle s’applique ou non. Si votre expression renvoie du texte comme « En retard » ou « À jour », elle ne constitue pas forcément le test logique attendu par la règle. Pour la mise en forme, privilégiez une formule qui renvoie clairement VRAI ou FAUX.
Comprendre les références relatives et absolues
Les références sont une source classique de confusion. Heureusement, une règle suit la même logique que les formules recopiées dans une feuille de calcul.
A2: la colonne et la ligne peuvent changer selon la cellule évaluée.$A$2: la colonne et la ligne restent fixes.$A2: la colonne reste fixe, mais la ligne peut changer.A$2: la ligne reste fixe, mais la colonne peut changer.
Pour colorer des lignes selon une valeur située en colonne D, utilisez généralement $D2 : la colonne D est fixe, et le numéro de ligne s’adapte. Pour colorer uniquement des cellules de la colonne D en fonction de leur propre valeur, une référence telle que D2 peut suffire.
Une bonne habitude consiste à vérifier la première cellule de la plage sélectionnée. Si votre sélection commence en ligne 2, la formule doit généralement se baser sur la ligne 2. Une formule commençant par D1 risque de décaler toute la mise en forme d’une ligne.
Modifier, tester et dépanner une règle
Si la couleur ne s’affiche pas comme prévu, commencez par ouvrir Accueil > Mise en forme conditionnelle > Gérer les règles. Vérifiez la formule et la plage à laquelle elle s’applique. Une règle correcte appliquée à la mauvaise plage donnera un résultat déroutant, même si Excel n’affiche aucun message d’erreur.
Voici quelques vérifications utiles :
- La formule commence-t-elle par le signe égal ?
- La référence utilisée correspond-elle à la première ligne de la plage ?
- Les textes sont-ils écrits exactement comme dans les cellules, espaces compris ?
- Les séparateurs d’arguments sont-ils des points-virgules ? Dans la plupart des installations françaises d’Excel, c’est le cas.
- Une autre règle ne prend-elle pas le dessus sur celle-ci ?
Pour tester une formule avant de l’ajouter à la mise en forme conditionnelle, saisissez-la temporairement dans une cellule vide. Si elle affiche VRAI ou FAUX selon le résultat attendu, sa logique est probablement correcte. Pensez ensuite à supprimer cette cellule de test.
Si plusieurs règles concernent la même plage, leur ordre peut également compter. Dans le gestionnaire des règles, vous pouvez les réorganiser et, selon les options disponibles, indiquer qu’Excel doit arrêter l’évaluation après une règle donnée. Cela évite qu’une règle générale remplace une couleur plus précise.
Quelques bonnes pratiques pour un tableau lisible
La mise en forme conditionnelle est puissante, mais un arc-en-ciel de couleurs n’aide pas toujours à comprendre un tableau. Choisissez des couleurs cohérentes et limitez-vous aux informations qui méritent vraiment d’attirer l’œil. Par exemple, réservez le rouge aux alertes et le vert aux éléments validés.
Donnez aussi des noms clairs aux statuts de votre tableau. Si certaines cellules contiennent « En retard », d’autres « Retard » et d’autres encore « À relancer », une formule ne pourra pas interpréter ces libellés comme une seule catégorie sans conditions supplémentaires.
Enfin, testez votre règle avec plusieurs cas : une ligne qui doit être colorée, une ligne qui ne doit pas l’être et une ligne incomplète. Ce petit contrôle vous évitera de découvrir une anomalie au moment d’envoyer votre fichier.
Avec quelques formules simples, vous pouvez transformer une liste difficile à parcourir en tableau immédiatement lisible. Commencez par une condition directe, puis ajoutez SI, ET ou OU lorsque vos critères le nécessitent. Et si une couleur apparaît au mauvais endroit, vérifiez d’abord les références : dans Excel, le coupable se cache souvent derrière un signe dollar oublié.
