L'ajout d'une mise en forme conditionnelle dans Excel vous permet d'appliquer différentes options de mise en forme à une cellule ou à une plage de cellules qui répondent aux conditions spécifiques que vous avez définies. La définition de telles conditions peut aider à organiser votre feuille de calcul et faciliter la numérisation. Les options de formatage que vous pouvez utiliser incluent les changements de couleur de police et d'arrière-plan, les styles de police, les bordures de cellule et l'ajout de formatage numérique aux données.
Excel a des options intégrées pour les conditions couramment utilisées telles que la recherche de nombres supérieurs ou inférieurs à une valeur particulière ou la recherche de nombres supérieurs ou inférieurs à la valeur moyenne. En plus de ces options prédéfinies, vous pouvez également créer des règles de mise en forme conditionnelle personnalisées à l'aide de formules Excel.
Ces instructions s'appliquent à Excel 2019, 2016, 2013, 2010 et Excel pour Microsoft 365.
Application de plusieurs conditions dans Excel
Vous pouvez appliquer plusieurs règles aux mêmes données pour tester différentes conditions. Par exemple, les données budgétaires peuvent être associées à des conditions qui appliquent des changements de mise en forme lorsque certains niveaux de dépenses sont atteints, tels que 50 %, 75 % et 100 % du budget total.
Dans de telles circonstances, Excel détermine d'abord si les différentes règles sont en conflit et, si c'est le cas, le programme suit un ordre de priorité défini pour déterminer quelle règle de mise en forme conditionnelle appliquer aux données.
Trouver des données qui dépassent 25 % et 50 % d'augmentation
Dans l'exemple suivant, deux règles de mise en forme conditionnelle personnalisées seront appliquées à la plage de cellules B2 à B5.
- La première règle vérifie si les données dans cellules A2:A5 sont supérieures à la valeur correspondante dans B2:B5 de plus de 25 %.
- La deuxième règle vérifie si les mêmes données dans A2:A5 dépassent la valeur correspondante dans B2:B5 de plus de 50 %.
Comme on peut le voir dans l'image ci-dessus, si l'une des conditions ci-dessus est vraie, la couleur d'arrière-plan de la cellule ou des cellules dans la plage B1:B4 changera.
- Pour les données où la différence est supérieure à 25 %, la couleur d'arrière-plan de la cellule passe au vert.
- Si la différence est supérieure à 50 %, la couleur de fond de la cellule passera au rouge.
Les règles utilisées pour accomplir cette tâche seront saisies à l'aide de la boîte de dialogue Nouvelle règle de formatage. Commencez par saisir les exemples de données dans les cellules A1 à C5 comme le montre l'image ci-dessus.
Dans la dernière partie du didacticiel, nous ajouterons des formules aux cellules C2:C4 qui montrent la différence exacte en pourcentage entre les valeurs dans les cellules A2:A5 et B2:B5; cela nous permettra de vérifier l'exactitude des règles de mise en forme conditionnelle.
Définition des règles de mise en forme conditionnelle
Tout d'abord, nous allons appliquer une mise en forme conditionnelle pour trouver une augmentation significative de 25 % ou plus.
La fonction ressemblera à ceci:
=(A2-B2)/A2>25%
- Mettez en surbrillance cellules B2 à B5 dans la feuille de calcul.
- Cliquez sur l' onglet Accueil du ruban.
-
Cliquez sur l'icône Formatage conditionnel dans le ruban pour ouvrir le menu déroulant.
- Choisissez Nouvelle règle pour ouvrir la boîte de dialogue Nouvelle règle de formatage.
- Sous Sélectionner un type de règle, cliquez sur la dernière option: Utiliser une formule pour déterminer les cellules à formater.
- Tapez la formule notée ci-dessus dans l'espace ci-dessous Formater les valeurs où cette formule est vraie:
- Cliquez sur le bouton Format pour ouvrir la boîte de dialogue. Cliquez sur l' onglet Remplir et choisissez une couleur.
- Cliquez sur OK pour fermer les boîtes de dialogue et revenir à la feuille de calcul.
- La couleur d'arrière-plan des cellules B3 et B5 devrait changer pour la couleur que vous avez sélectionnée.
Maintenant, nous allons appliquer une mise en forme conditionnelle pour trouver une augmentation de 50 % ou plus. La formule ressemblera à ceci:
- Répétez les cinq premières étapes ci-dessus.
- Tapez la formule fournie ci-dessus dans l'espace ci-dessous Formater les valeurs où cette formule est vraie:
- Cliquez sur le bouton Format pour ouvrir la boîte de dialogue. Cliquez sur l' onglet Remplir et choisissez une couleur différente de celle que vous avez choisie lors de la précédente série d'étapes.
- Cliquez sur OK pour fermer les boîtes de dialogue et revenir à la feuille de calcul.
La couleur d'arrière-plan de cell B3 doit rester la même indiquant que la différence en pourcentage entre les nombres dans cells A3 etB3 est supérieur à 25 % mais inférieur ou égal à 50 %. La couleur d'arrière-plan de cell B5 doit changer pour la nouvelle couleur que vous avez sélectionnée indiquant que la différence en pourcentage entre les nombres dans cells A5 et B5 est supérieur à 50 %.
Vérification des règles de mise en forme conditionnelle
Pour vérifier que les règles de mise en forme conditionnelle saisies sont correctes, nous pouvons entrer des formules dans les cellules C2:C5 qui calculeront la différence exacte en pourcentage entre les nombres dans les plagesA2:A5 et B2:B5.
La formule dans la cellule C2 ressemble à ceci:
=(A2-B2)/A2
- Cliquez sur cell C2 pour en faire la cellule active.
- Tapez la formule ci-dessus et appuyez sur la touche Entrée du clavier.
- La réponse 10 % doit apparaître dans cell C2, indiquant que le nombre dans cell A2 est supérieur de 10 % au nombre dans cellule B2.
- Il peut être nécessaire de modifier la mise en forme de la cellule C2 pour afficher la réponse sous forme de pourcentage.
- Utilisez la poignée de remplissage pour copier la formule de cell C2 à cells C3 à C5.
- Les réponses pour cellules C3 à C5 doivent être 30 %, 25 % et 60 %.
Les réponses dans ces cellules montrent que les règles de mise en forme conditionnelle sont exactes puisque la différence entre cellules A3 et B3 est supérieure à 25 pour cent, et la différence entre cellules A5 et B5 est supérieure à 50 pour cent.
Cellule B4 n'a pas changé de couleur car la différence entre cellules A4 et B4 est égale à 25 %, et notre règle de mise en forme conditionnelle spécifiait qu'un pourcentage supérieur à 25 % était nécessaire pour que la couleur d'arrière-plan change.
Ordre de priorité pour le formatage conditionnel
Lorsque vous appliquez plusieurs règles à la même plage de données, Excel détermine d'abord si les règles sont en conflit. Les règles conflictuelles sont celles où les options de formatage ne peuvent pas être appliquées aux mêmes données.
Dans notre exemple, les règles sont en conflit puisque les deux utilisent la même option de formatage - changer la couleur de fond de la cellule.
Dans le cas où la deuxième règle est vraie (la différence de valeur est supérieure à 50 % entre deux cellules), la première règle (la différence de valeur est supérieure à 25 %) est également vraie.
Étant donné qu'une cellule ne peut pas avoir deux arrière-plans de couleurs différentes en même temps, Excel doit savoir quelle règle de mise en forme conditionnelle elle doit appliquer.
L'ordre de priorité d'Excel indique que la règle la plus élevée dans la liste de la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle est appliquée en premier.
Comme indiqué dans l'image ci-dessus, la deuxième règle utilisée dans ce didacticiel est plus élevée dans la liste et, par conséquent, a priorité sur la première règle. Par conséquent, la couleur d'arrière-plan de cell B5 est verte.
Par défaut, les nouvelles règles vont en haut de la liste; pour modifier l'ordre, utilisez les flèches Haut et Bas dans la boîte de dialogue.
Application de règles non conflictuelles
Si deux ou plusieurs règles de mise en forme conditionnelle ne sont pas en conflit, les deux sont appliquées lorsque la condition que chaque règle teste devient vraie.
Si la première règle de mise en forme conditionnelle de notre exemple a formaté la plage de cellules B2:B5 avec une bordure orange au lieu d'une couleur d'arrière-plan orange, les deux règles de mise en forme conditionnelle ne seraient pas conflit puisque les deux formats peuvent être appliqués sans interférer avec l'autre.
Formatage conditionnel vs formatage régulier
En cas de conflit entre les règles de mise en forme conditionnelle et les options de mise en forme appliquées manuellement, la règle de mise en forme conditionnelle a toujours priorité et sera appliquée à la place de toute option de mise en forme ajoutée manuellement.
