Tutoriel Excel : la mise en forme conditionnelle

 Dans ce tutoriel on va s’attaquer à la mise en forme conditionnelle, Eh oui, quand on parle de la mise en forme on parle de couleur, de la taille, de la police, etc, mais ce qui va être nouveau pour vous c’est ce nouveau terme qu’on lui a associé « conditionnelle », La mise en forme est indispensable, mais la mise en forme conditionnelle est encore plus puissante. La mise en forme conditionnelle change l’apparence d’une cellule en fonction de son contenu.

Ce tutoriel à été realisé sous Excel 2016 mais il est aussi valable pour les version 2007, 2010 et 2013

Ne vous inquiétez pas c’est une nouvelle technique dont vous allez vous amusez en l’apprenant ????

Qu'est-ce que la mise en forme conditionnelle ?

On peut considérer la mise en forme conditionnelle comme une mise en forme automatique déclenchée par les conditions que vous définissez. Par exemple, vous pouvez utiliser la mise en forme conditionnelle pour modifier automatiquement la couleur des cellules contenant des valeurs supérieures ou inférieures à certaines valeurs. La mise en forme conditionnelle est un excellent moyen de mettre en évidence visuellement des informations importantes dans une feuille de calcul. 

Elle permet aux cellules d'apparaître avec certaines couleurs d'arrière-plan, en fonction de leurs valeurs. Par exemple, toutes les valeurs positives peuvent apparaître avec un arrière-plan bleu, tandis que les valeurs négatives avec un jaune. En fait, non seulement les couleurs d'arrière-plan peuvent être appliquées (bien qu'elles soient les plus couramment utilisées), mais de nombreux autres effets de style, tels que la taille de la police, la couleur de la police, les caractères gras, italique ou souligné, l'ajout de bordures, etc.

Quand utiliser la mise en forme conditionnelle :

La mise en forme conditionnelle vous permet de mieux visualiser vos données surtout quand vous traiter des tableaux avec des milliers d’entrées, on va voir par la suite des exemples des cas où on aura besoin de ce type de mise en forme :

Exemple 1 :  On peut utiliser la mise en forme conditionnelle par exemple pour souligner toutes les valeurs supérieures à 3000 dans un tableau de valeurs.

Exemple 2 :  Mettre en surbrillance uniquement la cellule contenant la valeur la plus élevée parmi un groupe de cellules. (Cela peut être pertinent lorsque vous souhaitez mettre en évidence le score le plus élevé de la classe ou le salaire le plus élevé dans une entreprise).

Exemple 3 :  Mettre en surbrillance toutes les valeurs en double dans une certaine plage (vous ne souhaitez pas rechercher et localiser facilement des valeurs répétitives gênantes dans une liste ?)

Exemple 4 : Une autre utilisation courante de la mise en forme conditionnelle consiste à mettre en évidence les valeurs dans un ensemble de données. Nous avons ici un tableau qui présente les chiffres des ventes mensuelles pour un groupe de vendeurs. Le quota de vente mensuel est de 5 000 DH. Nous allons donc utiliser le formatage conditionnel pour mettre en évidence les chiffres de vente mensuels inférieurs à cette valeur.

Créer une mise en forme conditionnelle :

La mise en forme conditionnelle est appliquée avec des règles. Tout d'abord, sélectionnez les cellules que vous souhaitez formater. Dans notre tableau des ventes sur lequel on va travailler, il s'agit de tous les chiffres des ventes mensuelles, à l'exclusion des totaux. Ouvrez ensuite le menu « Mise en forme conditionnelle », qui apparaît dans le groupe « Styles » de l'onglet « Accueil » du ruban, et vous allez voir qu’il existe de nombreux paramètres prédéfinis de format conditionnel dans ce menu mais pour le moment choisissez « nouvelle règle ».

La fenêtre qui s’affiche vous propose de créer une nouvelle mise en forme selon ce que vous voulez, par exemple on peut choisir de colorer les cellules selon leurs valeurs et donc on ara un dégradé de couleur qui va être plus foncé si la cellule contient une grande valeur et plus claire si c’est l’inverse, pour se faire on va choisir de « mettre en forme toutes les cellules d’après leur valeurs » et vous pouvez choisir les couleurs que vous souhaitez.

Mise en surbrillance des cellules

La mise en forme conditionnelle vous permet de mettre les cellules en surbrillance et selon différentes manières comme illustré dans le menu si dessous :

Donc on peut choisir de mettre en surbrillance les cellules ayant des valeurs :

-Supérieur /inférieur à une valeur donnée.

-égale à une valeur donnée.

-Comprises entre deux valeurs données.

-les valeurs qui sont en double…etc

Surligner les éléments supérieurs/inférieur à un seuil donné

 Par exemple nous allons choisir de mettre en surbrillance seulement les cellules ayant le chiffre des ventes mensuelle supérieur à 5000 DH. Ce type de règle nécessite deux entrées : le critère, ou condition, utilisé pour déclencher la règle, et le format à appliquer lorsque cette condition est remplie.

Alors pour ce faire il faut sélectionner les cellules des chiffres des ventes et allez dans « règles de mise en surbrillance des cellules » dans le menu « mise en forme conditionnelles » et choisissez « supérieur à ». Dans la fenêtre qui apparait vous saisissez le seuil ici « 5000 » et vous pouvez choisir le remplissage que vous voulez nous pouvons choisir parmi plusieurs options prédéfinies ou définir un format personnalisé. Pour l'instant, choisissons le préréglage "Remplissage rouge clair avec du texte rouge foncé", puis cliquez sur « OK ».

Une fois que nous avons cliqué sur OK, la règle est créée et affiche la mise en forme appliquée aux cellules répondant à la condition définie.

Et la même chose pour surligner les valeurs inférieures à une seuil donnée, on peut par exemple surligner les cellules ayant les valeurs de chiffre de vente inférieur à 5000 DH.

Remarque : Les règles qu’on applique « collent » aux cellules, de sorte que leur format change de manière dynamique lorsque leurs valeurs changent. Par exemple, si une cellule avait une valeur inférieure à 5000 (son fond était donc blanc) et que sa valeur devenait ensuite plus grande que 5000, son arrière-plan deviendrait automatiquement rouge clair. En d’autres termes le formatage conditionnel est entièrement automatique, si nous modifions une valeur de la table pour qu'elle soit supérieur à notre seuil de 5 000 DH, la règle que nous avons créée est déclenchée et le formatage est automatiquement appliqué.

Surligner les éléments entre deux valeurs

On peut mettre en surbrillance les cellules ayant les valeurs comprises entre deux seuils donnés, par exemple on va choisir de surligner les chiffres de ventes entre 4000 et 7000 DH, donc après avoir choisi le sou menu « Entre » vous allez saisir la valeur min et la valeur max et choisissez le remplissage que vous voulez.

Mettre en surbrillance les doublons :

Une autre possibilité est de mettre en évidence les valeurs en double dans une certaine plage de cellules sans avoir à le faire manuellement ! et sans avoir à créer aucune formule, par exemple on va surligner les chiffres d’affaire qui se répètent dans la table des ventes.

Cette fois ci vous allez choisir le sous menu « valeurs en double » toujours dans le menu « mise en forme conditionnelle », et dans la fenêtre qui s’affiche on vous demande si vous voulez de mettre en forme les valeurs en double ou bien les valeurs uniques, pour cet exemple on va choisir de surligner les valeurs en double, ensuite vous choisissez la couleur du remplissage.

Dans le résultat vous allez voir que seules les valeurs en double qui sont colorés alors que les autres sont restés avec leur couleur initiale.

Mettre en surbrillance les valeurs plus /moins élevé :

Une autre règle est de surligner les valeurs de plage haute ou basse dans une table.

Cette règle va nous permettre de mettre en forme ou en surbrillance :

-les 10 valeurs les plus/moins élevés.

-les 10% plus/moins élevés

-les valeurs supérieures/inférieures à une moyenne.

Dans cet exemple on va surligner les 8 chiffres de ventes les plus hauts, sélectionnez les cellules allez dans « 10 valeurs les plus élevés », une fenêtre apparait vous pouvez saisir le nombre de valeurs à surligner dans notre cas c’est « 8 ».

On peut aussi mettre en surbrillance les chiffres de vente qui sont supérieur ou inférieur à la moyenne, et pour cela vous avez qu’a choisir le sous menu « valeurs supérieures à la moyenne » ou bien « valeurs inférieures à la moyenne » et choisir le style de remplissage.

Remarque : nous avons la possibilité d’appliquer une mise en forme personnalisée, on doit juste choisir « format personnalisé » dans la fenêtre de la règle et de définir le format souhaité.

Alors, nous avons vu pour le moment comment mettre en forme (surtout colorer) les cellules qui vérifient une certaine condition, maintenant on va s’attaquer à d’autre règles qui sont plus amusantes !

Les barres de données :

Vous pouvez le deviner à partir de son nom, une barre de donnée est une sorte de mise en forme conditionnelle qui va affecter une barre proportionnelle à chaque cellule selon sa valeur, et donc la cellule avec la plus grande valeur va avoir une barre de 100% de la largeur de la cellule et ainsi de suite. On va donc sélectionner les cellules et choisir « barres de données » dans le menu « mise en forme conditionnelle » et choisir le style souhaité.

Comme vous pouvez le voir les cellules avec le chiffre d’affaire 6700 DH ont une barre de 100% la largeur de la cellule et les autres sont remplies proportionnellement.

Les nuances de couleurs :

Les nuances de couleurs permettent d’appliquer différentes couleurs de remplissage aux cellules selon leurs valeurs, et donc on aura par exemple une couleur verte aux plus hautes valeurs, une couleur bleue aux valeurs les plus basses et une couleur jaune pour les valeurs moyennes. Pour appliquer une nuance de couleur, sélectionnez la plage des cellules et allez dans « nuances de couleurs » toujours dans le menu « mise en forme conditionnelle » et choisissez l’échelle de couleurs que vous voulez.

 C’est une sorte de magie !

Les jeux d’icônes :

Il est également possible d’ajouter des icônes aux cellules selon leurs valeurs, et on peut choisir un groupe de 3, 4 ou 5 icones et chaque icone va être affectée à une plage de valeurs selon les valeurs minimum et maximum de la table. Par exemple pour notre cas, le plus grand chiffre de vente est 6700 et le plus bas est 2300, donc si on choisit par exemple un groupe d’icônes contenant 3 icones, nos cellules vont être divisé en trois groupes :

- le premier contient les cellules avec les chiffres de vente dans les 33% des valeurs les plus hautes : ce groupe va prendre la première icone.

- le deuxième contient les cellules avec les chiffres de vente dans les 33% des valeurs les plus basses : ce groupe va prendre la deuxième icone.

-le troisième contient les cellules avec les chiffres de vente qui sont au milieu : ce groupe va prendre la troisième icone.

Et la même chose si on choisit le group d’icone avec 4 ou 5 icones dans ce cas les cellules vont être divisé en 4 ou 5 groupes.

Gérer les règles de mise en forme :

Modifier une règle de mise en forme

Après avoir appliqué une règle sur les cellules vous avez la possibilité de modifier et de personnaliser si vous voulez cette mise en forme, pour ce faire sélectionnez les cellules dont vous voulez modifier la règle de mise en forme puis dans le menu « mise en forme conditionnelle » choisissez « gérer les règles »

 Et dans la fenêtre qui apparait entrez dans l’onglet « modifier la règle ».

 Une nouvelle fenêtre va s’ouvrir et vous propose plusieurs modifications à effectuer sur la règle concernée, dans cet exemple j’essaye de modifier la règle de jeux d’icone, et on me propose de changer les icones, de changer leurs ordres et aussi de changer les marges des valeurs auxquelles on va affecter chaque icone.

 Supprimer une règle de mise en forme :

Il y’aura des cas ou vous aurez besoin de supprimer une règle que vous avez appliquer à une marge de cellules, donc tous ce que vous avez à faire est de sélectionner les cellules concernés et d’aller sur « mise en forme conditionnelle » et toute en bas avant le dernier élément vous avez « effacer les règle », on va vous demander si vous voulez effacer les règles de la feuille entière ou bien uniquement pour les cellules sélectionnées, vous choisissez selon ce que vous cherchez à faire, dans notre exemple on va supprimer les règles des  cellules sélectionnées.