Microsoft a introduit dans son tableur Excel une fonctionnalité de mise en forme conditionnelle (MFC) à partir de sa version 2007.
Elle permet de changer le style d’une (plage) de cellule(s) selon sa(leur) valeur(s).
Les versions perpétuelles à compter de 2019 et abonnement 365 on introduit des règles avancées.
Accès
La mise en forme conditionnelle est disponible via un bouton dans l’onglet Accueil du ruban d’Excel.
En cliquant dessus, nous avons différentes options :
- plusieurs propositions de règles de mise en forme conditionnelles
- gestion des règles (création, modification, suppression)
Il n’existe aucun raccourci clavier par défaut.
Possibilités
Excel vous propose par défaut des mises en forme en fonction de vos attentes :
- Règles de mise en surbrillance des cellules
- Règles des valeurs de plage haute/basse
- Barre de données
- Nuance de couleurs
- Jeux d’icône
Dans ce cas, Excel va :
- soit appliquer directement la mise en forme conditionnelle dans le style proposé
- ouvrir une mini-fenêtre avec uniquement les valeurs à saisir pour notre condition et une liste de styles préformatés.
Vous pouvez choisir Format personnalisé pour ouvrir la fenêtre de Format de cellule pour personnaliser le style.
Pour chacune des dispositions, vous avez un lien Autres règles pour avoir accès à d’autres propositions. Cela vous ouvre une fenêtre de création d’une règle comme si vous aviez cliqué sur Nouvelle règle.
L’avantage de choisir Autres règles est d’ouvrir la fenêtre de nouvelle règle avec le type souhaité dans la partie supérieure.
Règles de mise en surbrillance des cellules
Ce type de règles va vérifier la valeur de la cellule et mettre en forme si elle respecte la règle choisie.
Dans ce cas, une mise en forme va s’appliquer. Si la valeur ne remplit pas la condition, la mise en forme reste celle par défaut.
Voici la liste complète :
| Accès dans l'accueil | Autres règles |
|---|---|
|
|
Testons en quelques-unes.
Exemple 1 : supérieur à
Dans une entreprise, je souhaite identifier visuellement les salariés qui ont plus de 10 ans d’ancienneté.
- Pour cela, je vais dans Accueil > Mise en forme conditionnelle >Règles de mise en surbrillance des cellules>Supérieur à
Une mini-fenêtre apparaît.
2 Je saisis ma valeur cherchée, soit 10, dans le champ de gauche.
3. Je choisis un style prédéfini puis je clique sur OK
Excel remplit de vert les cellules qui contiennent des valeurs strictement supérieure à 10.
C’est pour cela que la ligne 8 n’est pas prise en compte.
Vous pouvez remarquer que la couleur passe au premier plan par rapport au style du tableau (tableau structuré ou style réalisé manuellement).
L’avantage de l’utilisation de la mise en forme conditionnelle est que si la valeur d’une cellule change, la mise en forme aussi. Excel revérifie automatiquement après chaque modification des cellules.
Exemple 2 : texte spécifique ne contenant pas
Je veux mettre en forme les adresses mail qui ne contiennent pas le terme outlook.
Oui, y’a plus rapide avec d’autres fonctionnalités d’Excel comme le filtre, mais c’est juste à titre d’exemple pour la mise en forme conditionnelle.
La condition n’est pas proposée dans l’accueil du ruban.
- Sélectionnez la plage de cellules
- Il va falloir passer par Autres règles ou Nouvelle règle.
- Dans la nouvelle fenêtre, allez dans Appliquer une mise en forme uniquement aux cellules qui contiennent i elle n’est pas sélectionnée dans la partie supérieure
Dans la partie inférieure,
4. Sélectionner Texte spécifique dan la première liste déroulante puis ne contenant pas dans la seconde.
5. Saisir outlook dans le champ texte
6. Cliquez sur Format pour personnaliser le style affiché si la condition est remplie.
7. Une fois paramétré, cliquez sur OK
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
Règles des valeurs de plage haute/basse
Ces règles doivent être sélectionnées pour une plage de plusieurs cellules uniquement. Seules les cellules répondant à la condition choisie seront mises en forme.
Liste complète :
| Accès dans l'accueil | Autres règles |
|---|---|
|
|
Exemple 1 : les 10 valeurs les plus élevées
Pour les exemples à venir, nous allons nous servir d’un relevé météo.
Dans ce tableau, je souhaite révéler les 5 jours les plus chauds (température max).
- Je sélectionne les cellules
- Je vais dans Accueil > Mise en forme conditionnelle > Règles des valeurs de plage haute/basse > 10 valeurs les plus élevées
- Dans le premier champ, je modifie la valeur à 5
- Je sélectionne le style à donner
- Je valide cette règle en cliquent sur OK.
Exemple 2 : les valeurs égales et au-dessus de la moyenne
Cette fois-ci, je souhaite mettre en avant les jours qui ont une température minimale inférieur à la moyenne mensuelle.
- Je choisis ma plage de données
- Non disponible sur l’accueil, je sélectionne Autres règles ou Nouvelle règle
- Dans la partie supérieure de la fenêtre Nouvelle règle, je sélectionne Appliquer une mise en forme uniquement aux valeurs au-dessus ou en dessous de la moyenne
- Dans la partie inférieure, je sélectionne égales en en dessous dans la liste déroulante
- Je clique sur Format pour définir mon style. J’opte personnellement pour du fond bleu puis clique sur OK
- Je valide par simple clic sur OK
Barre de données
Excel va vérifier l’ensemble des valeurs de la plage sélectionnée puis afficher en arrière plan de cellule une barre de progression. La longueur va dépendre de la valeur de la cellule.
Pour être pertinente, cette règle est utile avec une plage de cellules.
Il y a deux styles :
- Dégradé : couleur avec un dégradé de transparence en fin de barre.
- Remplissage uni : couleur unie dans toute la barre
Dans la fenêtre Autres règles, vous pouvez :
- Personnaliser la valeur minimum et maximum
- Changer le type de valeur : Nombre, pourcentage, formule, centile ou automatique
- Choisir la couleur de la barre
- Ajouter une bordure et fixer sa couleur
- Choisir le sens de la barre : contexte (automatique), gauche->droite, droite->gauche
- Options pour les valeurs négatives.
Exemple : barre pour les précipitations
Une image parle plus que mille mots. Alors on va utiliser une barre de données pour représenter graphiquement les précipitations.
- Sélectionner les cellules correspondant aux données de précipitations.
- Je crée une nouvelle règle : je choisis volontairement de vous montrer l’ensemble des réglages plutôt que les modèles proposés.
- Dans la fenêtre de nouvelle règles, je devrais me trouver par défaut sur Mettre en forme toutes les cellules d’après leur valeur dans la partie supérieure, sinon cliquez dessus
- Pour le style, je choisis Barre de données dans la liste déroulante. Je peux même afficher uniquement cette barre en cliquant sur la case à cocher
- Pour Type et Valeur, je laisse tel quel car le va se baser sur les cellules sélectionnées pour définir le minimum et maximum
Pour d’autres situations, vous pouvez choisir ses informations pour répondre aux besoins. - Concernant l’apparence, j’ai tendance à choisir Dégradé en remplissage.
Pour notre exemple, je reste sur un bleu foncé - Il est possible de faire apparaître une bordure, pour notre cas, je laisse sans bordure
- Pour l’orientation, je le laisse sur contexte (Excel choisira selon les données)
Il y a un bouton Valeur négative et axe pour des options supplémentaires (pas pour notre exemple) et un aperçu de la barre - Pour valider cette règle, je clique sur OK
Le bouton Ordre inversé permet d’inverser l’ordre des icônes dans cette règle.
Jeux d'icônes
Elle ajoute une icône dans la partie gauche de la cellule (par défaut). Elle sera différente selon la valeur de la cellule par rapport aux autres valeurs de la plage de cellules. Ce type de disposition est idéale pour une plage de données.
Ce type de règle permet avant tout de séparer en plusieurs groupes les données et séparées chacune par des valeurs pivot.
Il existe différents jeux d’icônes :
- directionnel
- formes
- indicateurs
- contrôle d’accès
Dans la fenêtre Autres règles, vous pouvez paramétrer :
- le jeu d’icônes
- afficher uniquement l’icône, la valeur de cellule est masquée
- les règles (valeurs et types)
Exemple
Cette fois-ci, on va travailler sur l’ensoleillement.
Pour les besoins de cet article, je ne vais pas choisir un des jeux proposés mais les Autres règles pour avoir accès à l’ensemble des réglages.
- Sélectionnez la plage de cellules.
- Créez une nouvelle règle ou allez dans Autres règles de Jeux d’icônes.
- Pour le style de mise en forme, je choisis Jeux d’icônes dans la liste déroulante.
- Sélectionnez un style d’icônes à votre convenance. J’en prends un à 3 icônes pour notre exemple.
Vous pouvez afficher seulement les icônes dans leurs cellules en cliquant sur la case à cocher - Pour l’Icône, vous avez la possibilité de la changer (et même prendre une d’un autre jeu).
- Je laisse les opérateurs (>=), Valeur (67 et 33) et Type (Pourcentage) tels quels.
- Pour valider cette règle, je clique sur OK
Voici un extrait du tableau avec les mises en forme conditionnelle appliquées précédement :
- 2 des 5 jours les plus chauds sont en début de mois
- la moyenne étant de 3.64, toutes les valeurs égales ou inférieures à celle-ci sont bleutées.
- la longueur des barres de données est proportionnelle à la valeur de cellule par rapport aux valeurs minimale et maximale de la plage de cellules.
- seule E15 a un drapeau jaune, E17 un drapeau vert et le reste en vert.
J’expliquerai la dernière observation dans une partie spécifique de cet article.
Nuance de couleurs
Excel va collecte l’ensemble des valeurs de la plage sélectionnée puis afficher une couleur en arrière plan d’une cellule suivant sa valeur. Cette règle est pertinente pour une plage de cellules.
Pour les dispositions pré-programmées, les cellules vont être colorisées suivant sa valeur par rapports aux valeurs minimale et maximale de la plage de cellules.
Il existe une nuance à 2 ou trois couleurs..
Exemple
Reprenons notre relevé météo. On va réutiliser les températures Max.
Je vais aller créer une nouvelle règle pour vous montrer l’ensemble des réglages. Sachez que les modèles proposés colorent les cellules par défaut :
- 2 couleurs -> à partir des valeurs minimale et maximale de la plage
- 3 couleurs -> à partir des valeurs minimale et maximale de la plage et du 50e centile (médiane).
- Je crée une nouvelle règle
- Je sélectionne le style Échelle à trois couleurs
- Je ne touche pas au reste pour cet exemple
- Je clique sur OK
Comme vous pouvez le voir les couleurs de remplissage des cellules se sont adaptés par rapport au dégradé de 3 couleurs.
Toutefois, comme pour le jeu d’icônes, certains couleurs peuvent paraître étrange par rapport à leur valeur.
Type de valeurs : Nombre, Pourcentage, Centile ?
Dans certaines situations la mise en forme rencontre des incohérences par rapport à ce qu’on pensait voir affiché. Cela provient du type de valeur sélectionné dans la règle de mise en forme conditionnelle.
Voyons pour les 3 types principaux.
Nombre
Il n’y aura jamais de problème d’affichage si on utilise le type Nombre.
La raison est simple : les valeurs de cellules vont se baser sur des nombres déterminés dans la règle de mise en forme même si elle ne figue pas dans notre plage de cellules.
Exemple :
Restons sur notre relevé météo dénué de mise en forme conditionnelle. Je vais déterminer pour la température max 3 groupes.
- Je crée une nouvelle règle après avoir sélectionné mes cellules
- Je choisis une Échelle à trois couleurs
- Je saisis comme suit :
| Réglage | Minimum | Milieu | Maximum |
|---|---|---|---|
Type |
Nombre |
Nombre |
Nombre |
Valeur |
4 |
9 |
15 |
Couleur |
bleu |
blanc |
rouge |
Quoiqu’il arrive, les valeurs :
- <=4 : couleur bleue choisie dans la règle
- de 4 à 9 : dégradé de bleu à blanc
- =9 : couleur blanche
- de 9 à 15 : dégradé de blanc à rouge
- =>15 : couleur rouge choisie dans la règle
Type Pourcentage
C’est là que commence les problèmes !
Excel appelle cela pourcentage mais ce n’est pas le cas réellement. Ce n’est :
- ni un pourcentage le la valeur maximale
- ni un pourcentage de la somme des valeurs de la plage ou de sa moyenne
Excel utilise une interpolation linéaire entre Min et Max. Ce sera plus parlant avec un exemple. Je ne vais pas utiliser le relevé météo mais un simple exemple clair.
J’ai fait une liste de 10 nombres en G3:G12 : 20, 150, 200, 250, 350, 1500, 2005, 2010, 3000 et 4000.
Passons à notre règle :
- Je créé une nouvelle règle
- Je choisis un jeu d’icônes : les trois cercles RJV.
- Je choisis >=50 Pourcentage pour le vert et >=25 Pourcentage pour le jaune.
- Je clique sur OK
Certains peuvent se poser la question pour 2005. Pourquoi est-il en jaune par exemple ?
Je vais vous expliquer comment Excel calcule.
Pour le seuil de 50%, il fait :
Valeur Minimale de la plage + 50% x (Valeur Maximale de la plage - Valeur Minimale de la plage)
Ce qui donne pour notre exemple
20+0.5*(4000-20) = 2010
Pour le seuil de 25%, on obtient :
20+0.25*(4000-20) = 1015
Donc :
- Les valeurs supérieures et égales à 2010 auront l’icône verte
- Les valeurs supérieures et égales à 1015 ainsi que que inférieure à 2010 auront l’icône jaune
- Les valeurs inférieures à 1015 auront l’icône rouge
Type Centile
Je vais d’abord expliquer le principe du centile (ou pourcentile). Il indique les valeurs en dessous de laquelle se trouve un pourcentage de données.
Par exemple, le 25e centile représente les 25% des valeurs les plus basses. Sur 100 valeurs, il s’agit des 25 valeurs les plus basses.
Pour le 50e centile, il est composé des 50% les plus petites. On l’appelle aussi la médiane.
Excel applique ce théorie mathématique mais y ajoute son grain de sel.
Exemple :
Partons avec l’exemple de la même série de 10 nombres.
- Je créé une nouvelle règle
- Je choisis un jeu d’icônes : les trois cercles RJV.
- Je choisis >=50 Centile pour le vert et >=25 Centile pour le jaune.
- Je clique sur OK
Comment Excel décide quelle icône rattacher à la cellule ?
Il se base sur l’ordre croissant des valeurs avec la formule suivante :
(nombre de valeurs - 1) x centile +1
Si le résultat est un nombre entier, nous avons notre valeur qui sert de pivot, sinon il passe à une 2e formule :
Valeur inférieure + Centile x (Valeur supérieure - Valeur inférieure)
Pour notre exemple,
Commençons avec le 50e centile :
(10-1)*0.5+1=5.5
Le résultat se trouve donc entre la 5e et 6e valeur. Comme le résultat est décimal, passons à la 2nde formule :
5e valeur + 0.5 x (6e valeur - 5e valeur) = 350+0.5*(1500-350)=925
Faisons la même chose avec le 25e centile :
(10-1)*0.25+1=3.25
La valeur pivot se trouve entre la 3e valeur et 4e valeur de notre plage.
200+0.25*(250-200)=212.5
Au final,
- les valeurs supérieures ou égales à 925 auront l’icône verte
- les valeurs supérieures ou égales à 212.15 ainsi que inférieures à 925 auront l’icône jaune
- les valeurs inférieures à 212.15 auront l’icône rouge.
Cela pose moins de problème que pour les pourcentages mais cela peut arriver.
Dans notre exemple, le 50e centile c’est la moitié donc les 50% supérieures soient (5/10). Pour le 25e centile c’est le quart hors 1/4 de 10 qui n’est pas un nombre entier. Donc, un groupe possède 3 valeurs et l’autre 2.
Gestion des règles
Nous avons pu découvrir au début de l’article des options sous les modèles proposés par Excel quand on clique sur le bouton de Mise en forme conditionnelle dans l’onglet Accueil du ruban :
- Nouvelle règle que l’on a pu utiliser tout au long de l’article
- Effacer les règles
- Gérer les règles
Effacer les règles
Lorsque vous êtes au survol de Effacer les règles, Excel affiche une nouvelle liste.
Quelque soit les cellules que vous avez au préalable, les deux premières options sont disponibles :
- cellules sélectionnées
- feuille entière
Pour la 1ère, il faut d’abord au préalable sélectionner la plage qui où s’appliquent une règle de mise en forme conditionnelle.
Pour l’option feuille entière, vous n’avez pas besoin de vous embêter à sélectionner des cellules en premier lieu.
Excel supprime toutes les règles sur la feuille active.
Enfin, vous avez 2 options possiblement en gris non cliquables :
- tableau
- tableau croisé dynamique (TCD)
Il faut que vous soyez respectivement dans une cellule d’un tableau structuré ou d’un TCD pour avoir accès à ces options. Pour information, il n’est pas nécessaire de sélectionner l’intégralité du tableau, une seule cellule suffit.
Gérer les règles
Ce lien vous donne accès à une fenêtre listant les mises en forme conditionnelle crées.
Découvrons ensemble son interface
- La liste déroulante permet de déterminer la zone. Elle est composé de:
- Sélection actuelle : cellules sélectionnées avant d’ouvrir la fenêtre de gestion des règles.
- Cette feuille de calcul : feuille active
- Ensuite, vous pouvez avoir d’autres options facultatives :
- Feuille : Nom feuille -> pour chaque feuille existante dans le classeur (fichier) Excel
- Tableau : Nom du tableau -> pour chaque tableau structuré du classeur
- Tableau croisé dynamique : Nom TCD -> pour chaque TCD du classeur
2. En dessus de la liste des règles que l’on a paramétrer, vous avez des boutons pour :
- Créer une nouvelle
- Modifier la règle (à sélectionner au préalable)
- Supprimer la règle (à sélectionner au préalable)
3. Les flèches haut et bas servent à ordonner les différentes règles. Et oui, on peut appliquer plus MFC pour une même cellule. La 1ère règle s’appliquera avant celles en dessous.
4. Pour chaque règle, vous pouvez modifier la plage de cellule à laquelle elle s’applique.
5. La case à cocher Interrompre si vrai permet d’empêcher l’application d’autres MFC après cette ligne.
6. Vous avez des boutons pour valider (OK), Appliquer les modifications apportées sur les règles, Fermer la fenêtre.
Formule
Dernière partie et si importante. Lorsque vous ne trouvez pas comment appliquer votre mise en forme conditionnelle avec les options que l’on a développé dans cet article, il reste un dernier recours : l’utilisation d’une formule de calcul.
Dans la fenêtre Nouvelle règle de mise en forme, il y a une dernière rubrique dans la partie supérieure : Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Faisons la manipulation avec notre premier tableau contenant l’ancienneté des salariés. Disons que je veux appliquer un remplissage sur toute la ligne pour les salariés qui ont entre 5 et 9 ans d’ancienneté.
- Je sélectionne toutes mes données soient la plage A2:G9
- Je crée une nouvelle règle
- Je sélectionne la dernière option de la partie supérieure de la fenêtre
- Je saisis ma formule
=ET($G2>=5;$G2<=9)
Explication de la formule :
- ET : comme j’ai plusieurs conditions, j’utilise la fonctions ET pour définir les >=5 et <=9.
- Je mets le $ uniquement devant la référence de colonne. Ainsi, où que je sois sur la ligne, il vérifie l’ancienneté en colonne G puis en ligne 4, j’ai besoin que la recopie pour G2 passe en G4.
5. Je choisis le format à donner, en l’occurrence un remplissage vert
6. Je valide avec OK.
Télécharger le fichier
Merci d’avoir suivi cet article complet sue la mise en forme conditionnelle. Continuer à visiter may6.fr à la découverte de nouveaux tutoriels.
Je vous mets à disposition le fichier Excel utilisé.
