Pour colorer des cellules d'une feuille de calcul selon leur valeur, adoptez la mise en forme conditionnelle d'Excel ! Quel que soit le contenu du tableau, vous pouvez créer des règles personnalisées, adaptées à vos besoins

La mise en forme conditionnelle d'Excel est très pratique pour attirer l'attention sur des valeurs critiques ou dignes d'intérêt, mais aussi pour pointer des tendances ou pour ajouter des repères graphiques et colorés à des tableaux de données un peu rébarbatifs. Le tableur de Microsoft vous propose de nombreuses conditions prédéfinies et des mises en forme prêtes à l'emploi (couleurs dégradées, barres de données, jeux d'icônes, etc.), mais tout est personnalisable. D'ailleurs, si les conditions prévues ne vous conviennent pas, vous pouvez créer une formule pour définir vos propres critères de mise en forme conditionnelle.

Excel appelle ces conditions des règles. Que la cellule ou le tableau contienne des nombres, du texte, des dates ou des heures, si la condition définie par une règle est vérifiée – par exemple, si la valeur de la cellule est-elle inférieure à 10 –, Excel applique la mise en forme que vous avez indiquée. Il peut s'agit d'une couleur précise ou même d'une icône fléchée pour indiquer une hausse ou une baisse. Si la condition n'est pas vérifiée, Excel passe à la règle suivante, s'il y en a une. Et, bien entendu, si une valeur de votre tableau change, Excel vérifie à la volée si la règle de mise en forme conditionnelle s'applique ou non à la nouvelle valeur.

Certaines règles s'appliquent indifféremment à une seule cellule ou à plusieurs, chaque cellule sera de toute façon testée indépendamment du contenu des autres : par exemple "passer la cellule en rouge si sa valeur est inférieure à 10".

D'autres règles impliquent au contraire que vous les appliquiez à plusieurs cellules pour avoir une chance de voir la condition remplie : si vous demandez de "passer en rouge les valeurs en double", il faut évidemment fournir à Excel plusieurs valeurs, donc plusieurs cellules, pour qu'il vérifie s'il existe des valeurs en double dans cette liste.

• Microsoft fournit une aide en ligne assez complète et en français pour maîtriser la mise en forme conditionnelle d'Excel. Vous pourrez également y télécharger un classeur comportant plusieurs exemples commentés (mais cette fois en anglais).
• Et si vous peinez à définir vos règles et conditions de mise en forme, faites appel à la communauté sur notre forum bureautique consacré à Excel.

Voici quelques conseils pour créer vos premières règles de mise en forme conditionnelle. Toutes les options présentées dans cette fiche pratique sont disponibles dans Excel pour Windows et Excel pour Mac. La version gratuite d'Excel pour le Web offre des options bien plus limitées.

Appliquer une mise en forme conditionnelle dans Excel

Les règles prédéfinies sont parfaites pour découvrir les multiples facettes de la mise en forme conditionnelle. Il reste possible de modifier chaque règle pour personnaliser la condition et/ou la mise en forme.

La présentation des menus et fenêtres de mise en forme conditionnelle diffère un peu entre Excel pour Windows et Excel pour Mac, mais les comportements sont identiques.

• Sélectionnez d'abord la ou les cellules auxquelles doit s'appliquer la mise en forme conditionnelle, puis, dans l'onglet Accueil, groupe Styles, cliquez sur Mise en forme conditionnelle.

• Sur le tableau de notes ci-dessous, pour appliquer par exemple un fond vert aux notes supérieures à 15, sélectionnez Règles de mise en surbrillance des cellules > Supérieur à…

• Tapez la valeur souhaitée, par exemple 15, ou sélectionnez une cellule de la feuille de calcul contenant la valeur à tester (par exemple =$M$20 si cette cellule M20 contient la valeur 15), puis choisissez dans la liste déroulante une mise en forme, par exemple Remplissage vert avec texte vert foncé. Notez que la dernière option de cette liste est Format personnalisé, vous pouvez donc préciser les couleurs, la police, les bordures, etc. Pressez le bouton OK pour créer la règle.

• Pour cette même plage de cellules sélectionnées, vous pourriez par exemple aussi appliquer une deuxième mise en forme conditionnelle, comme un fond rouge pour les notes inférieures à 6, en choisissant cette fois : onglet Accueil > groupe Styles > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Inférieur à…

• La mise en forme conditionnelle est prioritaire sur une éventuelle mise en forme manuelle déjà appliquée à la cellule. Vous retrouverez la mise en forme manuelle si vous supprimez la règle conditionnelle.
• Deux autres remarques importantes. D'abord, comme partout dans les fenêtres d'options d'Excel, dans une zone de saisie (ci-dessous, les zones où sont inscrits 2019 et 2021), les touches fléchées gauche/droite du clavier n'ont pas un comportement habituel, elles servent à naviguer dans la feuille pour sélectionner une cellule, ce qui est irritant si l'on voulait juste se déplacer dans la zone de saisie pour modifier un caractère. Pressez une fois la touche F2 pour passer en mode édition classique et "récupérer" les touches fléchées (essayez fn+F2 sur certains Mac si la touche F2 ne fonctionne pas). Rappuyez sur F2 pour revenir au comportement initial.

• Deuxième remarque valable pour toutes les options de mise en forme conditionnelle ou presque : ces zones de saisie n'acceptent pas seulement une valeur numérique. Souvent, vous pouvez aussi y inscrire une date comme 01/07/2021, ou une heure comme 08:00:00, ou une lettre ou un texte, ou encore la référence d'une cellule (=$B$20) contenant une valeur à comparer dans la règle de mise en forme conditionnelle.
• Voyons maintenant rapidement les différentes Règles de mise en surbrillance des cellules proposées dans le menu.

• Une valeur supérieure ou inférieure : le contenu peut être, on l'a vu, un nombre, mais aussi une date, une heure, un texte ou une référence. Sur PC, pour préciser inférieur ou égal ou supérieur ou égal, vous devez créer la règle puis la modifier. Sur Mac, ce choix est directement possible dans la fenêtre de création de la règle grâce à la liste déroulante.

• Une valeur comprise entre deux bornes : il peut s'agir de deux valeurs numériques ou dates ou heures ou références de cellules du type =B20 ou =$B$20. Sur Mac, vous pouvez aussi directement choisir Non comprise entre ; sur PC, vous devez vous rendre dans les options de modification de la règle.

• L'option Entre deux bornes accepte aussi du texte : ci-dessous, les bornes inf et sup sont la lettre c et le texte fz. On veut ainsi appliquer un fond rouge à toutes les cellules dont le contenu commence par une lettre de c à f (c et Charles répondent à la condition, mais pas Lechat qui contient un c mais ne débute pas par c), en majuscule ou en minuscule (Charles et Dupond sont sélectionnés). Mais pourquoi fz ? Si notre borne supérieure était juste la lettre f, le mot forêt ne serait pas retenu. Nous indiquons donc pour borne supérieure tout "mot" commençant par f et dont la seconde lettre, si elle existe, est inférieure ou égale à z. Ce qui (sauf terme vraiment exotique ou onomatopée du genre fzzz) devrait inclure tous les mots de votre liste commençant par f !

• Un contenu égal à : indiquez une valeur numérique, du texte, une date, une heure, la référence à une cellule contenant la valeur à comparer. Sur Mac, vous pourrez choisir Différent de dans la liste déroulante ; sur PC, vous devrez modifier la règle.

• Un texte qui contient : tapez le texte ou indiquez la référence d'une cellule qui le contient. Si vous indiquez jean, les cellules contenant le texte Jean ou le texte Valjean ou Dejeant ou Jeannot rempliront la condition.

• En plus des lettres de l'alphabet, vous pouvez bien sûr aussi inscrire d'autres caractères, chiffres, signes de ponctuation, symboles, etc.

• Une date se produisant : Excel propose des dates telles que hier, aujourd'hui, demain, la semaine prochaine, le mois dernier, etc. Si vous préférez indiquer une date précise du type 01/01/2021, choisissez plutôt l'onglet Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Égal à et tapez la date voulue. Vous pouvez aussi créer une règle avec formule et utiliser par exemple la fonction AUJOURDHUI() ou toute autre fonction relative aux dates.

• Des valeurs en double : vous avez le choix, en fait, entre des données en double ou au contraire des valeurs uniques.

• Passons aux règles accessibles via l'onglet Accueil > groupe Styles > Mise en forme conditionnelle > Règles des valeurs de plage haute/basse. Elles donnent accès à d'autres conditions assez explicites et impliquent que vous appliquiez ces règles à plusieurs cellules : valeurs ou pourcentages les plus élevés ou les moins élevés, valeurs supérieures ou inférieures à la moyenne de la plage de cellule.

• Bien que le menu indique 10 ou 10%, quand vous choisissez l'une de ces règles, Excel vous permet de préciser une autre borne dans la fenêtre qui suit. Ci-dessous, nous choisissons de ne mettre en valeur que les 5 meilleures performances des athlètes (les temps les moins élevés).

• Poursuivons avec l'onglet Accueil > groupe Styles > Mise en forme conditionnelle > Barres de données, des options très intéressantes pour afficher une barre colorée représentant la valeur d'une cellule. Sélectionnez plusieurs cellules avant d'appliquer une telle règle. La barre colorée sera d'autant plus longue que la valeur de la cellule est élevée. Par défaut, une barre pleine correspond à la valeur max dans votre liste, mais cela se change aussi.

• Si vous avez par exemple un tableau de notes variant de 0 à 19, les valeurs 19 auront une barre pleine. Modifiez la règle pour fixer le Nombre maximum à 20 (la barre de la valeur 19 rétrécira donc un peu). Vous pouvez aussi cocher la case Afficher la barre uniquement, on voit le résultat ci-dessous pour la règle appliquée à la colonne Moyenne. : les valeurs numériques ont disparu.

• Passons aux options de l'onglet Accueil > groupe Styles > Mise en forme conditionnelle > Nuances de couleurs, qui appliquent une couleur différente aux cellules en se servant d'une palette de deux ou trois couleurs prédéfinies, mais entièrement personnalisables.

• Les mises en forme suggérées par Excel dans le menu vous permettent de choisir, par exemple, si les valeurs les plus élevées doivent être colorées en vert (ce qui convient à un chiffre d'affaires ou aux notes d'un élève) ou au contraire si les valeurs les plus élevées doivent figurer en rouge (le temps mis pour boucler un marathon, par exemple).

• En modifiant la règle, selon la manière dont vous jouez sur les valeurs de rupture et les deux ou trois couleurs adoptées, vous donnerez une coloration plutôt positive à vos données…

• … ou, au contraire, une vision plus dramatique des mêmes données.

• Terminons ce tour d'horizon avec les règles accessibles via l'onglet Accueil > groupe Styles > Mise en forme conditionnelle > Jeux d'icônes.

• Excel s'appuie sur les valeurs de la liste que vous lui fournissez pour définir quelle icône il attribuera à chaque cellule. Mais vous pourrez bien sûr adapter la condition et les icônes en modifiant la règle.

• Le jeu d'icônes Directionnel (les flèches) est décevant si vous souhaitez dégager une tendance entre deux colonnes – car la mise en forme conditionnelle ne compare pas deux jeux de données. Par exemple, si vous voulez savoir si les ventes ont progressé entre janvier et février, les icônes fléchées choisies par Excel seront aberrantes. Dans ce cas, créez d'abord une colonne Tendance pour soustraire janvier à février, c'est-à-dire =C2-B2 pour le Produit A sur l'exemple ci-dessous. Dupliquez cette formule pour les autres produits et sélectionnez ces valeurs calculées (plage D2:D6). Appliquez un jeu d'icônes Directionnel, par exemple 3 Flèches (en couleur).

• Le résultat ne nous convient toujours pas ! Ci-dessus, la baisse du Produit B est présentée par une flèche stable jaune, on préférerait une flèche en baisse et rouge. On modifie donc la règle via l'option du menu Gérer les règles.
• L'illustration ci-dessous vous montre nos modifications de la règle. Pour la flèche haussière verte, Si la valeur est >= 1 avec un Type = Nombre. Pour la flèche stable jaune, Si la valeur est < 1 et >= 0 avec un Type = Nombre. Pour la flèche baissière rouge, Si la valeur est < 0. En prime, on coche la case Afficher l'icône uniquement : les tendances calculées sont donc masquées pour ne garder que l'icône.

• Notez que dans la version gratuite d'Excel pour le Web, il est possible de créer des règles de mise en forme conditionnelle, mais sans possibilité : de modifier les règles, ni d'appliquer un format conditionnel coloré différent de ceux prédéfinis, ni de créer une formule.

Modifier une règle de mise en forme conditionnelle

Voici quelques conseils pour modifier les règles et gérer les éventuels conflits entre deux règles qui s'appliquent aux mêmes cellules. Dans Excel pour le Web, l'option Gérer les règles permet juste de les consulter et de les supprimer.

• Pour voir ou modifier les règles déjà appliquées à la feuille de calcul actuelle, sélectionnez éventuellement la plage de cellules concernées et cliquez sur onglet Accueil > groupe Styles > Mise en forme conditionnelle > Gérer les règles.

• Si vous n'avez pas au préalable sélectionné les cellules concernées par la règle, dans la liste déroulante Afficher les règles de mise en forme pour, indiquez par exemple que vous souhaitez modifier les règles de Cette feuille de calcul. La liste des règles en dessous s'adapte à ce choix.

• Sélectionnez la règle qui vous intéresse et pressez le bouton Modifier la règle.

• Vérifiez le type de contenu concerné : Valeur, Texte, Date, Cellule vide...

• Selon le contenu sélectionné, la liste déroulante suivante s'adapte, de même que la zone de saisie tout à droite. Par exemple pour une Valeur de la cellule > Inférieure à, tapez la valeur qui convient : si vous tapez 6, Excel transformera cette valeur en formule =6 quand vous presserez OK. Au lieu de taper une valeur, vous pouvez indiquer ou sélectionner une cellule de votre feuille de calcul, par exemple =$B$20.

• Excel transforme en une formule toutes les valeurs (nombre, texte, date, heure…) que vous tapez dans une règle. Si vous modifiez une valeur ou une formule d'une règle, n'oubliez pas de commencer par le signe égal.
• Les options de la fenêtre varient selon le type de règle et de mise en forme que vous avez défini. Pour l'évaluation d'une règle, Excel choisit parfois pour vous des pourcentages, parfois des centiles. Un centile est une valeur comprise entre 0 et 100. L'aide d'Excel explique que le centile "permet de visualiser un groupe de valeurs élevées (telles que le 20e centile supérieur) dans une proportion de nuance de couleur, et les valeurs basses (telles que le 20e centile inférieur) dans une autre proportion de nuance de couleur, car ils représentent les valeurs extrêmes qui peuvent biaiser la visualisation de vos données". C'est clair ! Enfin si ça ne l'est pas tant que ça, laissez tomber les pourcentages et les centiles et essayez avec un Type Nombre !

• Si vous modifiez les valeurs, la valeur du champ Minimum doit évidemment être inférieure à celles champs Milieu et Maximum.

• Si la gestion des dates et des horaires d'Excel ne vous est pas familière, vous serez surpris(e) de voir que quand vous éditez une règle, les dates et heures sont converties en nombres qui vous paraîtront "étranges". Par exemple le 01/01/2021 correspond en fait à la valeur 44197. Et une durée de 05:30:00 est égale à 0,229166666666667. C'est ainsi qu'Excel les stocke pour pouvoir faire facilement des calculs dessus (tri, nombre de jours entre deux dates…), puis il leur applique un format d'affichage plus compréhensible pour un être humain.

Si vous souhaitez modifier une telle date ou heure dans une règle, essayez de taper la nouvelle valeur à la place de la formule actuelle, tapez par exemple 01/02/2021. Si la règle ne fonctionne pas, le plus simple est de sortir momentanément de la règle, de taper la nouvelle valeur dans une cellule quelconque de votre feuille, par exemple 01/02/2021 en E2. Vous pourrez faire référence à cette cellule dans votre règle, =$E$2.

• Autre solution : via l'onglet Accueil, appliquez à cette cellule E2 un format Standard (au lieu de Date) pour découvrir le véritable nombre sous-jacent, puis copiez cette valeur dans le presse-papiers (Ctrl+C ou Cmd+C sur Mac) pour la coller (Ctrl+V ou Cmd+V sur Mac) dans la définition de la règle.

• Dans les options des règles d'Excel Windows/Mac, le bouton Format vous aidera à changer la mise en forme de la cellule lorsque la condition est vérifiée.

• Si deux règles ou plus s'appliquent à une même plage de cellules, la règle la plus haute dans la liste est prioritaire pour appliquer une mise en forme. Mais…
• Si deux règles se vérifient et n'entrent pas en conflit (par exemple si la première règle passe la cellule en rouge et la deuxième règle passe le texte en gras), alors la cellule s'écrira en gras et sur fond rouge.
• Si les deux règles qui se vérifient sont en conflit (par exemple, si l'une demande un fond rouge et l'autre un fond vert), c'est la règle qui figure en premier (en haut) dans la liste qui l'emporte. Ci-dessous donc, il y a conflit : sous la première règle (qui applique un fond rouge si la valeur est supérieure à zéro), les règles suivantes n'ont aucune chance d'être appliquées, puisque la première règle est toujours vraie avec nos données, toutes supérieures à zéro.

• En modifiant juste l'ordre d'évaluation des règles grâce aux boutons Monter et Descendre, les données adoptent la bonne mise en forme.

• La case Interrompre si Vrai n'est utile que si vous devez ouvrir votre classeur avec une version antérieure à Excel 2007, qui ne prend pas en charge plus de trois règles de mise en forme conditionnelle ni plusieurs règles appliquées à la même plage.

Créer une règle de mise en forme conditionnelle avec une formule

La plupart du temps, il est plus simple de piocher une règle prédéfinie qu'Excel propose dans ses menus, quitte à la modifier ensuite. Si aucune règle ne vous convient, voici quelques conseils pour créer une règle à partir d'une formule de votre cru. Si la formule n'est pas valide, la mise en forme sera ignorée par Excel pour Windows/Mac. L'option n'est pas permise dans Excel pour le Web.

Une formule est, par exemple, nécessaire pour évaluer les données dans des cellules qui ne se trouvent pas dans la plage sélectionnée, mais dans une autre colonne ou même une autre feuille de ce classeur.

• Votre formule doit renvoyer la valeur Vrai (autrement dit la valeur 1) ou Faux (0). La mise en forme ne sera appliquée que si le résultat est Vrai.
• Commencez par sélectionner les cellules auxquelles la mise en forme conditionnelle doit s'appliquer, ci-dessous la plage C3:C13. La première cellule sélectionnée étant C3, cette cellule C3 est la cellule active. C'est important car nous allons écrire la formule pour cette première cellule C3, elle s'adaptera automatiquement aux autres lignes.
  Cliquez sur l'onglet Accueil et, dans le groupe Styles, sur Mise en forme conditionnelle > Nouvelle règle. Vous pouvez aussi choisir Gérer les règles dans le menu, puis presser le bouton Nouvelle règle.

• Dans la fenêtre Nouvelle règle de mise en forme, cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

• Pour passer en vert les équipements (colonne C) dont le prix (colonne D) est par exemple supérieur ou égal à 800 euros, écrivez la formule qui s'applique à la cellule active C3 :
  =D3>=800

• Pressez ensuite le bouton Format pour choisir la couleur verte, puis le bouton OK. Les équipements de 800 euros et plus passent en vert. Si vous changez le prix d'un équipement de la liste, sa cellule changera de couleur.

• Compliquons un peu la formule en utilisant la fonction ET() d'Excel. Nous supprimons ici la première règle pour travailler sur les mêmes données. On resélectionne la même plage C3:C13, avec C3 pour cellule active et l'on crée une Nouvelle règle > Utiliser une formule...
• Pour passer en bleu un équipement à condition que ce soit un Smartphone (colonne C), et à condition que son prix soit >=700 (colonne D), on écrit la formule :
  =ET(C3="Smartphone";D3>=700)
• Voici le résultat :

• Vous pouvez notamment utiliser dans vos formules les fonctions ET, OU, SI d'Excel.

• Supprimons encore une fois cette règle pour travailler sur le même tableau de données. Cette fois, nous voulons appliquer à chaque ligne du tableau une couleur en fonction de la marque. Attention, on sélectionne ici la plage B3:C13, avec B3 pour cellule active et l'on va créer trois nouvelles règles.

• Voici les trois règles créées et nos trois formules.
  Pour Samsung, la formule est =($B3="Samsung")
  Pour Sony, =($B3="Sony")
  Pour LG, =($B3="LG")

• La notion de référence relative, absolue ou mixte sort du cadre de cette fiche pratique. Disons qu'Excel teste chaque cellule de la plage B3:C13. Grâce à la référence mixte $B3, la formule fige la colonne B grâce au signe $, et laisse varier le numéro de ligne (3, 4, 5…). Donc pour n'importe quelle cellule du tableau, Excel teste si la cellule sur la même ligne en colonne B (B3, B4, B5…) est égale à la marque indiquée dans la formule (Samsung, Sony ou LG). Si c'est le cas, il applique la couleur que nous avons choisie pour la marque.

Copier et coller une mise en forme conditionnelle

Vous avez plusieurs options pour changer la plage de cellules à laquelle une règle s'applique, ou pour copier une mise en forme conditionnelle sur d'autres cellules.

• Pour changer la plage de cellules à laquelle s'applique une mise en forme conditionnelle, affichez les règles de la feuille de calcul : onglet Accueil > groupe Styles > Mise en forme conditionnelle > Gérer les règles > Afficher les règles pour Cette feuille de calcul.
• Pour la règle qui vous intéresse, cliquez dans la zone S'applique à et sélectionnez dans la feuille une nouvelle plage de cellules. Vous pouvez aussi presser la touche Ctrl sur PC (ou la touche Cmd sur Mac) pour sélectionner deux ou plusieurs plages de cellules discontinues.

• Pour appliquer une mise en forme conditionnelle à d'autres cellules, vous pouvez aussi copier-coller une cellule ou plage de cellules en pressant Ctrl+C puis Ctrl+V sur PC, ou Cmd+C puis Cmd+V sur Mac.
• Autre méthode : cliquez sur une cellule sur laquelle la règle est déjà appliquée, puis cliquez sur l'onglet Accueil > Reproduire la mise en forme (icône Pinceau), puis appliquez le pinceau sur les nouvelles cellules devant accueillir la mise en forme conditionnelle.

• Quelle que soit la méthode de recopie du format conditionnel, il est parfois nécessaire d'adapter la règle pour qu'elle continue de fonctionner.

Rechercher des cellules avec une mise en forme conditionnelle

Voici comment rechercher toutes les mises en forme conditionnelles de la feuille de calcul ou uniquement les cellules ayant la même mise en forme conditionnelle.

• Pour trouver toutes les cellules, dans l'onglet Accueil, dans le groupe Édition, cliquez sur le bouton Rechercher et sélectionner.

• Cliquez dans le menu sur Mise en forme conditionnelle : toutes les cellules de la feuille de calcul sont sélectionnées.

• Pour trouver uniquement les cellules avec la même mise en forme conditionnelle, cliquez sur l'une de ces cellules. Dans l'onglet Accueil > groupe Édition > bouton Rechercher et sélectionner, cliquez sur Sélectionner les cellules. Cochez Formats conditionnels et, en dessous, Identiques. Pressez OK.

Supprimer une mise en forme conditionnelle

Bien entendu, une mise en forme conditionnelle n'est pas définitive. Et vous pouvez en effacer quand vous le souhaitez.

• Si votre feuille de calcul comporte plusieurs règles de mise en forme conditionnelle et que vous souhaitez n'en supprimer qu'une seule, sélectionnez d'abord la plage de cellules contenant la règle que vous souhaitez supprimer.
• Cliquez sur l'onglet Accueil > groupe Styles > Mise en forme conditionnelle > Effacer les règles.

• Selon vos besoins, choisissez Effacer les règles des cellules sélectionnées ou Effacer les règles de la feuille entière. Les deux dernières options ne sont actives que si vous avez appliqué des règles à un "tableau" Excel ou à un tableau croisé dynamique.
• Dans Excel pour Windows, vous pouvez aussi Supprimer la règle dans le Gestionnaire des règles de mise en forme conditionnelle.

• Cette option Supprimer la règle n'est pas (pour l'instant) proposée par le Gestionnaire des règles de mise en forme conditionnelle d'Excel pour Mac. Mais vous pouvez effacer les règles comme on l'a vu en passant par le menu.