Monsieur Excel
Pour tout savoir faire sur Excel !

29 mai 2015

Formats conditionnels (b)

Dans l’article précédent, nous avons soulevé deux problèmes relatifs aux modifications apportées aux formats conditionnels avec la version 2007 d’Excel : l’un tenait à la multiplicité des commandes et sous-commandes, l’autre à la nette augmentation du nombre de formats possibles.

Multiplicité des commandes et sous-commandes

L’inconvénient majeur lié à la multiplicité des commandes de format conditionnel est que – quand on cherche à définir un nouveau format conditionnel – avec toutes les commandes sauf une ! – on n’a aucune idée des autres formats conditionnels déjà définis pour la sélection active.

La seule commande qui vous permette de le savoir est la toute dernière commande « Gérer les règles… ». C’est pour cette raison que – dans la grande majorité des cas – je passe par cette commande pour définir mes formats conditionnels.


Il me paraît en effet irresponsable de définir un format conditionnel pour une sélection sans avoir quels sont les autres formats conditionnels s’appliquant aux cellules de cette sélection.

Dans le même souci de lisibilité et d’auditabilité de mes modèles, j’utilise presque toujours la dernière commande pour la création d’un format conditionnel : « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ». C’est parfois plus long que de passer par les autres commandes et sous-commandes mais, quand tous les formats conditionnels sont exprimés par des formules, on voit plus rapidement comment ils sont définis.

Nette augmentation du nombre de formats possibles

En ce qui concerne l’augmentation du nombre de formats possibles apparue avec Excel 2007, Microsoft a fait une bourde totalement idiote. Jusqu’à Excel 2003, quand on était limité à trois formats conditionnels, l’ordre de priorité était tout à fait naturel : le premier format avait priorité sur le second, qui lui-même avait priorité sur le troisième.

Depuis Excel 2007, pour une raison que je ne m’explique pas, et de façon totalement absurde, Microsoft a décidé que la première condition était la moins importante, puis la seconde, puis la troisième, … On doit donc définir en dernier la condition la plus importante ! C’est ridicule et pas du tout cohérent pour l’utilisateur.

S’il entre ses conditions de façon logique, en commençant par la plus importante, il est obligé, ensuite, d’utiliser les flèches haute et basse placées à droite du bouton « Supprimer la règle » pour rétablir les priorités réelles des conditions.

Remarque – Ce que je ne comprends pas, c’est que Microsoft n’aie pas profité des versions ultérieures d’Excel (2010 et 2013) pour corriger ce défaut de fabrication manifeste. D’autant plus que cette correction n’aurait posé aucun problème de compatibilité ascendante.


23 mai 2015

Formats conditionnels (a)

Les formats conditionnels d’Excel sont apparus pour la première fois en 1997. Il était alors possible de définir jusqu’à trois formats conditionnels d’un coup pour une cellule ou un groupe de cellules.

Les formats conditionnels sont extrêmement utiles, et ce pour de multiples raisons. Ils permettent par exemple de mettre en relief les valeurs les plus grandes, ou les plus petites, ou les doublons,… ou je ne sais quoi. La seule limite réelle est celle de votre imagination !

Avec Excel 2007 et l’apparition du ruban, le fonctionnement des formats conditionnels a été fortement modifié. On peut en particulier noter trois changements significatifs : la grande variété des commandes, la possibilité de modifier le format numérique, et le nombre bien plus grand de conditions possibles.

La grande variété des commandes

Comme le montre la copie d’écran ci-dessous, provenant d’un Excel 2010, il y a maintenant un grand nombre de commandes menant à des sous-commandes conduisant elles-mêmes à des dialogues, ce qui est indiqué par les «  » terminant le nom des sous-commandes.


Cette grande variété des commandes pose un problème. Avec toutes les commandes et les sous-commandes, on peut effectivement définir une grande variété de formats conditionnels, mais cela cache un piège majeur dont nous parlerons dans le prochain article.

La possibilité de modifier le format numérique

Jusqu’à Excel 2003, le format conditionnel permettait de modifier la police, le style, la couleur de la police ou du fond, et enfin l’encadrement.

Depuis Excel 2007, on a aussi accès dans via le format conditionnel à la commande « Nombre », ce qui permet de modifier aussi le format numérique, de la même façon qu’on peut le faire avec les formats numériques standards ou personnalisés.

Nombre de conditions possibles fortement augmenté

Jusqu’à Excel 2003, le format conditionnel permettait de définir trois conditions au maximum, donc de disposer pour une cellule de quatre formats possibles. Cette limite a sauté avec Excel 207 et l’on peut à présent définir un grand nombre de formats conditionnels. Je ne suis pas parvenu à en trouver la limite sur Internet, mais elle doit être conséquente.

Ceci dit, cela la possibilité de définir de multiples formats conditionnels pose aussi un problème sérieux, dont nous parlerons aussi dans le prochain article.

16 mai 2015

Formats personnalisés (c)

Dans les deux articles précédents, nous avons analysé les deux syntaxes possibles pour les formats personnalisés. La seconde syntaxe permet de prendre des libertés avec l’affichage de résultas dans Excel.

Nous avons repris ci-dessous le bloc A1:D5 utilisé dans le premier article. Puis nous avons entré en A8, recopiée vers la droite, la formule =A1. Et nous avons donné au bloc A8:D8 le format :
"-9 876,54";"Alfred";"1 234,56";"-56,78"


Remarque – Pour confondre encore mieux l’utilisateur, nous avons cadré la cellule B8 à gauche et la cellule D8 à droite. En effet, leurs valeurs réelles avaient entraîné des cadrages incohérents avec le résultat affiché…

Quand nous consultons la ligne 8, nous voyons que nous pouvons forcer Excel à afficher n’importe quoi, et tout cas un résultat très éloigné du résultat attendu !

Ceci dit, Excel ne set trompe pas et ne se fie qu’à la valeur réelle des cellules. Ainsi, quand nous entrons en A12 (recopiée à droite) la formule =A8 et que nous donnons un format Standard à la ligne 12, nous voyons que le résultat n’est pas faussé et que les vraies résultats attendus sont affichés.

Il y a aussi des utilisations sérieuses de cette possibilité de modifier le formatage. Par exemple, en A14 dont le contenu est 0,2, en utilisant le format reproduit en C14, nous parvenons à afficher à la fois un texte et un paramètre dans la même cellule. Et, pour mieux tromper le touriste, nous avons cadré la cellule A14 à gauche…

Il y a de multiples usages pour les formats personnalisés « trompeurs ». On peut par exemple utiliser un format personnalisé pour cacher un paramètre « sous » un texte, ce qui est plus léger que de devoir passer par un nom et qui représente une bonne façon de protéger un paramètre pour qu’il ne soit pas modifié par n’importe qui…

10 mai 2015

Formats personnalisés (b)

Dans l’article précédent, nous avons vu que les formats personnalisés d’Excel correspondaient – dans l’ordre – à « positif;négatif;nul;texte ».

En vous disant cela, j’ai fait – pour que ce premier article ne soit pas trop long – un mensonge par omission…

Il y a en effet deux types de formats personnalisés dans Excel. Le premier correspond à la définition présentée dans l’article précédent.

Le second type de format personnalisé correspond, dans l’ordre, à :
[condition 1]format 1;[condition 2]format 2;format 3

Les conditions doivent être encadrées de crochets droits. Il peut y avoir au maximum trois formats, et donc deux conditions.

Nous voyons dans le tableau ci-dessous deux exemples d’applications. Dans ces tableaux, la formule de B2 est =A2, et elle a été reproduite vers le bas.


Dans le bloc B2:B6, nous constatons l’effet d’un format personnalisé qui – selon la valeur – affiche le résultat en , en k€ ou en M€.

Remarque 1 – Attention ! Vous n’avez pas le droit de saisir « >=10^6 » pour la première condition. Le symbole d’exponentiation n’est pas reconnu…

Dans le bloc B8:B13, nous voyons comment des notes de 0 à 20 peuvent être évaluées avec un A pour les bonnes notes, un B pour les notes passables, et la valeur avec une décimale pour les mauvaises notes.

Remarque 2 – Les formats personnalisés avec des conditions ne peuvent gérer que des conditions simples. Il n’est par exemple pas possible de faire référence à une cellule dans une condition.


04 mai 2015

Formats personnalisés (a)

Dans l’article précédet, nous nous sommes intéressés au formatage en k€ ou en M€.

Au sujet du formatage, je constate souvent – quand j’interviens en entreprise comme consultant – que de nombreux utilisateurs ne maîtrisent pas bien les formats personnalisés d’Excel.

La commande « Format personnalisé » d’Excel donne accès à l’écran dont la partie supérieure est reproduite dans l’encart bleu de la copie d’écran ci-dessous.


Nous avons entré =1000*pi() en A2, =-A2 en B2, =somme(A2:B2) en C2 et un texte en D2.

Nous avons formaté les lignes 3 à 6 en utilisant, depuis le bloc « Nombre » de l’onglet « Accueil », les outils de formatage en euros, dollars, séparateur des milliers et pourcentages. On peut se poser la question de savoir quel format Excel a précisément utilisé quand on sélectionne un de ces formats. Pour le savoir, cliquez dans la cellule, passez par la commande « Format de cellule » et sélectionnez la dernière option, « Personnalisé », comme dans notre copie d’écran.

Vous constatez ainsi, avec les formats reproduits en colonne E, que les deux formats monétaires utilisés par Excel dans le ruban sont bien plus complexes que ce que l’on pourrait croire a priori.

Comment interpréter les formats personnalisés?

Pour moi, deux choses sont particulièrement importantes à comprendre en ce qui concerne les formats personnalisés d’Excel.

Un « 0 » représente un chiffre obligatoire, un « # » représente un chiffre facultatif. Ainsi, la valeur 3,6 est représentée en 4 par le format « 0 », en « 3,60 » par le format « 0,00 » et en 03,6 par le format « 00,## ».

Il peut y avoir jusqu’à quatre formats séparés par des « ; ». Ces quatre formats, quand ils sont tous présents, correspondent – dans l’ordre – à « positif;négatif;nul;texte ». Comme par hasard, c’est l’ordre dans lequel j’ai rangé les éléments dans les colonnes A à D.

Remarque 1 – Si vous voulez entièrement masquer le contenu d’une cellule, le format à utiliser est « ;;; ». C’est plus invisible qu’une police blanche sur fond blanc. En effet, dans ce dernier cas, le contenu de la cellule devient visible par contraste quand elle fait partie d’une sélection.

Remarque 2 – Si vous voulez savoir à quoi correspondent tous les autres codes utilisés dans les formats, consultez l’aide d’Excel…