Les mises en forme conditionnelles dans Excel
La mise en forme conditionnelle (MFC) colore automatiquement les cellules selon leur valeur : doublons, chiffres au-dessus d’un seuil, barres de données, nuances de couleurs, icônes, ou toute règle écrite avec une formule. Si les données changent, la mise en forme suit. Le laboratoire ci-dessous applique les règles en direct sur un tableau de ventes, exactement comme Excel.
MenuAccueil › Styles
Analyse rapideCtrl + Q
Gérer les règlesGestionnaire
Règle sur mesureFormule
Laboratoire MFC : choisissez une plage, un type de règle, puis Appliquer
Gestionnaire des règles (priorité de haut en bas)
1
Les doublons
- Sélectionnez la plage à contrôler (une colonne de noms, de références, d’e-mails).
- Accueil › Mise en forme conditionnelle › Règles de mise en surbrillance des cellules › Valeurs en double.
- Choisissez En double ou Uniques et un format, puis OK.
Dans le laboratoire, choisissez la plage A2:A11 · Commercial et Valeurs en double : Bruno Petit apparaît deux fois. Sur C2:C11 · CA, deux montants identiques ressortent aussi.
AttentionLa MFC signale les doublons, elle ne les supprime pas. Pour les supprimer : Données › Supprimer les doublons, en cochant les colonnes qui définissent un doublon. Faites une copie avant : l’opération est définitive.
AstucePour repérer une ligne entière en double (même nom et même région), utilisez une règle par formule :
=NB.SI.ENS($A$2:$A$11;$A2;$B$2:$B$11;$B2)>1 sur A2:F11.2
Les règles de mise en surbrillance des cellules
| Menu | Règle | Exemple |
|---|---|---|
| Règles de mise en surbrillance des cellules | Supérieur à / Inférieur à | CA supérieur à 50 000 € |
| Entre | Satisfaction entre 3 et 4 | |
| Égal à | Région égale à « Nord » | |
| Texte qui contient | Commercial contenant « Ma » | |
| Une date se produisant | Échéances de la semaine dernière, du mois prochain… | |
| Valeurs en double | Voir séquence 1 | |
| Règles des valeurs plus/moins élevées | 10 premiers / 10 derniers éléments | Les 3 meilleurs CA (le nombre est modifiable) |
| 10 % les plus élevés / les moins élevés | Le top 10 % d’une longue liste | |
| Au-dessus / en dessous de la moyenne | Évolutions inférieures à la moyenne de l’équipe |
AstuceLa valeur d’une règle peut être une cellule : tapez
=$H$1 dans la zone Supérieur à. Changez H1, toutes les couleurs se recalculent : pratique pour un seuil réglable.Bon à savoirLe bouton Analyse rapide (Ctrl + Q) sur une sélection propose un aperçu direct des principales mises en forme conditionnelles.
3
Les barres de données
Une barre de données dessine dans chaque cellule une barre proportionnelle à sa valeur : un mini-graphique intégré au tableau. Accueil › Mise en forme conditionnelle › Barres de données : dégradé ou remplissage plein.
| Réglage (Gérer les règles › Modifier) | Effet |
|---|---|
| Minimum / Maximum | Automatique, nombre, pourcentage, centile ou formule : fixez 0 pour comparer honnêtement |
| Afficher la barre uniquement | Masque les nombres : effet jauge |
| Valeur négative et axe | Les valeurs négatives partent vers la gauche, en rouge (testez sur la colonne Évolution) |
| Direction de la barre | De gauche à droite ou l’inverse |
AttentionPar défaut, la plus petite valeur a une barre presque vide même si elle est proche des autres. Si l’écart réel compte, fixez le minimum à 0.
4
Les nuances de couleurs
Les nuances (ou échelles) de couleurs colorent chaque cellule selon sa position entre le minimum et le maximum : une carte de chaleur. Accueil › Mise en forme conditionnelle › Nuances de couleurs.
| Variante | Usage |
|---|---|
| 2 couleurs (blanc › vert) | Intensité : plus c’est foncé, plus c’est élevé |
| 3 couleurs (rouge › jaune › vert) | Bon / moyen / mauvais, avec un point médian (50e centile par défaut, ou une valeur comme 0 pour une évolution) |
AstucePour une colonne où « petit » est bon (délais, coûts), choisissez l’échelle vert › jaune › rouge : le vert reste associé au bon résultat.
5
Les jeux d’icônes
Accueil › Mise en forme conditionnelle › Jeux d’icônes : flèches, feux, symboles, indicateurs. Avec 3 icônes, Excel découpe par défaut l’étendue en tiers : icône verte au-dessus de 67 %, jaune entre 33 % et 67 %, rouge en dessous.
| Réglage (Gérer les règles › Modifier) | Effet |
|---|---|
| Type : Pourcentage / Nombre / Centile / Formule | Passez en Nombre pour des seuils métier : flèche verte si Évolution >= 0,05, rouge si < 0 |
| Inverser l’ordre des icônes | Quand une valeur basse est bonne |
| Afficher l’icône uniquement | Tableau de bord épuré |
AttentionEn mode Pourcentage, les seuils sont relatifs au minimum et au maximum de la plage : la meilleure valeur a toujours une flèche verte, même si elle est mauvaise dans l’absolu.
6
Écrire une règle de mise en forme conditionnelle
- Sélectionnez toute la plage à colorer, en commençant par la première ligne de données (ici A2:F11 pour colorer des lignes entières).
- Accueil › Mise en forme conditionnelle › Nouvelle règle › Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Écrivez la formule pour la première cellule de la sélection (A2), comme si elle renvoyait VRAI ou FAUX :
=$C2<$D2. - Bouton Format : remplissage, police, bordure. OK.
| Formule (plage A2:F11) | Résultat |
|---|---|
=$C2<$D2 | Colore toute la ligne des commerciaux sous leur objectif |
=$B2="Nord" | Lignes de la région Nord |
=ET($C2>=$D2;$E2>0) | Objectif atteint et en progression |
=$C2=MAX($C$2:$C$11) | La ligne du meilleur CA |
=NB.SI($A$2:$A$11;$A2)>1 | Lignes dont le nom est en double |
=MOD(LIGNE();2)=0 | Une ligne sur deux (effet zébré) |
Attention au $
$C2 fige la colonne C (on compare toujours le CA) mais laisse la ligne varier : c’est ce qui colore la ligne entière. Avec C2 sans $, chaque colonne comparerait sa propre voisine ; avec $C$2, toutes les lignes suivraient la ligne 2.Gestionnaire des règlesMise en forme conditionnelle › Gérer les règles : modifier, supprimer, changer l’ordre (la règle du haut est prioritaire en cas de conflit de format), cocher Interrompre si vrai. Choisissez « Cette feuille de calcul » en haut pour tout voir.
AstucePour copier une MFC vers un autre tableau, utilisez le pinceau Reproduire la mise en forme. Pour tout retirer : Mise en forme conditionnelle › Effacer les règles.
Exercice de synthèse : le tableau des commerciaux
Reprenez le tableau du laboratoire (A1:F11) et appliquez les règles suivantes. Télécharger le fichier d’exercice (feuilles Énoncé et Corrigé avec les règles)
- A2:A11 : Valeurs en double en rouge clair. Résultat : les deux lignes « Bruno Petit ».
- C2:C11 : Barres de données bleues, minimum fixé à 0.
- E2:E11 : Jeu d’icônes 3 flèches en mode Nombre : verte si >= 0,05, jaune si >= 0, rouge sinon. Résultat : 4 flèches vertes, 3 jaunes, 3 rouges.
- F2:F11 : Nuances 3 couleurs rouge › jaune › vert.
- A2:F11 : formule
=$C2<$D2, texte rouge : 5 commerciaux sous leur objectif (Bruno ×2, David, Emma, Hugo). - Ouvrez le Gestionnaire des règles et placez la règle par formule en premier. Modifiez l’objectif de David à 44 000 : sa ligne ne doit plus être rouge.