Mise en forme conditionnelle dans Excel : le guide complet

Excel est la solution de référence pour stocker et représenter des données dans la plupart des équipes en entreprise. Que vous travailliez en finance, en ressources humaines ou en marketing, le volume de données conditionne forcément vos tâches d'analyse et de visualisation. Heureusement, Excel propose une fonctionnalité appelée mise en forme conditionnelle, qui vous aide à organiser et à gérer l'ensemble de vos données en quelques clics. Pour tirer le meilleur parti de cette fonctionnalité basée sur des règles, vous devez apprendre à bien l'utiliser.

Dans ce guide complet, vous apprendrez ce qu'est la mise en forme conditionnelle et les nombreux usages qu'elle offre dans Excel. Vous découvrirez les bases de la mise en forme conditionnelle dans Excel, ainsi que ses usages plus avancés lorsqu'elle est combinée aux formules IF ou AND. L'avantage de cette fonctionnalité d'Excel, c'est qu'elle peut être aussi simple ou aussi complexe que nécessaire, selon le niveau de personnalisation qu'exigent vos données. De la définition de plusieurs conditions à la création de règles de mise en forme, en passant par le choix des données de la feuille auxquelles elles s'appliquent, la mise en forme conditionnelle est l'une des fonctionnalités les plus utiles d'Excel pour gagner en productivité à tous les niveaux de l'entreprise.

Qu'est-ce que la mise en forme conditionnelle dans Excel ?

La mise en forme conditionnelle est une fonctionnalité des tableurs qui permet de mettre en forme des données selon un ensemble de conditions intégrées ou personnalisées. L'usage le plus courant consiste à mettre en évidence des informations clés grâce à un code couleur ou à supprimer les doublons en un clic.

Les conditions ou règles que vous définissez s'appliquent à une cellule ou à une plage de cellules, qui peut inclure des colonnes : la mise en forme des données de la cellule change donc automatiquement selon ce que précise la règle. Par exemple, vous pouvez demander à Excel de mettre en évidence (en vert, par exemple) toutes les cellules qui contiennent une valeur numérique "supérieure à" la valeur indiquée. Ce type de condition modifie la couleur, mais vous pouvez ajouter d'autres éléments de mise en forme, comme la taille ou le style de police.

Comment fonctionne la mise en forme conditionnelle dans Excel ?

La mise en forme conditionnelle modifie la mise en forme de vos données à l'aide de conditions qui fonctionnent comme des instructions "Si/Alors". Vous appliquez la condition de la règle à une plage de cellules donnée, puis vous choisissez le style de mise en forme appliqué aux cellules qui remplissent cette règle. Autrement dit : "Si (plage de données) est (condition de la règle), alors (style de mise en forme)" s'applique.

Selon la complexité de la règle que vous voulez appliquer, de la plus simple à la plus avancée, vous pouvez créer les types de conditions suivants dans Excel :

• Conditions intégrées : Excel propose une grande variété de conditions intégrées, de la mise en surbrillance de cellules aux règles des valeurs plus/moins élevées, applicables à différents types de données : nombres, texte ou dates.
• Conditions personnalisées : Elles s'obtiennent en combinant des formules Excel avec les conditions intégrées. Avec l'option "Nouvelle règle", vous pouvez inclure n'importe quelle formule, comme =AND ou =IF, pour gérer les données auxquelles vous voulez appliquer les conditions intégrées.
• Conditions multiples : Si vous devez mettre en forme des données précises simultanément, Excel vous permet d'utiliser des formules pour appliquer plusieurs conditions intégrées en même temps.

Types de base de mise en forme conditionnelle dans Excel

Nous commencerons par les types de mise en forme conditionnelle les plus utilisés dans Excel, selon leur facilité d'utilisation et leur champ d'application. Notez que les exemples s'appuient sur l'application de bureau Excel pour Mac. Excel pour le web vous permet aussi de créer et de modifier des règles de mise en forme conditionnelle, mais ses menus sont un peu différents.

Comment changer la couleur d'une cellule selon sa valeur avec la mise en forme conditionnelle ?

Ce type de mise en forme conditionnelle est l'un des plus utilisés dans Excel, car il aide à mettre en évidence les informations clés et à structurer visuellement les données pour les rendre plus lisibles, surtout lorsque vous travaillez avec de grands jeux de données.

Par exemple, imaginons que je travaille au service commercial d'un concessionnaire automobile international et que je doive indiquer quels modèles ont dépassé la marge bénéficiaire prévue. Voici comment appliquer la mise en forme conditionnelle pour changer la couleur d'une cellule selon sa valeur :

1. Sélectionnez la plage de cellules à laquelle vous voulez appliquer la règle. Ici, nous sélectionnons une plage dans la colonne 'Profit 5%'. Allez dans Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Supérieur à.

Mise en forme conditionnelle dans Excel – Règles de mise en surbrillance des cellules

2. Avant de choisir la couleur, saisissez la condition que les données doivent remplir. Ici, la valeur de la cellule doit être supérieure à 1 000. Ajoutez ensuite le style de mise en forme. Ici, nous mettons en forme toutes les cellules qui remplissent la condition avec le format prédéfini "Remplissage vert avec texte vert foncé".

Mise en forme conditionnelle dans Excel – Format des règles de mise en surbrillance des cellules

3. Cliquez sur "OK". Toutes les cellules au-dessus de la valeur indiquée prennent la couleur choisie.

Mise en forme conditionnelle dans Excel – Cellules mises en évidence en vert

Comme vous le verrez, Excel propose toute une gamme de règles de mise en forme par couleur applicables à n'importe quel type de données, pas seulement aux nombres. De plus, la dernière règle de mise en surbrillance s'applique aux valeurs en double, ce qui est utile pour les supprimer ensuite ou simplement pour repérer rapidement les informations répétées. Notre article vous sera très utile : découvrez comment trouver et supprimer les doublons dans Excel .

Comment appliquer une mise en forme conditionnelle à une colonne dans Excel ?

De la même manière que vous appliquez une règle de mise en forme conditionnelle à une cellule ou à une plage de cellules, vous pouvez l'appliquer à une colonne – il suffit de sélectionner toute la colonne avant d'utiliser la fonctionnalité. Voici comment appliquer une mise en forme conditionnelle à une colonne dans Excel avec les "Règles des valeurs plus/moins élevées".

1. Sélectionnez la colonne à laquelle vous voulez appliquer la mise en forme conditionnelle. Allez dans Mise en forme conditionnelle > Règles des valeurs plus/moins élevées > En dessous de la moyenne. Ici, je veux mettre en évidence les valeurs de bénéfice inférieures à la moyenne, calculée à partir des valeurs de la colonne.

Mise en forme conditionnelle dans Excel – Règles des valeurs plus/moins élevées, En dessous de la moyenne

2. Dans la boîte de dialogue, modifiez le format de mise en surbrillance selon vos préférences. Comme il s'agit d'une valeur négative, j'utilise "Remplissage rouge clair avec texte rouge foncé".

Mise en forme conditionnelle dans Excel – Appliquer une mise en forme conditionnelle à une colonne

3. Excel met automatiquement en évidence en rouge les valeurs de bénéfice inférieures à la moyenne.

Mise en forme conditionnelle dans Excel – Valeurs en dessous de la moyenne en rouge clair

Types avancés de mise en forme conditionnelle dans Excel

Maintenant que vous connaissez les usages de base de la mise en forme conditionnelle dans Excel, voyons des options plus avancées qui vous permettront de personnaliser les règles selon vos besoins précis.

Peut-on utiliser la mise en forme conditionnelle d'Excel avec une formule ?

Jusqu'ici, nous avons vu des exemples de mise en forme conditionnelle dans Excel avec des règles intégrées, comme les "Règles de mise en surbrillance des cellules" ou les "Règles des valeurs plus/moins élevées". Mais que faire si vous voulez appliquer plusieurs règles en même temps ? Heureusement, vous utilisez déjà Excel, et vous disposez donc de toute une série de formules. Voyons comment utiliser la formule COUNTIF (NB.SI) avec la mise en forme conditionnelle.

Formule =COUNTIF pour mettre en évidence les doublons

Si vous voulez appliquer une mise en forme conditionnelle aux cellules qui contiennent des valeurs en double, vous pouvez utiliser la règle intégrée dans Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double…

Mise en forme conditionnelle dans Excel – Mettre en évidence les valeurs en double

Cependant, vous pouvez aussi vouloir appliquer une mise en forme conditionnelle aux autres colonnes ou lignes qui contiennent ces valeurs en double. La formule =COUNTIF d'Excel permet de compter le nombre de cellules d'une plage sélectionnée qui remplissent un critère donné.

Syntaxe de la formule =COUNTIF

Voyons comment fonctionne la formule =COUNTIF en expliquant ses paramètres :

• Plage : Désigne la plage de cellules à laquelle nous appliquons la formule et la règle.
• Critères : Désigne les conditions que la plage doit remplir pour être comptée ou prise en compte par la règle.

Mise en forme conditionnelle avec COUNTIF : comment supprimer les doublons

Voici comment mettre en évidence les valeurs en double avec la formule =COUNTIF dans Excel :

1. Sélectionnez votre plage de cellules et allez dans Mise en forme conditionnelle > Nouvelle règle.

Mise en forme conditionnelle dans Excel – Créer une nouvelle règle

2. Une boîte de dialogue s'ouvre. Sélectionnez "Classique" dans le champ "Style" et "Utiliser une formule…" dans le menu déroulant.

Mise en forme conditionnelle dans Excel – Options de la nouvelle règle de mise en forme

3. En dessous, dans le champ de la formule, saisissez votre formule. Dans cet exemple, je saisis =COUNTIF(C1, "2006"), où la plage est C1, qui correspond à la colonne 'YEAR', et "2006" la valeur dont je veux trouver les doublons.

Mise en forme conditionnelle dans Excel – Formule COUNTIF

4. Choisissez la couleur de mise en forme que vous préférez. Ici, j'ai choisi le jaune.

Mise en forme conditionnelle dans Excel – Format jaune pour la formule COUNTIF

Une fois que vous avez cliqué sur "OK", tous les doublons de 2006 apparaissent en jaune.

Comment utiliser la mise en forme conditionnelle d'Excel avec plusieurs conditions ?

Nous avons utilisé une formule Excel, =COUNTIF, avec la règle de mise en forme conditionnelle "Valeurs en double". Voyons maintenant comment appliquer plusieurs conditions selon le type de données et le type de mise en forme que nous voulons appliquer.

Formule AND pour appliquer plusieurs conditions

La formule AND (ET) est aussi une fonction logique qui teste les conditions et renvoie vrai ou faux, selon qu'elles sont remplies ou non.

Pour combiner AND et IF dans une cellule, découvrez comment utiliser la combinaison des fonctions IF et AND dans Excel.

Pour poursuivre avec le cas d'usage, imaginons que je veuille appliquer une mise en forme conditionnelle aux valeurs VIN (numéro d'identification du véhicule) des voitures immatriculées à partir de 2006 qui ont généré un bénéfice supérieur au minimum, soit 1 153,00 $.

1. Suivez les étapes 1 et 2 de la section précédente pour créer une nouvelle règle. N'oubliez pas de sélectionner la plage de cellules à laquelle vous voulez appliquer les différentes conditions.

2. Saisissez la formule AND dans la zone correspondante. Dans cet exemple, je saisis la formule =AND(C1>=2006, F1>=1153). Tapez 1153 sans séparateur de milliers, car Excel interprète une virgule dans une formule comme le début d'un nouvel argument.

Mise en forme conditionnelle dans Excel – Mise en forme avec la formule AND

3. Modifiez le style de mise en forme selon vos préférences. Ici, j'ai choisi "Remplissage vert avec texte vert foncé".

Mise en forme conditionnelle dans Excel – Format vert pour la formule AND

4. Cliquez sur "OK" pour appliquer vos conditions. Comme le montre l'image ci-dessous, Excel met en évidence en vert uniquement les valeurs des cellules qui remplissent les différentes conditions définies dans la formule AND.

Mise en forme conditionnelle dans Excel – Formule AND appliquée

Comment appliquer une mise en forme conditionnelle à une cellule selon une autre cellule dans Excel ?

Et si, au lieu d'appliquer une règle à une valeur donnée, vous voulez vous baser sur la valeur d'une autre cellule de la feuille ? La valeur de cette cellule sert alors de valeur fixe lors de l'application de la règle de mise en surbrillance. Pour reprendre l'exemple de la marge bénéficiaire des voitures, la mise en surbrillance s'appliquait lorsqu'une valeur numérique dépassait le bénéfice prévu. Cependant, comme la marge bénéficiaire peut évoluer dans le temps, il peut être préférable de créer une règle qui dépend strictement d'une valeur contrôlée. Voici comment appliquer une mise en forme conditionnelle à une cellule Excel selon une autre cellule.

1. Dans la même feuille de calcul, ajoutez votre valeur contrôlée dans une cellule. Ici, j'ajoute ma valeur contrôlée ('Min profit') de 1 153,00 $ (colonne H) à droite de mon tableau.

Mise en forme conditionnelle dans Excel – Basée sur une autre cellule

2. Allez dans Mise en forme conditionnelle > Nouvelle règle.

Mise en forme conditionnelle dans Excel – Créer une nouvelle règle

3. Une boîte de dialogue s'ouvre : sélectionnez "Classique" dans le champ "Style" et "Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué" dans le menu déroulant.

Mise en forme conditionnelle dans Excel – Utiliser une formule pour la mise en forme

4. En dessous, dans le champ de la formule, sélectionnez la cellule qui contient votre valeur contrôlée, ici "$H$2". Pour mettre en évidence uniquement les bénéfices supérieurs à ce minimum, sélectionnez les valeurs de Profit 5% à partir de F2 et saisissez la comparaison comme règle, par exemple =F2>$H$2.

Mise en forme conditionnelle dans Excel – Nouvelle règle avec valeur contrôlée

Toute valeur supérieure à celle indiquée est désormais mise en évidence selon cette règle de mise en forme conditionnelle. Vous savez maintenant créer une règle dynamique : lorsque le bénéfice minimum change, la règle s'ajuste à la valeur fixe.

Conclusion

La mise en forme conditionnelle d'Excel s'est révélée être un outil polyvalent. Comme ce guide l'a montré, les règles intégrées vous font déjà gagner du temps sur les tâches quotidiennes répétitives en appliquant des modifications en masse, comme un code couleur ou la suppression de cellules en double. Et si vous voulez qu'Excel effectue des actions plus avancées, avec plusieurs conditions ou des critères plus élaborés, vous pouvez aussi les combiner avec des formules.

Vous pouvez désormais gagner du temps pour nettoyer et organiser des jeux de données, grands ou petits. Nous espérons que ce guide vous a montré qu'une feuille de calcul Excel va bien au-delà d'un simple système de stockage de données et constitue une référence complète pour les tâches liées à la planification, à l'organisation et à la prise de décision.

Si vous voulez essayer la mise en forme conditionnelle dans Google Sheets, lisez notre article sur comment utiliser la mise en forme conditionnelle dans Google Sheets. Pour y repérer les entrées répétées, découvrez comment mettre en évidence les doublons dans Google Sheets.