Mettez en surbrillance les lignes en fonction d'une valeur de cellule dans Excel (mise en forme conditionnelle)

Regardez la vidéo - Mettez en surbrillance les lignes en fonction des valeurs de cellule dans Excel

Si vous préférez lire des instructions écrites à la place, vous trouverez ci-dessous le didacticiel.

La mise en forme conditionnelle vous permet de formater une cellule (ou une plage de cellules) en fonction de la valeur qu'elle contient.

Mais parfois, au lieu de simplement mettre la cellule en surbrillance, vous souhaiterez peut-être mettre en surbrillance toute la ligne (ou la colonne) en fonction de la valeur d'une cellule.

Pour vous donner un exemple, j'ai ci-dessous un ensemble de données où j'ai mis en surbrillance toutes les lignes où le nom du représentant des ventes est Bob.

Dans ce didacticiel, je vais vous montrer comment mettre en évidence des lignes en fonction d'une valeur de cellule à l'aide d'une mise en forme conditionnelle à l'aide de différents critères.

Cliquez ici pour télécharger le fichier d'exemple et suivre.

Mettre en surbrillance des lignes en fonction d'un critère de texte

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez mettre en évidence tous les enregistrements où le nom du représentant commercial est Bob.

Voici les étapes à suivre :

1. Sélectionnez l'ensemble de données complet (A2:F17 dans cet exemple).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
4. Cliquez sur « Nouvelles règles ».
5. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
6. Dans le champ de formule, saisissez la formule suivante : =$C2="Bob"
7. Cliquez sur le bouton « Formater ».
8. Dans la boîte de dialogue qui s'ouvre, définissez la couleur dans laquelle vous souhaitez que la ligne soit mise en évidence.
9. Cliquez sur OK.

Cela mettra en évidence toutes les lignes où le nom du représentant des ventes est « Bob ».

Cliquez ici pour télécharger le fichier d'exemple et suivre.

Comment ça marche?

La mise en forme conditionnelle vérifie chaque cellule pour la condition que nous avons spécifiée, qui est =$C2="Bob"

Ainsi, lorsqu'il analyse chaque cellule de la ligne A2, il vérifie si la cellule C2 porte ou non le nom de Bob. Si c'est le cas, cette cellule est mise en surbrillance, sinon ce n'est pas le cas.

Notez que l'astuce ici est d'utiliser un signe dollar ($) avant l'alphabet de la colonne ($C1). En faisant cela, nous avons verrouillé la colonne pour qu'elle soit toujours C. Ainsi, même lorsque la cellule A2 est vérifiée pour la formule, elle vérifiera C2, et lorsque A3 est vérifié pour la condition, elle vérifiera C3.

Cela nous permet de mettre en évidence la ligne entière par une mise en forme conditionnelle.

Mettre en surbrillance les lignes en fonction d'un critère numérique

Dans l'exemple ci-dessus, nous avons vu comment rechercher un nom et mettre en surbrillance toute la ligne.

Nous pouvons utiliser la même méthode pour vérifier également les valeurs numériques et mettre en évidence les lignes en fonction d'une condition.

Supposons que j'ai les mêmes données (comme indiqué ci-dessous) et que je souhaite mettre en évidence toutes les lignes où la quantité est supérieure à 15.

Voici les étapes à suivre :

1. Sélectionnez l'ensemble de données complet (A2:F17 dans cet exemple).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
4. Cliquez sur « Nouvelles règles ».
5. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
6. Dans le champ de formule, saisissez la formule suivante : =$D2>=15
7. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur dans laquelle vous souhaitez que la ligne soit mise en évidence.
8. Cliquez sur OK.

Cela mettra en évidence toutes les lignes où la quantité est supérieure ou égale à 15.

De même, nous pouvons également l'utiliser pour avoir des critères pour la date.

Par exemple, si vous souhaitez mettre en évidence toutes les lignes dont la date est postérieure au 10 juillet 2021-2022, vous pouvez utiliser la formule de date ci-dessous :

=$A2>DATE(2018,7,10)

Mettez en surbrillance les lignes en fonction de plusieurs critères (ET/OU)

Vous pouvez également utiliser plusieurs critères pour mettre en évidence des lignes à l'aide de la mise en forme conditionnelle.

Par exemple, si vous souhaitez mettre en surbrillance toutes les lignes où le nom du représentant commercial est « Bob » et la quantité est supérieure à 10, vous pouvez le faire en procédant comme suit :

1. Sélectionnez l'ensemble de données complet (A2:F17 dans cet exemple).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
4. Cliquez sur « Nouvelles règles ».
5. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
6. Dans le champ de formule, saisissez la formule suivante : =ET($C2=”Bob”,$D2>10)
7. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur dans laquelle vous souhaitez que la ligne soit mise en évidence.
8. Cliquez sur OK.

Dans cet exemple, seules les lignes sont mises en surbrillance lorsque les deux conditions sont remplies (ceci est fait à l'aide de la formule ET).

De même, vous pouvez également utiliser la condition OU. Par exemple, si vous souhaitez mettre en surbrillance les lignes où le représentant commercial est Bob ou la quantité est supérieure à 15, vous pouvez utiliser la formule ci-dessous :

=OU($C2="Bob",$D2>15)

Cliquez ici pour télécharger le fichier d'exemple et suivre.

Mettez en surbrillance les lignes de différentes couleurs en fonction de plusieurs conditions

Parfois, vous souhaiterez peut-être mettre en surbrillance des lignes dans une couleur en fonction de la condition.

Par exemple, vous pouvez mettre en surbrillance toutes les lignes où la quantité est supérieure à 20 en vert et où la quantité est supérieure à 15 (mais inférieure à 20) en orange.

Pour ce faire, vous devez créer deux règles de mise en forme conditionnelle et définir la priorité.

Voici les étapes à suivre :

1. Sélectionnez l'ensemble de données complet (A2:F17 dans cet exemple).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
4. Cliquez sur « Nouvelles règles ».
5. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
6. Dans le champ de formule, saisissez la formule suivante : =$D2>15
7. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur sur Orange.
8. Cliquez sur OK.
9. Dans la boîte de dialogue « Gestionnaire des règles de mise en forme conditionnelle », cliquez sur « Nouvelle règle ».
10. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
11. Dans le champ de formule, saisissez la formule suivante : =$D2>20
12. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur sur Vert.
13. Cliquez sur OK.
14. Cliquez sur Appliquer (ou OK).

Les étapes ci-dessus rendraient toutes les lignes avec une quantité supérieure à 20 en vert et celles avec plus de 15 (mais inférieures à 20 en orange).

Comprendre l'ordre des règles :

Lorsque vous utilisez plusieurs conditions, il est important de vous assurer que l'ordre des conditions est correct.

Dans l'exemple ci-dessus, la condition de couleur verte est au-dessus de la condition de couleur orange.

Si c'est l'inverse, toutes les lignes seront uniquement colorées en orange.

Pourquoi?

Parce qu'une ligne où la quantité est supérieure à 20 (disons 23) satisfait nos deux conditions (=$D2>15 et =$D2>20). Et puisque la condition Orange est au top, il a la préférence.

Vous pouvez modifier l'ordre des conditions à l'aide des boutons Déplacer vers le haut/bas.

Cliquez ici pour télécharger le fichier d'exemple et suivre.

Mettez en surbrillance les lignes où une cellule est vide

Si vous souhaitez mettre en surbrillance toutes les lignes où l'une des cellules est vide, vous devez vérifier chaque cellule à l'aide de la mise en forme conditionnelle.

Voici les étapes à suivre :

1. Sélectionnez l'ensemble de données complet (A2:F17 dans cet exemple).
2. Cliquez sur l'onglet Accueil.
3. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
4. Cliquez sur « Nouvelles règles ».
5. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
6. Dans le champ de formule, saisissez la formule suivante : =COUNTIF($A2:$F2,””)>0
7. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur sur Orange.
8. Cliquez sur OK.

La formule ci-dessus compte le nombre de cellules vides. Si le résultat est supérieur à 0, cela signifie qu'il y a des cellules vides dans cette ligne.

Si l'une des cellules est vide, la ligne entière est mise en surbrillance.

Mettez en surbrillance les lignes en fonction de la sélection déroulante

Dans les exemples couverts jusqu'à présent, toutes les conditions ont été spécifiées avec la boîte de dialogue de mise en forme conditionnelle.

Dans cette partie du didacticiel, je vais vous montrer comment le rendre dynamique (afin que vous puissiez entrer la condition dans une cellule dans Excel et qu'il mettra automatiquement en évidence les lignes en fonction).

Vous trouverez ci-dessous un exemple dans lequel je sélectionne un nom dans la liste déroulante et toutes les lignes portant ce nom sont mises en surbrillance :

Voici les étapes pour créer ceci :

1. Créez une liste déroulante dans la cellule A2. Ici, j'ai utilisé les noms du représentant des ventes pour créer la liste déroulante. Voici un guide détaillé sur la façon de créer une liste déroulante dans Excel.
2. Sélectionnez l'ensemble de données complet (C2:H17 dans cet exemple).
3. Cliquez sur l'onglet Accueil.
4. Dans le groupe Styles, cliquez sur Mise en forme conditionnelle.
5. Cliquez sur « Nouvelles règles ».
6. Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
7. Dans le champ de formule, saisissez la formule suivante : =$E2=$A$2
8. Cliquez sur le bouton « Formater ». Dans la boîte de dialogue qui s'ouvre, définissez la couleur sur Orange.
9. Cliquez sur OK.

Désormais, lorsque vous sélectionnez un nom dans la liste déroulante, il mettra automatiquement en surbrillance les lignes où le nom est le même que celui que vous avez sélectionné dans la liste déroulante.

Vous souhaitez en savoir plus sur la recherche et la mise en évidence dans Excel ? Vérifiez les vidéos ci-dessous.