Mise en forme conditionnelle - Utilisation des règles prédéfinies

Continuons de parler dans cette formation Excel de la mise en forme conditionnelle et avec ce deuxième article qui va vous montrer comment utiliser les deux groupes d’outils de la mise en forme conditionnelle qui sont : Règles de mise en surbrillance des cellules et Règles des valeurs plus/moins élevées.
Mise en forme conditionnelle -les règles prédéfinies


Choisissez votre règle puis appliquer la mise en forme

Dans le premier article de la mise en forme conditionnelle, vous avez vu comment appliquer une mise en forme à une plage de cellules en répondant à une règle prédéfinie par Excel.
Mais avec ces deux groupes d’outils, vous n’allez mettre en forme que quelques cellules de votre plage sélectionnée, qui répondent aux critères que vous allez définir à l’aide des opérateurs de comparaison présents dans ces deux groupes.

Liste des règles - Mise en forme conditionnelle


Appliquer une mise en forme conditionnelle à l’aide des règles de mise en surbrillance des cellules

  • Veuillez tout d’abord cliquez sur la commande Mise en forme conditionnelle puis sélectionner Règles de mise en surbrillance des cellules.
Règles de mise en surbrillance de cellules

  • Dans le sous-menu qui apparaît, vous avez une liste d’opérateurs de comparaison que vous pouvez appliquer à des valeurs de type :
    • Numérique : Supérieur à, inférieur à, Entre, Egal à et valeurs en doubles
    • Texte: Texte qui contient, Egal à
    • Date : Une date se produisant et aussi Supérieur à, inférieur à, Entre, Egal à

Appliquer une mise en forme à des cellules contenant des données numériques

Téléchargez ce classeur exemple sur lequel on va travailler : Factures.xlsx
La méthode qui suit peut s’appliquer à des nombres, des nombres monétaires, nombres scientifiques…. 
Sélection de la plage de cellules

  • Cliquez sur la commande Mise en forme conditionnelle puis sélectionnez Règles de mise en surbrillance des cellules.
  • Dans le sous-menu qui apparaît, cliquez sur Supérieur à….
Opérateur supérieur à

  • Dans la boîte de dialogue qui s’ouvre, Excel affiche par défaut un nombre dans la zone d’entrée de valeurs et applique une mise en forme.
Boite de dialogue Supérieur à

  • Supprimer donc ce nombre et entrez votre valeur désirée ou bien cliquez sur le bouton Réduire la boîte de dialogue puis sélectionnez un montant de votre plage de cellules.
  • Cliquez encore sur le bouton Réduire la boîte de dialogue.
Saisir un critère de mise en forme conditionnelle

  • Dans la zone avec, cliquez sur la flèche pour sélectionner la mise en forme à appliquer ou cliquez sur Format personnalisé pour définir votre mise en forme personnelle.
Choisir la mise en forme

  • La boite de dialogue Format de cellule s’affiche, vous pouvez donc définir taille de police et sa couleur:
Mettre en forme le texte de la cellule



 la bordure de la cellule :

Mettre en forme de la bordue de la cellule

et la couleur de remplissage:

Remplissage de la cellule

  • Quand vous aurez terminé, cliquez sur le bouton OK.
  • Validez enfin votre choix et remarquez le résultat.
Mise en forme appliqué à cellules contenant des nombres

  • De la même manière vous pouvez appliquer une mise en forme en utilisant les autres opérateurs : Inférieur à…., Entre…, Egal à …, (A vous de jouer :) !!
  • L’opérateur Valeurs en double : vous permet de mettre en forme soit les cellules contenant des valeurs répétées ou les valeurs uniques.
Valeurs en doubles- Mise en forme conditionnelle


Appliquer une mise en forme à des cellules contenant des données textuelles

Valeurs de type texte

  • Cliquez sur la commande Mise en forme conditionnelle puis sélectionnez Règles de mise en surbrillance des cellules, et cliquez sur Texte qui contient…
Opérateur Texte qui contient

  • De la même façon donc tapez votre critère et choisissez la mise en forme à appliquer.
Mise en forme conditionnelle - valeurs textuelles

  • Validez enfin.
  • Dans la zone de critère, tapez par exemple une seule lettre R, et vous remarquerez que toutes les cellules contenant des noms où se trouve la lettre R, sont mises en forme.
Noms de villes contenant R

  • Vous pouvez utiliser aussi des caractères génériques : * et ?
    • Tapez R*S : pour trouver les noms de villes où la lettre R vient avant la lettre S dans l’ordre des lettres.
Noms de villes contenant R et S

    • Si vous tapez S*R, vous allez voir la différence.
    • Tapez maintenant R?, le nom Montpellier sera éliminé de la liste car la lettre R n’est suivie d’aucun caractère.
Utiliser le point d'interrogation dans les critères


Appliquer une mise en forme à des cellules contenant des données de type date

  • Sélectionnez la plage de cellules contenant des dates puis cliquez sur Une date se produisant …
  • Dans la boite de dialogue qui s’affiche, il suffit tout simplement de sélectionner le critère à partir de la liste disponible et de définir la mise en forme à appliquer.
Cellules avec valeurs Date

  • Et si vous voulez saisir vos dates manuellement, utilisez les opérateurs Supérieur à, Inférieur à, Entre, Égal à…
  • Cliquez par exemple sur Entre dans le menu Règles de mise en surbrillance des cellules.
Opérateur Entre

  • Dans la boite de dialogue qui s’affiche, entrez la première date et la dernière date puis choisissez la mise en forme à appliquer et validez.
Mise en forme - opérateur Entre


Appliquer une mise en forme conditionnelle à l’aide des Règles des valeurs plus/moins élevées

A partir du groupe Règles des valeurs plus/moins élevées, vous pouvez mettre en forme les cellules contenant par exemple :

10 Valeurs les plus élevées

  • Sélectionnez votre plage de cellules puis cliquez sur la commande Mise en forme conditionnelle puis sélectionnez Règles des valeurs plus/moins élevées.
  • Dans le sous-menu qui apparaît, cliquez sur 10 Valeurs les plus élevées

  • Dans la boite de dialogue qui s’affiche, sélectionnez la mise en forme à appliquer.
Choix de la mise en forme

  • Vous pouvez aussi modifier la valeur 10 par d’autres chiffres, par exemple entrez 5 si vous aimez que les cellules contenant les 5 premières valeurs soient mise en forme.

10% des valeurs les plus élevées

Dans le premier exemple, vous avez défini un nombre des valeurs à choisir, mais dans cet exemple vous allez choisir un taux de valeurs.
  • Sélectionnez donc votre plage de cellules puis cliquez sur 10% des valeurs les plus élevées dans le sous-menu Règles des valeurs plus/moins élevées
10% des valeurs élevées

  • Une boîte de dialogue s’affiche comme dans l’exemple précédent.
  • Excel affiche par défaut un taux de 10%, que vous pouvez changer en tapant le chiffre désiré (sans ajouter %).
Utilisation de l'opérateur 10% des valeurs

  • Choisissez après, la mise en forme à appliquer et validez.

Et pour appliquer les mises en forme aux valeurs les moins élevées, suivez les mêmes démarches précédentes en utilisant cette fois les deux opérateurs : 10 valeurs les moins élevées et 10% des valeurs les moins élevées.

Opérateur 10 valeurs moins élevées


Valeurs supérieurs ou Inférieurs à la moyenne :

Pour ces deux opérateurs, Excel calcule lui-même la moyenne des valeurs sélectionnées puis vous affiche une boite de dialogue pour vous permettre de choisir la mise en forme à appliquer.

Opérateur Supérieur à la moyenne



Et voilà, nous arrivons à la fin de ce deuxième article sur la mise en forme conditionnelle, il nous reste donc de découvrir comment créer nos propres règles pour appliquer la mise en forme désirée. Rendez-vous au prochain article.

Mise en forme conditionnelle

Vous avez vu dernièrement dans cette formation Excel, comment filtrer certaines valeurs ou données dans une plage de cellules répondant à certains critères ou conditions, en utilisant l’outil Filtrer dans Excel.
La mise en forme conditionnelle consiste une autre solution pour filtrer vos données mais d’une façon automatique et à l’aide des couleurs, des symboles ou des barres …
Mise en forme conditionnelle partie 1

En effet, l’outil Mise en forme conditionnelle d’Excel et comme son nom l’indique, vous permet d’appliquer automatiquement une mise en forme (ombrage de cellule, Taille de police…) à une plage de cellules en répondant à certaines conditions. Et de cette façon vous pouvez comparer visuellement les valeurs que contient cette plage de cellules.

Exemples de mises en forme conditionnelles

Voici trois exemples de tableaux où on a appliqué des mises en forme conditionnelles:

Mise en forme conditionnelle - Nuances de couleurs


  • La mise en forme appliquée dans ce tableau utilise les nuances de couleurs d’une graduation de deux couleurs : le rouge et le blanc, plus on a de rouge plus le montant est élevé, et plus on a de blanc plus le montant est bas.
Mise en forme conditionnelle - Barres de données



  • Dans ce tableau, on a utilisé des barres de données pour comparer le nombre de population de certains pays. Une barre trop longue montre que le nombre est plus élevé, et si elle est courte cela signifie que le nombre est bas.
Mise en forme conditionnelle - Jeux d'icones


  • Dans ce dernier exemple, on a utilisé des jeux d’icônes dans la mise en forme conditionnelle pour comparer les notes des étudiants dans chaque matière. Par exemple, la note la plus élevée reçoit une flèche orientée verticalement vers le haut, et pour la note la plus faible, la flèche est orientée verticalement vers le bas.

Comment appliquer une mise en forme conditionnelle ?

Sélectionner une plage de cellules

  • Sous l’onglet Accueil et dans le groupe Style, cliquez sur la commande Mise en forme conditionnelle.
Bouton Mise en forme conditionnelle

  • Un menu s’affiche.

D’après ce menu, Excel va vous orienter pour appliquer la mise en forme conditionnelle désirée.
Si vous remarquez le contenu de ce menu, il est divisé en trois parties :

Partie 1 : Excel vous présente les règles (conditions) à choisir en premier puis la mise en forme à appliquer après.

Menu Mise en forme conditionnelle - les règles


Partie 2 : Excel vous présente les trois types de mise en forme à sélectionner sans vous demander de choisir les règles à appliquer. Excel applique par défaut sa règle qui a pour objectif de vous aider à différencier les valeurs les plus élevées des valeurs les plus basses.

Menu Mise en forme conditionnelle - les types de formats


 Partie 3 : Ici, Excel vous donne la main de créer vos propres règles de mise en forme et de définir les formats à appliquer pour répondre à ces règles. Aussi, vous pouvez modifier ou effacer vos mises en forme utilisées.

Menu Mise en forme conditionnelle - Gestion des règles


Utiliser les nuances de couleurs pour appliquer une mise en forme conditionnelle

Commençons par le plus simple…
  • Sélectionnez votre plage de cellules et cliquez sur Mise en forme conditionnelle puis pointez la souris sur le choix Nuances de couleurs.
  • Dans le sous-menu qui s’affiche, faites passer le pointeur de la souris sur les options disponibles :
  • Vous pouvez choisir entre deux types de nuances de couleurs : Echelle de deux couleurs ou Echelle de trois couleurs.
  • Remarquez qu’Excel affiche une info bulle lorsque vous amenez le pointeur de la souris sur une bouton sélectionné et que sa mise en forme est appliquée sur votre plage de cellules.
Echelle de couleurs sélectionnée

  • Si vous êtes satisfaits de l’Echelle sélectionnée cliquez sur son bouton.
Nuance de couleur appliquée



Utiliser les barres de données pour appliquer une mise en forme conditionnelle

  • Sélectionnez la plage de cellules comme dans l’exemple suivant puis cliquez sur Mise en forme conditionnelle.
Colonne Population

  • Sélectionnez Barres de données.
  • Un sous-menu apparaît avec deux catégories : Dégradé et Remplissage uni.
Menu Barres de données

  • Et comme dans l’exemple précédent, quand vous amenez le pointeur de la souris sur un bouton de cette liste, vous voyez une info-bulle qui s’affiche et qu’Excel applique la mise en forme à votre plage de cellules.
Barre de données orange sélectionnée

  • Cliquez donc sur la barre de données désirée.
Barresde données appliquées



Utiliser les jeux d’icônes pour appliquer une mise en forme conditionnelle

  • Sélectionnez la plage de cellules comme dans l’exemple suivant puis cliquez sur Mise en forme conditionnelle.
Colonnes de matières

  • Sélectionnez Jeux d’icônes.
  • Dans le sous-menu qui apparaît, vous avez quatre catégories de jeux d’icônes à choisir : Directionnel, formes, Indicateurs et contrôle d’accès.
4 Catégories de jeux d'icônes

  • Cliquez sur le jeu d’icônes que vous aimez appliquer.
Jeux d'icônes appliquées



Voilà nous arrivons à la fin de ce premier article sur l’utilisation de la mise en forme conditionnelle, le prochain article sera consacré à l’utilisation des règles de mise en forme qui se trouvent dans la première partie du menu Mise en forme conditionnelle.

Utiliser des caractères génériques et des formules dans les critères des filtres élaborés

L’article d’aujourd’hui va compléter ce que vous avez vu sur les filtres avancés ou élaborés dans cette formation ExcelVous allez donc utiliser les caractères génériques: l’astérisque et le point d’interrogation dans les critères de filtre, et vous allez aussi découvrir comment utiliser les calculs ou les formules dans ces filtres élaborés.

Utiliser des caractères génériques et des formules dans les critères des filtres élaborés

Utiliser les caractères génériques dans les critères de filtre

  • Ouvrez votre classeur Filtres élaborés.xlsx puis tapez dans la zone de critères et sous l’étiquette Ville M*
Utiliser caractère générique

  • Cliquez sur votre plage de données et cliquez sur Avancé sous l’onglet Données pour afficher la boite de dialogue Filtre avancé.
  • Dans la zone Plage, vous verrez qu’Excel affiche la référence de la plage sélectionnée.
  • Dans Zone de critères supprimez la référence affichée puis sélectionnez votre zone de critère qui contient M*
  • Cliquez sur OK

Excel affiche alors toutes les lignes contenant les noms des villes commençant par M

Résultat filtre avancé avec critère contenant astérisque

  • Remplacez maintenant le critère M* par *R* et appliquez le filtre élaboré
  • Excel affiche toutes les lignes qui contiennent les noms des villes contenant la lettre R.
Utiliser l'asterisque dans un critère de filtre


Note : Vous remarquez sans doute qu’Excel affiche les noms commençant et se terminant par  R, ceci est dû à ce que l’astérisque remplace 0 ou plusieurs caractères.

Utiliser le point d’interrogation

Si on veut filtrer les données de la ville de Reims mais qu’on ne sait pas si son nom contient i ou non.
  • Ecrivez donc Re?ms dans la zone de critères et appliquez votre filtre.
  • Voici le résultat :
Utiliser le point d'interrogation dans les critères de filres élaborés


Le point d’interrogation remplace alors un seul caractère d’un mot.

Filtrer les cellules vides ou non vides

Si votre plage de données contient des cellules vides et que vous voulez les isoler voici comment procéder :

Les cellules vides dans une colonne

  • Supposons que la colonne Date de naissance contient des cellules vides
  • Tapez donc dans la zone de critères et sous l’étiquette Date de naissance le signe  =
  • Cliquez sur le bouton Avancé et appliquez votre filtre.

Filtrer les cellules vides dans une colonne


Des cellules vides dans toute la plage de données

On veut maintenant isoler toutes les cellules vides de la plage de données, alors tapez sous chaque étiquette et dans des lignes différentes de la zone de critères le signe = comme suit:

Filtrer toutes les cellules vides


Filtrer les cellules non vides

Cette fois vous allez utiliser le signe <> à la place du signe =
Pour ignorer les cellules vides dans la colonne Ville, tapez <> sous l’étiquette Ville et appliquez votre filtre.

Filtrer des cellules non vides


Et si vous aimez ignorer toutes les cellules vides de votre tableau, tapez <> sous chaque étiquettes dans la zone de critères et dans la même ligne.

Filtrer toutes les cellules non vides


Utiliser des critères calculés ou des critères sous forme de formules

Vous pouvez extraire les données de votre plage de données et qui répondent à certains critères basés sur l’utilisation des formules ou des fonctions.

Par exemple, si vous voulez afficher les lignes dont le montant de vente réalisé dépasse la moyenne.

  • Ce que vous allez faire en premier c’est calculer la moyenne des montants des ventes.
  • Ensuite vous allez demander à Excel de comparer le montant réalisé dans chaque cellule de la colonne Mt réalisé au montant moyen.
  • Pour cela :
  • Ecrivez la formule suivante dans une cellule de la zone de critère, choisissez A2 comme exemple :
=D8>MOYENNE(D8:D28)
  • D8 est la référence de la première cellule de la colonne Mt réalisé.
  • MOYENNE(D8:D28) : Calcule la moyenne des montants de ventes, ici le montant moyen est 1 355,06 €
  • Si le montant qui se trouve dans la cellule D8 est supérieur à 1 355,06 €, Excel affiche Vrai, sinon il affiche Faux.
  • Dans notre cas c’est Faux.

Critère renvoyant Faux



  • Maintenant vous allez filtrer vos données.
  • Mais avant de procéder à la création du filtre, vous devez respecter les règles suivantes :
    • Le nom de l’étiquette qui est en dessus de la cellule contenant la formule doit être différent des noms des étiquettes de la plage de données, ou bien vide.
    • Les références des cellules de la formule doivent être relatives et non absolues.
    • La formule écrite doit renvoyer un résultat Vrai ou Faux.
  • Passons maintenant à la création du filtre, cliquez sur Avancé et sélectionnez votre plage de données et votre critères (Nom de l’étiquette même s’il est vide et la formule) puis validez.

Utiliser un critère calculé


Utiliser deux critères calculés

Vous pouvez utiliser plusieurs critères calculés pour filtrer vos données, voici donc comment procéder pour deux critères :

  • Gardez le premier critère que vous avez vu en haut écrit dans A2, et ajoutez le critère suivant : l’année de naissance est 1999
  • Ecrivez donc dans la cellule B2 la formule suivante :
=ANNEE(B8)=1999
  • B8 désigne la première cellule de la colonne Date de Naissance.
  • Dans notre cas le résultat renvoie Faux. Et le nom de l’étiquette est différent des noms des étiquettes de la plage de données.
  • Créez alors votre filtre élaboré et sélectionnez les deux cellules contenant les deux critères calculés.

Utiliser deux criteres calculés


Note : vous pouvez choisir de combiner ces deux critères en les plaçant dans la même ligne ou dans deux lignes différentes, c’est-à-dire utiliser la fonction ET ou OU comme vous l’avez vu dans l’article précédent : Comment utiliser des filtres élaborés ou avancés dans Excel ?