Mise en forme conditionnelle dans Excel : le guide ultime avec des exemples

La mise en forme conditionnelle est l'une des fonctionnalités les plus simples mais les plus puissantes des feuilles de calcul Excel.

Comme son nom l'indique, vous pouvez utiliser la mise en forme conditionnelle dans Excel lorsque vous souhaitez mettre en évidence des cellules qui remplissent une condition spécifiée.

Il vous donne la possibilité d'ajouter rapidement une couche d'analyse visuelle sur votre ensemble de données. Vous pouvez créer des cartes thermiques, afficher des icônes croissantes/décroissantes, des bulles Harvey et bien plus encore à l'aide de la mise en forme conditionnelle dans Excel.

Utilisation de la mise en forme conditionnelle dans Excel (exemples)

Dans ce didacticiel, je vais vous montrer sept exemples étonnants d'utilisation de la mise en forme conditionnelle dans Excel :

• Identifiez rapidement les doublons à l'aide de la mise en forme conditionnelle dans Excel.
• Mettez en surbrillance les cellules dont la valeur est supérieure/inférieure à un nombre dans un ensemble de données.
• Mettre en surbrillance les 10 premières/dernières valeurs (ou 10 %) dans un jeu de données.
• Mise en évidence des erreurs/vides à l'aide de la mise en forme conditionnelle dans Excel.
• Création de cartes thermiques à l'aide de la mise en forme conditionnelle dans Excel.
• Mettez en surbrillance chaque nième ligne/colonne à l'aide de la mise en forme conditionnelle.
• Recherchez et mettez en surbrillance à l'aide de la mise en forme conditionnelle dans Excel.

1. Identifiez rapidement les doublons

La mise en forme conditionnelle dans Excel peut être utilisée pour identifier les doublons dans un ensemble de données.

Voici comment procéder :

• Sélectionnez le jeu de données dans lequel vous souhaitez mettre en évidence les doublons.
• Allez dans Accueil -> Mise en forme conditionnelle -> Règles de surbrillance des cellules -> Valeurs en double.
• Dans la boîte de dialogue Dupliquer les valeurs, assurez-vous que Dupliquer est sélectionné dans le menu déroulant de gauche. Vous pouvez spécifier le format à appliquer en utilisant le menu déroulant de droite. Vous pouvez utiliser certains formats existants ou spécifier votre propre format à l'aide de l'option Format personnalisé.
• Cliquez sur OK.

Cela mettrait instantanément en évidence toutes les cellules qui ont un doublon dans l'ensemble de données sélectionné. Votre ensemble de données peut se trouver dans une seule colonne, plusieurs colonnes ou dans une plage de cellules non contiguë.

Voir également: Le guide ultime pour rechercher et supprimer les doublons dans Excel.

2. Mettez en surbrillance les cellules avec une valeur supérieure/inférieure à un nombre

Vous pouvez utiliser la mise en forme conditionnelle dans Excel pour mettre rapidement en évidence les cellules contenant des valeurs supérieures/inférieures à une valeur spécifiée. Par exemple, mettre en surbrillance toutes les cellules dont la valeur des ventes est inférieure à 100 millions, ou mettre en surbrillance les cellules dont les notes sont inférieures au seuil de réussite.

Voici les étapes à suivre :

• Sélectionnez l'ensemble de données complet.
• Allez à Accueil -> Mise en forme conditionnelle -> Règles de surbrillance des cellules -> Supérieur à… / Inférieur à…
• En fonction de l'option que vous sélectionnez (supérieure ou inférieure à), une boîte de dialogue s'ouvrira. Disons que vous sélectionnez l'option "Supérieur à". Dans la boîte de dialogue, entrez le numéro dans le champ de gauche. L'intention est de mettre en évidence les cellules qui ont un nombre supérieur à ce nombre spécifié.

• Spécifiez le format à appliquer aux cellules qui remplissent la condition en utilisant la liste déroulante à droite. Vous pouvez utiliser certains formats existants ou spécifier votre propre format à l'aide de l'option Format personnalisé.
• Cliquez sur OK.

Cela mettrait instantanément en évidence toutes les cellules avec des valeurs supérieures à 5 dans un ensemble de données.Remarque : Si vous souhaitez mettre en évidence des valeurs supérieures à 5, vous devez à nouveau appliquer une mise en forme conditionnelle avec les critères « Égal à ».

Le même processus peut être suivi pour mettre en évidence les cellules avec une valeur inférieure à une valeur spécifiée.

3. Mettre en surbrillance Top/Bas 10 (ou 10%)

La mise en forme conditionnelle dans Excel peut identifier rapidement les 10 premiers éléments ou les 10 % supérieurs d'un ensemble de données. Cela peut être utile dans les situations où vous souhaitez voir rapidement les meilleurs candidats par scores ou les meilleures valeurs de transaction dans les données de vente.

De même, vous pouvez également identifier rapidement les 10 derniers éléments ou les 10 % les plus bas d'un jeu de données.

Voici les étapes à suivre :

• Sélectionnez l'ensemble de données complet.
• Allez à Accueil -> Mise en forme conditionnelle -> Règles du haut/du bas -> 10 premiers éléments (ou %) / 10 derniers éléments (ou %).
• En fonction de ce que vous sélectionnez, il ouvrira la boîte de dialogue. Supposons que vous ayez sélectionné les 10 meilleurs éléments, puis cela ouvrirait une boîte de dialogue comme indiqué ci-dessous :
• Spécifiez le format à appliquer aux cellules qui remplissent la condition en utilisant la liste déroulante sur la droite. Vous pouvez utiliser certains formats existants ou spécifier votre propre format à l'aide de l'option Format personnalisé.
• Cliquez sur OK.

Cela mettrait instantanément en évidence les 10 premiers éléments de l'ensemble de données sélectionné. Notez que cela ne fonctionne que pour les cellules qui contiennent une valeur numérique.

De plus, si vous avez moins de 10 cellules dans l'ensemble de données et que vous sélectionnez les options pour mettre en surbrillance les 10 premiers éléments/les 10 derniers éléments, toutes les cellules seront mises en surbrillance.

Voici quelques exemples du fonctionnement de la mise en forme conditionnelle :

4. Mettre en évidence les erreurs/les blancs

Si vous travaillez avec beaucoup de données numériques et de calculs dans Excel, vous saurez l'importance d'identifier et de traiter les cellules qui contiennent des erreurs ou qui sont vides. Si ces cellules sont utilisées dans d'autres calculs, cela pourrait conduire à des résultats erronés.

La mise en forme conditionnelle dans Excel peut vous aider à identifier et à mettre en évidence rapidement les cellules contenant des erreurs ou vides.

Supposons que nous ayons un ensemble de données comme indiqué ci-dessous :

Cet ensemble de données a une cellule vide (A4) et des erreurs (A5 et A6).

Voici les étapes pour mettre en évidence les cellules vides ou contenant des erreurs :

• Sélectionnez le jeu de données dans lequel vous souhaitez mettre en surbrillance les cellules vides et les cellules avec des erreurs.
• Allez dans Accueil -> Mise en forme conditionnelle -> Nouvelle règle.
• Dans la boîte de dialogue Nouvelle règle de formatage, sélectionnez Utiliser une formule pour déterminer les cellules à formater.
• Saisissez la formule suivante dans le champ de la section « Modifier la description de la règle » :
  =OU(ESTVIDE(A1),ESTERREUR(A1))
  • La formule ci-dessus vérifie toutes les cellules pour deux conditions - si elle est vide ou non et si elle contient une erreur ou non. Si l'une des conditions est VRAIE, elle renvoie VRAI.
• Définissez le format que vous souhaitez appliquer aux cellules vides ou comportant des erreurs. Pour ce faire, cliquez sur le bouton Formater. Cela ouvrira la boîte de dialogue « Format des cellules », où vous pouvez spécifier le format.
• Cliquez sur OK.

Cela mettrait instantanément en évidence toutes les cellules vides ou contenant des erreurs.

Noter: Vous n'avez pas besoin d'utiliser toute la plage A1:A7 dans la formule dans la mise en forme conditionnelle. La formule mentionnée ci-dessus utilise uniquement A1. Lorsque vous appliquez cette formule à toute la plage, Excel vérifie une cellule à la fois et ajuste la référence. Par exemple, lorsqu'il vérifie A1, il utilise la formule =OR(ISBLANK(A1),ISERROR(A1)). Lorsqu'il vérifie la cellule A2, il utilise ensuite la formule =OR(ISBLANK(A2),ISERROR(A2)). Il ajuste automatiquement la référence (car ce sont des références relatives) en fonction de la cellule analysée. Vous n'avez donc pas besoin d'écrire une formule distincte pour chaque cellule. Excel est assez intelligent pour changer la référence de la cellule tout seul

Voir également: Utiliser IFERROR et ISERROR pour gérer les erreurs dans Excel.

5. Création de cartes thermiques

Une carte thermique est une représentation visuelle des données où la couleur représente la valeur dans une cellule. Par exemple, vous pouvez créer une carte thermique dans laquelle une cellule avec la valeur la plus élevée est colorée en vert et il y a un décalage vers la couleur rouge lorsque la valeur diminue.

Quelque chose comme indiqué ci-dessous :

L'ensemble de données ci-dessus a des valeurs comprises entre 1 et 100. Les cellules sont mises en surbrillance en fonction de la valeur qu'elles contiennent. 100 obtient la couleur verte, 1 obtient la couleur rouge.

Voici les étapes pour créer des cartes thermiques à l'aide de la mise en forme conditionnelle dans Excel.

• Sélectionnez l'ensemble de données.
• Allez dans Accueil -> Mise en forme conditionnelle -> Échelles de couleurs et choisissez l'un des schémas de couleurs.

Dès que vous cliquez sur l'icône de la carte thermique, le formatage est appliqué à l'ensemble de données. Vous pouvez choisir parmi plusieurs dégradés de couleurs. Si vous n'êtes pas satisfait des options de couleur existantes, vous pouvez sélectionner plus de règles et spécifier la couleur que vous souhaitez.

Remarque : De la même manière, vous pouvez également appliquer des ensembles de barres de données et d'icônes.

6. Mettez en surbrillance toutes les autres lignes/colonnes

Vous pouvez mettre en évidence des lignes alternatives pour augmenter la lisibilité des données.

Celles-ci sont appelées les lignes zébrées et peuvent être particulièrement utiles si vous imprimez les données.

Il existe maintenant deux manières de créer ces lignes zébrées. Le moyen le plus rapide consiste à convertir vos données tabulaires en un tableau Excel. Il appliquait automatiquement une couleur aux rangées alternées. Vous pouvez en savoir plus à ce sujet ici.

Une autre méthode consiste à utiliser la mise en forme conditionnelle.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :

Voici les étapes pour mettre en évidence des lignes alternatives à l'aide de la mise en forme conditionnelle dans Excel.

• Sélectionnez l'ensemble de données. Dans l'exemple ci-dessus, sélectionnez A2:C13 (ce qui exclut l'en-tête). Si vous souhaitez également inclure l'en-tête, sélectionnez l'ensemble de données complet.
• Ouvrez la boîte de dialogue Mise en forme conditionnelle (Accueil-> Mise en forme conditionnelle-> Nouvelle règle) [Raccourci clavier - Alt + O + D].
• Dans la boîte de dialogue, sélectionnez la boîte de dialogue « Utiliser une formule pour déterminer les cellules à formater ».
• Saisissez la formule suivante dans le champ de la section « Modifier la description de la règle » :
  =ISODD(LIGNE())

• La formule ci-dessus vérifie toutes les cellules et si le numéro de ROW d'une cellule est impair, alors elle renvoie VRAI. Le format conditionnel spécifié serait appliqué à toutes les cellules qui renvoient VRAI.
• Définissez le format que vous souhaitez appliquer aux cellules vides ou comportant des erreurs. Pour ce faire, cliquez sur le bouton Formater. Cela ouvrira la boîte de dialogue « Format des cellules », où vous pouvez spécifier le format.
• Cliquez sur OK.

C'est ça! Les lignes alternatives de l'ensemble de données seront mises en surbrillance.

Vous pouvez utiliser la même technique dans de nombreux cas. Tout ce que vous avez à faire est d'utiliser la formule appropriée dans la mise en forme conditionnelle. Voici quelques exemples:

• Mettez en surbrillance les lignes paires alternatives : =ISEVEN(ROW())
• Mettez en surbrillance les lignes d'ajout alternatives : =ISODD(ROW())
• Mettez en surbrillance toutes les 3 lignes : =MOD(ROW(),3)=0

7. Rechercher et mettre en surbrillance les données à l'aide de la mise en forme conditionnelle

Celui-ci est une utilisation un peu avancée de la mise en forme conditionnelle. Cela vous ferait ressembler à une rockstar Excel.

Supposons que vous ayez un jeu de données comme indiqué ci-dessous, avec le nom des produits, le représentant des ventes et la géographie. L'idée est de taper une chaîne dans la cellule C2, et si elle correspond aux données de n'importe quelle(s) cellule(s), alors cela devrait être mis en surbrillance. Quelque chose comme indiqué ci-dessous :

Voici les étapes pour créer cette fonctionnalité de recherche et de mise en évidence :

• Sélectionnez le jeu de données.
• Allez dans Accueil -> Mise en forme conditionnelle -> Nouvelle règle (Raccourci clavier - Alt + O + D).
• Dans la boîte de dialogue Nouvelle règle de formatage, sélectionnez l'option "Utiliser une formule pour déterminer les cellules à formater".
• Saisissez la formule suivante dans le champ de la section « Modifier la description de la règle » :
  =ET($C$2””,$C$2=B5)
• Définissez le format que vous souhaitez appliquer aux cellules vides ou comportant des erreurs. Pour ce faire, cliquez sur le bouton Formater. Cela ouvrira la boîte de dialogue « Format des cellules », où vous pouvez spécifier le format.
• Cliquez sur OK.

C'est ça! Maintenant, lorsque vous entrez quelque chose dans la cellule C2 et appuyez sur Entrée, toutes les cellules correspondantes seront mises en surbrillance.

Comment cela marche-t-il?

La formule utilisée dans la mise en forme conditionnelle évalue toutes les cellules de l'ensemble de données. Disons que vous entrez au Japon dans la cellule C2. Maintenant, Excel évaluerait la formule pour chaque cellule.

La formule renverrait TRUE pour une cellule lorsque deux conditions sont remplies :

• La cellule C2 n'est pas vide.
• Le contenu de la cellule C2 correspond exactement au contenu de la cellule dans l'ensemble de données.

Par conséquent, toutes les cellules contenant le texte Japon sont mises en surbrillance.

Télécharger le fichier exemple

Vous pouvez utiliser la même logique, pour créer des variantes telles que :

• Mettez en surbrillance la ligne entière au lieu d'une cellule.
• Mettez en surbrillance même en cas de correspondance partielle.
• Mettez en surbrillance les cellules/lignes au fur et à mesure que vous tapez (dynamique) [Vous allez adorer cette astuce :)].

Comment supprimer la mise en forme conditionnelle dans Excel

Une fois appliquée, la mise en forme conditionnelle reste en place, sauf si vous la supprimez manuellement. En tant que meilleure pratique, gardez la mise en forme conditionnelle appliquée uniquement aux cellules où vous en avez besoin.

Comme il est volatile, cela peut conduire à un classeur Excel lent.

Pour supprimer la mise en forme conditionnelle :

• Sélectionnez les cellules dont vous souhaitez supprimer la mise en forme conditionnelle.
• Allez dans Accueil -> Mise en forme conditionnelle -> Effacer les règles -> Effacer les règles des cellules sélectionnées.
  • Si vous souhaitez supprimer la mise en forme conditionnelle de toute la feuille de calcul, sélectionnez Effacer les règles de la feuille entière.

À savoir à propos de la mise en forme conditionnelle dans Excel

• Formatage conditionnel dans volatile. Cela peut conduire à un classeur lent. Utilisez-le uniquement en cas de besoin.
• Lorsque vous copiez et collez des cellules contenant une mise en forme conditionnelle, la mise en forme conditionnelle est également copiée.
• Si vous appliquez plusieurs règles sur le même ensemble de cellules, toutes les règles restent actives. En cas de chevauchement, la règle appliquée en dernier est privilégiée. Vous pouvez toutefois modifier l'ordre en modifiant l'ordre à partir de la boîte de dialogue Gérer les règles.