Lorsque vous entrez un nombre dans une cellule, vous pouvez afficher ce nombre dans une variété de formats différents. Excel a pas mal de formats de nombres intégrés, mais parfois aucun d’entre eux n’est exactement ce dont vous avez besoin. Ce chapitre explique comment créer des formats de nombres personnalisés et fournit de nombreux exemples que vous pouvez utiliser tels quels ou adapter à vos besoins.

À propos de la forme numérique

Par défaut, toutes les cellules utilisent le format de nombre standard (Ce que vous tapez est ce que vous obtenez). Mais si la cellule n’est pas assez large pour afficher le nombre entier, le format Standard arrondit les nombres avec des décimales et utilise la notation scientifique pour les grands nombres. Dans de nombreux cas, le format de nombre général fonctionne très bien, mais la plupart des gens préfèrent spécifier un format de nombre différent pour plus de cohérence.

Excel applique automatiquement un format numérique intégré à une cellule en fonction des critères suivants :

■ Si un nombre contient une barre oblique (/), il peut être converti en un format de date ou un format de fraction.

■ Si un nombre contient un tiret (-), il peut être converti en un format de date.

■ Si un nombre contient deux points (:) ou est suivi d’un espace et de la lettre A ou P (majuscule ou minuscule), il peut être converti en un format d’heure.

■ Si un nombre contient la lettre E (majuscule ou minuscule), il peut être converti en notation scientifique (également appelée format exponentiel). Si le nombre ne correspond pas à la largeur de la colonne, il peut également être converti dans ce format.

Formater des nombres à l’aide du ruban

Le groupe Nombre de l’onglet Accueil du ruban contient plusieurs commandes permettant d’appliquer rapidement les formats de nombre courants. La commande déroulante Format de nombre vous donne un accès rapide à 11 formats de nombre courants.

De plus, le groupe Nombre contient quelques boutons. Lorsque vous cliquez sur l’un de ces boutons, les cellules sélectionnées prennent le format numérique spécifié. Le tableau suivant résume les formats que ces boutons exécutent.

Tableau 2.2.5-a : Combinaison de touches

Utilisation de la boîte de dialogue Format de cellule pour formater des nombres

Pour un contrôle maximal de la mise en forme des nombres, utilisez l’onglet Nombre dans la boîte de dialogue Format de cellule. Vous pouvez accéder à cette boîte de dialogue de plusieurs manières :

■ Cliquez sur le lanceur de boîte de dialogue en bas à droite du groupe Nombre de l’onglet Accueil.

■ Choisissez Accueil / Nombre / Format de nombre / Autres formats de nombre.

■ Appuyez sur Ctrl+1.

L’onglet Nombre de la boîte de dialogue Format de cellule contient 12 catégories de formats de nombre parmi lesquels choisir. Lorsque vous sélectionnez une catégorie dans la zone de liste, le côté droit de la boîte de dialogue change pour afficher les options appropriées.

Voici les catégories de format de nombre, ainsi que quelques commentaires généraux :

■ Standard : le format par défaut ; il affiche les nombres sous forme d’entiers, de décimales ou en notation scientifique si la valeur est trop large pour tenir dans la cellule.

■ Nombre : spécifiez le nombre de décimales, s’il faut utiliser le séparateur des milliers de votre système (par exemple, une virgule) pour séparer les milliers et comment afficher les nombres négatifs.

■ Devise : spécifiez le nombre de décimales, choisissez un symbole de devise et affichez les nombres négatifs. Ce format utilise toujours le symbole séparateur des milliers du système (par exemple, une virgule) pour séparer les milliers.

■ Comptabilité : diffère du format de devise en ce que les symboles de devise s’alignent toujours verticalement, quel que soit le nombre de chiffres affichés dans la valeur.

■ Date : choisissez parmi une variété de formats de date et sélectionnez les paramètres régionaux de vos formats de date.

■ Heure : choisissez parmi un certain nombre de formats d’heure et sélectionnez les paramètres régionaux pour vos formats d’heure.

■ Pourcentage : choisissez le nombre de décimales ; affiche toujours un signe de pourcentage.

■ Fraction : choisissez parmi neuf formats de fraction.

■ Scientifique : Affiche les nombres en notation exponentielle (avec un E) : 2,00E+05 = 200 000. Vous pouvez choisir le nombre de décimales à afficher à gauche de E.

■ Texte : lorsqu’il est appliqué à une valeur, Excel traite la valeur comme du texte (même si elle ressemble à une valeur). Cette fonction est utile pour des éléments tels que les numéros de pièce numériques et les numéros de carte de crédit.

■ Spécial : contient des formats de nombres supplémentaires. La liste varie en fonction de la région que vous choisissez. Pour les paramètres régionaux anglais (États-Unis), les options de formatage sont le code postal, le code postal +4, le numéro de téléphone et le numéro de sécurité sociale.

■ Personnalisé : Définit des formats de nombres personnalisés qui ne sont inclus dans aucune des autres catégories.

Création d’un format de nombre personnalisé

Lorsque vous créez un format de nombre personnalisé, il peut être utilisé pour formater n’importe quelle cellule du classeur.

Vous pouvez créer environ 200 formats de nombres personnalisés dans chaque classeur. La figure suivante montre la catégorie Personnalisé dans l’onglet Nombre de la boîte de dialogue Format de cellule.

Vous pouvez créer des formats de nombres non inclus dans les autres catégories. Excel vous offre une grande flexibilité dans la création de formats de nombres personnalisés.

Les formats numériques personnalisés sont stockés avec le classeur dans lequel ils sont définis. Pour rendre le format personnalisé disponible dans un autre classeur, vous pouvez simplement copier une cellule qui utilise le format personnalisé dans l’autre classeur.

Parties d’une chaîne de format numérique

Les codes de format numérique sont une chaîne de symboles qui définissent la façon dont Excel affiche les données que vous stockez dans les cellules. Nous verrons comment décrire les formats, mais nous devons d’abord expliquer comment Excel interprète ces symboles. Chaque code de format numérique est composé d’un maximum de 4 sections séparées par un point-virgule ( ; ), ce qui vous permet de spécifier différents codes de format pour les nombres positifs, les nombres négatifs, les valeurs zéro et le texte. Les codes sont classés dans l’ordre suivant :

Format positif ; Format négatif ; Format zéro ; Format texte

Si vous n’utilisez pas les quatre sections d’une chaîne de format, Excel interprète la chaîne de format comme suit :

■ Si vous n’utilisez qu’une seule section : la chaîne de format s’applique à tous les types d’entrées numériques.

■ Si vous utilisez deux sections : La première section s’applique aux valeurs positives et aux zéros, et la deuxième section s’applique aux valeurs négatives.

■ Si vous utilisez trois sections : la première section s’applique aux valeurs positives, la deuxième section s’applique aux valeurs négatives et la troisième section s’applique aux zéros.

■ Si vous utilisez les quatre sections : La dernière section s’applique au texte stocké dans la cellule.

Vous pouvez choisir d’ignorer le formatage pour l’une des sections du milieu en entrant Standard. Par exemple, si vous souhaitez uniquement affecter des nombres positifs et du texte, vous pouvez saisir un code de format de nombre avec cette disposition :

Section1; Standard; Standard; Section 4

Standard supprime toute mise en forme des données saisies, alors faites attention à la façon dont vous l’utilisez. Les nombres négatifs avec le code de format Standard n’afficheront pas le signe moins devant leur nombre.

Codes de format de nombres personnalisés

Le tableau suivant répertorie les codes de formatage disponibles pour les formats personnalisés, ainsi que de brèves descriptions.

Exemple 1 : Modification de la couleur de la police

L’une des choses les plus simples que vous puissiez faire avec les codes de format numérique est de changer la couleur de la police dans les cellules concernées. La syntaxe pour le faire est simple :

[Nom de la couleur]

Choisissez simplement la section qui correspond à la partie de la droite numérique dont vous souhaitez changer la couleur et indiquez la couleur entre crochets. Les options de couleur sont les suivantes noir, vert, bleu, magenta, jaune, cyan et rouge.

À titre d’exemple, nous pouvons fournir un code couleur distinct pour chaque partie d’un code de format numérique :

[Rouge]Standard; [Bleu]Standard; [Magenta]Standard; [Cyan]Standard

Le code Standard indique simplement à Excel de représenter les nombres tels qu’ils ont été saisis par l’utilisateur. La sortie de ce code de format numérique ressemble à ceci :

Notez que le nombre négatif de la ligne 3 n’obtient pas automatiquement un signe négatif (-) devant lui. Nous remplaçons le format par défaut des nombres négatifs dans la cellule. Notez également que le format de couleur n’affecte rien sur la présentation ; le nombre de décimales reste le même, tout comme l’alignement des données à gauche ou à droite des cellules.

Exemple 2 : Ajout de texte avec des codes de format numérique

Vous pouvez ajouter du texte autour des nombres avec des codes de format numérique en insérant le texte dans une section de l’une des deux manières suivantes :

Caractères simples

Pour les caractères uniques (comme un symbole @ avant un nombre), tapez une barre oblique inverse (\) suivie du symbole. Le code de format de nombre :

\@Standard

Résultats dans la sortie suivante :

Notez que le signe moins précède toujours le nombre négatif. Notez également que la valeur Texte n’est pas affectée par l’ajout du symbole @.

Il est important de noter que cela est différent du résultat si nous avons étendu notre directive de format à chaque section du code de format de nombre :

\@Standard;\@ Standard;\@ Standard;\@ Standard

Résultats dans la sortie suivante :

Notez que le signe moins a disparu du nombre négatif et que la valeur Texte reçoit maintenant le symbole @.

Chaînes de texte

Pour ajouter une chaîne de texte entière à un nombre (comme ajouter des « unités » à la fin d’un nombre), nous entourons la chaîne de texte de guillemets. Le code de format de nombre :

Standard”unités”

Résultats dans la sortie suivante :

Encore une fois, c’est différent de :

Standard “unités”; Standard “unités” ; Standard “unités” ; Standard “unités”

Ce qui se traduit par :

Exemple 3 : Zéro (0)

Voici un exemple du code zéro en action. Les exemples suivants utilisent ce code de format de nombre :

0,00

Les zéros dans le code de format numérique représentent un chiffre forcé. 

Exemple 4 : Point d’interrogation (?)

Voici un exemple de code de point d’interrogation en action. Les exemples suivants utilisent ce code de format de nombre :

0,??

Un point d’interrogation représente un espace réservé à un chiffre. Ajoute des espaces pour les zéros insignifiants de chaque côté du point décimal afin que les points décimaux s’alignent lorsqu’ils sont formatés avec une police à largeur fixe.

Exemple 5 : Signe dièse (#)

Voici un exemple du code dièse en action. Les exemples suivants utilisent ce code de format de nombre :

#, ##

Le signe dièse dans le code de format numérique représente un chiffre facultatif. Cela signifie que lorsque le numéro affiché n’a pas besoin du chiffre en question, il sera omis du numéro affiché.

Exemple 6 : L’espace

Voici un exemple du code espace en action. Les exemples suivants utilisent ce code de format de nombre :

$??? ???,00

L’espace dans le code de format de nombre représente les séparateurs de milliers dans le nombre affiché. 

Exemple 7 : Astérisque (*)

Voici un exemple du code astérisque en action. Les exemples suivants utilisent ce code de format de nombre :

*=0.##

L’astérisque dans le code de format numérique représente le modificateur de caractère répétitif. Il est utilisé avec un caractère pour afficher un chiffre répété qui remplit l’espace vide dans une cellule.

Exemple 8 : Souligner (_)

Voici un exemple du code de soulignement en action. Les exemples suivants utilisent ce code de format de nombre :

_(#,##_);(#,##)

Le trait de soulignement dans le code numérique représente le modificateur de caractère espace. Il est utilisé avec un caractère pour afficher un espace vide de taille égale au caractère spécifié. 

Exemple 9 : Fractions (/)

Excel prend en charge un certain nombre de formats de nombres de fractions intégrés. (Sélectionnez la catégorie Fraction dans l’onglet Nombre de la boîte de dialogue Format de cellule.) Par exemple, pour afficher la valeur 0,125 sous forme de fraction avec 8 comme dénominateur, sélectionnez Comme huitièmes (4/8) dans la liste Type

Les fractions sont spéciales, car elles nécessitent un changement d’unités. Le nombre 0,23 est représenté par 23/100, mais 0,25 peut être simplifié en 1/4 ou représenté par 25/100 . De même, 1,25 peut être représenté par 1 1/4 ou la fraction impropre 5/4 . La façon dont Excel affiche le nombre dépend de la façon dont vous construisez le code de format de nombre.

Les fractions arrondissent effectivement les valeurs à la fraction la plus proche possible. Ils suivent également les directives du signe dièse et des symboles de point d’interrogation avec lesquels ils sont associés.

Entier avec fractions réduites

Une représentation assez typique pour les fractions est de garder les nombres entiers indépendants du reste de la fraction. La représentation de ceci est relativement simple et peut être faite avec des signes dièse et des points d’interrogation à un effet légèrement différent.

En utilisant la notation point d’interrogation (?), le code de format de nombre suivant :

#???/???

Produit un reste de fraction avec jusqu’à trois chiffres :

L’alignement de la barre de fraction est conservé quel que soit le nombre de chiffres utilisés. Si nous limitons le nombre de chiffres de chaque côté de la fraction à 2, Excel arrondira le nombre à la valeur de fraction la plus proche. Le format de code numérique suivant :

#??/??

Modifie la représentation de 0,23 de 23/100 à 3/13.

Si vous ne souhaitez pas conserver l’alignement autour de la barre de fraction, vous pouvez utiliser un code de format de nombre de fraction similaire qui utilise des signes dièse.

Fractions incorrectes

Si vous préférez regrouper la partie entière d’une valeur dans la fraction elle-même, vous pouvez le spécifier dans le code de format de nombre.

En utilisant la notation dièse, le code de format de nombre suivant :

###/###

Produit une fraction impropre comportant jusqu’à trois chiffres :

Fractions à base fixe

Il est également possible de forcer Excel à arrondir les fractions à un dénominateur spécifique en le spécifiant dans le code de format numérique.

Voici un exemple de code de base fixe en action. Les exemples suivants utilisent ce code de format de nombre :

/15

Le résultat est un reste de fraction arrondi qui va au nombre le plus proche de quinzième.

Exemple 10 : Pourcentages (%)

Tout comme les fractions, les pourcentages sont contrôlés par les codes de format numérique qui les accompagnent. Un pourcentage de base peut être obtenu avec un symbole dièse dans le code de format numérique :

#%

Résultats dans la sortie suivante :

Vous pouvez également spécifier des pourcentages fractionnaires, comme indiqué avec ce code de format de nombre :

##/#%

Résultats en fractions à un chiffre dans les pourcentages si nécessaire :

Enfin, comme toujours, vous pouvez spécifier le nombre de chiffres significatifs avec décimales :

#,0%

Résultats dans une décimale alignée à la 10e place :

Exemple 11 : Notation scientifique (A)

Il est difficile de lire des nombres extrêmement petits et extrêmement grands de manière conventionnelle à cause de tous les zéros de début et de fin. La notation scientifique corrige cela en déplaçant la décimale vers les chiffres pertinents, de sorte que 0,0000001 peut devenir 1 x 10 ^-7. Excel utilise la notation E pour cela, de sorte que le même nombre serait 1E-07. Ainsi, comme vous vous en doutez, la lettre majuscule E signale une notation scientifique sous forme de code de format numérique.

Sinon, la notation scientifique dans Excel est contrôlée par les mêmes codes numériques que les pourcentages et les fractions. Il a besoin d’un code de format numérique devant le E pour décrire les chiffres pertinents et d’un plus ( + ) et d’un autre code de format numérique derrière pour décrire le traitement du chiffre exponentiel.

Voici un exemple de code de notation scientifique en action. Les exemples suivants utilisent ce code de format de nombre :

#E+#

Vous pouvez également obtenir une notation plus cohérente avec des zéros. Les exemples suivants utilisent ce code de format de nombre :

0,00E+00

 Notez que dans ce cas, la décimale et l’exposant sont tous deux contraints à 2 chiffres significatifs, qu’ils soient ou non nécessaires. Le compromis est qu’il maintient la sortie beaucoup plus cohérente, avec une longueur de chaîne prévisible.

Valeurs de mise à l’échelle

Vous pouvez utiliser un format de nombre personnalisé pour mettre un nombre à l’échelle. Par exemple, si vous travaillez avec de grands nombres, vous souhaiterez peut-être afficher les nombres en milliers (c’est-à-dire afficher 1 200 000 comme

1200). Le nombre réel bien sûr, sera utilisé dans les calculs qui impliquent cette cellule. Le formatage n’affecte que la façon dont il est affiché.

Affichage des valeurs en milliers

La chaîne de format suivante affiche les valeurs sans les trois derniers chiffres à gauche de la décimale et sans décimale. En d’autres termes, la valeur apparaît comme si elle était divisée par 1 000 et arrondie à aucune décimale.

# ###[espace]

[espace] représente un espace à ajouter à la fin du code # ###.

Une valeur avec ce format numérique apparaît comme si elle était divisée par 1 000 et arrondie à deux décimales.

# ###,00[espace]

Affichage des valeurs en centaines

La chaîne de format suivante affiche les valeurs en centaines, avec deux décimales. Une valeur avec ce format numérique apparaît comme si elle était divisée par 100 et arrondie à deux décimales.

0″,”00

Le tableau suivant montre des exemples de ces formats de nombres.

 Affichage des valeurs en millions

La chaîne de format suivante affiche les valeurs en millions sans décimales. Une valeur avec ce nombre apparaît comme si elle était divisée par 1 000 000 et arrondie à aucune décimale :

# ###[espace][espace]

[espace][espace] représente deux espaces

Une variation de cette chaîne de format

Une valeur avec ce nombre apparaît comme si elle était divisée par 1 000 000 et arrondie à deux décimales :

# ###,00[espace][espace]

Voici une autre variante

Cette chaîne de format ajoute la lettre M à la fin de la valeur :

# ###[espace][espace]M

La chaîne de format suivante est un peu plus complexe. Il ajoute la lettre M à la fin de la valeur, affiche les valeurs négatives entre parenthèses et affiche des zéros :

# ###,0[espace][espace]”M”_);(# ###,0[espace][espace]”M)”;0,0″M”_)

Le tableau suivant montre des exemples de ces chaînes de format.

2.2.5.2.4 Codes de format de nombre personnalisés pour les dates et heures

Le tableau suivant répertorie les codes utilisés pour créer des formats personnalisés pour les dates et les heures.

Lorsque vous entrez une date dans une cellule, Excel formate la date à l’aide du format de date abrégé du système. Vous pouvez modifier ce format en utilisant le Panneau de configuration de Windows (Options régionales et linguistiques).

Excel fournit de nombreux formats de date et d’heure intégrés et utiles. Le tableau suivant montre d’autres formats de date et d’heure que vous pouvez trouver utiles. La première colonne du tableau indique le numéro de série date/heure.

Spécification des conditions

Le format de nombre personnalisé suivant affiche du texte, en fonction de la valeur de la cellule :

[<10]”Trop bas”;[>10]”Trop haut”;”Juste comme il faut”

Si la valeur est inférieure à 10, il affiche Trop bas. Si la valeur est supérieure à 10, il affiche Trop élevé. Si la valeur est exactement 10, il affiche Just right. Notez que vous ne pouvez spécifier qu’une ou deux conditions, plus un “autre”.

En règle générale, l’utilisation de la fonction de mise en forme conditionnelle d’Excel est une meilleure solution pour la mise en forme basée sur une valeur.