Cet article va vous montrer toutes les façons de formater vos grands nombres en milliers, millions ou milliards.
Les grands nombres sont souvent utilisés dans une feuille de calcul Excel, ce qui peut les rendre difficiles à lire s’ils ne sont pas formatés correctement.
Un nombre en millions ou même en milliards peut être difficile à évaluer et peut facilement être mal interprété par un facteur de 10.
Heureusement, Excel propose plusieurs façons simples de formater les nombres avec des séparateurs de virgules pour aider l’utilisateur à les lire.
Si vous avez déjà consulté des feuilles de calcul financières, les chiffres sont souvent arrondis au millier le plus proche, car ils sont souvent très élevés. Ils peuvent même être arrondis au million le plus proche si les chiffres sont extrêmement élevés.
Non seulement l’affichage des chiffres sous forme de milliers ou de millions facilite la lisibilité, mais il permet également de tout faire tenir sur une seule page.
Dans cet article, je vais vous montrer 5 façons simples d’afficher vos grands nombres en milliers ou en millions. Téléchargez le cahier d’exercices d’exemple pour suivre le mouvement !
Utilisez une formule simple pour formater les nombres en milliers, millions ou milliards.
Une façon simple d’afficher des nombres en milliers ou en millions est d’utiliser une formule simple pour diviser le nombre par un millier ou un million.
= B3 / 1000Pour obtenir un nombre en milliers d’unités, vous pouvez utiliser la formule ci-dessus.
La cellule B3 contient le nombre original et cette formule calculera le nombre de milliers, en indiquant le reste sous forme de nombre décimal.
= B2 / 1000000Pour obtenir un nombre en millions d’unités, vous pouvez utiliser la formule ci-dessus. Là encore, tout ce qui est inférieur à un million apparaîtra sous forme de décimale.
= B2 / 10 ^ 6L’un des problèmes ici pourrait être de s’assurer que vous avez saisi le nombre correct de zéros. C’est une erreur facile à commettre. Une façon de contourner ce problème est d’utiliser la formule ci-dessus.
Le caractère carat(^) élève la valeur de 10 à la puissance 6, soit 1 million. Vous pouvez utiliser cette même astuce lorsque vous souhaitez afficher des valeurs en milliards puisque 10^9 est égal à 1 milliard.
= ROUND ( B3 / 10 ^ 6, 1 )Vous pouvez également utiliser la fonction ROUND pour supprimer toute décimale excédentaire du résultat. La formule ci-dessus ne donnera qu’un seul chiffre après la virgule.
En revanche, si vous souhaitez conserver la précision mais n’afficher qu’une seule décimale, vous pouvez modifier le format.
- Sélectionnez les cellules que vous souhaitez formater.
- Allez dans l’onglet Accueil du ruban.
- Cliquez sur la commande de format décimal pour diminuer le nombre de décimales. Vous devrez peut-être cliquer plusieurs fois sur cette commande en fonction du nombre de décimales que vous souhaitez afficher.
Utilisez le Paste Special pour formater les nombres en milliers, millions ou milliards.
C’est une autre façon de diviser vos chiffres par mille ou par million, mais sans utiliser de formule.
Vous pouvez utiliser une option spéciale de collage pour diviser un nombre par un millier ou un million.
L’inconvénient est qu’il écrasera le nombre original et que le processus est un peu plus compliqué. Vous voudrez peut-être stocker les chiffres ailleurs dans votre feuille de calcul avant d’utiliser cette technique !
- Entrez le nombre du diviseur, tel que 1000, dans une autre cellule.
- Copiez la cellule du numéro de diviseur (cellule D2 dans cet exemple) en utilisant Ctrl + C ou en cliquant avec le bouton droit de la souris sur la cellule et en sélectionnant Copier dans le menu contextuel. Une fois copiée dans le presse-papiers, la cellule sera entourée d’une ligne de tirets verts.
- Sélectionnez les cellules que vous voulez diviser.
- Cliquez avec le bouton droit de la souris sur le grand nombre à diviser et sélectionnez Collage spécial dans le menu contextuel ou appuyez sur Ctrl + Alt + V sur votre clavier.
Cela ouvrira le menu Collage spécial.
- Sélectionnez l’option Diviser qui se trouve dans la section Opération du menu Collage spécial. Vous pouvez également sélectionner l’option Valeurs dans la section Collage afin de ne pas écraser votre mise en forme.
- Appuyez sur le bouton OK.
Cela remplacera vos grands nombres par des valeurs qui ont été divisées par 1000.
Vous pouvez utiliser exactement le même processus pour diviser vos valeurs par un million, un milliard ou toute autre valeur nécessaire.
Utilisez la fonction TEXT pour formater les nombres en milliers, millions ou milliards.
La fonction TEXTE d’Excel est extrêmement utile pour formater les chiffres et les dates dans des formats particuliers.
Dans ce cas, vous pouvez l’utiliser pour formater vos chiffres en milliers, millions ou milliards. Le résultat sera converti en valeur texte, ce qui signifie que cette option ne doit pas être utilisée si vous devez effectuer d’autres calculs avec les chiffres.
Syntaxe de la fonction TEXT
TEXT ( value, format )- La valeur est la valeur numérique que vous voulez formater.
- format est une chaîne de texte qui représente la règle de format pour la sortie.
La fonction TEXT prend un nombre et renvoie une valeur texte qui a été formatée.
Exemple avec la fonction TEXT
Dans ce billet, la fonction TEXT sera utilisée pour ajouter des séparateurs de virgule, masquer les chiffres des milliers et ajouter un k à la fin pour signifier que le résultat est en milliers.
Dans cet exemple, le grand nombre se trouve dans la cellule B3, et la formule TEXT est utilisée dans la cellule C3.
= TEXT ( B3, "#,##0, " ) & "k"La formule ci-dessus affiche le nombre de la cellule B3 sous la forme d’une valeur textuelle en milliers, accompagnée d’un k pour indiquer que le nombre est en milliers.
Vous avez remarqué que les résultats sont alignés à gauche ? Cela indique que les résultats sont des valeurs de texte et non des chiffres.
Le symbole de hachage(#) est utilisé pour fournir des caractères de remplacement pour les chiffres des nombres dans la chaîne de formatage. Le symbole de la virgule (,) est utilisé pour fournir un symbole de séparation entre les groupes de 3 chiffres.
Le zéro est inclus pour que, si le nombre est inférieur à 1000, la cellule affiche un 0 au lieu de rien. Si ce n’était qu’un caractère de remplacement(#), la cellule n’afficherait rien au lieu d’un 0 lorsque le nombre dans B3 est inférieur à 1000.
La dernière virgule (,) est nécessaire pour indiquer que les nombres inférieurs à 1000 resteront cachés car il n’y a pas de caractères de remplacement après cette virgule.
Notez que même lorsque le nombre dans la cellule B3 est supérieur à un million, les caractères de remplacement et les virgules ne doivent être fournis que pour un seul groupe de 3 chiffres.
Le nombre dans la cellule C3 est beaucoup plus facile à lire que le nombre brut dans la cellule B3.
= TEXT ( B3, "#,##0,, " ) & "M"Utilisez la formule ci-dessus si vous souhaitez afficher les chiffres en millions.
= TEXT ( B3, "#,##0,,, " ) & "B"Utilisez la formule ci-dessus si vous souhaitez afficher les chiffres en milliards. Une virgule supplémentaire est tout ce qui est nécessaire pour cacher chaque ensemble de 3 chiffres dans les résultats et tout caractère peut être ajouté à la fin si nécessaire.
Utilisez un format personnalisé pour afficher les nombres en milliers, millions ou milliards.
Il est également possible de formater vos nombres en milliers ou en millions en utilisant un format de nombre personnalisé.
Ainsi, vous n’avez pas besoin de modifier le chiffre original ou d’utiliser une formule pour afficher les chiffres dans le format souhaité.
Vous ne perdez pas non plus la précision de vos chiffres car ils restent intacts et apparaissent seulement différemment.
Pour ajouter un format personnalisé, procédez comme suit.
- Sélectionnez les cellules pour lesquelles vous souhaitez ajouter une mise en forme personnalisée.
- Appuyez sur Ctrl + 1 ou cliquez avec le bouton droit de la souris et choisissez Format Cells pour ouvrir la boîte de dialogue Format Cells.
- Allez dans l’onglet Nombre du menu Format des cellules.
- Sélectionnez Personnalisé dans les options de la catégorie.
#,##0, "k"- Ajoutez la chaîne de format ci-dessus dans le champ Type.
- Appuyez sur le bouton OK.
#,##0,, "M"Si vous avez besoin d’afficher vos chiffres en millions, utilisez plutôt la chaîne de format ci-dessus à l’étape 5.
#,##0,,, "B"Si vous devez afficher vos chiffres en milliards, vous pouvez utiliser la chaîne de format ci-dessus.
Comme avec la fonction TEXT, les symboles dièse (#) servent de caractères de remplacement pour les nombres, et le zéro garantit que si le nombre est égal à zéro, un zéro sera affiché plutôt qu’un blanc.
Pour afficher des caractères à la fin de vos chiffres, vous devrez les insérer entre deux guillemets.
Sous le champ Type, vous remarquerez qu’il existe un large éventail de formats personnalisés prédéfinis. Vous pouvez sélectionner l’un d’entre eux et le modifier de la même manière que les formats de milliers ou de millions ci-dessus.
Il s’agit d’un moyen facile de créer un format de milliers ou de millions qui autorise également les nombres négatifs.
#,##0,, "M";[Red](#,##0,,) "M"Par exemple, l’exemple ci-dessus formatera les nombres en millions mais affichera également les nombres négatifs en rouge avec les parenthèses qui les entourent.
Tous les nouveaux formats que vous créez seront répertoriés avec tous les autres formats personnalisés prédéfinis et vous pouvez utiliser le bouton Supprimer pour supprimer tous les formats personnalisés que vous avez créés.
L’avantage d’utiliser un format personnalisé est que vous pouvez toujours effectuer d’autres calculs sur les chiffres formatés. Les chiffres d’origine restent inchangés. Seule l’apparence des chiffres a changé.
Utilisez la mise en forme conditionnelle pour formater les nombres en milliers, millions ou milliards.
Vous pouvez également formater les nombres en fonction de leur valeur. Pour ce faire, il suffit de créer une règle de formatage conditionnel pour une cellule ou une plage de cellules, de sorte que la situation suivante se produise.
- Affiche le nombre comme d’habitude s’il est inférieur à 1k
- Affiche le nombre en milliers lorsqu’il est compris entre 1k et 1M
- Affiche le nombre en millions lorsqu’il est supérieur à 1M
Suivez ces étapes pour créer ce format conditionnel.
- Sélectionnez la cellule ou la plage de cellules que vous souhaitez mettre en forme.
- Allez dans l’onglet Accueil du ruban.
- Cliquez sur la commande Formatage conditionnel qui se trouve dans le groupe Styles.
- Sélectionnez Nouvelle règle dans les options.
- Sélectionnez l’option Formater uniquement les cellules qui contiennent qui se trouve dans la liste Sélectionner un type de règle.
- Dans les paramètres de formatage des cellules, sélectionnez Valeur de la cellule et Supérieur ou égal à dans les menus déroulants, puis entrez 1000 dans la zone de saisie.
- Cliquez sur le bouton Format pour choisir le formatage qui s’affichera lorsque les conditions seront remplies.
Cela ouvrira une version allégée du menu Format de cellules avec seulement un onglet Nombre, Police, Bordure et Remplissage.
- Allez dans l’onglet Nombre.
- Sélectionnez Personnalisé dans les options de catégorie disponibles.
#,##0, "k"- Saisissez la chaîne ci-dessus dans la section Type. Si vous avez déjà utilisé cette mise en forme personnalisée ailleurs, vous pourrez également la sélectionner dans les options prédéfinies situées sous le champ Type.
- Appuyez sur le bouton Ok.
Cette opération ferme le menu Format des cellules et le menu Nouvelle règle de formatage redevient actif. Vous devriez maintenant voir un aperçu de la mise en forme qui sera appliquée lorsque la formule sera vraie.
- Appuyez sur le bouton OK pour fermer le menu Nouvelle règle de mise en forme et enregistrer votre nouvelle règle de mise en forme conditionnelle.
Vous verrez maintenant que tous les chiffres de votre sélection apparaissent normaux lorsqu’ils sont inférieurs à 1000 et apparaissent en milliers lorsqu’ils sont supérieurs à 1000.
Répétez maintenant les étapes ci-dessus pour ajouter une autre règle de formatage conditionnel afin de prendre en charge les nombres supérieurs à 1000000.
Le processus est exactement le même que précédemment, avec de légères modifications concernant le format des cellules en entrée et le format personnalisé utilisé.
À l’étape 6, entrez la valeur 1000000 dans le champ de saisie.
#,##0,, "M"Dans l’étape 10, utilisez le format personnalisé ci-dessus.
Vous verrez maintenant vos chiffres apparaître avec un mélange de formatage en fonction de la valeur. Les nombres inférieurs à 1 000 apparaissent normalement, tandis que les nombres supérieurs à 1 000 mais inférieurs à 1 million sont affichés en milliers, et les nombres supérieurs à 1 million sont affichés en millions.
Pour les nombres inférieurs à 1k, le format général par défaut est adéquat car il n’y a pas besoin de séparateurs de virgules.
Si vous attendez des nombres négatifs, vous devrez répéter le processus pour ajouter des règles pour les nombres inférieurs ou égaux à -1000 et inférieurs ou égaux à -1000000.
Conclusions
Il existe de nombreuses façons dans Excel de présenter de grands nombres à l’utilisateur.
Tous ces éléments facilitent la lecture des chiffres et évitent les erreurs lorsque quelqu’un lit un chiffre dans un rapport.
Si le rapport est de grande largeur, il peut s’avérer nécessaire de présenter les chiffres en milliers, en millions ou même en milliards pour qu’ils puissent tenir sur la page.
Grâce à la mise en forme personnalisée des chiffres et à la mise en forme conditionnelle, il existe de nombreuses façons de mettre en évidence des valeurs spécifiques de chiffres afin d’attirer l’attention de l’utilisateur.