Pour mieux visualiser vos données , Excel permet d’appliquer automatiquement un format spécifique à vos données en fonction de leur valeurs. Afficher les 10 plus grandes valeurs en vert? Surligner les doublons en rouge? Colorier les valeurs en dessous de la moyenne? Aucun problème avec les mises en forme conditionnelles !
- Créer une mise en forme conditionnelle
- Gérer les règles de mise en forme conditionnelle
- Les barres de données
- Modifier une mise en forme conditionnelle par défaut
- Les échelles de couleur
- Les jeux d’icônes
- Surligner des valeurs particulières
- Mettre en évidence les doublons
- Mettre en surbrillance avec une formule
- Surligner les valeurs les plus élevées ou moins élevées
- Comment comparer deux plages de cellules
- Comment colorier une ligne sur deux
- Afficher les doublons une fois en rouge, une fois en vert
- Gérer les conflits entre plusieurs règles
Créer une mise en forme conditionnelle
Pour créer une mise en forme conditionnelle, commencez par sélectionner la plage de données que vous voulez formatter, puis cliquez sur Mise en forme conditionnelle dans le menu Format
Vous pouvez également accéder au menu de mise en forme conditionnelle depuis le ruban dans la catégorie Format.
Vous pouvez alors créer une nouvelle mise en forme conditionnelle depuis le menu déroulant qui s’affiche.
Gérer les règles de mise en forme conditionnelle
Pour connaitre toutes les règles de mise en forme conditionnelle appliquées à une plage de cellules, sélectionnez cette plage et cliquez sur Gérer les règles depuis le ruban.
Le menu de gestion des MFC s’ouvre alors et affiche les règles appliquées à la sélection.
- Vous pouvez supprimer une règle en cliquant sur – , en ajouter une nouvelle en cliquant sur +
- Vous pouvez changer les règles appliquées à la sélection en cliquant sur Modifier la règle
- Le menu Afficher les règles de mise en forme pour vous permet de choisir entre afficher les règles pour la sélection seulement, pour une feuille du classeur en particulier ou bien pour tout le classeur.
- Les flèches Modifier l’ordre de règle vous permettent de modifier l’ordre dans lesquelles s’appliquent les règles (la plus haute est prioritaire)
- La checkbox Interrompre si Vrai vous permet de spécifier si les autres règles de MFC doivent encore s’appliquer si la première est déjà vérifiée.
Les barres de données
La mise en forme conditionnelle en barre de données permet de représenter dans chaque cellule une barre de données proportionnelle à la valeur de la cellule. La plus grande valeur de la plage aura ainsi une barre prenant 100% de la largeur, les autres valeurs auront une barre proportionnelle.
Pour créer une MFC en barres de données, sélectionnez cette option dans le menu MFC du ruban (vous avez plusieurs options de couleurs mais le comportement sera le même).
Des barres de données apparaissent alors dans les cellules de la sélection:
La valeur la plus petite (ici 9) possède une toute petite barre de données qui n’apparait quasiment pas, à l’inverse la plus grande (318) possède une barre qui remplit quasiment toute la largeur de la cellule. Les autres cellules sont remplies proportionnellement.
Le format est dynamique: si les valeurs de la plage changent, le format changera aussi. Par exemple on va insérer une très grande valeur (800): toutes les barres de couleur sont modifiées automatiquement. C’est là tout l’intérêt des MFC: Appliquer une mise en forme dynamique pour automatiquement visualiser vos données.
Modifier une mise en forme conditionnelle par défaut
Une fois la règle par défaut appliquée, vous pouvez la modifier facilement depuis le menu Gérer les MFC > Modifier la règle. Le menu d’édition de la règle s’ouvre alors:
Vous pouvez y modifier (dans le cas d’une barre de données)
- Le format appliqué: couleurs de remplissage et bordures pour la barre de données
- Les valeurs utilisées pour déterminer le minimum et le maximum de la barre
- La position de l’axe (par défaut sur la gauche s’il s’agit uniquement de valeur positive, on peut aussi le placer au milieu ou à droite si des valeurs sont négatives)
Les échelles de couleur
Il est également possible de d’afficher des couleurs de fond dépendant des valeurs relatives: par exemple les plus hautes en vert, les plus basses en rouge, et au milieu en jaune.
Pour cela, sélectionnez la règle Nuance de couleurs > Echelle de couleur Vert – Jaune – Rouge
La plage de valeur se colore alors selon des nuances de vert, jaune et rouge. Le rouge correspond au minimum et au valeurs qui en sont proches, le vert aux valeurs proches du maximum. Les valeurs proches de la médiane sont coloriées en nuances de jaune.
Il est également possible de créer un fond en dégradé, avec une cellule remplie pour le maximum et un fond quasiment blanc pour le minimum. Pour cela il faut choisir l’une des règles Nuance de couleurs qui contiennent une seule couleur, par exemple Echelle de couleur Vert – Blanc
La plage de cellule affiche alors différentes nuances de vert, la maximum étant vert foncé, les valeurs proches du minimum ont un fond quasiment transparent, et les valeurs du milieu possèdent un fond vert clair.
Les jeux d’icônes
Vous pouvez également choisir une série d’icônes et afficher pour chaque valeur une icône correspondante de la série. Pour cela choisissez Jeux d’icônes puis le type d’icône que vous souhaitez afficher.
A chaque fois, vous avez le choix entre 3 à 5 icônes. Les valeurs entre le minimum et le maximum seront alors séparées en intervalles égaux et une icône sera associée à chaque intervalle. Par exemple avec 3 flèches, une flèche rouge sera si la valeur tombe dans les 33% des valeurs les plus basses entre le minimum et maximum, et une flèche verte pour les 33% les plus hautes. Les valeurs entre les deux seront affichées avec une flèche jaune.
Vous pouvez bien sur modifier ces valeurs dans l’écran modifier la règle:
Vous pouvez également:
- Modifier le jeu d’icônes,
- Choisir d’afficher une icône particulière pour chaque valeur
- Changer les intervalles
- Changer le type de valeur qui délimite les intervalles, par exemple choisir de fixer des valeurs de votre choix
Surligner des cellules avec une mise en forme conditionnelle
L’une des applications les plus communes des MFC est de surligner certaines valeurs, en fonction de leur valeur, qu’elle soient numériques ou de type texte.
Surligner des valeurs particulières
Vous pouvez choisir d’appliquer un format spécifique à certaines valeurs, notamment:
- Les valeurs supérieures/inférieures à un seuil
- Les valeurs comprises entre deux bornes
- Une seule valeur spécifique
- Les valeurs qui contiennent un texte spécifique
- Une date se produisant le jour, la semaine ou le mois courant, passé ou futur
- Les valeurs en double
Par exemple, nous allons choisir de mettre en surbrillance uniquement les cellules qui contiennent un 2. Il faut donc choisir l’option Texte qui contient, puis renseigner 2 dans le menu qui s’ouvre alors.
L’option « Mettre en forme avec » vous permet de choisir un format prédéfini à appliquer ou d’ouvrir un menu pour personnaliser le format à appliquer.
Toutes les cellules qui contiennent un 2 se retrouvent surlignées comme voici ci-dessous:
Mettre en évidence les doublons
Une option intéressante est de mettre en surbrillance les doublons dans une série de valeurs. Plus pratique que de les déterminer à la main! Et vous n’avez même pas besoin de formule. Pour cela, sélectionnez l’option Valeurs en double
Dans le menu qui s’affiche, vous pouvez alors choisir de mettre en surbrillance soit les valeurs en double, soit les valeurs uniques.
Si vous sélectionnez valeurs en double, le résultat est le suivant:
Les valeurs en doubles apparaissent coloriées, les autres n’ont pas changé.
Mettre en surbrillance avec une formule
La mise en surbrillance est personnalisable à l’infini, puisqu’il est possible de surligner des cellules à l’aide d’une formule. Pour cela, choisissez Nouvelle règle dans le menu des MFC:
Dans le menu suivant, sélectionnez classique, puis entrez la formule suivante:
Résultat: les cellules contenant un nombre pair sont coloriées
C’est véritablement l’une des fonctions les plus puissantes des mises en forme conditionnelles. Nous avons utilisé une fonction simple ici mais la plupart des conditions imaginables sont utilisables, si vous trouvez la formule!
Surligner les valeurs les plus élevées ou moins élevées
Vous pouvez choisir de surligner les valeurs les plus hautes ou les plus basses d’une plage:
- 10 valeurs les plus/moins élevées
- 10% les plus élevées ou les plus basses
- Au dessus/ en dessous de la moyenne
Pour cela sélectionnez l’option Règles de valeurs plus/moins élevées
Dans le menu suivant, vous pouvez choisir si vous voulez afficher les valeurs les plus hautes ou les plus basses, le nombre de valeurs et si vous voulez un nombre fixe ou en pourcentage.
Nous allons afficher les 5 valeurs les plus élevées:
Comment comparer deux plages de cellules
Une des applications très pratique des formats conditionnels est de résoudre le problème suivant: Avec deux listes de valeurs A et B (en colonnes par exemple), comment savoir quelles valeurs sont dans A mais pas dans B et dans B mais pas dans A?
C’est résolu en 2 secondes avec un petit format conditionnel: il suffit d’appliquer une règle de surbrillance des valeurs en double en sélectionnant les 2 colonnes.
Puis dans le menu suivant, choisissez l’option Unique pour surligner les valeurs uniques:
Résultat: Les cellules qui soit dans A soit dans B mais pas les deux, sont surlignées. Toutes les autres sont en double et sont laissées tel quel.
Comment colorier une ligne sur deux
Pour colorier une ligne sur deux, nous allons faire appel à une mis en forme à l’aide d’une formule. Nous allons entrer la formule suivante:
Cela donne bien le résultat attendu:
Afficher les doublons une fois en rouge, une fois en vert
Je vous invite à lire comment faire dans cet article
Gérer les conflits entre plusieurs règles
Parfois, plusieurs règles de mise en forme conditionnelle s’appliquent. Comment être sur que c’est bien la bonne règle qui prime?
Dans l’exemple ci-dessous, nous appliquons la couleur jaune à toutes les valeurs supérieures à 10 et la couleur verte à toutes les valeurs supérieures à 18. Les cellules contenant 19 devraient donc être en vert. Pourquoi toutes les cellules sont-elles en jaune?
Il ne faut pas oublier que les règles les plus hautes sont prioritaires ( elles sont exécutées de bas en haut si vous préférez). Même si la règle verte est vérifiée, c’est la ligne jaune qui gagne. Pour que ce soit la règle verte qui s’applique si les deux sont vérifiées, il faut remonter la règle verte avec les flèches Modifier l’ordre de la règle.
Si la règle verte est en premier, elle est bien exécutée correctement.
Et plus encore!
Les possibilités de créer des formats conditionnels sont virtuellement infinies. Partagez vos astuces préférées dans les commentaires!
Vraiment un GRAND Merci !!!!!!