Mises en forme conditionnelles

La mise en forme conditionnelle permet de définir la mise en forme d'une cellule si le contenu de celle-ci répond à une ou plusieurs conditions.

L'objet de la mise en forme conditionnelle est de mettre en avant visuellement une cellule en fonction de sa valeur.

Elle permet de modifier :

• la mise en forme du contenu
• le format
• la couleur de fond de la cellule
• les bordures

La mise en forme conditionnelle permet de définir la mise en forme d'une cellule si le contenu de celle-ci répond à une ou plusieurs conditions.

L'objet de la mise en forme conditionnelle est de mettre en avant visuellement une cellule en fonction de sa valeur.

Elle permet de modifier :

• la mise en forme du contenu
• le format
• la couleur de fond de la cellule
• les bordures

Il existe des mises en forme conditionnelles prédéfinies, que l’on applique directement à la plage de cellule sélectionnée.

Nous allons découvrir ces différentes possibilités à partir du tableau ci-dessous :

Assurez-vous d'avoir sélectionné la plage de cellules à mettre en forme, puis cliquez sur Mise en forme conditionnelle dans l'onglet Accueil du ruban (groupe Styles); pour découvrir les différents styles prédéfinis :

Mise en surbrillance des cellules

Cette règle de mise en forme conditionnelle permet de mettre en évidence des cellules en comparant leur contenu à des seuils. Voici les critères disponibles :

Une boîte de dialogue apparaît, qui nous permet de saisir notre seuil de 500 et de choisir le format à appliquer dans la liste déroulante de gauche :

Et voici le résultat :

Explorez les autres critères, il y en a forcément un qui correspond à votre besoin !

Il est également possible de modifier les réglage de cette mise en forme conditionnelle prédéfinie. Vous pourrez ainsi appliquer d'autres couleurs que celles-proposées par défaut, ou ajouter une bordure à la barres, etc...
Pour cela, choisissez Gérer les règles dans le ruban :

La boîte de dialogue du gestionnaire des règles de mise en forme conditionnelle s'affiche. Nous verrons plus tard plus en détail le fonctionnement de cette boîte de dialogue. Dans l'immédiat, double-cliquez sur la ligne Barre de données (ou activez le bouton Modifier la règle) :

Une fenêtre s'affiche, et vous pouvez alors modifier la couleur de remplissage, le type et la couleur de bordure, l'orientation de la barre, le format de la barre en cas de valeur négative, ainsi que les paramètres concernant l'axe de séparation entre valeurs positives/négatives :

Valeurs de plage haute/basse

Cette mise en forme conditionnelle permet de mettre avant des valeurs ou des pourcentages parmis les plus ou les moins élevés :

• 10 valeurs les plus élevées...
• 10% les plus élevés...
• 10 valeurs les moins élevées...
• 10% les moins élevés...
• Valeurs supérieures à la moyenne...

Nous allons choisir de mettre en surbrillance les 10 valeurs les plus élevées de notre tableau. Dans le ruban, nous activons le bouton Mise en forme conditionnelle, et nous choisissons Règles des valeurs de plage haute/basse puis 10 valeurs les plus élevées... :

Une boîte de dialogue apparaît, qui nous permet de saisir le nombre de valeurs les plus élevées à mettre en avant (ici, 10) et de choisir le format à appliquer dans la liste déroulante de gauche :

Et voici le résultat :

Vous avez également la possibilité de modifier les réglages des styles prédéfinis. Pour cela, choisissez Gérer les règles dans le ruban, puis, dans la boîte de dialogue du gestionnaire des règles de mise en forme conditionnelle qui s'affiche, double-cliquez sur la ligne Haut 10 (ou activez le bouton Modifier la règle).

Dans la fenêtre qui s'affiche,  vous pouvez modifier la couleur utilisée, mais également définir le nombre de valeurs à mettre en avant, préciser s'il s'agit des premières valeurs ou des dernières, ou encore si le classement doit être effectué en pourcentage de la plage sélectionnée :

Barres de données

Avec cette mise en forme conditionnelle, chaque cellule affiche une barre de couleur proportionnelle à sa valeur :

Le résultat s'affiche directement en fonction du style choisi :

Vous avez également la possibilité de modifier les réglages des styles prédéfinis. Pour cela, choisissez Gérer les règles dans le ruban, puis, dans la boîte de dialogue du gestionnaire des règles de mise en forme conditionnelle qui s'affiche, double-cliquez sur la ligne Barre de données (ou activez le bouton Modifier la règle).

Dans la fenêtre qui s'affiche,  vous pouvez définir précisément les critères et le style de mise en forme :

Nuances de couleurs

Les nuances de couleurs permettent de faire ressortir la distribution des valeurs des cellules grâce à un dégradé de couleurs :

Il n'y a pas d'autre réglage à effectuer ici, le résultat s'affiche directement en fonction du style choisi :

Vous avez également la possibilité de modifier les réglages des styles prédéfinis. Pour cela, choisissez Gérer les règles dans le ruban, puis, dans la boîte de dialogue du gestionnaire des règles de mise en forme conditionnelle qui s'affiche, double-cliquez sur la ligne Barre de données (ou activez le bouton Modifier la règle).

Dans la fenêtre qui s'affiche,  vous pouvez définir précisément les critères et le style de mise en forme :

Jeux d'icônes

Avec cette mise en forme conditionnelle, chaque cellule se voit affecter d'une icône fonction de sa valeur par rapport à l'ensemble des cellules sélectionnées :

Il n'y a pas d'autre réglage à effectuer ici, le résultat s'affiche directement en fonction du style choisi :

Vous avez également la possibilité de modifier les réglages des styles prédéfinis. Pour cela, choisissez Gérer les règles dans le ruban, puis, dans la boîte de dialogue du gestionnaire des règles de mise en forme conditionnelle qui s'affiche, double-cliquez sur la ligne Jeu d'icônes (ou activez le bouton Modifier la règle).

Dans la fenêtre qui s'affiche,  vous pouvez définir précisément les critères et le style de mise en forme :

Vous trouverez dans cet article un exemple de mise en forme conditionnelle avec un jeu d'icônes pour faire apparaître une tendance de manière graphique.

Comment créer une mise en forme conditionnelle avec formule ?

Les mise en formes conditionnelles prédéfinies offrent la plupart du temps la solution aux besoins de mise en forme des cellules en fonction de certains critères.

Mais parfois, les régles prédéfinies n'apportent pas la solution recherchée. C'est le cas notamment si le critère doit s'appliquer non pas en fonction de la valeur contenue dans la formule elle-même, mais de celle contenue dans une autre cellule.
Il faut alors utiliser une formule qui permettra de définir si le critère est rempli ou pas, et appliquer (ou non) le format adéquat.

La formule doit donner un résultat VRAI (ou 1) ou FAUX (ou 0). Si le résultat est VRAI, la mise en forme conditionnelle s'applique.

Attention : la mise en forme sera inopérante si la formule n'est pas valide.

Création de la règle de mise en forme conditionnelle

Imaginons un tableau de gestion des stocks dans lequel on souhaiterait mettre en surbrillance les produits dont le nombre d'articles en stock est inférieur au montant spécifié dans une cellule (souvenez-vous : il faut autant que possible éviter d'intégrer des nombre en dur dans les formules de calcul).
Voici le tableau sur lequel nous allons travailler. Le seuil de commande (50 articles) a déjà été saisi dans la cellule B9, et la plage de cellules à formater a été sélectionnée (B2:B7) :

Pour créer une mise en forme conditionnelle avec formule, cliquez d'abord sur le bouton Mise en forme conditionnelle dans l'onglet Accueil du ruban, puis choisissez Nouvelle règle... :

La boîte de dialogue Modifier la règle de mise en forme apparaît alors. Sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

Ensuite, saisissez la formule dans la partie inférieure de la boîte de dialogue :

• Vous pouvez saisir toute sorte de formule, tant que le résultat donne VRAI (1) ou FAUX (0).
• Vous devez saisir la formule comme si elle ne concernait que la première cellule de la plage à mettre en forme, Excel se charge de l'adapter aux autres cellules.

Ici, nous souhaitons savoir si le contenu des cellules B2:B7 est inférieur au seuil de commande saisi en B9. Si nous avions saisi cette formule directement dans la feuille, par exemple dans la plage de cellule C2:B7, nous aurions saisi la formule =B2<$B$9 en C2 puis nous l'aurions recopiée vers le bas.
C'est donc cette formule que nous entrons dans la boîte de dialogue :

Petit rappel sur les adresses des cellules et les références relatives ou absolues : l'utilisation du signe $ permet de figer une ligne et/ou une colonne d'une adresse, et d'éviter ainsi qu'elles ne soient décalées ou incrémentées lors des copier/coller. Pour en savoir plus, lisez ce cours !

Il faut maintenant préciser à Excel le format à utiliser si le résultat de la formule est vrai : cliquez ensuite sur le bouton Format, qui permet d'ouvrir la boîte de dialogue Format de cellule. Dans notre exemple, nous changerons la couleur de fond en orange lorsque le seuil est atteint :

Cliquez sur Ok pour fermer la fenêtre Format de cellule, puis à nouveau sur Ok pour fermer la fenêtre Modifier la règle de mise en forme (ou sur le bouton Appliquer pour voir le résultat sans fermer la fenêtre). Le résultat de la mise en forme apparaît alors à l'écran : 

Comment gérer les règles de mise en forme conditionnelle ?

Nous avons vu dans les sections précédentes les différentes manières de créer des règles de mise en forme conditionnelle.
Il est maintenant temps de voir comment gérer ces règles : comment modifier leur ordre d'application, les copier ou les supprimer.

Accéder à la boîte de dialogue de gestion des règles

Le ruban nous permet d'accéder au Gestionnaire des règles de mise en forme conditionnelle, qui centralise la gestion des règles.

Dans l'onglet Accueil, cliquez sur Mise en forme conditionnelle, puis choisissez Gérer les règles :

La fenêtre du gestionnaire s'affiche alors. Vous y trouverez vous trouverez la liste de toutes les règles qui s'appliquent à la cellule ou la plage de cellule que vous avez sélectionnées :

La première colonne vous indique la règle utilisée (jeu d'icône, barre de données, formule, etc...).

La deuxième vous donne un aperçu du format appliqué lorsque la règle est vraie.

Dans la troisième colonne est précisée la zone à laquelle s'applique la règle.

La quatrième colonne comporte des cases à cocher qui vous permettent d'interrompre l'application des règles suivantes si la règle sélectionnée est vraie.

Par défaut, le gestionnaire fait apparaître les règles applicables à la sélection en cours. Mais il est possible de visualiser l'ensemble des règles applicable à la feuille sélectionnée, ou une autre feuille du classeur, en cliquant sur la liste déroulante Afficher les règles de mise en forme pour :

Modifier une règle de mise en forme conditionnelle

Nous avons vu dans un cours précédent comment modifier une règle de mise en forme conditionnelle, en cliquant sur le bouton Modifier la règle du gestionnaire :

La fenêtre de modification qui apparaît s'adapte à la règle qui a été choisie : elle va varier selon qu'il s'agit de jeu d'icônes, de mise en surbrillance, de plages haute/basse ou de formules, etc...

Voici la fenêtre qui s'affiche en présence d'une mise en forme contionnelle en barre de données :

Détail important : la zone à laquelle s'applique la mise en forme conditionnelle ne peut être modifiée que depuis la fenêtre du gestionnaire. Cliquez sur la ligne de la colonne S'applique à qui vous intéresse, et modifiez en direct la zone (vous pouvez utiliser le bouton pour sélectionner la plage dans la feuille) :

Changer l'ordre des mises en forme conditionnelles

Si plusieurs mises en forme conditionnelles s'appliquent pour la même plage de cellules, vous pouvez changer leur ordre d'application pour faire primer l'une sur l'autre. Pour cela, sélectionnez une ligne et remontez la  ou descendez la dans la liste grâce aux boutons Monter et Descendre (validez en cliquant sur Ok, ou observez le résultat en cliquant sur Appliquer):

Interrompre l'application des règles si l'une d'entre elle est vraie

La case à cocher Interrompre si Vrai permet d'arrêter l'application des règles de formatage à la règle en cours si celle-ci est vraie. De cette manière, les autres règles seront ignorées.

Dans l'exemple qui suit, on souhaite avoir un formatage particulier sur fond vert pour le tiers des produits les plus performants, et un jeu d'icônes pour les deux tiers les moins performants :

Vous trouverez un exemple d'utilisation de cette fonctionnalité dans cet article, qui décrit comment exclure les cellule vides d'une mis en forme conditionnelle.

Supprimer une règle de mise en forme conditionnelle

Si une règle de mise en forme conditionnelle n'est plus nécessaire, il suffit de la retirer du gestionnaire en cliquant sur le bouton Supprimer la règle :

Vous pouvez également utiliser le ruban : cliquez sur Mise en forme conditionnelle, choisissez Effacer les règles. Vous pouvez alors :

• effacer les règles des cellules sélectionnées,
• ou choisir d'effacer les règles de la feuille entière,
• ou choisir d'effacer les règles du tableau si la cellule active est positionnée sur un tableau,
• ou encore choisir d'effacer les règle du tableau croisé dynamique si la cellule active est postionnées sur un tableau croisée dynamique.

Comment copier une mise en forme conditionnelle ?

Vous avez créé un format conditionnelle à une plage, et vous souhaitez pouvoir le réutiliser à un autre emplacement sans avoir à tout refaire ?

Le bouton Reproduire la mise en forme présent dans le bouton (onglet Accueil) vous permettra de réaliser cette opération facilement :

Sélectionnez une cellule qui contient la mise en forme conditionnelle que vous souhaitez copier, puis cliquez sur Reproduire la mise en forme. Sélectionnez ensuite les cellules sur lesquelles vous souhaitez appliquer la mise en forme conditionnelle :

Si vous devez appliquer la mise en forme à plusieurs plages de cellules différentes, double-cliquez sur le bouton Reproduire la mise en forme, au lieu de cliquer simplement dessus. Puis sélectionnez l'une après l'autre les différentes plages auxquelles la mise en forme conditionnelle doit être appliquée. Lorsque vous avez terminé, cliquez sur le bouton Reproduire la mise en forme pour le désactiver, ou appuyez sur la touche Echap.

Comment trouver des cellules avec une mise en forme conditionnelle particulière ?

Cellules avec mise en forme conditionnelle

Il peut être utile parfois de vérifier les celulles disposant de mises en forme conditionnelle (quelle qu'elle soit) dans une feuille de calcul.

Pour cela, allez dans l'onglet Accueil, et cliquez sur le bouton Rechercher et sélectionner (groupe Edition), puis sur Mise en forme conditionnelle :

Cete opération a pour effet de sélectionner toutes les cellules de la feuille de calculs qui se voient appliquer une mise en forme conditionnelle.

Cellules avec une mise en forme conditionnelle identique

Pour trouver les cellules comportant la même mise en forme conditionnelle, cliquez d'abord sur l'une des cellules qui contient la mise en forme qui vous intéresse.

Puis, allez dans l'onglet Accueil, et cliquez sur le bouton Rechercher et sélectionner, puis sur Sélectionner les cellules :

Dans la boîte de dialogue qui apparaît, cochez les cases Formats conditionnels et Identiques, puis validez en cliquant sur Ok :

Toutes les cellules de la feuille de calculs qui se voient appliquer la même mise en forme conditonnelle sont alors sélectionnées.