7. Mise en forme conditionnelle
La mise en forme conditionnelle permet de définir une mise en forme (essentiellement des couleurs de police ou d’arrière-plan de cellules…) qui sera appliquée aux cellules répondant à une condition. Cela a pour intérêt de mettre en évidence des cellules qui répondent à certains critères ou de montrer des variations ou des tendances.
Un grand avantage avec cette technique est qu’après avoir défini une mise en forme conditionnelle, Excel met à jour automatiquement la mise en forme, chaque fois que vous opérez des mises à jour au niveau de votre feuille.
Pour définir une mise en forme conditionnelle, nous utiliserons :
- Soit la commande Mise en forme conditionnelle de l’onglet Accueil du ruban
- Soit le bouton Analyse rapide
Nous allons vous donner une présentation de ce bouton Analyse rapide. Ensuite, nous verrons une multitude d’exemples concrets de mise en forme conditionnelle.
7.1. Le bouton Analyse rapide
Ce bouton introduit à partir de la version 2013 d’Excel, apparait dès que vous sélectionnez une plage de cellules… au coin inférieur droit de la sélection : 
Cliquez sur le bouton Analyse rapide, pour voir apparaître des options de mise en forme conditionnelle.
|
Si les cellules sélectionnées contiennent uniquement du texte, les options qui apparaissent sont :
|
 |
|
Si les cellules sélectionnées contiennent des nombres, les options qui apparaissent sont :
|
 |
D’autres commandes de mise en forme conditionnelle sont disponibles à partir de la commande Mise en forme conditionnelle du groupe Styles de l’onglet Accueil du ruban.
7.2. Valeurs inférieures à, supérieures à, comprises entre
Soit l’extrait suivant d’une feuille de calcul :
Appliquons une mise en forme pour les valeurs de la colonne B comme suit :
- Valeurs supérieures à 8000 en vert
- Valeurs comprises entre 4000 et 8000 en jaune
- Valeurs inférieures à 4000 en rouge
Sélectionnez la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Supérieurs à :
Au niveau de la boite de dialogue Supérieur à qui apparaît, entrez la valeur 8000 et sélectionnez Remplissage vert avec texte vert foncé :
Gardez la plage B2:B14 sélectionnée et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Entre :
Au niveau de la boite de dialogue Entre, entrez la valeur 4000, la valeur 8000 et sélectionnez Remplissage jaune avec texte jaune foncé :
Sélectionnez encore la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Règles de mise en surbrillance des cellules, puis choisissez Inférieurs à :
Au niveau de la boite de dialogue Inférieur à, entrez la valeur 4000 et sélectionnez Remplissage rouge clair avec texte rouge foncé :
Voilà le résultat :
Modifions une valeur ; par exemple au niveau de la ligne 4, mettons la valeur 2200 et validons par la touche Entrer du clavier. Remarquez que la cellule B4 prend automatiquement la couleur rouge :
7.3. Cellules contenant le texte
Soit l’extrait de feuille de calcul suivante :
Supposons que nous voulons montrer les cas habitants au Cours Victor Hugo. Procédons comme suit :
Sélectionnez la plage C4:C16. Le bouton Analyse rapide apparait en bas de la sélection. Cliquez sur le Bouton Analyse rapide puis sur le bouton Cellules contenant le texte:
La boite de dialogue Texte qui contient apparait :
Au niveau de cette boite de dialogue, vous pouvez saisir le texte cherché, c’est-à-dire « Victor Hugo ». Mais, puisque le texte cherché est écrit à la cellule B1, cliquez sur cette cellule. Excel ajoute la valeur « =$B$1 ». L’avantage est que, si vous voulez chercher un autre texte, il suffit de le saisir au niveau de la cellule B1, sans avoir à refaire la mise en forme.
Au niveau de la liste des formats, vous pouvez choisir un format de la liste ou bien choisir Format personnalisé…
En choisissons Format personnalisé…, la boite de dialogue Format de cellule apparaît. Vous pouvez composer ici le format qui vous convient en utilisant les 4 onglets de cette boite de dialogue:
Choisissez un format et validez en cliquant sur le bouton OK.
Cliquez ensuite sur le bouton OK de la boite de dialogue Texte qui contient. Remarquez les champs mis en évidence à l’aide du format que vous avez choisi:
Maintenant, modifiez le texte de la cellule B1 par « Bergeret » par exemple, et validez par la touche Entrer du clavier. Remarquez que la mise en forme est actualisée automatiquement par Excel:
7.4. Valeurs en double
Soit l’extrait de la feuille de calcul suivante contenant au niveau de la colonne A des adresses Mail :
Normalement, il ne doit pas y avoir d’adresse Mail en double, sinon, il s’agit d’erreur de saisi. Utilisons donc cette technique de mise en forme conditionnelle pour retrouver et corriger les cas éventuels de doublons.
Sélectionnez la plage A2:A14 et cliquez sur le Bouton Analyse rapide puis sur le bouton Valeurs en double:
Excel met en évidence les valeurs en double de la sélection:
7.5. Barres de données
Soit l’extrait suivant d’une feuille de calcul :
Sélectionnez la plage B2:B14 et cliquez sur le Bouton Analyse rapide puis sur le bouton Barres de données:
Vous pouvez procéder autrement. Sélectionnez la plage B2:B14 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Barres de données, puis choisissez la couleur qui vous convient :
Excel montre l’importance des valeurs de la sélection de manière similaire à un graphique :
7.6. Jeux d’icônes
Soit l’extrait suivant d’une feuille de calcul :
Sélectionnez la plage B2:B15 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Jeux d'icônes, puis choisissez un ensemble d’icônes :
Le résultat est comme suit :
L’inconvénient avec ce résultat est que la couleur verte indique les grands nombres d’infractions (6 et 7), alors qu’elle doit indiquer les petits nombres. D’autres part, je suppose que nous voulons une répartition différente entre les 3 couleurs, soit par exemple :
- Couleur rouge pour 4 et plus
- Couleur orange pour 2 et 3
- Couleur verte pour 1
Pour avoir ce résultat, procédez comme suit :
Sélectionnez encore la plage B2:B15 et cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Jeux d'icônes, puis choisissez Autres règles :
La boîte de dialogue Nouvelle règle de mise en forme apparaît :
Cliquez sur le bouton Ordre inverse des icônes, modifiez le Type en « Nombre » et les valeurs comme suit :
Validez à l’aide du bouton OK. Vous deviez avoir le résultat suivant :
7.7. Nouvelle règle de mise en forme
Soit l'extrait de la feuille de calcul suivante:
Nous voulons mettre en évidence les noms des abonnées pour lesquels le don dépasse 4 000 Euros et qui habitent à Paris par exemple. Pour cela, nous allons créer une nouvelle règle de mise en forme.
- Sélectionnez la plage de cellules à la colonne A où est inscrit les noms des abonnées. Par exemple la plage A2:A80.
- Cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Nouvelle règle..
Au niveau de la boite de dialogue Nouvelle règle de mise en forme qui apparaît :
- Cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué de la liste Sélectionnez un type de règle...
- Au champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie entrez la formule :
=ET(E2>4000;F2="Paris")
Le résultat de cette formule est vrai lorsque les conditions 'E2>4000' et 'F2="Paris"' sont tous les deux vrais. Consultez nos rubriques sur les Fonctions Excel 2016 pour plus d'information sur la fonction ET.
- Cliquez ensuite sur le bouton Format... pour ouvrir la boite de dialogue Format de cellule et choisissez le format qui vous convient. L'utilisation de cette boite de dialogue est mentionnée à la section 7.3. ci-dessus.
- Valider à l'aide du bouton OK.
Voici le résultat :
J'attire votre attention sur le fait que dans la formule j'ai utilisé les adresses des cellules E2 et F2. Ces cellules correspondent à la 1ère cellule de la sélection. C'est à dire celle située en haut à gauche de la sélection; soit A2. La formule est appliquée aux autres cellules de la sélection de manière similaire à celle de la copie incrémentée.
Et si nous voulons mettre en évidence toute la ligne pour laquelle la condition est vraie. Comme ça :
Explications à la section suivante.
7.8. Consulter et modifier les règles de mise en forme
Modifions la règle définie à la section précédente afin de mettre en évidence toute la ligne pour laquelle la condition est vraie :
- Cliquez sur une cellule de la plage A2:A80 ; là où est définie la mise en forme conditionnelle
- Cliquez sur la commande Mise en forme conditionnelle au niveau de l’onglet Accueil du ruban, choisissez Gérer les règles...
- La boite de dialogue Gestionnaire des règles de mise en forme conditionnelle s'ouvre
Au niveau de cette boite de dialogue,
- Vous pouvez afficher les règles de mise en forme pour :
- La sélection actuelle
- La feuille en cours
- Ou chacune des feuilles du classeur en cours
- La liste des règles est affichée au tableau en bas. A l'extrait ci-dessus, deus lignes correspondant à deux règles de mise en forme
- Les boutons Nouvelle règle..., Modifier la règle... et Supprimer la règle... permettent comme l'indiquent leurs noms de :
- Créer une nouvelle règle de mise en forme
- Modifier la règle sélectionnée à la liste en bas
- Supprimer la règle sélectionnée à la liste en bas
Je rappelle que nous voulons Modifier la règle définie à la section précédente afin de mettre en évidence toute la ligne pour laquelle la condition est vraie à savoir; don supérieur à 4000 et ville égal Paris. Pour cela, à la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle :
- Modifier le champ S'applique à pour la règle en :
=$A$2:$F$80
NB. Remarquez que ce champ est modifiable à la boite de dialogue.
NB. Vou pouvez maintenant cliquez sur le bouton OK et voir le résultat. le reste de la ligne ne prend pas la mise en forme définie.
Pourquoi ? Je rappelle encore une fois qu'avec les références de cellule relatives E2 et F2, Excel incrémente le E et le F pour les cellules des colonnes B, C, ... C'est à dire que pour la cellule B2 par exemple, Excel vérifiera les conditions aux cellules F2 et G2...
Il faut, donc, modifier la formule de la règle en : =ET($E2>4000;$F2="Paris"). Pour cela :
- Cliquez sur la ligne de la règle que nous voulons modifier
- Cliquez sur le bouton Modifier la règle...
- Au niveau de la boite de dialogue Modifier la règle de mise en forme qui apparaît, entrez au champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie la formule :
=ET($E2>4000;$F2="Paris")
- Cliquez sur le bouton OK pour revenir à la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle
- Cliquez sur le bouton OK et voyez le résultat :
7.9. Annuler la mise en forme conditionnelle
Pour annuler la mise en forme conditionnelle pour une plage de cellules, sélectionnez cette palge de cellule et utilisez le bouton Analyse rapide, puis le bouton Annuler la mise en forme :
NB. Vous pouvez également annuler une mise en forme conditionnelle en passant par la boite de dialogue Gestionnaire des règles de mise en forme conditionnelle comme mentionné à la section précédente.
7.10. Copie de mise en forme conditionnelle
| S’agissant d’une mise en forme, sa copie d’une plage de cellules à une autre doit se faire à l’aide du bouton Reproduire la mise en forme |  |
Mais, si vous faites une copie de contenu de cellules, par exemple, à l’aide de la technique de copie incrémentée, alors la mise en forme est copiée par Excel en même temps.
Exercice
Exercice – Recherche de doublons