Utiliser la mise en forme conditionnelle d’Excel

Utiliser la mise en forme conditionnelle d’Excel

Suite à la demande d’un abonné, nous allons découvrir la mise en forme conditionnelle sous Excel qui vous permet de visualiser d’un seul coup d’œil vos données.

Pour ce tutoriel, je vais utiliser un extrait de mon tableau de stock. Vous retrouverez ce fichier Excel dans l’Espace membres. Plusieurs règles de mise en forme étant possibles, je vais vous montrer ces différentes possibilités.

Pour commencer, je vais utiliser les Règles de surbrillance des cellules et les Règles des valeurs plus ou moins élevées. Je sélectionne l’ensemble des cellules de la colonne Stock sans sa ligne d’en-tête et, dans l’onglet Accueil, je clique sur Mise en forme conditionnelle Mise en forme conditionnelle d’Excel.

Dans Règles de surbrillance des cellules, je peux choisir de mettre en surbrillance les valeurs supérieures à une valeur donnée. Je clique sur Supérieur à… Supérieur à et, dans la boîte de dialogue qui s’affiche, je saisis la valeur de référence ou je clique sur une cellule (cela permettra de modifier la valeur de référence très rapidement si elle se trouve dans votre classeur) :

Boîte de dialogue Supérieur à

J’indique la mise en forme à appliquer (soit grâce aux mises en forme proposées par défaut, soit en cliquant sur Format personnalisé et en indiquant le format désiré) :

Boîte de dialogue Format personnalisé

Avant de cliquer sur Ok, vous pouvez déjà voir si le résultat vous convient. Cliquez sur Ok pour valider la mise en forme conditionnelle.

Si vous modifiez votre valeur de référence ou si vous ajoutez ou modifiez une donnée de la colonne Stock, votre mise en forme conditionnelle se mettra à jour automatiquement. Cette mise à jour des mises en forme et le fait de pouvoir personnaliser le format dans lequel mettre les données répondants à la condition fonctionnent pour toutes les autres mises en forme conditionnelles.

Avec les autres Règles de surbrillance des cellules, vous avez :

  • Inférieur à… qui fonctionne de la même manière que Supérieure à… mais dans le sens inverse ;
  • Entre… qui vous permet de mettre en surbrillance les cellules comprises entre 2 valeurs ;
  • Égal à… vous permet de mettre en surbrillance uniquement les valeurs égales à la valeur de référence ;
  • Texte qui contient… s’adresse plutôt à du texte, mais si vous utilisez des nombres, il fonctionne un peu comme Égal à… à la différence que cela ne prend pas la totalité de la cellule. C’est-à-dire que si vous saisissez 25, Excel mettra en surbrillance les 25 mais aussi les 250, les 125…tout ce qui contient 25. Alors que si j’utilise Égal à…, ce sera uniquement les cellules contenant 25. C’est la seule possibilité qui vous permet de saisir du texte car les autres n’acceptent que les valeurs numériques ;
  • Une date se produisant… permet de mettre en surbrillance des dates, c’est-à-dire selon les valeurs indiquées par défaut par Excel (hier, aujourd’hui, demain, cette semaine, le mois prochain…). Pour indiquer des valeurs différentes, utilisez plutôt la ligne Entre… en indiquant les dates ;
  • Valeurs en double… permet de détecter les doublons ou d’isoler les valeurs qui ne sont pas en double si vous choisissez Uniques à la place d’En double.

Dans Règles des valeurs plus ou moins élevées, à la différence de Règles de surbrillance des cellules où vous n’étiez pas obligé de sélectionner une plage de cellules mais où vous pouviez appliquez une mise en forme conditionnelle sur une seule cellule, sauf pour les Valeurs en double, vous allez devoir sélectionner plusieurs cellules puisque les mises en forme proposées se calculent selon une plage. Dans le cas contraire, votre mise en forme ne sera pas significative. Vous avez :

  • 10 valeurs les plus élevées… met en surbrillance les 10 valeurs les plus élevées de votre plage de cellules. Vous avez la possibilité de choisir plus ou moins de 10 valeurs ;
  • 10 % les plus élevé(e)s… met en surbrillance 10 % des valeurs les plus élevées. Vous avez aussi la possibilité d’indiquer plus ou moins de 10 % ;
  • 10 valeurs les moins élevées… fonctionne de la même manière que les 10 valeurs les plus élevées, mais pour les moins élevées ;
  • 10 % les moins élevé(e)s… fonctionne de la même manière que les 10 % les plus élevé(e)s, mais pour les moins élevées ;
  • Valeurs supérieures à la moyenne… met en surbrillance toutes les valeurs supérieures à la moyenne de votre plage de cellules ;
  • Valeurs inférieures à la moyenne… fonctionne de la même manière que la fonction précédente, mais pour les valeurs inférieures.

Dans les mises en forme conditionnelles, vous avez aussi :

  • Barres de données Barres de données vous permet d’afficher une barre en dégradé ou de couleur unie selon la valeur contenue dans la cellule. Plus la valeur est grande, plus la barre sera longue. La valeur la plus grande de votre plage de cellules sera celle contenant la barre la plus longue (celle qui occupera la cellule entière). Si vous avez une trop grande fourchette, il se peut que certaines barres n’apparaissent pas. Cette fonction vous permet de comparer des valeurs sans avoir besoin de créer un graphique ;
  • Nuances de couleurs Nuances de couleurs fonctionne de la même manière que les barres de données mais en utilisant des nuances de couleurs à la place de barre de couleurs. C’est-à-dire que la cellule sera entièrement d’une couleur et les couleurs seront nuancées pour différencier les valeurs ;
  • Jeux d’icônes Jeux d’icônes fonctionne de la même manière que les Barres de données et les Nuances de couleurs, mais avec des icônes, toujours en rapport avec l’ensemble de la plage de cellules.

Vous avez ensuite la possibilité de créer une nouvelle règle directement en cliquant sur Nouvelle règle… Nouvelle règle.

Nouvelle règle de mise en forme

Effacer les règles Effacer les règles vous permettra d’effacer les règles des cellules sélectionnées, de la feuille entière ou du tableau simple ou croisé dynamique.

Enfin, Gérer les règles… Gérer les règles  vous permet d’effacer certaines règles et de modifier la façon d’afficher les mises en forme conditionnelle.

Nous allons terminer par modifier cet affichage. Commencez par choisir quelles règles afficher grâce au menu déroulant. Pour cet exemple, je choisis un jeu d’icônes pour la plage sélectionnée (celui des indicateurs, sans cercle Indicateurs, sans cercle) et clique sur Gérer les règles. Si vous avez plusieurs règles, sélectionnez celle à modifier et cliquez sur Modifier la règle.

Dans cette boîte de dialogue, je vais indiquer les fourchettes pour appliquer les mises en forme. Je peux modifier la mise en forme ici aussi, choisir un autre jeu d’icônes ou un autre style de mise en forme, inverser l’ordre des icônes ou afficher uniquement l’icône (dans ce cas, il n’y aura plus de nombre visible dans la cellule, mais seulement dans la barre de formule).

Puis, j’indique les fourchettes, sachant que je peux choisir une autre icône provenant d’un autre jeu d’icônes pour une fourchette, ce qui rendra votre style d’icône personnalisé. Commencez d’abord par indiquer le Type car la Valeur change.

Pour la coche verte Coche verte, j’indique 200 comme Valeur et Nombre pour le Type.

Pour le point d’exclamation orange Point d’exclamation orange, j’indique 50 comme Valeur et Nombre pour le Type.

La dernière icône Croix rouge s’adapte selon vos paramètres.

Cliquez sur Ok et vous aurez votre mise en forme conditionnelle.

Modifier la règle de mise en forme

Dans le gestionnaire des règles, vous pouvez aussi créer une Nouvelle règle et Supprimer la règle en surbrillance.

Pour terminer, sachez que, si vous appliquez plusieurs mises en forme conditionnelles à une cellule, vous pouvez modifier la priorité. Pour cela, utilisez les flèches haut Flèche haut priorité et bas Flèche bas priorité du gestionnaire pour monter ou descendre une règle.

Voici un dernier exemple, je conserve le jeu d’icônes paramétré et j’ajoute une règle de mise en surbrillance pour les valeurs supérieures à 200. Cette règle s’ajoute au-dessus de celle du jeu d’icônes dans le gestionnaire et, si je laisse les choses ainsi, j’ai par exemple, la cellule B104 qui contient à la fois une icône et est en rouge. Pour n’appliquer qu’une seule des 2 règles, je coche Interrompre si vrai.

Gestionnaire des règles de mise en forme conditionnelle d’Excel

Pour voir le résultat sans quitter le gestionnaire, je clique sur Appliquer, sinon Ok. Comme la cellule B104 contient une valeur supérieure à 200, la règle est VRAIE et le processus de mise en forme conditionnelle s’arrête pour cette cellule et continue pour les autres cellules ne répondant pas à cette règle.

Téléchargez « Utiliser la mise en forme conditionnelle d’Excel » au format PDF

Je vous propose un fichier annexe dans l’Espace Membres contenant le fichier utilisé dans ce tutoriel.

Newsletter

24 réactions au sujet de « Utiliser la mise en forme conditionnelle d’Excel »

  1. Merci. C’était vraiment une idée géniale de partager votre expérience avec les autres. Ce sont des cours précis et clairs. Je peux vous aider au niveau des connaissances concernant Excel et Word…

  2. Bonsoir,
    Je voudrais vous contacter si c’est possible par téléphone pour avoir des informations sur Excel 2007.
    Actuellement, je suis en train de réaliser un tableau de ventes d’immeubles avec plusieurs apparts, avec menu déroulant et calculs de surface pondération et calculs de pourcentage selon orientation immeuble et selon hauteur étage de l’immeuble.

    Merci par avance.

    Cordialement.

    Tony

  3. Bonjour Lydia,
    Sur des fichiers Excel comportant de nombreuses lignes, j’utilise une mise en forme conditionnelle simple et ces quelques lignes de codes à entrer dans le développeur (VBA). Ceci me permet de colorer à l’écran la ligne complète lorsque je passe sur une cellule.
    Pour surligner la ligne, quand on est dans une cellule :
    – Sélectionner la zone
    – Format/Mise en forme conditionnelle : =LIGNE()=CELLULE("ligne")
    – Choisir la couleur de fond
    – VBA :
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
    End Sub

  4. Bonsoir,
    J’aime bien le mini-guide Office et ça m’a donné des idées pour les supports que je suis en train de créer pour l’ECDL niveau avancé. Ceux du niveau de base étant déjà fait.
    J’avoue qu’étant Expert ECDL, de temps à autre, j’ai besoin d’astuces plus pointues.
    En tout cas, super site ! Bravo.

  5. Tu m’as sauvé la vie !!!! Merci beaucoup, très clair et pertinent, pile ce qu’il me fallait alors que ça fait une semaine bientôt que j’y travaille !

  6. J’aimerais savoir comment je colore ma ligne en jaune si la cellule qui se situe en colonne I est égale à OK.
    Je sais que c’est dans mise en forme conditionnelle, mais je ne trouve pas la formule.
    Merci d’avance.

  7. Bonjour Lydia,
    J’ai un souci avec la mise en forme conditionnelle qui est le suivant :
    Sur une feuille de calcul, selon le tableau dans lequel je suis, j’ai fait en sorte que les données utilisées pour réaliser un calcul précis soient mises en surbrillance. Par contre, lorsque je rajoute une ou plusieurs lignes, la règle ne se met pas à jour pourquoi cela ?

    1. Bonjour,

      J’ai le même problème auquel je n’ai pas trouvé de solution malheureusement. La plage de la mise en forme conditionnelle s’écrit automatiquement avec des $, ce qui fige la plage. À chaque fois que j’ajoute des lignes, je mets à jour la plage. Toutefois, cela ne le fait par pour tous, certains tableaux se mettent à jour tout seuls, cela doit être dû aux calculs, je pense (pas de problèmes avec les valeurs en double par exemple).

  8. Bonjour Lydia,

    Merci pour tous ces superbes tutos bien techniques. Je connaissais assez bien la MFC, seulement là, lorsque j’insère une ligne de facturation pour faire le suivi des règlements (avec toutes les bonnes formules qui vont avec), l’ordre des mises en forme conditionnelles sur la partie « solde_creance » se mélange, voire se copie.

    Je n’arrive donc pas à maintenir l’ordre de ces règles et je cherche inlassablement une réponse, mais en VBA, pour directement l’intégrer à ma macro « création d’une nouvelle ligne de facture ». Ainsi, je n’aurai plus de problème.

    As-tu une idée ? Voici mon problème :
    …. A – B – C – D
    1 100,00 € – 0,00 € – 100,00 €
    2 100,00 € – 50,00 € – 50,00 €
    3 100,00 € – 100,00 € – 0,00 €
    4 100,00 € – 100,00 € – 0,00 € – x

    SI C = A ALORS EN ROUGE
    SI C < A ALORS ORANGE
    SI C = 0 ALORS VERT
    SI C = 0 ET D ="x" ALORS BLANC

    Un grand merciiiiiii 🙂

    1. Bonjour Grégoire,

      Je ne comprends pas votre problème de MFC qui se mélange. Dans un de mes fichiers, j’ai bien les MFC qui ne se mettent pas à jour quand j’ajoute une ligne, mais qui se mélangent, je ne vois pas.

      1. Tout simplement, au lieu d’avoir en premier la couleur blanche qui se met en forme, puis la verte, rouge et enfin marron, ça devient rouge, orange, vert, blanc.
        N’étant plus le même ordre, les priorités sont différentes et le résultat n’est donc plus le même non plus.
        De plus, ces mêmes règles de MFC se doublent, dédoublent, et je me retrouve par moment avec une dizaine de règles copiées à devoir supprimer après insertion automatique de la nouvelle ligne.
        Voulez-vous voir le fichier ?

        1. Avez-vous essayé de copier votre feuille sur une autre et de créer les mises en forme dans le bon ordre (peut-être un fichier corrompu) ?
          Pour ce qui est des doubles, j’ai ça aussi.

  9. Bonjour,
    J’essaye dans un fichier d’appliquer un format conditionnel après avoir recherché des mots-clés dans des colonnes spécifiques.

    Je dois tester plusieurs colonnes dans ma feuille qui, potentiellement, contiennent la valeur « férié » par exemple.

    Cela fonctionne bien sur mon premier tableau, mais j’aimerais appliquer ce format à d’autres colonnes.

    Ma formule est :
    =TROUVE("Férié";($H7)) = OK pour mon premier tableau
    Comment faire pour appliquer ce format à d’autres colonnes sans devoir réécrire la formule ?

    1. Bonjour Corinne,

      Avez-vous essayé de glisser la formule en enlevant les signes $ afin de ne pas se référer à chaque fois à la cellule H7 si ce n’est pas le but ?

  10. Bonjour,
    Je voudrais mettre en rouge les lignes où la date d’entrée d’une enquête est supérieure à 10 jours par rapport à la date de traitement de cette enquête, en orange celle qui sont supérieures à 5 jours et en vert celles traitées dans les 5 jours suivants.
    Je ne trouve pas la formule adéquate, pouvez-vous m’aider ?

    1. Bonjour,

      Dans une autre cellule, il faut saisir une formule du type : =Datetraitement-Dateenquete (ce qui vous donnera la différence en nombre de jours).
      Ensuite, sur la plage de cellules contenant ces formules, allez dans Mise en forme conditionnelle et choisissez Appliquer une liste en forme uniquement aux cellules qui contiennent – Valeur de la cellule – Supérieure ou égale à (ou comprise entre) – =10. Puis appliquer la couleur rouge et faites de même pour l’orange et le vert.
      Il suffira ensuite de modifier le « S’applique à » en modifiant la colonne de départ à A pour que ce soit toute la ligne qui soit colorée.

    1. Bonjour,
      Je reviens vers vous concernant cette formule : =Datetraitement-Dateenquete (ce qui vous donnera la différence en nombre de jours).
      J’aimerais, en même temps, que ça me compte que les jours ouvrés (du lundi au vendredi). Comment faire ?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

CAPTCHA
Change the CAPTCHA codeSpeak the CAPTCHA code