La mise en forme conditionnelle

Les principes

La mise en forme conditionnelle facilite la mise en évidence de cellule qui présentent un intérêt ou qui se trouvent dans ou en dehors des normes définies. 

Elle modifie l'aspect d'une plage de cellules en fonction d'une condition que vous indiquez.

Si la condition est vraie, la plage de cellules est mise en forme en fonction de cette condition, si la condition est fausse, la plage de cellules n'est pas mise en forme.

Lorsque la valeur de la cellule change et ne correspond plus aux conditions indiquées, la mise en forme conditionnelle ne s'applique plus.

La mise en forme conditionnelle EXCEL 2003

Elle permet de modifier :

• Le style (gras, italique, barré…) et la couleur du texte
• Le style et la couleur de l'encadrement
• La couleur de fond de la cellule

Excel 2003 n'autorise que 3 mises en forme conditionnelles possibles pour une cellule.

La mise en forme conditionnelle EXCEL 2007

Elle permet de modifier :

• Le style (gras, italique, barré…) et la couleur du texte
• Le style et la couleur de l'encadrement
• La couleur de fond de la cellule
• Le format des nombres.

Excel 2007 fournit les nouveaux types de mise en forme conditionnelle suivants :

• Les échelles de couleur
• Les barres de données
• Les Jeux d'icônes
• Les valeurs classées en bas ou en haut
• Les valeurs au-dessus ou en-dessous des valeurs moyennes
• Les valeurs uniques ou dupliquées

Ces types de mise en forme conditionnelle ne sont pas reconnus par les versions précédentes d’Excel si vous enregistrez votre classeur Excel 2007 dans un format antérieur.

EXCEL 2007 autorise jusqu'à 64 mises en forme conditionnelles possibles pour une cellule

Les différents types de règles.

Vous pouvez créer directement vos règles sans passer par les assistants.

Commencez toujours par sélectionner la plage de cellules où la mise en forme conditionnelle doit s’appliquer.

Puis sélectionnez "Nouvelle règle" à partir de l'Onglet Accueil, Groupe Style, bouton Mise en forme conditionnelle

Une boîte de dialogue vous permet d’effectuer vos choix parmi six types de mise en forme conditionnelle

• Mettre en forme toutes les cellules d’après leur valeur

A utiliser pour les nuances de couleur, les barres de données ou les jeux d’icône.

• Appliquer une mise en forme uniquement aux cellules qui contiennent

Indiquez les critères de sélection (valeur spécifique, valeur comprise entre, cellules vides…)

• Appliquer une mise en forme uniquement aux valeurs rangées parmi les premières ou les dernières valeurs

Indiquez les X valeurs souhaitées en valeur ou en pourcentage

• Appliquer une mise en forme uniquement aux valeurs au-dessus ou en dessous de la moyenne

Indiquez la mise en forme souhaitée

• Appliquer une mise en forme uniquement aux valeurs uniques ou aux doublons

Sélectionnez dans la liste le choix unique ou le choix en double

• Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué

A utiliser lorsque la cellule à mettre en forme n’est pas la cellule sur laquelle portera le critère.

Exemple : Mettre en rouge le nom des collaborateurs qui n’ont pas effectué 4h d’accueil.

Tapez = puis saisissez la formule de calcul à vérifier.

Cette formule doit pouvoir renvoyer la valeur vrai ou la valeur faux.

(Exemple : = A1<4)

 

Si la condition est vérifiée, la mise en forme définie s’appliquera

Les graphiques sparkline

Apparus avec la version 2010 d'Excel, les sparkline sont des petits graphiques insérés directement dans une cellule de calcul.
Ils permettent de visualiser rapidement une tendance ou de repérer les valeurs max - min d'une série de valeurs.

Prenons un exemple :

Voici les résultats semestriels de trois agences. 
La colonne H doit permettre de visualiser en un seul coup d'oeil l'évolution des ventes.
Un graphique sparkline inséré dans les cellules H2:H4 va répondre à ce besoin.

Création du sparkline

1. Placez le curseur dans la cellule H2 puis dans l'onglet INSERTION sélectionnez le groupe Graphiques sparkline. 
2. Choisissez l'un des 3 types proposés : En courbes, Histogramme, ou Positif/négatif.
   
   
     
3. Dans la boîte Création, indiquez la plage des cellules contenant les données sur lesquelles vous souhaitez baser le sparkline.
   (Ici les cellules B2:G2 contenant les ventes de l'agence de Lyon)

Après validation, recopiez le contenu de la cellule H2 sur les cellules H2:H4.
Un sparkline s'affichera dans chacune des cellules.

Désormais tout changement de valeur dans une des cellules sources du sparkline se répercutera sur le graphique. 

Modifier la présentation d'un sparkline.

Un onglet dédié aux manipulations sur les graphiques sparkline s'affiche lorsque le curseur se trouve sur une cellule contenant un graphique sparkline.
Vous pouvez :

• Modifier le type de sparkline

• Afficher un point (=marqueur) représentant chaque valeur de cellule source

• Mettre en évidence la valeur la plus haute ou la plus basse

• Modifier la couleur du graphique ou des marques
• Effacer un ou la totalité des sparkline

Il est préférable de positionner le graphique sparkline à coté des cellules contenant les données à représenter et ce afin de faciliter la lisibilité des sparklines.

Présentation

Faciliter la diffusion des outils informatiques en entreprise

Validation des données

Par défaut, il est possible de saisir tout type de valeur dans une cellule Excel.
Cela peut se révéler problématique notamment lors de la saisie d'informations dans une liste de données.
Dans ce dernier cas, saisir une date dans un champ devant contenir un montant numérique ou saisir un chiffre à la place d'un nom peut entraîner des erreurs de calcul en cascade.

Mais Excel sait parfaitement contrôler la saisie de valeurs dans une cellule et interdire des données incohérentes.

Cette fonctionnalité s'appelle la validation des données. 

Elle va permettre de définir le type de données autorisé dans une cellule et va même pouvoir afficher un message à l'utilisateur en cas d'incohérence.

Prenons un exemple simple : 

 Dans ce tableau, il faudrait que

• Tout autre saisie qu'un nombre soit rejetée dans la colonne montant depenses
• Que le montant de la valeur remboursée soit plafonné à 100
• Que la date depense soit forcement une date et en plus de l'année 2016 
• Seules les valeurs Nice, Vienne ou Lyon soient autorisées dans la colonne agence

N'autoriser que des nombres dans une cellule

Sélectionner les cellules E2:E100

Cliquer sur l'onglet Données et choisissez Validation des données dans le groupe "Outils de données"

Par défaut, la boîte de dialogue indique que tout est autorisé. Aucun contrôle n'est effectué sur la saisie.

Vous allez indiquer à Excel que seules les valeurs numériques décimales inférieures à 200 seront autorisées.

Pour cela, cliquer sur la liste des autorisations

Excel vous permet de ne valider que la saisie de nombre entier, de nombre décimaux, de date
Choisissez "Décimal" et constatez que vous pouvez encore plus restreindre les nombres autorisés avec des critères de type supérieur, inférieur, différent, compris entre... 

Pour vérifier si cela fonctionne, essayez de saisir un texte ou un nombre > 200, votre saisie sera refusée par Excel.

Idem pour restreindre la saisie des dates de dépenses à l'année 2016

Créer une liste de validation

Notre société ne compte que trois agences : Nice, Lyon et Chartres. Pour pouvoir exploiter de manière optimale les données de notre liste notamment dans un tableau croisé dynamique, il faut impérativement les écrire à l'identique sur chaque ligne.
Chartre, chartres et chartes sont trois informations différentes pour Excel.

1. Commencez par sélectionner les cellules où la validation de données doit s'appliquer. Dans notre exemple, nous sélectionnons c2:c100
2. Cliquer sur l'onglet Données et choisissez Validation des données dans le groupe "Outils de données"
3. Dans la boîte qui s'affiche, cliquez Liste dans la zone autoriser puis saisissez dans la zone source la liste des valeurs autorisées dans la zone source

C'est la solution la plus simple si les données sources ne varient pas.

(Vous pouvez également saisir la liste des valeurs dans des cellules de la même feuille et utiliser ce bloc de cellules comme source de valeurs)

4. Laissez cocher les choix : liste déroulante et ignorer si vide (sauf si vous considérez que la saisie d'une valeur est obligatoire)

Constatez que dorénavant, une liste déroulante facilite le choix de l'agence et qu'aucune autre valeur n'est permise.

Modifier le message d'erreur

Le message standard d'Excel n'est pas explicite et ne précise pas quelle est la règle de validation à respecter. C'est à vous d'indiquer tout cela.
Nous allons préciser que le montant remboursé est plafonné à 100 euros dans le champ valeur remboursée

1. Commencez par renseigner les critères de validation souhaités

2. Dans l'onglet Options, indiquez un message d'aide à la saisie qui s'affichera sous forme d'info-bulle

3. Dans l'onglet Alerte d'erreur, saisissez le message à afficher lorsque la saisie contredit les critères de validation.