Mise en forme conditionnelle avec formule Excel
Maîtrisez la mise en forme conditionnelle avec formule dans Excel : surlignez des lignes entières, des dates dépassées et gérez les règles de $.
Par Ayoub Farid · · 3 min de lecture
Les règles prédéfinies (valeurs supérieures à, doublons, nuances de couleurs) répondent aux besoins de base. Mais grâce à une formule, vous pouvez tout mettre en surbrillance dans votre feuille de calcul : une ligne entière selon un statut « En retard », des échéances prévues dans les sept jours ou des ventes inférieures à leur objectif.
Comment ajouter une règle basée sur une formule
- Sélectionnez la plage à mettre en forme, en commençant bien par la cellule supérieure gauche (par exemple A2:F100).
- Rendez-vous dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Saisissez une formule qui renvoie VRAI ou FAUX (TRUE ou FALSE) pour la cellule située en haut à gauche de la sélection.
- Définissez le format souhaité, puis cliquez sur OK.
Excel applique ensuite la formule à chaque cellule de la plage, en ajustant les références exactement comme si vous utilisiez la poignée de recopie. Note : dans la version française d'Excel, les formules s'écrivent avec des points-virgules comme séparateurs et les fonctions sont traduites, bien que nous utilisions ici la syntaxe standard anglophone pour les exemples.
La règle du symbole $
C'est ici que la majorité des utilisateurs rencontrent des erreurs.
Pour mettre en surbrillance toute la ligne lorsque la colonne F indique « En retard » :
=$F2="Late"$F: référence absolue sur la colonne. Excel analyse toujours la colonne F, même pour appliquer le format aux colonnes A, B, C…2(sans $) : la référence reste relative pour la ligne afin de descendre ligne par ligne lors de l'évaluation.
Sans ce signe $, la colonne B évaluerait G2, la colonne C évaluerait H2, et le formatage serait décalé. Pour en savoir plus, consultez notre guide sur les références mixtes.
Exemples de formules utiles
| Élément à surligner | Formule (la plage commence à la ligne 2) |
|---|---|
| Dates dépassées (colonne C) | =AND($C2<TODAY(),$C2<>"") |
| Échéance dans les 7 jours | =AND($C2>=TODAY(),$C2<=TODAY()+7) |
| Sous l'objectif global en F1 | =$B2<$F$1 |
| Sous l'objectif individuel (colonne C) | =$B2<$C2 |
| Doublons dans la colonne A | =COUNTIF($A$2:$A$500,$A2)>1 |
| Les 3 meilleures ventes | =$B2>=LARGE($B$2:$B$100,3) |
| Une ligne sur deux | =MOD(ROW(),2)=0 |
| Champ obligatoire vide | =$D2="" |
*(Rappel : dans Excel en français, TODAY s'appelle AUJOURDHUI, COUNTIF correspond à NB.SI, et les arguments sont séparés par un point-virgule).*
Testez d'abord votre formule
Saisissez votre formule dans une cellule vide à côté de la ligne 2, puis étirez-la vers le bas (recopier vers le bas). Si elle affiche la valeur VRAI (TRUE) sur les lignes voulues, votre formule fonctionnera parfaitement dans votre règle de mise en forme conditionnelle. C'est une excellente technique lors d'un test Excel d'embauche.
Gérer et corriger les règles existantes
Dans Accueil > Mise en forme conditionnelle > Gérer les règles, vous pouvez visualiser l'ensemble de vos règles, leurs formules et leurs plages d'application. Lorsque le tableur ne surligne pas les bonnes cellules, cela provient presque systématiquement d'un mauvais placement du signe $ ou d'une formule qui commence sur une ligne différente de la plage sélectionnée.
Questions fréquentes
Comment mettre en surbrillance une ligne entière selon la valeur d'une cellule ?
Sélectionnez votre tableau, créez une règle avec une formule du type =$F2="Late", en plaçant le symbole $ uniquement devant la lettre de la colonne.
Pourquoi ma mise en forme conditionnelle ne s'applique-t-elle pas aux bonnes cellules ?
Les références de votre formule ne correspondent probablement pas à la première cellule en haut à gauche de la plage sélectionnée, ou les symboles $ sont mal positionnés.
Comment mettre en valeur des dates antérieures à aujourd'hui ?
Utilisez la formule =$C2<TODAY() (ou =AUJOURDHUI() dans la version française) dans votre règle.
Google Sheets prend-il en charge la mise en forme conditionnelle par formule ?
Oui : allez dans Format > Mise en forme conditionnelle, puis choisissez l'option « La formule personnalisée est ».