La mise en forme conditionnelle permet de mettre en évidence certaines valeurs ou de rendre des cellules faciles à identifier. Elle modifie l'apparence d'une plage de cellules en fonction d'une condition ou de plusieurs critères. Dans cet article, vous allez apprendre les astuces de la mise en forme conditionnelle et comprendre comment mettre en évidence les données que vous souhaitez avancer dans votre analyse.
Nous utiliserons le tableau ci-dessous tout au long de l’article afin d’illustrer les différentes fonctionnalités de la mise en forme conditionnelle excel. Le tableau le prix de différentes variétés de plantes en fonction de la date à laquelle elles sont vendues.
1/ Mise en forme conditionnelle à l’aide du Ruban Excel
- Dans un premier temps, sélectionnez la plage des montants puis cliquez sur Mise en forme conditionnelle de l’onglet Accueil.
- Si vous choisissez Règles de mise en surbrillance des cellules, plusieurs options s’offrent à vous. Vous pouvez surligner les montants qui sont supérieurs, inférieurs à un certain montant, compris entre deux montants, égaux à un certain montant… et même constituer vos propres règles à l’aide du menu “Autres règles…”
Exemple 1
Lorsque vous sélectionnez “inférieur à”, vous pouvez saisir un montant maximum (ex: 10) en dessous duquel les valeurs seront mises en surbrillance comme ci-dessous : toutes les plantes dont le prix est inférieur à 10€ seront mises en surbrillance grâce au format personnalisé que vous aurez choisi. Ici, les cellules remplissant ce critère ressortent bleues avec des caractères rouges.
Exemple 2
Vous pouvez également faire ressortir toutes les cellules contenant un certain texte, ou des dates se produisant hier, aujourd’hui, demain, dans les 7 derniers jours, la semaine dernière, cette semaine, la semaine prochaine, le mois dernier, ce mois, ou le mois prochain
- Vous pouvez aussi choisir Règle des valeurs de plage haute/ basse. Si vous voulez mettre en rouge les 5 valeurs les plus élevées, il vous faut sélectionner “10 valeurs les plus élevées” puis remplacer 10 par 5 dans la boîte de dialogue. Vous pouvez ensuite modifier le format dans lequel vous souhaitez faire apparaître vos valeurs (ex: Remplissage rouge clair avec texte rouge foncé).
Vous obtenez ceci :
- Vous pouvez aussi choisir Barres de données, Nuances de couleurs ou Jeux d’icônes.
Exemple avec Jeu d’icônes
Choisissez 3,4 ou 5 icônes selon le nombre de tranches que vous souhaitez.
- Pour modifier ces règles à votre convenance, cliquez dans Accueil /Mise en forme conditionnelle /Gérer les règles.
Et en double cliquant sur la règle Jeu d’icônes on obtient :
(À modifier comme on le souhaite)
Si on a défini plusieurs règles pour une plage de cellule et que l’on a coché Interrompre si vrai les règles suivantes ne seront plus appliquées si la première est vraie.
Attention : quand on copie / colle des cellules, la mise en forme conditionnelle est aussi copiée.
N’hésitez pas à aller dans Gérer les règles dans Accueil / Mise en forme conditionnelle pour créer, modifier ou supprimer une règle. Regardez bien les plages de cellules auxquelles ces règles s’appliquent.
Comme il faut bien sélectionner la plage de cellules avant de modifier les règles, il serait judicieux de nommer cette plage dans le ruban Formules / Définir un nom afin de pouvoir se retrouver si votre feuille Excel contient beaucoup de données.
Et ensuite il suffit de sélectionner le nom dans la liste déroulante à gauche de la barre de formule, avant de modifier les règles de la mise en forme conditionnelle.
On peut aussi modifier la priorité des règles avec les flèche.
2/ Mettre en forme toute une ligne
Plus intéressant ! Excel permet également de mettre toute une ligne et non seulement une colonne en couleur en fonction de la valeur d’une colonne :
Pour obtenir le tableau de gauche il faut sélectionner la plage de cellules qui correspond à la colonne Plante puis cliquer sur l’onglet Accueil / Mise en forme conditionnelle, sélectionnez le type de règle : Appliquer une mise en forme uniquement aux cellules qui contiennent.
Pour obtenir le tableau de droite il faut sélectionner tout le tableau et utiliser une formule avec une référence mixte : figer la colonne mais pas la ligne, c’est-à-dire mettre un dollar devant la colonne et rien devant la ligne ou appuyer 3 fois sur la touche F4 ou Fn F4 :
3/ Insertion de fonctions dans la mise en forme conditionnelle
On peut aussi insérer toutes les fonctions d’Excel dans la mise en forme conditionnelle si on choisit utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Cas pratique 1
Représenter sur un planning les dates de début et de fin de vente des plantes comme ci-dessus. Figer la colonne pour les dates de début et de fin et la ligne pour les dates du mois avec des références mixtes et utiliser la fonction ET.
Il faut sélectionner la plage de cellules quadrillée du tableau et faire Accueil/ Mise en Forme Conditionnelle / Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué :
Ecrire la formule : =ET(F$3>=$A4;F$3<=$B4) puis cliquez sur le bouton Format… et onglet Remplissage
Cas pratique 2
Mise en surbrillance des week-ends et de la date du jour dans un planning
Créons deux règles en cliquant sur Nouvelle Règle puis Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et dans la zone Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie, saisir votre formule mais vous ne pouvez pas insérer automatiquement les fonctions, il faut les connaître.
Cliquez une nouvelle fois sur Nouvelle Règle ou bien dans Gérer les règles pour voir toutes les règles de votre sélection
Voici les deux formules à écrire :
=F$10=AUJOURDHUI()
=OU(JOURSEM(F$10)=7;JOURSEM(F$10)=1)
Mettez une référence mixte en figeant la ligne qui contient toutes les dates du mois et utilisons la fonction Joursem qui renvoie un numéro de 1 à 7 du dimanche au samedi.
On peut revenir sur les formules en cliquant sur Gérer les règles et double cliquer sur la règle que l’on veut modifier.
Voici le résultat obtenu :
