La mise en forme conditionnelle est une puissante fonction proposée par Excel. Comme son nom l'indique, elle consiste à appliquer une mise en forme particulière à une sélection en fonction d'une ou plusieurs conditions.

Cela permet de rendre votre feuille de calcul plus attrayante et mieux encore, la mise en forme nous permet de donner du sens à nos données et de mettre en valeur les informations importantes en un clin d’œil.

Obtenez votre fichier d'exercice gratuit

Le meilleur moyen d'apprendre sur Excel est de pratiquer.

J'ai donc ajouté un exemple gratuit afin que vous puissiez suivre les instructions et pratiquer en même temps. Téléchargez le avant de continuer.

Astuce 1 : Mettre en lumière des cellules

Supposons que nous ayons une base de données clients avec les chiffres d’affaires générés par chacun.

Vous voulez par exemple repérer les clients qui ont généré moins de 100 000 euros afin de comprendre pourquoi.

Faisons cela ensemble.

La première chose à faire est de sélectionner la colonne Chiffre d’affaires, c’est-à-dire la plage H4:H107.

Dans le ruban, onglet Accueil, cliquer sur Mise en forme conditionnelle.

Pointer la souris sur Règles de mise en surbrillance des cellules et cliquer sur Inférieur à…

Une fenêtre apparaît alors.

En inscrivant 100 000 à gauche, nous indiquons que « quelque chose » doit se produire lorsque la valeur de la cellule est inférieure à 100 00.

Quel est ce « quelque chose » ? Nous l’indiquons à droite de la boite de dialogue. Ici nous avons choisi « Remplissage rouge clair avec texte rouge foncé ». Vous pouvez évidemment choisi une autre mise en forme parmi celles proposées.

Si les mises en forme proposées par Excel ne vous conviennent pas, vous pouvez créer les vôtres en choisissant Format personnalisé…

Voilà!

Nous avons appris à identifier de manière visuelle des valeurs spécifiques.

Nous pouvons évidemment appliquer cela aux textes, nous allons voir comment dans la suite.

Astuce 2 : Mettre en lumière des cellules qui contiennent du texte

Nous avons vu précédemment comment mettre en lumière les valeurs inférieures à 100 000.

Nous pouvons utiliser la même méthode pour mettre en forme les cellules contenant (ou pas) une chaîne de caractère donnée.

Recherchons par exemple dans la même feuille les clients de la catégorie Industrie.

Sélectionner les éléments de la colonne Catégorie Clients, autrement dit la plage B4:B107.

Puis à nouveau dans le ruban, onglet Accueil, cliquer sur Mise en forme conditionnelle.

Pointer la souris sur Règles de mise en surbrillance des cellules et cliquer sur Texte qui contient…

Une fenêtre apparaît alors, semblable à celle que nous avons vu précédemment.

Astuce 3 : Modifier une règle de mise en forme

Vous pouvez avoir besoin de modifier une règle de mise en forme conditionnelle.

Pour cela, comme précédemment, cliquer sur Mise en forme conditionnelle dans l’onglet Accueil.

Puis Gérer les règles…

La fenêtre suivante apparaît :

1. « Afficher les règles de mise en forme pour : » est paramétré sur « Sélection actuelle » par défaut. Changer cela en mettant « Cette feuille de calcul ».
2. Choisir la règle que vous souhaitez changer.
3. Cliquer sur le bouton « Modifier la règle ».

Une autre boite de dialogue apparaît avec laquelle vous pouvez changez ce que vous voulez sur la règle.

Voyons ensemble les différentes options mises en évidence dans la capture d'écran.

1. Ici vous pouvez changer le type de règle. Par exemple, au lieu d'appliquer une mise en forme aux cellules qui contiennent une certaine valeur, vous pouvez créer une règle plus avancée en utilisant une formule.
2. Ensuite pour ce type de règle par exemple, vous pouvez changer le déclencheur. Ainsi par exemple, la mise en forme ne s'appliquerait plus lorsqu'une cellule contient un « Texte spécifique » mais plutôt en fonction de la « Valeur de la cellule » ou pour des « Cellules vides ».
3. Si vous avez gardé « Texte spécifique » vous pouvez décider si la mise en forme s'applique aux cellules « contenant », « ne contenant pas », « commencant par » ou « se terminant par » le texte spécifié au point 4.
4. Ici vous entrez votre texte.
5. En cliquant sur Format..., vous pouvez changer la mise en forme et faire parler votre côté artistique.

Pour changer la plage de cellules concernée par les règles de mise en forme conditionnelle, vous n'avez pas besoin d'aller dans la fenêtre de modification de règle de mise en forme vue ci-dessus.

Vous avez juste besoin de cliquer sur « S'applique à » au niveau de la règle que vous souhaitez changer dans le Gestionnaire des règles de mise en forme conditionnelle.

Astuce 4 : Supprimer une règle de mise en forme conditionnelle

Enfin, cliquer sur Effacer les règles de la feuille entière.

Néanmoins, vous pouvez avoir besoin d'en garder quelques unes.

Pour cela cliquer plutôt sur Gérer les règles...

Nous retrouvons alors le Gestionnaire des règles de mise en forme conditionnelle.

1. « Afficher les règles de mise en forme pour : » est paramétré sur « Sélection actuelle » par défaut. Changer cela en mettant « Cette feuille de calcul ».
2. Choisir la règle que vous souhaitez supprimer.
3. Cliquer sur le bouton « Supprimer la règle ».

Astuce 5 : Trouver les 8 valeurs les moins élevées

Supposons maintenant que nous cherchions les 8 clients avec lesquels vous avez généré le moins de chiffre d'affaires.

Sur Excel, il y a plusieurs méthodes pour le faire, voyons comment le réaliser avec une mise en forme conditionnelle.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil (vous commencez à avoir l'habitude). Pointer la souris sur Règles des valeurs de plage haute/basse puis cliquer sur 10 valeurs les moins élevées...

1. Dans le champ de gauche, vous fixez le nombre N d'éléments dont les valeurs sont les moins élevées. C'est paramétré par défaut sur 10. Changeons cela à 8.
2. Dans le champ de droite, vous choisissez la mise en forme à appliquer aux 8 cellulles contenant les valeurs les moins élevées.

C'est aussi simple que cela.

Vous pouvez évidemment mettre en forme votre Top 8 (ou n'importe quel nombre comme nous l'avons vu) en utilisant exactement la même méthode.

Il suffit de cliquer sur 10 valeurs les plus élevées... à la place.

Astuce 6 : Utiliser les barres de données

Les barres de données sont très utiles pour montrer les valeurs les plus et les moins élevées.

Les barres de données sont simplement des barres dans vos cellules.

La longueur de la barre dépend de la valeur de la cellule comparée à celles des cellules sélectionnées. Ainsi une barre courte signifie que la valeur est petite comparée au reste des données et une longue barre signifie que la valeur est importante comparée au reste.

Concrètement, comment on fait?

Supposons que dans notre feuille de calcul, nous nous intéressions aux quantités vendues. Nous sélectionnons alors la plage D4:D107.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Pointer la souris sur Barre de données. Il apparaît alors plusieurs options.

Les options de mise en forme sont réparties entre "Dégradé" et "Remplissage uni".

• Dégradé signifie tout simplement que la couleur de chaque barre pâlit.
• Remplissage uni signifie que la barre a une couleur unie.

La différence entre les "Nuances de couleur" et les "barres de Données" n'est qu'une question de présentation.

En utilisant les "Nuances de couleur", on assigne des couleurs à toutes les cellules de votre sélection.

• Une couleur est assignée à la cellule avec la valeur la plus basse
• Une autre couleur est assignée à la cellule avec la valeur la plus haute
• On assigne un mélange des 2 couleurs décrites ci-dessus pour toutes les cellules et les valeurs entre les extremum ou alors choisir une 3è couleur

Appliquons cela!

Sélectionnons la colonne des Prix toujours dans le même onglet, dans la colonne G (plage G4:G107).

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Pointer la souris sur Nuances de couleur. Il apparaît alors plusieurs options.

En pointant la souris sur les différentes options proposées, vous pouvez constater les changements de couleur sur votre sélection (plage G4:G107). Cliquer simplement sur l'option qui vous plaît le plus.

Les Jeux d'icônes sont d'autres outils pour donner du sens à vos données. Ceux-ci sont au nombre de 3 à 5 selon les jeux que vous choisissez.

Le principe est toujours le même :

• Une icône (souvent verte) est affectée aux valeurs les plus hautes (premiers 33% si il y a 3 icônes, 25% pour 4 icônes et 20% pour 5 icônes)
• Une icône (souvent rouge) est affectée aux valeurs les plus basses (derniers 33% si il y a 3 icônes, 25% pour 4 icônes et 20% pour 5 icônes)
• Une ou plusieurs autres aux valeurs intermédiaires 

Voyons un exemple ensemble.

Gardons la même plage que précédemment, les Prix, la plage G4:G107 que nous sélectionnons.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Pointer la souris sur Jeux d'Icônes. Il apparaît alors plusieurs options.

Je vous invite à tester chacune des options pour voir le résultat.

J'ai par exemple choisi les 3 premiers symboles du groupe Forme.

Astuce 9 : Trouver des doublons

La mise en forme conditionnelle permet également d'identifier les doublons dans une base de données.

Attention! Ne pas confondre l'outil Supprimer les doublons accessible dans l'onglet Données et l'identification des doublons avec une mise en forme conditionnelle.

Voici comment faire cela.

Sélectionner la base de données dans laquelle vous souhaitez trouver les doublons. Ce ne sont pas nécessairement des nombres, cela peut être également des textes ou même des dates.

Reprenons notre fichier et déterminons ensembles les dates de livraison en doublons par exemple.

Sélectionnons donc la plage C4:C107.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Pointer la souris sur Règles de mise en surbrillance des cellules et cliquer sur Valeurs en double...

Dans la boite de dialogue qui apparaît, choisir la mise en forme qui convient et cliquer sur OK.

Toutes les dates en doublon apparaissent alors avec une mise en forme (ici en jaune dans notre exemple).

Astuce 10 : Cacher les valeurs à 0

Sélectionner la plage dans laquelle vous voulez cacher les valeurs à 0.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Pointer la souris sur Règles de mise en surbrillance des cellules et cliquer sur Égal à...

Apparaît alors une boite de dialogue avec 2 champs.

Dans le champ de gauche, entrer 0.

Dans le champ de droite, cliquer sur la flèche et choisir Format personnalisé...

Dans la nouvelle boite qui apparaît, aller dans Police et changer la couleur pour qu'elle corresponde à la couleur de la cellule .

Ainsi tous les 0 de la sélection disparaissent puisque les couleurs des 0 et des cellules sont les mêmes.

Astuce 11 : Créer une règle dépendant du contenu d'une autre cellule

Dans certains cas, vous avez intérêt à garder de la flexibilité dans vitre mise en forme conditionnelle.

Autrement dit, si vous créez une mise en forme conditionnelle qui met en forme des cellules au-dessus d'une certaine valeur, il peut être judicieux que cette valeur ne soit pas paramétrée directement dans la règle mais plutôt entrée dans une cellule à laquelle la règle ferait référence.

Ce qui vous permettrait de changer le seuil de déclenchement de la mise en forme sans avoir à modifier la règle, simplement en modifiant une cellule.

Réalisons ensemble un exemple pour illustrer cela. Nous allons faire mettre  en forme les dates de la colonne C qui sont 45 jours antérieures à la date entrée dans la celulle J3.

Sélectionner la plage C4:C107.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Cliquer sur Nouvelle règle...

1. Sélectionner "Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué"
2. Entrer ensuite la formule =C4<$J$3-45 (que nous expliquons plus bas si besoin)
3. Choisir le format qui vous convient
4. Cliquer sur OK

A propos de la formule :

1. La première partie [=C4] est une référence à la première cellule de la plage sélectionnée. Excel comprend que ce n'est pas une référence absolue (pas de $).
2. C'est une référence absolue à la cellule J3 (avec les $), ce qui signifie qu'Excel fera tous les calculs avec cette cellule.
3. L'expression [<$J$3 - 45] signifie que chaque cellule antérieure de 45 jours à la date dans la cellule J3 correspond à nos critères et doit être mise en forme.

Ainsi vous constatez que les cellules correspondant aux critères deviennent rouges (c'est la mise en forme que j'ai choisi). Ces cellules sont antérieures de plus de 45 jours à la date de la cellule J3 (01/07/2019).

Astuce 12 : Hiérarchiser les mises en formes conditionnelles

Si plusieurs mises en forme conditionnelles sont appliquées à la même cellule, il faut savoir gérer un tel cas.

Prenons l'exemple suivant :

Règle 1 : Toutes les lignes de la base de données dont la date est ultérieure à la date limite (01/07/2019) sont colorées en bleu.

Règle 2 : Toutes les quantités inférieures à 5000 sont en rouge.

Les 2 règles sont importantes. Une s'applique à la ligne entière, l'autre aux cellules de la colonne D.

Nous allons utiliser l'onglet Exemple 2 pour ce cas là.

Si plusieurs mises en forme conditionnelles sont appliquées à la même cellule, il faut savoir gérer un tel cas.

Par exemple, si on considère la règle 2 ci-dessus, toutes les cellules de la colonne D dont la valeur est inférieure à 5000 devraient être rouges... or ce n'est pas le cas.

Pourquoi?

Parce qu'il y a la règle 1 qui impose la couleur bleue.

Jetons un coup d’œil au Gestionnaire des règles de mise en forme conditionnelles.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Cliquer sur Gérer les règles...

Vous constatez que la règle qui transforme les cellules en rouge est en seconde position.

En revanche, si on inverse l'ordre, la règle devient prioritaire et les cellules concernées deviendront rouges et non bleues.

Pour cela, appliquons cela :

1. « Afficher les règles de mise en forme pour : » est paramétré sur « Sélection actuelle » par défaut. Changer cela en mettant « Cette feuille de calcul ».
2. Sélectionner la première règle.
3. Cliquer sur la flèche « Descendre ».
4. Cliquer sur OK.

On obtient le résultat suivant :

Astuce 13 : Mettre en forme une ligne entière en fonction de la valeur d'une cellule

Vous pouvez décider de mettre en forme toute une ligne de votre base de données en fonction d'une valeur.

Voyons cela avec un exemple.

Reprenons notre fichier de données clients. Supposons que nous voulions mettre en forme toutes les lignes pour lesquelles les quantités vendues sont supérieures à 5000.

Pour cela, sélectionner toute la base de données, c'est à dire la plage A4:H107.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Cliquer sur Nouvelle règle...

Nous reprenons la même méthodologie que pour l'astuce 11 :

Sélectionner "Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué"

Entrer ensuite la formule =$D4>5000 . Cette formule signifie que la mise en forme s'appliquera quand seulement la quantité est supérieure à 5000, d'où le $ pour figer la colonne dans la formule.

Choisir le format qui vous convient.

Cliquer sur OK.

Voila le résultat!!

Essayez d'appliquer une mise en forme plus jolie que la mienne!

Astuce 14 : Mettre en forme une colonne entière en fonction de la valeur d'une cellule

Vous pouvez décider de mettre en forme toute une colonne de votre base de données en fonction d'une valeur.

Voyons cela avec un exemple.

Dans l'onglet Exemple 3 de notre fichier de pratique, nous avons un exemple de planning avec différentes tâches et le nombre de jours alloués par mois. Ce planning est sur un fond coloré.

Ce que nous souhaitons : Lorsqu'un mois connaît au moins un jour de travail, la colonne correspondante doit devenir blanche.

Pour cela, sélectionner la plage sur laquelle la mise en forme conditionnelle doit s'appliquer, ici la plage B4:AF9.

Cliquer sur Mise en forme conditionnelle dans l'onglet Accueil. Cliquer sur Nouvelle règle...

Sélectionner "Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué" et entrer la formule suivante :

=ESTNUM(B$9).

La référence dit à Excel de vérifier si dans la ligne 9 de chaque colonne de la plage si le critère est rempli (pas uniquement dans la colonne B... grâce à la position judicieuse du $).

La formule ESTNUM contrôle si la valeur entrée est un nombre.

Cliquer sur Format...

1. Aller dans l'onglet "Remplissage".
2. Cliquer sur "Aucune couleur".
3. Cliquer sur OK.

Cliquer à nouveau sur OK.

Instantanément vous voyez les colonnes B à J (lignes 4 à 9) devenues blanches.

Essayez d'entrer par exemple un nombre dans la cellule K6 pour voir le changement!

Cela conclut les meilleures astuces pour la mise en forme conditionnelle!