Excel : Maîtrisez la Mise en Forme Conditionnelle Basée sur une Autre Cellule

La Mise en Forme Conditionnelle Excel : Vue d'Ensemble

La mise en forme conditionnelle est une fonctionnalité d'Excel qui permet d'appliquer automatiquement des formats (couleur de fond, couleur de police, icônes, etc.) à des cellules en fonction de règles que vous définissez. C'est un moyen efficace de visualiser rapidement des tendances, des anomalies ou des données importantes dans vos feuilles de calcul.

Les bases de la mise en forme conditionnelle

Avant d'aborder la mise en forme conditionnelle basée sur une autre cellule, il est important de comprendre les bases :

1. Sélectionner les cellules : Commencez par sélectionner les cellules auxquelles vous souhaitez appliquer la mise en forme conditionnelle.
2. Accéder à la mise en forme conditionnelle : Dans l'onglet « Accueil » du ruban Excel, cliquez sur « Mise en forme conditionnelle ».
3. Choisir une règle : Excel propose plusieurs règles prédéfinies, comme « Supérieur à », « Inférieur à », « Entre », « Égal à », etc.
4. Définir les critères : Spécifiez les critères de la règle (par exemple, la valeur à comparer).
5. Choisir un format : Sélectionnez le format à appliquer (couleur de fond, couleur de police, etc.).

Mise en Forme Conditionnelle en Fonction d'une Autre Cellule : Le Guide

C'est là que les choses deviennent intéressantes. Au lieu d'utiliser des valeurs fixes ou des règles prédéfinies, vous pouvez baser la mise en forme conditionnelle sur la valeur d'une autre cellule. Cela permet de créer des mises en forme dynamiques qui s'adaptent automatiquement aux changements dans votre feuille de calcul.

Exemple 1 : Mettre en évidence les lignes avec un statut spécifique

Imaginez que vous avez un tableau de suivi de projets avec une colonne « Statut » (par exemple, « En cours », « Terminé », « En attente »). Vous voulez mettre en évidence toutes les lignes dont le statut est « Terminé ».

Étapes :

1. Sélectionnez toutes les cellules de votre tableau (à l'exception des en-têtes).
2. Cliquez sur « Mise en forme conditionnelle » > « Nouvelle règle… ».
3. Choisissez « Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué ».

4. Dans le champ « Appliquer une mise en forme aux valeurs où cette formule est vraie : », entrez la formule suivante (en supposant que la colonne « Statut » est la colonne D et que la première ligne de données est la ligne 2) :

   =$D2="Terminé"

   Important : Le signe $ devant le D fixe la colonne, tandis que l'absence de $ devant le 2 permet à la ligne de s'incrémenter pour chaque ligne du tableau. Ceci est crucial pour que la règle s'applique correctement à toutes les lignes. 5. Cliquez sur « Format… » et choisissez le format que vous souhaitez appliquer (par exemple, une couleur de fond verte). 6. Cliquez sur « OK » pour fermer les boîtes de dialogue.

Explication de la formule :

• =$D2 : Fait référence à la cellule D2 (la première cellule de la colonne « Statut » dans la sélection). Le $ fixe la colonne D, ce qui signifie que la formule comparera toujours la valeur de la colonne D, quelle que soit la cellule à laquelle la mise en forme est appliquée.
• ="Terminé" : Compare la valeur de la cellule D2 à la chaîne de texte « Terminé ».
• Si la valeur de D2 est égale à « Terminé », la formule renvoie VRAI, et la mise en forme est appliquée à la ligne correspondante. Sinon, la formule renvoie FAUX, et la mise en forme n'est pas appliquée.

Capture d'écran (description textuelle) : Une capture d'écran montrant la fenêtre "Nouvelle règle de mise en forme", avec l'option "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué" sélectionnée. La formule =$D2="Terminé" est entrée dans le champ correspondant. Le bouton "Format..." est mis en évidence.

Exemple 2 : Comparer des valeurs entre deux colonnes

Supposons que vous avez deux colonnes de chiffres (par exemple, « Budget » et « Dépenses »). Vous voulez mettre en évidence les cellules de la colonne « Dépenses » qui dépassent le budget correspondant dans la colonne « Budget ».

Étapes :

1. Sélectionnez les cellules de la colonne « Dépenses » auxquelles vous souhaitez appliquer la mise en forme conditionnelle.
2. Cliquez sur « Mise en forme conditionnelle » > « Nouvelle règle… ».
3. Choisissez « Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué ».

4. Dans le champ « Appliquer une mise en forme aux valeurs où cette formule est vraie : », entrez la formule suivante (en supposant que la colonne « Budget » est la colonne B et la colonne « Dépenses » est la colonne C, et que la première ligne de données est la ligne 2) :

   =$C2>$B2

   Important : Ici, nous ne fixons pas les colonnes avec le signe $ car nous voulons comparer chaque cellule de la colonne C avec la cellule correspondante dans la colonne B. 5. Cliquez sur « Format… » et choisissez le format que vous souhaitez appliquer (par exemple, une couleur de fond rouge). 6. Cliquez sur « OK » pour fermer les boîtes de dialogue.

Explication de la formule :

• =$C2 : Fait référence à la cellule C2 (la première cellule de la colonne « Dépenses » dans la sélection).
• >$B2 : Compare la valeur de la cellule C2 à la valeur de la cellule B2 (la cellule correspondante de la colonne « Budget »).
• Si la valeur de C2 est supérieure à la valeur de B2, la formule renvoie VRAI, et la mise en forme est appliquée. Sinon, la formule renvoie FAUX, et la mise en forme n'est pas appliquée.

Capture d'écran (description textuelle) : Une capture d'écran montrant la fenêtre "Nouvelle règle de mise en forme", avec l'option "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué" sélectionnée. La formule =$C2>$B2 est entrée dans le champ correspondant. Le bouton "Format..." est mis en évidence.

Exemple 3 : Utiliser une liste déroulante pour contrôler la mise en forme

Vous pouvez créer une liste déroulante dans une cellule et utiliser la valeur sélectionnée dans cette liste pour contrôler la mise en forme conditionnelle d'autres cellules. Cela permet aux utilisateurs de modifier facilement les critères de mise en forme.

Étapes :

1. Créer la liste déroulante :
   • Sélectionnez une cellule où vous voulez créer la liste déroulante (par exemple, la cellule E1).
   • Allez dans l'onglet « Données » et cliquez sur « Validation des données ».
   • Dans l'onglet « Options », choisissez « Liste » dans le champ « Autoriser ».
   • Dans le champ « Source », entrez les valeurs que vous voulez afficher dans la liste déroulante, séparées par des virgules (par exemple, « Terminé, En cours, En attente »).
   • Cliquez sur « OK ».
2. Appliquer la mise en forme conditionnelle :
   • Sélectionnez les cellules auxquelles vous souhaitez appliquer la mise en forme conditionnelle.
   • Cliquez sur « Mise en forme conditionnelle » > « Nouvelle règle… ».
   • Choisissez « Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué ».

   • Dans le champ « Appliquer une mise en forme aux valeurs où cette formule est vraie : », entrez la formule suivante (en supposant que la liste déroulante est dans la cellule E1 et que la colonne « Statut » est la colonne D et que la première ligne de données est la ligne 2) :

     =$D2=$E$1

     Important : Ici, nous fixons la cellule E1 avec les signes $ car nous voulons toujours comparer la valeur de la colonne D avec la valeur de cette cellule spécifique. * Cliquez sur « Format… » et choisissez le format que vous souhaitez appliquer. * Cliquez sur « OK » pour fermer les boîtes de dialogue.

Explication de la formule :

• =$D2 : Fait référence à la cellule D2 (la première cellule de la colonne « Statut » dans la sélection).
• =$E$1 : Fait référence à la cellule E1 (la cellule contenant la liste déroulante). Le $ fixe la cellule E1, ce qui signifie que la formule comparera toujours la valeur de la colonne D à la valeur sélectionnée dans la liste déroulante.
• Si la valeur de D2 est égale à la valeur sélectionnée dans E1, la formule renvoie VRAI, et la mise en forme est appliquée. Sinon, la formule renvoie FAUX, et la mise en forme n'est pas appliquée.

Capture d'écran (description textuelle) : Deux captures d'écran. La première montre la fenêtre "Validation des données" avec l'option "Liste" sélectionnée et les valeurs "Terminé, En cours, En attente" entrées dans le champ "Source". La deuxième capture montre la fenêtre "Nouvelle règle de mise en forme", avec l'option "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué" sélectionnée. La formule =$D2=$E$1 est entrée dans le champ correspondant. Le bouton "Format..." est mis en évidence.

Conseils et Astuces pour une Mise en Forme Conditionnelle Efficace

• Utilisez des noms de plages nommées : Au lieu d'utiliser des références de cellules directes dans vos formules, vous pouvez définir des noms de plages nommées pour rendre vos formules plus lisibles et plus faciles à maintenir. Par exemple, vous pouvez nommer la colonne « Statut » « Statut » et utiliser la formule =$Statut2=$E$1.
• Gérez les règles de mise en forme conditionnelle : Excel dispose d'un gestionnaire de règles de mise en forme conditionnelle qui vous permet de visualiser, modifier, supprimer et réorganiser les règles appliquées à une feuille de calcul. Pour accéder au gestionnaire de règles, cliquez sur « Mise en forme conditionnelle » > « Gérer les règles… ».
• Copiez la mise en forme conditionnelle : Vous pouvez copier la mise en forme conditionnelle d'une cellule à une autre en utilisant l'outil « Reproduire la mise en forme » (le petit pinceau dans l'onglet « Accueil »).
• Soyez cohérent : Utilisez une palette de couleurs cohérente et des formats clairs pour rendre vos feuilles de calcul faciles à comprendre.
• Testez vos règles : Assurez-vous de tester vos règles de mise en forme conditionnelle avec différentes valeurs pour vous assurer qu'elles fonctionnent comme prévu.

Erreurs Courantes à Éviter

• Oublier de fixer les colonnes ou les lignes : C'est l'erreur la plus courante. Assurez-vous de bien comprendre quand utiliser le signe $ pour fixer les colonnes ou les lignes dans vos formules.
• Utiliser des formules incorrectes : Vérifiez attentivement vos formules pour vous assurer qu'elles sont correctes et qu'elles renvoient les résultats attendus.
• Appliquer trop de règles de mise en forme conditionnelle : Trop de règles peuvent rendre votre feuille de calcul difficile à lire et peuvent ralentir les performances d'Excel.
• Ne pas tester les règles : Testez toujours vos règles avec différentes valeurs pour vous assurer qu'elles fonctionnent correctement.

Conclusion

La mise en forme conditionnelle en fonction d'une autre cellule est un outil puissant pour dynamiser vos feuilles de calcul Excel et faciliter l'analyse de vos données. En maîtrisant les techniques présentées dans cet article, vous pourrez créer des mises en forme sophistiquées qui s'adaptent automatiquement aux changements dans vos données. N'hésitez pas à expérimenter et à explorer les nombreuses possibilités offertes par cette fonctionnalité.