Utiliser la mise en forme conditionnelle dans Excel (Le guide ultime + 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 les cellules qui répondent à une condition spécifiée.

Elle 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 en utilisant le formatage conditionnel dans Excel.

Utiliser le formatage conditionnel dans Excel (exemples)

Dans ce tutoriel, je vous montrerai sept exemples étonnants d’utilisation du formatage conditionnel dans Excel :

• Identifier rapidement les doublons en utilisant le formatage conditionnel dans Excel.
• Surligner les cellules dont la valeur est supérieure/inférieure à un nombre dans un ensemble de données.
• Surligner les 10 (ou 10 %) valeurs supérieures/inférieures dans un ensemble de données.
• Surligner les erreurs/les blancs en utilisant le formatage conditionnel dans Excel.
• Créer des cartes thermiques à l’aide du formatage conditionnel dans Excel.
• Mettre en évidence chaque Nième ligne/colonne à l’aide du formatage conditionnel.
• Recherche et mise en évidence à l’aide du formatage conditionnel dans Excel.

1. Identifiez rapidement les doublons

Le formatage conditionnel dans Excel peut être utilisé pour identifier les doublons dans un ensemble de données.

Voici comment vous pouvez le faire :

• Sélectionnez l’ensemble de données dans lequel vous voulez mettre en évidence les doublons.
• Voir Accueil -> Mise en forme conditionnelle -> Règles de mise en évidence des cellules -> Valeurs dupliquées.
• Dans la boîte de dialogue Valeurs dupliquées, assurez-vous que Duplicate est sélectionné dans la liste déroulante de gauche. Vous pouvez spécifier le format à appliquer en utilisant la liste déroulante de droite. Il existe certains formats existants que vous pouvez utiliser, ou spécifier votre propre format en utilisant l’option Format personnalisé.
• Cliquez sur OK.

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

See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.

2. Mettre en évidence les cellules dont la valeur est supérieure/inférieure à un nombre

Vous pouvez utiliser le formatage conditionnel dans Excel pour mettre rapidement en évidence les cellules qui contiennent des valeurs supérieures/inférieures à une valeur spécifiée. Par exemple, mettre en évidence toutes les cellules dont la valeur des ventes est inférieure à 100 millions, ou mettre en évidence les cellules dont les notes sont inférieures au seuil de passage.

Voici les étapes à suivre pour ce faire :

• Sélectionner l’ensemble des données.
• Accueil -> Mise en forme conditionnelle -> Règles de mise en évidence des cellules -> Supérieur à… / Less Than..
• Selon l’option que vous sélectionnez (plus grand que ou moins grand que), une boîte de dialogue s’ouvrirait. Disons que vous sélectionnez l’option  » plus grand que « . Dans la boîte de dialogue, entrez le nombre dans le champ à gauche. L’intention est de mettre en évidence les cellules dont le nombre est supérieur à ce nombre spécifié.

• Spécifiez le format à appliquer aux cellules qui répondent à la condition en utilisant la liste déroulante sur la droite. Il existe certains formats existants que vous pouvez utiliser, 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 dont les valeurs sont supérieures à 5 dans un ensemble de données.

Note : si vous souhaitez mettre en évidence les valeurs supérieures à 5, vous devez appliquer à nouveau la mise en forme conditionnelle avec le critère « égal à ».

Le même processus peut être suivi pour mettre en évidence les cellules dont la valeur est inférieure à des valeurs spécifiées.

3. Mise en évidence des 10 (ou 10 %) supérieurs/inférieurs)

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 pourrait être utile dans les situations où vous voulez voir rapidement les meilleurs candidats par les scores ou les meilleures valeurs de transaction dans les données de vente.

De la même manière, vous pouvez également identifier rapidement les 10 éléments les plus bas ou les 10 % les plus bas dans un ensemble de données.

Voici les étapes pour le faire :

• Sélectionner l’ensemble de données entier.
• Accueil -> Mise en forme conditionnelle -> Règles supérieures/inférieures -> 10 premiers éléments (ou %) / 10 derniers éléments (ou %).
• Selon ce que vous sélectionnez, cela ouvrira la boîte de dialogue. Disons que vous avez sélectionné les 10 premiers éléments, alors il ouvrira une boîte de dialogue comme indiqué ci-dessous :
• Spécifiez le format à appliquer aux cellules qui répondent à la condition en utilisant la liste déroulante à droite. Il existe certains formats existants que vous pouvez utiliser, ou spécifier votre propre format à l’aide de l’option Format personnalisé.
• Cliquez sur OK.

Ceci 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.

Aussi, si vous avez moins de 10 cellules dans le jeu de données, et que vous sélectionnez les options pour mettre en évidence les 10 premiers éléments/les 10 derniers éléments, alors toutes les cellules seraient mises en évidence.

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

4. Mise en évidence des erreurs/blanchiments

Si vous travaillez avec beaucoup de données numériques et de calculs dans Excel, vous sauriez l’importance d’identifier et de traiter les cellules qui ont 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 qui présentent des erreurs ou qui sont vides.

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

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

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

• Sélectionnez l’ensemble de données dans lequel vous souhaitez mettre en évidence les cellules vides et les cellules comportant des erreurs.
• Accueil -> Mise en forme conditionnelle -> Nouvelle règle.
• Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez Utiliser une formule pour déterminer les cellules à mettre en forme.
• Entrez la formule suivante dans le champ de la section ‘Modifier la description de la règle’ :
  =OR(ISBLANK(A1),ISERROR(A1)).
• La formule ci-dessus vérifie toutes les cellules pour deux conditions – si elle est vide ou non et si elle a 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  » Format « . Cela ouvrira la boîte de dialogue  » Format des cellules « , où vous pourrez spécifier le format.

Cliquez sur Ok.

Cela mettrait instantanément en évidence toutes les cellules qui sont soit vides, soit comportant des erreurs.

Note : il n’est pas nécessaire d’utiliser toute la plage A1:A7 dans la formule en mise en forme conditionnelle. La formule mentionnée ci-dessus utilise uniquement A1. Lorsque vous appliquez cette formule à l’ensemble de 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’elle vérifie la cellule A2, elle utilise la formule =OR(ISBLANK(A2),ISERROR(A2)). Elle ajuste automatiquement la référence (car il s’agit de 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 🙂

See Also: Using IFERROR and ISERROR to handle errors in excel.

5. Création de cartes thermiques

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

Comme illustré ci-dessous :

L’ensemble de données ci-dessus a des valeurs comprises entre 1 et 100. Les cellules sont mises en évidence 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 en utilisant le formatage conditionnel dans Excel.

• Sélectionner l’ensemble de données.
• 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, elle appliquerait la mise en forme à l’ensemble de données. Il existe plusieurs dégradés de couleurs parmi lesquels vous pouvez choisir. Si vous n’êtes pas satisfait des options de couleurs existantes, vous pouvez sélectionner plus de règles et spécifier la couleur que vous voulez.

Note : De manière similaire, vous pouvez également appliquer des ensembles de cartes de données et d’icônes.

6. Surligner une ligne/colonne sur deux

Vous pouvez vouloir surligner des lignes alternées pour augmenter la lisibilité des données.

Ce sont les lignes zébrées et elles pourraient être particulièrement utiles si vous imprimez les données.

Il y a maintenant deux façons de créer ces lignes zébrées. La façon la plus rapide consiste à convertir vos données tabulaires en un tableau Excel. Il a automatiquement appliqué une couleur aux lignes alternées. Vous pouvez en savoir plus à ce sujet ici.

L’autre moyen consiste à utiliser le formatage conditionnel.

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

Voici les étapes pour mettre en évidence les lignes alternées à 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 alors l’ensemble des données.
• Ouvrir la boîte de dialogue de formatage conditionnel (Accueil-> Formatage conditionnel-> Nouvelle règle) .
• Dans la boîte de dialogue, sélectionnez la boîte de dialogue ‘Utiliser une formule pour déterminer les cellules à formater’.
• Entrez la formule suivante dans le champ de la section ‘Modifier la description de la règle’ :
  =ISODD(ROW()).

• La formule ci-dessus vérifie toutes les cellules et si le numéro 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 ‘Format’. Cela ouvrira la boîte de dialogue  » Format des cellules « , où vous pourrez spécifier le format.
• Cliquez sur OK.

C’est tout ! Les lignes alternatives du jeu de données seront mises en évidence.

Vous pouvez utiliser la même technique dans de nombreux cas. Il vous suffit d’utiliser la formule correspondante dans la mise en forme conditionnelle. Voici quelques exemples:

• Mettre en évidence les lignes paires alternées : =ISEVEN(ROW()).
• Mise en évidence des rangs d’addition alternés : =ISODD(ROW()).
• Mettre en évidence chaque 3ème ligne : =MOD(ROW(),3)=0

7. Rechercher et mettre en évidence des données à l’aide du formatage conditionnel

Ceci est une utilisation un peu avancée du formatage conditionnel. Elle vous ferait passer pour une rockstar d’Excel.

Supposons que vous ayez un jeu de données comme celui présenté 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 de caractères dans la cellule C2, et si elle correspond aux données d’une ou plusieurs cellules, alors cela devrait être mis en évidence. Quelque chose comme indiqué ci-dessous:

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

• Sélectionner le jeu de données.
• Accueil -> Mise en forme conditionnelle -> Nouvelle règle (raccourci clavier – Alt + O + D).
• Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez l’option ‘Utiliser une formule pour déterminer les cellules à mettre en forme’.
• Entrez la formule suivante dans le champ de la section ‘Modifier la description de la règle’ :
  =AND($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 ‘Format’. Cela ouvrira la boîte de dialogue  » Format des cellules « , où vous pourrez spécifier le format.
• Cliquez sur OK.

C’est tout ! Maintenant, lorsque vous saisissez quoi que ce soit dans la cellule C2 et que vous appuyez sur la touche Entrée, cela met en surbrillance toutes les cellules correspondantes.

Comment cela fonctionne-t-il ?

La formule utilisée dans le formatage conditionnel évalue toutes les cellules de l’ensemble de données. Disons que vous saisissez Japon dans la cellule C2. Maintenant, Excel évaluerait la formule pour chaque cellule.

La formule renverrait VRAI 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.

Hence, toutes les cellules qui contiennent le texte Japon sont mises en évidence.

Télécharger le fichier d’exemple

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

• Mettre en surbrillance la ligne entière au lieu d’une cellule.
• Surligner même lorsqu’il y a une correspondance partielle.
• Surligner les cellules/lignes à mesure que vous tapez (dynamique) .

Comment supprimer le formatage conditionnel dans Excel

Une fois appliqué, le formatage conditionnel reste en place à moins que vous ne le supprimiez manuellement. Comme meilleure pratique, gardez le formatage conditionnel appliqué uniquement aux cellules où vous en avez besoin.

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

Pour supprimer le formatage conditionnel :

• Sélectionnez les cellules dont vous voulez supprimer le formatage conditionnel.
• Accueil -> Mise en forme conditionnelle -> Effacer les règles -> Effacer les règles des cellules sélectionnées.
  • Si vous voulez supprimer la mise en forme conditionnelle de toute la feuille de calcul, sélectionnez Effacer les règles de toute la feuille.

Éléments importants à connaître sur la mise en forme conditionnelle dans Excel

• La mise en forme conditionnelle dans volatile. Il peut conduire à un classeur lent. Ne l’utilisez qu’en cas de besoin.
• Lorsque vous copiez-collez des cellules qui contiennent un formatage conditionnel, ce dernier est également copié.
• 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 à partir de la boîte de dialogue Gérer les règles.

Vous pourriez également aimer les didacticiels Excel suivants :

• La bonne façon d’appliquer le formatage conditionnel dans un tableau croisé dynamique.
• Retrouver et supprimer les doublons dans Excel.
• Le guide ultime de l’utilisation du formatage conditionnel dans Google Sheets.

.