La mise en forme conditionnelle Excel permet de garder le contrôle sur les indicateurs clés de votre classeur, d’avoir un accès rapide aux informations mais surtout d’avoir à la fois une vision globale et synthétique de la situation.
Pourquoi Utiliser Une Mise En Forme Conditionnelle ?
La mise en forme conditionnelle Excel consiste à choisir une mise en forme particulière pour certaines données sous une condition spécifiée à l’avance.
Cette fonctionnalité permet d’optimiser la visibilité des données et de faciliter leur analyse et interprétation, de détecter les erreurs de saisie et les valeurs aberrantes, d’alerter en cas de risque ou de situation critique.
L’intérêt de la mise en forme conditionnelle réside également dans le fait qu’elle soit dynamique.
Ce qui veut dire qu'une fois paramétrée, nous n'avons plus besoin d’actualiser à chaque mise à jour de données.
Les Différents Types De Mise En Forme Conditionnelle
La mise en forme conditionnelle met à notre disposition deux possibilités :
Le point commun entre ces deux aspects sont les types de règles à utiliser :
Les Règles De Surbrillance
Cette règle permet de mettre en évidence certaines valeurs par rapport à une valeur seuil qu’on définit nous même, grâce à une mise en forme particulière.
Il existe plusieurs règles de surbrillance possibles :
Supérieur/inférieur à
Grâce à cette règle on peut attirer l’attention sur les cellules contenant les valeurs supérieures à une valeur donnée.
Si par exemple, nous voulons nous concentrer sur les produits de ce tableau de suivi de commandes dont la quantité dépasse les 10 unités.
Pour cela :
Sur la nouvelle fenêtre on renseigne notre valeur seuil et on choisit notre mise en forme :
Les cellules ciblées seront marquées en vert :
On procède exactement de la même façon pour la règle « inférieur à », il suffit juste de choisir une mise en forme différente (rouge):
Entre
Le principe consiste à fixer l’intervalle de valeurs qu’on veut mettre en évidence.
Par exemple, pour repérer les montants de commandes comprises entre 100 et 400, voici ce qu'il faut faire :
Le résultat :
Egal à
Sur un tableau de bord volumineux, si on veut s’intéresser uniquement à un critère précis, la règle "Egal à" est parfaitement adaptée.
Per exemple, si nous voulons nous concentrer seulement sur l’analyse d’une année en particulier, la règle « Egal à » répondra parfaitement à notre besoin car elle permet d’attribuer une mise en forme particulière à toutes les cellules de l’année en question.
La procédure est la suivante :
Il suffit de donner l’année en question, soit l'année 2015.
Choisissez ensuite la mise en forme :
Ainsi toutes les cellules contenant « 2015 » seront mises en évidence :
Texte Qui Contient
Un tableau de bord ne contient pas que des valeurs, il contient souvent du texte.
Pour analyser les informations textuelles , nous pouvons utiliser la règle « Texte qui contient ».
Par exemple, pour mettre en évidence tous les produits en rupture de stock afin de prendre les mesures nécessaires, il suffit de ressortir toutes les cellules contenant " En rupture de stock ".
Les étapes sont :
Saisir le texte à mettre en évidence et choisir la mise en forme:
Ainsi nous pouvons rapidement repérer tous les produits en rupture de stock :
Une Date Se Produisant
Si nous voulons analyser les résultats d’une période donnée, la règle « Une date se produisant » est la plus adaptée.
Pour cela :
Choisissez ensuite la période et créez la mise en forme :
Nous pouvons ainsi repérer facilement les commandes du mois dernier :
Valeur En Double
Partager un tableau de bord entre plusieurs utilisateurs est souvent à l’origine de plusieurs erreurs de saisie.
Et repérer ces erreurs manuellement est long et fastidieux.
La règle « valeurs en double » nous permettra de détecter les erreurs survenues lors de la saisie des références des commandes.
Il suffit de choisir la mise en forme :
Il est facile ainsi de repérer facilement les références en double et de les corriger:
Les Règles Des Valeurs Plus/Moins Elevées
Cette règle est utilisée pour le groupe de valeurs le plus ou moins élevés.
Ce qui veut dire que nous pouvons regrouper soit un nombre de valeurs, un pourcentage de valeurs ou bien les valeurs supérieures ou inférieures à la moyenne.
Dans les exemples que je vais vous montrer, nous allons nous intéresser aux suivis et aux tendance des quantités de commandes.
Pour cela, nous allons utiliser les règles suivantes :
10 Valeurs Les Plus/Moins Élevées
Pour l'utiliser :
Il ne reste plus qu'à sélectionner les valeurs élevées que nous voulons regrouper et de créer la mise en forme adéquate.
Comme résultat, vous verrez les 10 premières valeurs en surbrillance :
C'est exactement la même procédure pour les 10 valeurs les moins élevées.
Il suffit juste de choisir une mise en forme différente.
10% Les Plus/Moins Élevées
Il suffit de choisir le pourcentage des valeurs les plus élevées pour lesquelles on veut attribuer une mise en forme particulière :
C'est aussi le même procédé pour créer la mise en forme :
Voici le résultat :
On procède de la même façon pour les 10 % des valeurs les moins élevés avec évidemment une mise en forme différente.
Valeurs Supérieures/Inférieures à La Moyenne
Cette règle nous permet de regrouper les valeurs supérieures à la moyenne et vous pouvez procéder comme suit :
Personnalisez la mise en forme :
Les valeurs supérieures à la moyenne apparaissent comme suit :
Pour les valeurs inférieures à la moyenne, il suffit de choisir une mise en forme différente.
Les Barres De Données
Cette règle permet de représenter la tendance des données en attribuant à chaque cellule une barre de taille proportionnelle à celle de la valeur contenue dans la cellule.
Cette règle partage les valeurs en trois groupes :
Pour appliquer cette règle, nous allons encore utiliser notre tableau de suivi des ventes des produits.
Voici comment procéder :
Les Nuances De Couleurs
Cette règle est une autre façon de représenter les tendances des valeurs d'un classeur Excel à l’aide d’un dégradé de couleurs.
Les valeurs seront mises en évidence de la plus élevée à la moins élevée grâce à un dégradé de couleurs qui évolue du plus foncé au moins foncé.
pour mettre en place une telle mise en forme, il suffit de sélectionner les données, choisir la règle et les nuances de couleurs que l'on souhaite appliquer :
Les Jeux D’Icônes
Cette règle permet d’attirer l’attention sur la tendance des valeurs à l’aide d’icônes significatives.
Si on choisit de partager des valeurs en trois groupes ou en quatre groupes avec cette règle, chaque groupe sera représentées par des icônes selon leurs valeurs: faible, moins faible, élevée ou plus élevée.
Il est très simple d'appliquer cette règle.
Il suffit de choisir la plage de données correspondante et de spécifier les icônes à utiliser.
Les icônes sont proposés par Excel lui même.
Il suffit de choisir quel jeu d'icônes appliquer :
Visuellement, ça donne un réel impact :
Les Règles Personnalisées
Excel nous procure aussi la possibilité de personnaliser des règles de mise en forme conditionnelle grâce à des formules.
Les situations sont souvent différentes les unes des autres et elles varient selon le domaine d'activités.
Pour ce tuto, je vais appliquer des règles personnalisées pour 3 cas :
Cas N°1 : Mise En Forme Conditionnelle Suite à Une Comparaison
Pour comparer les ventes de 2020 par rapport à celles de l’année précédente.
Et mettre en forme les produits dont les ventes on baissé ainsi que ceux dont les ventes ont augmenté, nous allons procéder comme suit :
Saisir la formule "F3<E3" pour comparer les cellules de la colonne E (qui correspond aux ventes de l'année 2019) avec celles de la colonne F.
Personnaliser ensuite une mise en forme qui sera choisie chaque fois qu’une valeur de la colonne est inférieure à celle de la colonne E :
La mise en forme à appliquer est simple à créer.
Le résultat doit être le suivant :
De la même façon, nous allons saisir la formule « F3>E3 » comme nouvelle règle afin de mettre en évidence les ventes ayant augmenté avec une mise en forme différente de la règle précédente.
Cela aura pour résultat de faire apparaître les ventes ayant augmenté en vert :
Cas N°2 : Mise En Forme Selon Le Contenu D'Une Cellule
Pour tracer les produits en rupture de stock, nous allons définir une nouvelle règle comme ceci :
Saisir la formule "F6=$F$8" pour appliquer une mise en forme spécifique pour toutes les cellules dont le contenu est égal à celui de la cellule F6 qui est égal à "En rupture de stock".
Il suffit ensuite de créer sa propre mise en forme :
En validant, vous verrez tous les produits "En rupture de stock" en surbrillance avec la mise en forme adaptée:
Cas N°3 : Mettre En Évidence Des Dates
Pour détecter les erreurs de saisie, nous pouvons définir une nouvelle règle pour repérer les dates erronées.
Par exemple, nous pouvons vérifier toutes les dates antérieures à la date du jour.
Pour cela :
La formule est la suivante : "B6>Aujourdhui()".
Cette formule permettra de mettre en évidence toutes les dates antérieures à la date du : jour.
Appliquer une mise en forme personnalisée en cliquant sur le bouton "Format" :
Les dates concernées deviennent ainsi facilement repérables.
Et s'il y a une erreur de saisie, la faute sera simple à détecter et à corriger.
Comment Effacer Une Mise En Forme Conditionnelle Excel
Astuce : Mise En Forme Conditionnelle Selon Les Valeurs D’Autres Cellules
Il est possible d'utiliser la mise en forme conditionnelle Excel selon les valeurs d’autres cellules.
Cette manipulation a l'avantage de personnaliser votre classeur avec une très grande liberté.
Pour l'exemple que je vais vous montrer, nous allons mettre en forme les quantités de commandes lorsque les produits sont en rupture de stock.
Voici comment faire :
Saisir la formule "F6=F$20$" ou "F6=0" (si vous voulez que le seuil soit zéro), définir le format de la mise en forme à appliquer puis valider :
Au final, vous verrez les quantités de produits dont le stock doit être alimenté.
Conclusion
La mise en forme conditionnelle Excel est une fonctionnalité riche en termes de possibilités.
Si les règles prédéfinies sont pratiques et très faciles à mettre en place, les règles personnalisées ne sont pas plus compliquées du moment qu’on maîtrise à un certain niveau le paramétrage des formules.
