Formatage conditionnel dans Excel : instructions et utilisation

Utilisez ces instructions pour créer une variété de formats

La mise en forme conditionnelle dans Microsoft Excel est un outil utile pour mettre en évidence des tableaux, des rapports ou des cellules, pour mettre en évidence une zone ou plusieurs colonnes, pour analyser des données, pour clarifier des différences ou pour identifier des tendances. Des barres de données, des jeux de symboles ou des échelles colorées sont utilisés pour cela. Selon les conditions spécifiées, l'affichage des cellules est modifié ou la plage de cellules est formatée. Vous pouvez créer le vôtre pour les nombreuses conditions intégrées.

La mise en forme conditionnelle a du sens et vous aide

La mise en forme conditionnelle dans Excel peut être appliquée de plusieurs manières, qu'il s'agisse d'un tableau Excel, d'une plage spécifique de cellules ou d'un rapport de tableau croisé dynamique. Diverses actions et modèles de format sont à votre disposition. L'avantage décisif pour les utilisateurs d'Excel est que des valeurs ou des résultats spéciaux peuvent être mis en évidence par un codage couleur. Si une valeur change, le formatage est automatiquement ajusté - en fonction des conditions.

Info: Le tableau croisé dynamique ("Tableau croisé dynamique") est un tableau spécial avec lequel une grande quantité de données peut être calculée, résumée et analysée. De plus, vous pouvez rapidement faire des comparaisons et identifier des tendances dans les données d'examen.

Mise en forme conditionnelle avec des règles existantes pour les nombres, le contenu du texte ou les dates

Si vous formatez avec un opérateur de comparaison, les cellules peuvent être trouvées plus facilement. Vous pouvez rendre les valeurs plus visibles, par exemple plus grandes ou plus petites qu'une valeur définie ou certaines valeurs qui se situent entre une limite supérieure et inférieure. Cela signifie que les données souhaitées ou recherchées sont affichées en couleur. Un formatage rapide ou un formatage avancé sont disponibles pour l'édition. Avec le formatage étendu, vous pouvez travailler avec des formules, par exemple pour marquer automatiquement une colonne Excel en couleur.

Exemples:

• Un commis d'entrepôt souhaite mettre en évidence tous les stocks de moins de 15 pièces avec un fond coloré.
• Toutes les villes de plus de 150 000 habitants doivent être indiquées dans un tableau.
• Une entreprise manufacturière souhaite mettre en évidence tous les biens fabriqués entre le 1er janvier 2022-2023 et le 31 janvier 2022-2023 en rouge.

Formatage rapide:

Sélection d'une ou plusieurs cellules.

Cliquez sur "Démarrer" dans l'onglet.

Allez à la flèche à côté du bouton "Mise en forme conditionnelle" dans "Style".

Cliquez sur "Règles de mise en évidence des cellules".

Sélectionnez la commande souhaitée, par ex. B. supérieur à, inférieur à, entre, date, etc.

Entrez les valeurs et sélectionnez le format.

Formatage étendu :

1. Sélectionnez une ou plusieurs cellules dans un tableau ou un rapport de tableau croisé dynamique.

2. Allez dans l'onglet "Accueil", dans le groupe Styles, cliquez sur la flèche à côté de "Mise en forme conditionnelle".

3. Cliquez sur "Gérer les règles" : La boîte de dialogue "Gestionnaire des règles de mise en forme conditionnelle" apparaît.

4. Sélectionnez l'action:

   • Cliquez sur « Nouvelle règle » : La boîte de dialogue « Nouvelle règle de mise en forme » apparaît.
   • Pour modifier un format, assurez-vous que la feuille de calcul ou le tableau correct est sélectionné dans la zone de liste « Afficher la règle de mise en forme pour ».
   • Dans le champ « S'applique à », accédez à « Réduire la boîte de dialogue »; puis sélectionnez la nouvelle plage de cellules sur la feuille de calcul.
   • Cliquez sur le bouton "Développer la boîte de dialogue".
   • Sélectionnez la règle et cliquez sur "Modifier la règle" : La boîte de dialogue "Modifier la règle de mise en forme" apparaît.

5. Pour modifier la plage des champs de la zone de valeur « Appliquer la règle à » d'un rapport de tableau croisé dynamique, procédez comme suit :

   • Sélection: Cliquez sur "Ces cellules uniquement"
   • Champ correspondant : Cliquez sur "Toutes les cellules avec les mêmes champs"
   • Champ de valeur : Cliquez sur "Toutes les cellules"

6. Sous "Sélectionner le type de règle", cliquez sur "Formater uniquement les cellules qui contiennent"

Vous pouvez maintenant effectuer des actions sous "Modifier la description de la règle" dans le champ de liste "Formater les cellules uniquement avec":

Format par numéro, date ou heure :

Pour ce faire, sélectionnez une valeur de cellule et un opérateur de comparaison et saisissez le nombre, la date ou l'heure. Exemple: Pour "Entre", entrez 300 et 500 ou sélectionnez "Égal à" et entrez une date.

Vous pouvez utiliser une formule pour récupérer un nombre, une date ou une valeur d'heure.

Format par texte :

Sélectionnez "Texte spécifique" et "Opérateur relationnel".

Saisissez du texte, par ex. B. sur "contient" le mot "or" ou sur "commence par" un préfixe que vous souhaitez rechercher.

Un maximum de 255 caractères peut être saisi.

Les valeurs peuvent être renvoyées à l'aide de formules. Pour ce faire, la formule doit commencer par (=). Les formules invalides ne sont pas formatées. Il est conseillé de tester d'abord la formule pour les erreurs.

Formater par date :

• Sélectionnez la date et la date de comparaison, par ex. B. Hier, la semaine prochaine ou ce mois-ci

Ensuite : Pour spécifier un format, cliquez sur "Format", la boîte de dialogue "Format des cellules" apparaît. Sélectionnez le format de nombre, le format de remplissage, le cadre ou la police souhaité, cliquez sur "OK".

Il est possible de choisir plusieurs formats. Vous pouvez voir quels formats ont été sélectionnés dans le champ "Aperçu".

Formatage conditionnel avec bases de données, schémas de couleurs et jeux de symboles

Le contenu et les valeurs des cellules peuvent être mis à niveau visuellement avec des bases de données, des échelles de couleurs ou des jeux de symboles et rendus plus attrayants et plus clairs. Si les valeurs d'une plage de cellules doivent être comparées les unes aux autres, c'est plus facile avec cette mise en forme conditionnelle.

Mettre en forme les cellules avec des barres de données

Les barres de données facilitent la comparaison des cellules entre elles. La valeur dans la cellule est indiquée par la longueur de la barre. Plus la valeur est élevée, plus la barre de données est longue, plus la valeur est basse, plus la barre est courte. Cela permet de déterminer rapidement des valeurs hautes ou basses.

Formatage rapide:

Marquez la zone souhaitée (cellule, tableau ou feuille entière).

Dans l'onglet Accueil, cliquez sur le bouton Mise en forme conditionnelle.

Allez dans "barre de données" et sélectionnez un remplissage.

Formater les cellules avec des bordures colorées

Les échelles de couleurs permettent de classer plus facilement les différentes informations de données et de pouvoir comparer rapidement leur contenu entre eux. Selon la nuance de couleur, des valeurs hautes ou basses sont affichées. Vous pouvez formater les cellules avec une échelle de 2 ou 3 couleurs.

Échelle à 2 couleurs

Formatage rapide:

Sélectionnez une cellule, un tableau ou une feuille entière.

Sous l'onglet Accueil, cliquez sur Mise en forme conditionnelle.

Allez dans "échelle de couleurs" et cliquez sur le format d'échelle de 2 couleurs souhaité. La couleur supérieure correspond aux valeurs les plus élevées, la couleur inférieure aux valeurs inférieures.

Échelle à 3 couleurs

La troisième couleur permet la visualisation des valeurs moyennes - en plus de la représentation en couleur des valeurs hautes et basses comme dans l'échelle à 2 couleurs.

Formatage rapide:

Sélectionnez une cellule, un tableau ou une feuille entière.

Sous l'onglet Accueil, cliquez sur Mise en forme conditionnelle.

Allez dans "échelle de couleurs" et cliquez sur le format d'échelle de 3 couleurs souhaité. La couleur supérieure correspond aux valeurs les plus élevées, la couleur inférieure aux valeurs inférieures.

Mettre en forme les cellules avec des jeux de symboles

Avec un ensemble de symboles, les données peuvent être visualisées et classées dans des catégories distinctes. Un symbole représente une certaine plage de valeurs. La mise en forme conditionnelle dans Excel propose différents symboles colorés : flèches, points, croix, points d'exclamation et bien plus encore.

Formatage rapide:

Sélectionnez une cellule, un tableau ou une feuille entière.

Sous l'onglet Accueil, cliquez sur Mise en forme conditionnelle.

Allez dans "Ensembles de symboles" et sélectionnez l'ensemble de symboles souhaité

Créez vous-même des règles de mise en forme

Si vous ne trouvez pas d'option appropriée dans la mise en forme conditionnelle offerte par Excel, vous pouvez facilement créer des règles individuelles de mise en forme.

Marquez la plage de cellules à formater de manière conditionnelle.

Allez dans "Mise en forme conditionnelle" dans l'onglet "Accueil".

Sélectionnez "Nouvelle règle" et validez par "OK".

Si vous ne trouvez pas l'option dont vous avez besoin pour votre propre règle de mise en forme, une formule logique peut vous aider. Par exemple, vous pouvez utiliser une formule pour colorer automatiquement les cellules Excel dès que le contenu est disponible. Inversement, les cellules vides peuvent être formatées de manière conditionnelle.

Exemple: L'application d'une formule vous permet de comparer des données en dehors de la plage sélectionnée. Cela inclut même les données qui se trouvent sur une feuille de calcul différente dans le même classeur. Ou vous pouvez comparer des valeurs avec un résultat renvoyé par une fonction. La formule doit être logique et renvoyer "true" ou "false".

En mettant en surbrillance les cellules directement sur la feuille de calcul, vous pouvez entrer des références de cellules dans une formule. Ce sont des références de cellules absolues. Cependant, Excel peut également ajuster les références de cellule pour chaque cellule de la zone sélectionnée. Si vous le souhaitez, sélectionnez les références de cellules relatives.

Instructions pas à pas pour créer une règle

1. Dans l'onglet "Accueil", groupe "Styles", allez sur la flèche à côté de "Mise en forme conditionnelle" et cliquez sur "Gérer les règles". La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche.

2. Les actions suivantes sont possibles :

   • Si vous souhaitez ajouter un format conditionnel, allez dans "Nouvelle règle".
   • Modifier un format conditionnel :
   • • Vérifiez que vous êtes sur la bonne feuille de calcul.
     • Tout d'abord, dans S'applique à, cliquez sur Réduire la boîte de dialogue. Sélectionnez ensuite la nouvelle plage de cellules et accédez à "Développer la boîte de dialogue".
     • Sélectionnez la règle souhaitée, cliquez sur "Modifier la règle". "Modifier la règle de formatage" apparaît.

3. Sous "Sélectionner le type de règle", cliquez sur "Utiliser la formule pour déterminer les cellules à formater".

   • Sous "Modifier la description de la règle", saisissez une formule dans "Valeurs de format pour lesquelles cette règle est vraie". La formule commence par (=) et doit retourner « vrai » (1) ou « faux » (0).
   • Cliquez sur Format pour afficher la boîte de dialogue Format de cellule.
   • Sélectionnez maintenant le numéro, la police, le format de remplissage, etc. qui doivent être utilisés si la valeur de la cellule est vraie et confirmez avec "OK". Les formats sélectionnés sont affichés dans "Aperçu".

Par exemple, la formule COUNTIF renvoie des valeurs logiques. Si cette formule est utilisée, des valeurs en double peuvent être déterminées ou le contenu qui se produit à une certaine fréquence peut être mis en évidence en couleur. De cette façon, des objets uniques peuvent également être trouvés et mis en évidence en couleur. D'autres formules qui peuvent être utilisées pour la mise en forme conditionnelle sont

• REST : les couleurs de ligne en alternance peuvent être définies automatiquement
• IDENTIQUE : Avec cette formule, les données que vous recherchez peuvent être marquées dans un tableau Excel qui correspond à une valeur spécifiée
• ISERROR : marque automatiquement les cellules vides dans le tableau Excel ou fait disparaître les valeurs d'erreur du classeur
• NOT et ISNUMBER : formate toutes les cellules Excel qui ne contiennent pas de nombres
• MAX : la plus grande valeur de la cellule est mise en évidence dans le tableau
• MIN : permet la mise en forme conditionnelle de la plus petite valeur dans la liste Excel afin que le minimum puisse être vu d'un coup d'œil
• GAUCHE : en fonction d'une chaîne de caractères au début, les cellules Excel sont automatiquement formatées et reçoivent une couleur de texte uniforme
• GRAND et GAUCHE : permettent de colorer une cellule avec un nouveau contenu
• DROITE : les cellules peuvent être formatées en fonction du dernier caractère de la chaîne
• AUJOURD'HUI: Avec cela, les rendez-vous peuvent être formatés en couleur à l'aide de la méthode des feux de circulation ou les valeurs de date peuvent être mises en évidence qui seront bientôt mises à jour
• MOIS ou ANNÉE : permet de diviser la liste Excel en mois ou en années; Avec YEAR, les valeurs de date de l'année en cours peuvent également être marquées et les valeurs annuelles peuvent être mises en évidence
• OU et JOUR SEMAINE : met en évidence les valeurs selon le jour de la semaine en couleur

Exemple de création d'une règle pour les vérifications ET et OU :

Pour cet exemple, deux valeurs sont utilisées pour la mise en forme conditionnelle. Si la première règle ne s'applique pas, la deuxième règle s'applique automatiquement.

Première règle : Un acquéreur souhaite vérifier si l'achat immobilier envisagé est conforme à son budget. Il peut apporter jusqu'à 80 000 € de ses fonds propres à titre d'acompte et assurer le service d'une mensualité de prêt d'un montant maximum de 1 200 €. Si à la fois l'acompte et le versement du prêt remplissent ces conditions, les cellules correspondantes sont marquées en vert.

Deuxième règle : Si l'acompte ou l'acompte ne répondent pas aux exigences, les cases correspondantes sont marquées en rouge.

La formule pour créer la première règle en utilisant la couleur verte est la suivante :

= ET (SI ($ G$ 4 <= 80000,1); SI (ABS ($ G $ 5) <= 1200,1))

La formule pour créer la deuxième règle en utilisant la couleur rouge est la suivante :

= OU (SI ($ G$ 4 = 1200,1))

Transférer et supprimer la mise en forme conditionnelle

La mise en forme conditionnelle peut être copiée et appliquée à d'autres ou à de nouvelles données dans la feuille de calcul ou supprimée à nouveau.

Transférer la mise en forme conditionnelle

Pour copier la mise en forme conditionnelle et la coller dans d'autres cellules ou plages de cellules, utilisez le bouton "Transférer le format".

Sélectionnez les cellules avec la mise en forme conditionnelle que vous souhaitez copier et coller dans d'autres cellules et cliquez dessus.

Allez maintenant dans "Démarrer" puis "Format de transfert". Le curseur apparaît sous la forme d'un pinceau.

Vous pouvez ajouter une mise en forme conditionnelle à une cellule ou à une plage de cellules en déplaçant le pinceau sur la cellule.

Avec ESC, vous mettez fin à l'utilisation de la brosse.

Vous pouvez toujours utiliser le pinceau pour insérer une mise en forme conditionnelle en double-cliquant sur "Transférer le format".

Alternativement, la mise en forme conditionnelle peut être copiée à l'aide des raccourcis clavier et collée dans une autre cellule Excel.

Effacer la mise en forme conditionnelle

Une distinction est faite entre un processus de suppression sur une feuille de calcul et un processus de suppression dans une plage de cellules.

Effacer sur une feuille de calcul ou dans toute la feuille de calcul

Allez dans "Mise en forme conditionnelle" dans l'onglet "Accueil".

Cliquez ensuite sur "supprimer les règles"

Cliquez ensuite sur "Supprimer les règles dans toute la feuille".

Vous pouvez également supprimer la mise en forme conditionnelle à l'aide de la boîte de dialogue "Sélectionner le contenu".

Supprimer dans la plage de cellules

Mettez en surbrillance les cellules qui contiennent la mise en forme conditionnelle souhaitée.

Cliquez sur le bouton "Loupe d'analyse de lecture" (le bouton est situé en bas à droite à côté des données sélectionnées).

Allez dans "Supprimer le format".

Rechercher et supprimer les mêmes formats conditionnels dans toute la feuille de calcul

Cliquez sur la cellule avec le format conditionnel approprié.

Dans l'onglet "Accueil", cliquez sur la flèche à côté de "Rechercher et sélectionner", puis cliquez sur "Sélectionner le contenu".

Cliquez sur « Formats conditionnels ».

Sous "Vérification des données", allez à "Identique" (cela sélectionnera toutes les cellules avec le même formatage conditionnel).

Dans l'onglet "Démarrer", allez dans "Mise en forme conditionnelle", puis "Supprimer les règles" et "Supprimer les règles dans les cellules sélectionnées".

Mise en forme conditionnelle dans le rapport de tableau croisé dynamique

Avec un rapport de tableau croisé dynamique, les utilisateurs peuvent rapidement résumer de grandes quantités de données. Plage de cellules, feuille de calcul Excel ou rapport de tableau croisé dynamique - la mise en forme conditionnelle fonctionne pratiquement de la même manière partout. Le rapport de tableau croisé dynamique ne contient que quelques fonctionnalités spéciales que vous devez connaître :

• Certains formats conditionnels ne fonctionnent pas sur les champs de la plage de valeurs d'un rapport de tableau croisé dynamique.
• Si vous modifiez la disposition du rapport de tableau croisé dynamique de quelque manière que ce soit, la mise en forme conditionnelle persistera jusqu'à ce que les champs des données sous-jacentes soient supprimés.
• La mise en forme conditionnelle des champs dans la plage de valeurs peut être basée sur la hiérarchie des données. La plage de cette mise en forme conditionnelle est déterminée par le niveau immédiatement inférieur ou immédiatement supérieur de la hiérarchie en lignes pour une ou plusieurs colonnes ou en colonnes pour une ou plusieurs lignes.
• La plage de mise en forme conditionnelle des champs de la plage de valeurs peut être définie à l'aide de trois méthodes :

• • sélection
  • Champ correspondant
  • Champ de valeur

La méthode par défaut est la sélection.

Définition de zone par sélection

Allez dans "Appliquer la règle de formatage à".

Cliquez sur la boîte de dialogue Nouvelle règle de mise en forme ou sur la boîte de dialogue Modifier la règle de mise en forme.

Cette méthode convient à une série contiguë de champs dans la plage de valeurs, par ex. B. des données d'enquête pour une région, ainsi que pour une série de champs non contigus dans la plage de valeurs, telles que des données d'enquête pour différentes régions à différents niveaux de la hiérarchie des données.

Définition de zone via un champ correspondant

Cette méthode fonctionne si vous avez

• voulez éviter d'avoir à sélectionner de nombreux éléments sans rapport
• souhaitez formater de manière conditionnelle plusieurs champs dans la plage de valeurs pour un niveau de hiérarchie de données
• Vous voulez exclure des résultats partiels

Définition de zone via un champ de valeur

Cette méthode fonctionne si vous avez

• voulez éviter d'avoir à sélectionner de nombreux éléments sans rapport
• souhaitez mettre en forme conditionnellement plusieurs champs dans la plage de valeurs pour tous les niveaux hiérarchiques
• Vous souhaitez inclure des totaux et des résultats partiels.

Conclusion : la mise en forme conditionnelle dans Excel facilite le suivi des données

Si vous souhaitez mettre en évidence certaines valeurs de données dans votre tableau Excel en couleur, la mise en forme conditionnelle dans Excel est idéale. Vous avez deux options pour appliquer la mise en forme conditionnelle. Soit vous utilisez les règles fournies par Excel pour mettre en évidence du contenu textuel, des valeurs numériques ou des dates en couleur. Ou vous pouvez définir vous-même une règle avec diverses conditions dans lesquelles les valeurs pertinentes doivent être marquées. Vous pouvez facilement et efficacement comparer les données entre elles à l'aide de barres de données, de barres de couleurs et de jeux de symboles. Si les options de conception ne sont plus nécessaires, vous pouvez les supprimer en quelques clics.

Plus d'articles sur la mise en forme conditionnelle

• Comment générer automatiquement des flèches dans les listes Excel
• Afficher les valeurs de temps négatives dans les tableaux Excel
• Colorer les pourcentages négatifs en rouge
• Reconnaître les pourcentages négatifs en un coup d'œil
• Formater les nombres avec un format personnalisé en couleur
• Mettre en forme les cellules Excel qui n'ont pas la valeur spécifiée
• Stockez automatiquement les cellules avec les formats de devise correspondants
• Marquer les cellules en couleur à l'aide d'une fonction macro
• Utilisez une mise en forme conditionnelle et mettez en surbrillance les données supérieures ou égales à la troisième plus grande cellule
• Valeurs de vérité inversées