Format de nombres

Changer de format de nombre, c'est par exemple indiquer le nombre de décimales après la virgule que l'on souhaite voire apparaître, c'est aussi l'utilisation d'un séparateur de milliers, ou d'un pourcentage, ou encore choisir le format des dates, etc...

Il existe un format ou plusieurs format prédéfini pour chaque type de données. Excel vous offre même la possibilité de créer des formats personnalisés.

Avant toute chose, vous trouverez dans l'onglet Accueil du ruban un groupe nommé Nombre qui permet d'ajuster le format de vos données, qui s'agisse de nombre, de dates ou de texte. C'est à partir de cette interface que vous pourrez définir le format approprié à votre saisie :

Texte ou nombre ?

Avant toute chose, rappelez-vous qu'en règle générale, lorsque vous saisissez une donnée dans Excel, le contenu est automatiquement appuyé à gauche s'il s'agit d'un texte, et à droite s'il s'agit d'un nombre. On le voit bien dans l'exemple du tableau suivant, la différence entre données chiffrées (montants et dates) et le texte (ligne de titre, colonne lieu et colonne libellé) :

C'est un élément à connaître, car Excel ne réalise que des calculs sur des données qu'il analyse comme étant des nombres.

Par exemple, si le séparateur décimal paramétré dans votre ordinateur est la virgule, quelle est la différence de traitement entre la valeur 10,5 et la valeur 10.5 ?

Et bien dans le premier cas la valeur est analysée comme un nombre, elle est appuyée à droite et pourra être prise en compte dans un calcul,

Dans le deuxième cas, la valeur est reconnue comme étant du texte (car le "." n'est pas un séparateur décimal, il est donc analysé comme un simple caractère), elle est appuyée à gauche et ne pourra pas être prise en compte dans un calcul (et vous n'aurez pas de message d'erreur pour vous avertir !).

Le format "Standard" : le format qui n'en est pas un...

Vous aurez également remarqué le format Standard proposé dans le ruban :

C'est un format un peu particulier, et à vrai dire il ne s'agit pas vraiment d'un format de nombre...

Avec ce format, les nombres apparaissent sans format spécifique. Si vous entrez un nombre entier, il n'indiquera aucun 0 après la virgule (par exemple, 100 s'affiche, et pas 100,00). Si vous entrez un nombre avec 6 décimales, il ne limitera pas le nombre des décimales affichées (par exemple 5,456397 s'affichera tel quel, et pas 5,46), etc...

Pour remettre à zéro le format d'une cellule, vous pouvez utiliser le format Standard.

Formats simples

Nombres et décimales

Lorsqu'on s'intéresse au formatage des nombres, c'est d'abord le format des décimales et séparateur de milliers qu'on souhaite adapter.

En matière de décimale, la présentation la plus courante est 2 chiffres après la virgule. Concernant le séparateur de milliers, c'est l'espace qui est le plus utilisé chez les francophones. Nous avons dans le ruban un outil qui formate directement les nombres avec cette apparence :

Voici le résultat :

Vous souhaiterez peut-être modifier le nombre de décimales après la virgule. Là encore, le ruban offre le nécessaire :

Vous disposez de plus de possibilités de formatage en cliquant sur la petite flèche visible en bas à droite du groupe Nombre :

En cliquant sur ce petit bouton, appelé lanceur de boîte de dialogue, vous faites apparaître la fenêtre Format de cellule. Dans la liste Catégorie, choisissez Nombre. Outre la gestion des décimales et du séparateur de milliers, vous trouverez des formats prédéfinis pour gérer l'apparence des nombres négatifs, avec notamment la possibilité de les faire ressortir en rouge :

Devises

Si vous avez saisi des montants correspondant à des sommes d'argent, il est parfois important de faire apparaître le symbole monétaire. Les formats les plus courants vont vous y aider, ils sont disponibles dans le ruban. Voici une démonstration rapide du formatage en euros ou en dollars :

La boîte de dialogue Format de cellule vous offre des possibilités supplémentaires : choix du symbole monétaire, couleur rouge pour les nombres négatifs.

Profitez-en pour regarder la catégorie Comptabilité présente dans la boîte de dialogue : vous remarquerez que ce format est très proche du format monétaire, la mise en forme des nombres négatifs en moins...

Pourcentages

Les pourcentages sont fréquemment utilisés dans les tableaux. Là encore, le ruban vous met à disposition le format adéquat (la boîte de dialogue Format de cellule ne vous apportera pas de réglage supplémentaire...) :

Vous remarquerez que le format s'applique y compris dans la barre de formule.

Attention, si vous appliquez le format Pourcentage à une cellule dont le contenu a déjà été saisi, pensez à entrez les nombres en les divisant par 100. En effet, Excel multiplie automatiquement le nombre présent dans la cellule par 100. La valeur 10 deviendrait ainsi 1000%.

Le plus simple est :

• soit de taper le caractère % immédiatement après le nombre au moment de la saisie de la cellule, et le format Pourcentage s'applique correctement (et automatiquement) au montant saisi :

• soit d'appliquer d'abord le format Pourcentage à la cellule vide, puis de saisir le montant.

Des raccourcis pour les formats les plus courants
Il existe des raccourcis clavier pour appliquer directement un format à une cellule. Pour en savoir plus, lisez cet article !

Comment formater les dates ?

Cliquez sur la liste déroulante disponible dans le groupe Nombre (onglet Accueil) dans le ruban. Les deux formats qui nous intéressent sont date courte et date longue :

Date courte correspond à une date du type 01/01/2023, et date longue à une date du type dimanche 1 janvier 2023 (Excel n'écrit pas 1er, et c'est bien dommage...) :

Si vous omettez l'année, Excel comprendra qu'il s'agit de  l'année en cours, qu'il ajoutera automatiquement. C'est bien pratique pour accélérer sa saisie en présence d'un grand volume de données à enregistrer manuellement ! Ainsi, si l'année en cours est 2023, 10/05 sera automatiquement transformé en 10/05/2023.

La boîte de dialogue Format de cellule (accessible via le lanceur du groupe Nombre) vous offre un large choix d'autres présentations de dates. Vous y trouverez certainement le format qui vous intéresse, sans avoir besoin de créer un format personnalisé :

Comment formater les heures ?

La saisie des heures dans Excel s'effectue en saisissant un  :" entre les heures et les minutes, et entre les minutes et les secondes.
Si vous saisissez 18:00, Excel comprendra qu'il s'agit de 18 heures. Si vous saisissez 18:30:15, Excel comprendra 18 heures 30 et 15 secondes.

Cliquez sur la liste déroulante disponible dans le groupe Nombre (onglet Accueil) dans le ruban. Vous y trouverez un format simple de type hh:mm:ss pour les heures :

Il existe d'autres formats prédéfinis accessibles via la boîte de dialogue Format de cellule :

Cependant, vous ne trouverez pas le format de type 18h30 ou 18 heures 30. Dommage... Mais il sera toujours possible de passer par un format personnalisé, que nous aborderons un peu plus tard dans ce cours.

Comment formater les fractions ?

Vous aurez parfois besoin de faire apparaître des nombres en tant que fraction, et non pas que nombre avec décimale.

Mais attention : la saisie de "1/2" par exemple n'est pas automatiquement interprété par Excel en tant que fraction, mais en tant que date !

Une première méthode consiste à saisir notre fraction en respectant la règle suivante : saisissez d'abord la partie entière, puis un espace, puis la fraction.
Par exemple : 1 1/2 ou 3 2/3.
Cette saisie ne sera pas interprété comme du texte, malgré la présence d'un espace, mais bien comme un nombre sur lequel vous pourrez faire des calculs.

Comment faire en l'absence de partie entière, par exemple pour indiquer juste une moitié, ou trois quart ? C'est simple : il suffit juste de taper un 0 en guise de partie entière. Par exemple : 0 1/2 pour une moitié, ou 0 3/4.

Notez qu'après avoir saisi une fraction, la barre de formule présente le nombre sous sa forme décimale, et la cellule le présente sous forme fractionnaire :

Inversement, les nombres décimaux que vous tapez dans une cellule formatée en fraction seront bien sûr affichés sous forme de fraction. Par exemple, la saisie "1,6667" s'affichera sous la forme "1 2/3".

Sachez aussi qu'Excel va systématiquement simplifier les fractions que vous saisissez. Ainsi, 24/48 sera simplifié en 1/2.

La boîte de dialogue "Format de cellule" (accessible via le lanceur du groupe "Nombre") vous offre plus de possibilités en termes de présentation, qu'il s'agisse :

• d'adapter le nombre de chiffres visibles dans la fraction et donc la précision de celle-ci (arrondi à la valeur de fraction la plus proche) ,
• ou de travailler en demis, quarts, huitièmes, seizièmes, dixièmes ou centièmes :

Formats spéciaux : codes postaux, n° de Sécurité sociale, n° de téléphone

Excel dispose de formats spéciaux prédéfinis pour les codes postaux, les n° de Sécurité sociale et les n° de téléphone.

Ouvrez la boîte de dialogue "Format de cellule" avec le lanceur du groupe "Nombre", et jetez un coup d’œil à la catégorie "Spécial" :

Pensez à ces formats particuliers pour mettre en format rapidement les n° de téléphone et n° de Sécurité sociale dans vos tableaux (la plus-value concernant les codes postaux est moins évidente) :

Comment créer un format personnalisé ?

Les formats prédéfinis proposés par Excel couvrent un large panel de situations.

Mais il arrive parfois d'avoir besoin de créer son propre format, par exemple pour indiquer un horaire de type 18h05 et non pas 18:05, ou ajouter une unité à un nombre (13 m², 15 jours, 20 ml...).

Commencez par ouvrir la boîte de dialogue Format de cellule (accessible via le lanceur du groupe Nombre), choisissez Personnalisée dans les catégories disponibles, et sélectionnez le type qui se rapproche le plus du format à obtenir :

Les arguments du format personnalisé

Un format personnalisé est saisi (ou modifié) dans le champ Type. Il contient entre un et quatre arguments séparés par un point virgule :

<format nombre positif> ; <format nombre négatif> ; <format zéro> ; <format texte>

Chaque argument va nous donc permettre de détailler le format choisi pour les nombres positifs, les nombres négatifs, les valeurs nulles ou le texte qui pourront être saisis dans la cellule.
Il ne sera pas possible de prévoir deux formats différents pour le même d'argument...

Sachez qu'il n'est pas nécessaire de prévoir un format pour chacun des arguments. En effet :

• Si votre format ne contient qu'un argument, il s'applique aux nombres positifs.
• Si votre format contient deux arguments, le premier concernera les nombres positifs, le second les nombres négatifs.
• Si votre format contient trois arguments, le premier concernera les nombres positifs, le second les nombres négatifs, et le troisième les valeurs nulles.
• Et enfin si votre format contient quatre arguments, le premier concernera les nombres positifs, le second les nombres négatifs, le troisième les valeurs nulles, et le quatrième les valeurs alphanumériques.

Si vous souhaitez masquer une saisie dans certains cas, vous pouvez omettre l'argument de la manière suivante (ici, pour masquer les nombres négatifs) : ####.#;;0.
Saisir ;;; aura pour effet de masquer le contenu de la cellule quel que soit sa valeur.

Personnaliser le format des nombres

Vous avez remarqué que chaque argument se compose de codes, pour aboutir à une expression du type # ###,#0 par exemple. Nous allons passer en revue ces codes, avec quelques exemples :

Le symbole #

Indique un chiffre, par exemple : ###,## est le format à saisir pour un arrondi à deux chiffres après la virgule.
Ainsi, la saisie 45,356 affichera 45,36. Mais 45,3 s’affichera tel quel.

Le symbole 0

Indique un chiffre tout en obligeant Excel à afficher des 0 si le chiffre n’existe pas :

• Avec le format ###,#0, 45,3 s’affichera 45,30, et 45 s’affichera 45,00.
• Avec le format 00#, 32 s’affichera 032.

Les guillemets ""

Ils permettent d'ajouter un texte dans le format du nombre. Ce peut être une unité, ou n'importe quoi d'autre. Par exemple :

• # ##0" jours" affiche le mot jours systématiquement après le nombre.
• # ##0" m²" affiche m² systématiquement après le nombre.
• "prix total : "###0,00 affiche le terme prix total systématiquement avant le nombre.
• "date : "jj/mm/aa affiche la date saisie dans la cellule précédée de la mention date : (voir plus bas pour les codes de format de date).

Le symbole @

C'est le symbole qui représente le texte saisi dans la cellule.

Par exemple, le format #;#;0;@"( texte)" affichera systématiquement la mention (texte) après le texte saisi dans la cellule.

Espace

Un espace apparaîtra tel quel. Il permet notamment d’ajouter un séparateur de milliers : avec le format # ###,##, la saisie 53723,15 s’affiche 53 723,15.

Mais attention ! Le fait de mettre un espace à droite du format de nombre a pour effet de l’afficher divisé par 1000. Cela est très utile pour afficher des unités en kilo : km, k€, kHz, kW…

Entendons nous bien : le nombre lui-même reste tel quel, ce n'est que son affichage qui est modifié.
Ainsi, chaque espace ajouté à droite du format d’un nombre effectue une division par 1000 pour son affichage à l'écran. Nous devons donc ajouter un espace pour les milliers, deux espaces pour les millions, trois espaces pour les milliards, etc...

Par exemple, avec le format # ##0,0  "M€" contenant deux espaces après le 0,0, le nombre 10 000 000 000 s’affichera 10 M€.

Le symbole ?

Permet d'ajouter des espaces de part et d’autre du séparateur décimal pour que celui-ci soit aligné. Par exemple, le format #?,???? permet d'afficher :

Vous pouvez également utiliser le point d’interrogation ( ?) dans vos formats personnalisés de fractions, pour aligner le numérateur. Essayez ce format # ???/??? par exemple :

Le symbole _

Ce symbole permet de réserver un espace de la taille du caractère placé immédiatement après (qui ne sert qu'à déterminer la taille de l'espace à réserver, mais ne s'affiche pas).

Par exemple, en compatbilité, les nombres négatifs peuvent s'afficher entre parenthèses.
Le format 0;(0) nous permet de réaliser cet affichage particulier. Mais la parenthèse de droite décale systématiquement les nombres négatifs légèrement à gauche, alors que les nombres positifs sont plus rapprochés du bord droit de la cellule.
Le format 0_);(0) permet de résoudre cette difficulté. L'argument des nombres positifs réserve un espace de la largeur d'une parenthèse fermante, permettant ainsi aux nombres positifs d'être alignés à gauche, au même niveau que les nombres positifs :

Le symbole *

Il s'agit d'un symbole "joker" permettant de répéter le caractère qui le suit pour remplir la cellule.

On peut l'utiliser pour remplir la cellule d'un caractère décoratif, le point par exemple. Ainsi, le format *.# ##0,00 permet d'obtenir l'affichage suivant :

Mais on peut également s'en servir pour justifier les nombres négatifs, en appuyant le signe moins sur le bord gauche de la cellule, le nombre lui-même restant appuyé à droite. Le format # ##0;-* # ##0 (remarquez l'astérisque suivi d'un espace, placé derrière le signe moins des nombres négatifs) permet de réaliser l'affichage suivant :

Le symbole \

Permet d'afficher le caractère suivant. Il est surtout utilisé pour afficher les symboles que nous avons listés précédemment, et qui servent à coder le format : par exemple, l'astérisque, le dièse ou le signe pourcentage.

On peut également placer ces symboles entre guillemets pour obtenir le même résultat

En plaçant le symbole devant le caractère, celui-ci s'affichera tel quel :

Valeur	Format	Affichage
53	\*0 ou "*"0,	*53
53	\#0 ou "#"0	#53
53	\%0 ou "%"0	%53

Autres symboles

Pour être tout à fait complet, voici la liste des codes qui peuvent s'utiliser tels quels dans un code de format :

$	Dollar
+-	Plus, moins
()	Parenthèses
{}	Accolades
<>	Supérieur, Inférieur
=	Egal
:	Deux points
^	Puissance
'	Apostrophe
/	Anti slash
!	Point d'exclamation
&	Esperluette
~	Tilde

Personnaliser le format des dates et heures

Format de dates

Les formats de dates utilisent les codes j pour les jours, m pour les mois, et a pour les années.

Pour les jours :

• j : affiche le numéro du jour (1, 2, ..., 30,31)
• jj: affiche le numéro du jour sur deux chiffres (01, 02, ..., 30, 31)
• jjj : affiche les 3 premières lettres du nom du jour (lun, mar, ..., sam, dim)
• jjjj : affiche le nom du jour de la semaine en entier (lundi, mardi, ..., samedi, dimanche)

Pour les mois :

• m : affiche le numéro du mois (1, 2, ..., 11, 12)
• mm: affiche le numéro du mois sur deux chiffres (01, 02, ..., 11,12)
• mmm : affiche les 3 premières lettres du mois (jan, fev, ..., nov, dec)
• mmmm : affiche le mois en entier (janvier, février, ..., novembre, décembre)

Pour les années :

• a ou aa : affiche l'année sur 2 chiffres.
• aaa ou aaaa : affiche l'année sur 4 chiffres.

Quelques exemples de formats de date personnalisés :

jj/mm/aa
01/01/2023
05/02/2023
15/03/2023
31/10/2023
15/12/2023

j/m/a
1/1/23
5/2/23
15/3/23
31/10/23
15/12/23

jjj j mmm aaaa
dim 1 janv 2023
dim 5 févr 2023
mer 15 mars 2023
mar 31 oct 2023
ven 15 déc 2023

jj mmmm aaaa
01 janvier 2023
05 février 2023
15 mars 2023
31 octobre 2023
15 décembre 2023

jj mmmm aaaa
01 janvier 2023
05 février 2023
15 mars 2023
31 octobre 2023
15 décembre 2023

jj-mm-aaaa
01-01-2023
02-01-2023
03-01-2023
04-01-2023
05-01-2023

Format de temps

Les formats de temps utilisent le code h pour les heures, m pour les minutes, et s pour les secondes. Voici un exemple de format simple : h:m, qui affiche 1 heure 5 minutes sous la forme 1:5.

On peut également doubler ces codes pour afficher systématiquement la donnée sur deux chiffres. Le format hh:mm affiche 1 heure 5 minutes sous la forme 01:05.

Il n'existe pas de format prédéfini pour afficher 1 h 5 mn. Le plus simple est d'utiliser un format personnalisé du type : h" h " m" mn".

Les codes h, m et s présentent les fractions horaires, minutes et secondes du temps saisi dans la cellule.
Cela veut dire que ces codes limitent l'affichage des heures à 24 heures maximum, des minutes à 60 minutes maximum, et des secondes à 60 secondes maximum également.
Dans ces conditions, comment exprimer une durée en heures supérieure à 24, ou une durée en minutes ou secondes supérieure à 60 ? Il suffit d'indiquer le code entre crochet : [h] par exemple.

De cette façon, le nombre 0,0416667 qui équivaut à 1 heure, s'affichera 0 sec avec le format s" sec" (car il n'y a pas de seconde excédentaire par rapport à 1 heure), mais s'affichera 3600 sec avec le format [s]" sec".

Comment ajouter de la couleur aux formats de nombres ?

Que diriez-vous d'agrémenter vos données avec un peu de couleur ?

Il peut être intéressant de faire ressortir visuellement des informations, par exemple les valeurs négatives, et les couleurs peuvent facilement nous y aider.

Excel permet d'utiliser des couleurs dans le formats personnalisés. 8 couleurs sont prises en charge par défaut : noir, blanc, bleu, rouge, jaune, vert, cyan, magenta.

Le nom de la couleur doit être saisi entre crochets. Ainsi, le format [bleu]# ##0;[Rouge]-# ##0 permet de faire apparaître les valeurs positives en bleu, et les valeurs négatives en rouge :

Les 8 couleurs proposées sont assez sommaires. Heureusement, Excel nous propose un plus large choix dans une palette de 56 couleurs :

Pour utiliser ces couleurs, il suffit de mettre le numéro entre crochet, précédé du mot couleur, comme par exemple [couleur43]# ##0;[couleur46]-# ##0 pour formater les nombres positifs en vert clair, et les nombres négatifs en orange.

Comment ajouter des conditions à un format ?

Excel permet d'ajouter des conditions dans un format personnalisé. Avant de s'appliquer, le format va tester le contenu de la cellule et appliquer le formatage défini.

La condition est indiquée entre crochets. Le contenu de la cellule est testé avec les opérateurs > (supérieur à ),< (inférieur à), >= (supérieur ou égal à), <= (inférieur ou égal à), <> (différent de) ou = (égal à).

Par exemple, [Vert][<10]0;[Bleu][>30]0;Standard permet d'afficher en vert les valeurs inférieures à 10, en bleu les valeurs supérieures à 30, et applique le format standard pour les valeurs intermédiaires.

On peut se servir des conditions pour gérer le pluriel des unités par exemple. Plus haut dans ce cours, nous avons utilisé le format # ##0" jours" pour afficher le mot jours systématiquement à la suite du nombre saisi.
Mais dans notre exemple, la saisi d'un 0 et d'un 1 dans la cellule seront affichés respectivement  0 jours et 1 jours, ce qui n'est pas correct en matière d'accord du singulier et du pluriel.

Le format [>1]# ##0" jours ;# ##0" jour" nous permet de gérer ce cas de figure. Il teste la valeur de la cellule, et ajoute la mention jours lorsque le nombre saisi est supérieur à 1, et la mention jour dans les autres cas.