Exemples d’utilisation de la mise en forme conditionnelle

Dans cet article de la formation Excel, vous allez voir quelques exemples d’utilisation de la mise en forme conditionnelle.
quelques exemples d’utilisation de la mise en forme conditionnelle


Utiliser le bouton d’analyse rapide (Excel 2013/2016)

Excel vous aide à appliquer rapidement une mise en forme conditionnelle à votre plage de cellules pour mettre en évidence vos données intéressantes à partir du bouton d’analyse rapide.

Lorsque vous sélectionnez votre plage de cellules, le bouton d’analyse rapide s’affiche en bas de la sélection.

Cliquez sur ce bouton et sélectionnez sous l’onglet Mise en forme, le type de mise en forme à appliquer: Barre de données, Echelle de couleurs, jeux d’icônes, Règles de mise en surbrillance des cellules …

Utilisation du bouton d'analyse rapide


Note : Dans la galerie Analyse rapide vous remarquez qu’il y a d’autres outils qu’on peut utiliser : Graphiques, Tableaux, Totaux.

Afficher ou masquer le bouton d’analyse rapide

  • Cliquez sur Fichier puis sur Options.
  • Cochez la case Afficher les options d’analyse rapide lors de la sélection qui se trouve sous la catégorie Options d’interface utilisateur.
Afficher le bouton d'analyse rapide

  • Et pour masquer le bouton d’analyse rapide décochez-la.
  • Cliquez sur OK enfin !!

Afficher les barres de données uniquement

Bares de données - Autres règles

  • Dans la boite de dialogue Nouvelle règle de mise en forme, cochez la case Afficher la barre uniquement.
  • Puis sous Apparence de la barre sélectionnez les couleurs pour la barre de données.
Afficher la barre uniquement

  • Cliquez sur OK

Vous pouvez faire une autre chose aussi, c’est d’ajouter une autre colonne contenant les mêmes valeurs de la colonne que vous voulez mettre en forme.
Une colonne va afficher les valeurs et l’autre va afficher uniquement les barres de données.
  • Dans l’exemple suivant nous allons copier les valeurs de la colonne Populations dans la colonne de droite C.
  • Sélectionnez donc la cellule C2 et tapez =B2 puis validez.
  • Copiez la formule dans toutes les autres cellules de la même colonne.
Formule pour copier des valeurs

  • Sélectionnez maintenant la deuxième colonne et appliquez la mise en forme en ajoutant des barres de données uniquement en suivant les étapes décrites en dessus.
Valeurs et barres de données


Note : Vous pouvez aussi afficher uniquement les jeux d’icônes en masquant les valeurs de vos cellules. A vous de le faire ;)

Détectez les erreurs dans votre tableau

Il se peut que votre tableau affiche des erreurs, en appliquent des calculs, tels que #DIV/0!, #VALEUR ! …. et que vous voulez les découvrir en appliquant une mise en forme conditionnelle.
  • Sélectionnez votre tableau entier et cliquez sur Mise en forme conditionnelle puis sur Nouvelle règle.
  • Dans la boite de dialogue Nouvelle règle de mise en forme, sélectionnez Appliquez une mise en forme uniquement aux cellules qui contiennent.
  • Dans la zone Modifier la description de la règle sélectionnez Erreurs.
  • Cliquez ensuite sur le bouton Format et choisissez une couleur de remplissage. (Rouge par exemple)
  • Validez enfin.
Detecter les erreurs

  • Les cellules contenant des erreurs seront donc coloriées de rouge.
Erreur détéctée


Appliquer un ombrage à des lignes en alternance

Dans l’article style des tableaux, vous avez vu comment créer des bandes de lignes en alternance en appliquant un style de tableau. Vous pouvez aussi appliquer un ombrage aux lignes de votre tableau en alternance en utilisant l’outil mise en forme conditionnelle.
Pour ce faire, voici deux procédures à suivre :

La première :

  • Dans l’exemple suivant, sélectionnez la plage de cellules de A2 à D19, et cliquez sur Mise en forme conditionnelle puis sur Nouvelle règle.
  • Sélectionnez Utiliser une formule pour déterminer pour quelle cellule le format sera appliqué et tapez la formule suivante : =MOD(LIGNE();2)
Colorier des lignes en altérnance

  • Cliquez ensuite sur le bouton Format pour choisir une couleur de remplissage et validez enfin.
Ombrage de lignes en altérnance


La deuxième :

  • Suivez les mêmes étapes précédentes et tapez la formule suivante : =IMPAIR(LIGNE())=LIGNE() puis choisissez une couleur de remplissage.
La fonction IMPAIR

  • Répétez les mêmes étapes encore et insérez cette fois la formule : =PAIR(LIGNE())=LIGNE() puis sélectionnez une couleur différente de remplissage.
Le fonction PAIR

  • Vous aurez un résultat comme ceci :
Colorier des lignes en altérnance en utilisant des fonctions Impair et Pair


Trouver des valeurs en double

Nous avons traité ce sujet dans l’article Utilisation des règles de mise en forme prédéfinies, en utilisant l’option Valeurs en doubles. Cependant vous pouvez utiliser des formules pour rechercher les valeurs qui ne sont pas uniques en leur appliquant une mise en forme.
  • Dans l’exemple suivant, sélectionnez la plage de cellules A2:D19
  • Créez une nouvelle règle et tapez la formule suivante : =NB.SI($A$2:$D$19;$A2)>1
  • Nous avons défini ici uneréférence absolue pour la plage de cellules $A$2:$D$19 et aussi nous avons fixé la colonne A, parce que nous voulons chercher seulement les valeurs en doubles dans cette colonne.
Trouver des valeurs en double dans une colonne

  • Sélectionnez ensuite une couleur de remplissage en cliquant sur le bouton Format.
  • Validez lorsque vous aurez terminé.
Résultat des valeurs en double


Supposons maintenant que toutes les valeurs d’une ligne sont répétées dans une autre ligne de votre tableau et que vous voulez la chercher en appliquant une mise en forme conditionnelle.
  • Commencez par copier les données de la première ligne de votre tableau dans la cellule E2 en utilisant la formule suivante : =A2&B2&C2.
  • Copiez la formule dans le reste des cellules de E3 à E19
Concatener des valeurs

  • Sélectionnez à présent la plage de cellule A2 :C19 puis cliquez sur Nouvelle règle.
  • Tapez la formule suivante : =NB.SI($E$2:$E$19;$E2)>1 et choisissez la mise en forme à appliquer.
Détecter des valeurs en double en utilisant NBSI

  • Dans l’image qui suit, vous allez voir ce qui se passe lorsque nous répétons la saisie des valeurs d’une ligne entière.
Exemple de detection des valeurs en double


Rechercher un jour précis de la semaine

Si la colonne de votre tableau contient une liste des dates et que vous voulez rechercher toutes les dates du week-end dans cette colonne en appliquant une mise en forme conditionnelle, utilisez la fonction JOURSEM comme règle de mise en forme.
  • Dans l’exemple suivant, voyons comment fonctionne la fonction JOURSEM
  • Tapez dans la cellule C2 : =JOURSEM(A2 ;
  • Excel vous affiche une liste des chiffres et que chaque chiffre désigne un ordre des jours de la semaine.
Utilisation de la fonction JOURSEM

  • Par exemple le chiffre 2 renvoie l’ordre suivant :
    • 1 lundi
    • 2 mardi
    • 3 mercredi
    • 4 jeudi
    • 5 vendredi
    • 6 samedi
    • 7 dimanche
  • Donc si j’écris la fonction comme ça : =JOURSEM(A2 ;2), Excel renvoie 4 pour la date de la cellule A2, c’est-à-dire le 4ème jour (Jeudi).
Chiffre renvoyé par la fonction JOURSEM

  • Revenons maintenant à notre cas pour appliquer une mise en forme à des cellules qui contiennent des dates du week-end.
  • Créez alors une nouvelle règle et tapez la fonction suivante :  =JOURSEM(A2;2)>=6 puis choisissez la mise en forme à appliquer en cliquant sur le bouton Format.
Mise en forme conditionnelle pour trouver les dates des week-ends

  • Alors si le chiffre renvoyé par la fonction est 6 ou 7 dans l’ordre des jours défini(2), la cellule sera mise en forme.
  • Et bien sûr le 6ème et le 7ème jour sont Samedi et Dimanche.
  • Voici donc le résultat obtenu.
Jours de week end mis en forme



Utiliser une formule en tant que règle de mise en forme conditionnelle

Nous arrivons aujourd’hui dans cette formation Excel au cinquième article de la série Mise en forme conditionnelle, et avec cette leçon qui va vous expliquer comment utiliser une formule en tant que règle de mise en forme conditionnelle.
comment utiliser une formule en tant que règle de mise en forme conditionnelle



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


  • Sélectionnez en premier votre plage de cellules puis cliquez sur Mise en forme conditionnelle puis sur Nouvelle règle.
Commande Nouvelle règle

  • Dans la boite de dialogue Nouvelle règle de mise en forme, sélectionnez : Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Saisie de la formule

  • Cliquez ensuite dans la zone de texte : Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie.
  • Et tapez votre formule donc.
Mais veuillez tout d'abord noter ceci :

  • Comme s’est écrit dans le titre de la zone de saisie de formule, cette dernière doit renvoyer une valeur logique vraie en testant une condition donnée.
  • La formule ne doit pas renvoyer d’erreur pour que la mise en forme conditionnelle soit appliquée.
  • Vous pouvez soit entrer une formule personnelle soit utiliser des fonctions logiques comme Si, Et, Ou, Non
  • Vous devez avoir une idée sur l’utilisation des formules.
Voilà, essayons maintenant de voir un exemple de formule que nous allons utiliser pour savoir si elle renvoie une valeur (Vrai ou Faux).

  • Dans le tableau suivant, la colonne Qte_St contient des quantités de stock pour chaque produit.
Tableau quantité en stock

  • Nous voulons donc savoir quels sont les produits qui ont une quantité épuisée c-à-d inférieure à 10.
  • Pour cela, nous allons entrer la formule suivante dans la cellule : C2

=B2<10

Exemple de formule


  • Une formule commence toujours par le signe =
  • B2<10 signifie que si la valeur dans la cellule B2 est inférieure à 10, Excel affichera Vrai, si non Excel va afficher Faux.
  • Lorsque nous tapons Entrée pour valider la formule, nous obtenons le résultat suivant :
Valeur fausse

  • Nous constatons donc que la formule renvoie une valeur vraie ou fausse.
  • Nous allons copier la même formule dans toutes les cellules qui restent...
Copier une formule


  • Et vous remarquerez donc que les cellules qui ont des quantités épuisées sont B4, B7, B8, B10, B14.

Cellules renvoyant valeur Vrai

Cette formule donc, nous allons l’entrer dans la zone Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie pour mettre en forme les cellules de la colonne Qte_St qui renvoient la valeur logique (Vrai).

Règle utilisant une formule



  • Nous allons cliquer après sur Format.
  • Sous l’onglet Police, nous choisissons Gras et la couleur Rouge foncé.
Choix de style et de couleur de police

  • Sous l’onglet Remplissage, nous sélectionnons la couleur Orange.
Couleur de remplissage

  • Nous validons en fin.
  • Vous voyez alors que ce sont les mêmes cellules trouvées dans notre test (B4, B7, B8, B10, B14) qui sont mises en forme.
Mise en forme des cellules contenant des quantités inférieures à 10

Utilisation de la fonction ET

Vous allez voir dans cet exemple comment utiliser la fonction Et pour mettre en forme des cellules où deux conditions sont remplies.
Essayez premièrement de faire un test de votre formule pour vous assurer qu’elle ne renvoie pas d’erreur.

  • Dans le tableau suivant, on veut mettre en forme la cellule qui contient le nom du produit dont le nom du pays est Allemagne et pour lequel le montant réalisé est supérieur à 1000 euros.
  • Sélectionnez donc la cellule E2 par exemple et tapez = suivi de ET et d’une parenthèse ouvrante " ( "
Début d'insertion de la fonction ET

  • Excel vous demande de taper la première valeur logique, écrivez donc : B2= "Allemagne" puis tapez  " ; "
  • Ecrivez la deuxième valeur logique : D2>1000.
  • Tapez maintenant une parenthèse fermante " ) " et tapez enfin la touche Entrée sur le clavier.
Utilisation de la fonction ET

  • Excel affiche Faux, car seulement une condition qui est remplie : nom du pays est Allemagne. Pour le montant, il est inférieur à 1000 euros.
  • Copiez alors la formule dans toutes les autres cellules de E3 à E19
  • Vous remarquez qu’une seule cellule qui affiche la valeur (Vrai), c.-à-d. que dans ce cas les deux conditions sont remplies. 
Cellule C8 répondant aux critères de la règle saisie

  • Le produit concerné est donc Céréales qui se trouve dans la cellule C8.
Appliquez maintenant une mise en forme conditionnelle en utilisant cette formule.

=ET(B2="Allemagne";D2>1000)


Utiliser la fonction ET comme règle

  • Cliquez sur le bouton Format et choisissez le vert comme couleur de remplissage et validez en fin.
  • La cellule C8 est coloriée donc en vert.
Resultat Fonction ET appliquée

Utilisation de la fonction OU


  • Remplacez « ET » par « OU » dans la formule précédente et appliquez une mise en forme.
  • Vous remarquez que plusieurs cellules sont mises en forme.
Utilisation de la fonction OU comme règle de mise en forme

  • En effet la fonction OU renvoie une valeur vraie si une seule condition au moins est remplie.

Appliquer une mise en forme conditionnelle à la ligne entière

Si vous aimez que toute la ligne qui contient la cellule qui répond à la règle de mise en forme appliquée en utilisant une formule, suivez la démarche suivante :
  • Dans l’exemple de l’utilisation de la fonction ET, sélectionnez tout d’abord toutes les cellules de votre tableau (sauf bien sûr la ligne d’entête ;))
  • Tapez la formule =ET(B2="Allemagne";D2>1000) comme vous l’avez fait précédemment.
  • Ajoutez maintenant le signe $ devant la lettre B et devant la lettre D.
  • Choisissez le format désiré et validez.
  • Voilà donc Excel met en forme la ligne 8 de votre tableau.
L’article prochain va traiter différentes utilisations de la mise en forme conditionnelle.

Gérer les règles de mise en forme conditionnelle

Voici le quatrième article de la Formation Excel sur l’utilisation de la mise en forme conditionnelle, dans lequel vous allez découvrir comment gérer les règles de mise en forme conditionnelle : Modification, suppression… avec une solution importante pour supprimer toutes les règles de mise en forme conditionnelle de votre CLASSEUR !
Gèrer les règles


Gérer les règles de mise en forme conditionnelle

Si votre classeur contient une ou plusieurs mises en forme conditionnelles, vous pouvez les modifier ou les gérer à partir de la boite de dialogue : Gestionnaire des règles de mise en forme conditionnelles.
Et pour afficher cette boite, veuillez tout d’abord sélectionner une cellule de la plage de cellules où est appliquée une mise en forme conditionnelle.
Ensuite, cliquez sur Mise en forme conditionnelle puis sur Gérer les règles.

Commande gérer les règles


Voilà donc la boite de dialogue Gestionnaire des règles de mise en forme conditionnelles est affichée.

Gestionnaire des règles de mise en forme conditionnelle


Elle contient alors plusieurs options, dont la première est la liste des mises en forme conditionnelles appliquées.

Liste des règles


Étudions donc les autres composants de cette boite :

Afficher les règles de mise en forme pour

Cette option affiche le choix Sélection actuelle, c’est-à-dire la cellule ou la plage de cellules que vous avez sélectionnée avant de faire afficher cette boite.

Et en bas vous avez toutes les mises en forme appliquées à votre sélection.

Cliquez sur la liste déroulante pour voir les autres choix :

Afficher les règles de mise en forme pour

  • Cette feuille de calcul : vous permet d’afficher toutes les mises en forme conditionnelles appliquées à la feuille en cours de sélection.
  • Feuille-Nom feuil : ce choix vous permet d’afficher les mises en forme conditionnelles appliquées à une feuille de votre classeur.


Nouvelle règle

Bouton Nouvelle règle

Vous avez aussi la possibilité de créer d’autres mises en forme en cliquant sur ce bouton. Pour plus de détails lisez cet article : Créer une nouvelle règlede mise en forme conditionnelle.

Modifier la règle

Bouton Modifier les règles

Avant de cliquer sur ce bouton pour modifier une mise en forme conditionnelle appliquée, sélectionnez-là en premier.

Excel va vous afficher la boite de dialogue de la création d’une nouvelle règle que vous avez utilisé lors de l’article précédent. Faites donc vos modifications et validez pour revenir à votre boite de gestion des règles.

Supprimer la règle

Bouton Supprimer la règle

Sélectionner la mise en forme conditionnelle ou la règle désirée puis cliquez sur ce bouton. Votre règle sera effacée.

Les flèches Monter et Descendre

Les flèches Monter et desscendre

Vous permettent de changer l’ordre d’exécution des règles. Sélectionnez la règle que vous voulez qu’elle s’exécute la première puis cliquez sur la flèche Monter pour la positionner avant les autres règles de la liste.

Dans l’exemple suivant nous avons deux règles appliquées sur des dates de RDV : la première est Dates se produisant cette semaine et nous avons choisi la couleur rouge comme mise en forme, et quant à  la deuxième c’est Dates se produisant Demain avec une couleur verte pour colorier ses cellules.

Exemple de deux règles appliquées


Vous remarquez que la mise en forme appliquée pour la deuxième règle n’est pas visible… dans ce cas nous devons donner la priorité à cette règle.
Sélectionnez donc la deuxième règle Dates se produisant Demain et cliquez sur Monter puis cliquez sur OK.

Utiliser les flèches Monter et descendre


Voilà donc, nous pouvons distinguer les lignes des RDV pour Demain.

Interrompre si vrai

Dans l’exemple suivant, nous avons appliqué trois règles de mise en forme conditionnelles qui seront exécutées dans l’ordre suivant :

Trois règles appliquées


R1 : Si la cellule contient un montant inférieur à 50000 (Condition vraie) alors elle sera coloriée en jaune, si non, c.-à-d. si elle contient un montant supérieur à 50000 (Condition fausse), aucune mise en forme ne sera appliquée.

R2 : Si la cellule contient un montant supérieur à 80000 (Condition vraie), elle sera remplie d’une couleur rouge. Et si la cellule contient un montant inférieur à 80000 (Condition fausse) elle ne sera pas mise en forme.

R3 : Mettre en forme toutes les cellules en affichant des barres de données pour différencier les valeurs les plus élevées des plus basses.

Lorsque ces règles sont appliquées, vous verrez que votre plage de cellules est remplie de couleurs d’une façon gênante et que vous ne pouvez pas analyser vos donner. (mélange de couleurs et barres de données ..)

Chauveauchement des règles


Alors, si vous voulez par exemple, voir les cellules où la première règle est remplie, cochez sa case Interrompre si vrai.

Interompre si vrai


Excel va donc colorier en jaune les cellules qui répondent à cette règle et va ignorer les autres mises en forme appliquées aussi à ces cellules. Ici sont les barres de données qui ne seront pas affichées.

Et puisque les autres cellules ne sont pas coloriées en jaune parce que leurs montants ne sont pas inférieurs à 50000 (la condition est fausse) alors Excel n’élimine pas les mises en forme appliquées à ces cellules et il va donner la priorité à la deuxième règle puis à la troisième.

Résultat Interompre si Vrai


Et si vous cochez les deux cases Interrompre si vrai pour les deux premières règles, Excel va masquer les barres de données dans les cellules qui répondent à ces deux règles.

Interompre si Vrai deux règles

Voici le résultat obtenu :

Résultat Interompre si Vrai deux règles


S’applique à

Affiche la référence de la plage de cellules où s’est appliquée une règle de mise en forme conditionnelle. Vous pouvez donc modifier cette plage de cellules en cliquant sur le bouton Réduire la boite de dialogue et en sélectionnant ensuite la nouvelle plage.

Option s'applique à


Supprimer les règles de mise en forme conditionnelle

  • Vous avez vu en haut comment supprimer une règle à partir de la boite de dialogue Gestionnaires des règles.
  • Vous pouvez aussi supprimer ces règles de mise en forme de deux autres manières :
  • Cliquez sur le bouton Mise en forme conditionnelle, puis sélectionnez Effacer les règles :
    • Si vous voulez supprimer vos règles d’une cellule ou des cellules de votre plage de cellules, sélectionnez-la (sélectionnez-les) et cliquez sur: Effacer des règles des cellules sélectionnées
Suprimer les règles des cellules sélectionnées

    • Si vous voulez supprimer toutes les règles de votre feuille de calcul, cliquez sur Effacer des règles de la feuille entière.
Supprimer les règles de la feuille entière


Supprimer toutes les règles de mise en forme de votre classeur

  • Vous pouvez le faire à l’aide d’une simple Macro
  • Sous l’onglet Développeur et dans le groupe Contrôles, cliquez sur Insérer puis sur Bouton, tracez ensuite le bouton sur votre feuille de calcul.
Insérer un bouton de commande

  • Dans la boite de dialogue Affecter une macro, cliquez sur Nouvelle
  • Collez le code suivant:

                   Application.ScreenUpdating = False
                   For Each TmpSht In ThisWorkbook.Sheets
                      TmpSht.Cells.FormatConditions.Delete
                   Next
                   Application.ScreenUpdating = True


Insertion du code VBA


  • Tapez Alt+F11 pour revenir en mode affichage Excel
  • Cliquez enfin sur votre bouton ajouté
Supprimer les règles du classeur entier

  • Toutes les mises en forme seront supprimées de votre classeur.
L'article prochain sera consacré à la création des nouvelles règles en utilisant des formules.