7 astuces étonnantes de format de nombre personnalisé Excel (vous devez savoir)

Le formatage Excel Custom Number est le vêtement pour les données dans les cellules Excel. Vous pouvez les habiller comme vous le souhaitez. Tout ce dont vous avez besoin est un peu de savoir-faire sur le fonctionnement du format de nombre personnalisé Excel.

Avec la mise en forme personnalisée des nombres dans Excel, vous pouvez modifier la façon dont les valeurs dans les cellules s'affichent, tout en conservant la valeur d'origine intacte.

Par exemple, je peux faire apparaître une valeur de date dans différents formats de date, tandis que la valeur sous-jacente (le nombre qui représente la date) resterait la même.

Dans ce didacticiel, je couvrirai tout sur le format de nombre personnalisé dans Excel et vous montrerai également des exemples très utiles que vous pouvez utiliser dans votre travail quotidien.

Alors, commençons!

Construction de format de nombre personnalisé Excel

Avant de vous en montrer l'incroyable, laissez-moi vous expliquer brièvement comment fonctionne la mise en forme personnalisée des nombres dans Excel.

De par sa conception, Excel peut accepter les quatre types de données suivants dans une cellule :

• Un nombre positif
• un nombre négatif
• 0
• Chaînes de texte (cela peut inclure des chaînes de texte pur ainsi que des chaînes alphanumériques)

Un autre type de données qu'Excel peut accepter sont les dates, mais puisque toutes les valeurs de date et d'heure sont stockées sous forme de nombres dans Excel, elles seraient couvertes dans le cadre du nombre positif.

Pour n'importe quelle cellule d'une feuille de calcul dans Excel, vous pouvez spécifier comment chacun de ces types de données doit être affiché dans la cellule.

Et ci-dessous se trouve la syntaxe dans laquelle vous pouvez spécifier ce format.

;;;

Notez que tous ces éléments sont séparés par des points-virgules (;).

Tout ce que vous entrez dans une cellule d'Excel appartiendrait à l'une de ces quatre catégories et, par conséquent, un format personnalisé peut être spécifié.

Si vous mentionnez seulement :

• Un format: Il est appliqué aux quatre sections. Par exemple, si vous écrivez simplement Général, il sera appliqué aux quatre sections.
• Deux formats: Le premier est appliqué aux nombres positifs et aux zéros, et le second est appliqué aux nombres négatifs. Le format de texte par défaut devient Général.
• Trois formats: le premier est appliqué aux nombres positifs, le second est appliqué aux nombres négatifs, le troisième est appliqué à zéro et le texte disparaît car rien n'est spécifié pour le texte.

Si vous souhaitez en savoir plus sur la mise en forme personnalisée des nombres, je vous recommande vivement la section Aide Office.

Comment cela fonctionne et où entrer ces formats sont abordés plus loin dans ce didacticiel. Pour l'instant, gardez à l'esprit qu'il s'agit de la syntaxe du format de n'importe quelle cellule

Comment fonctionne le format de nombre personnalisé dans Excel

Ensuite, vous ouvrez un nouveau classeur Excel. Par défaut, toutes les cellules de toutes les feuilles de calcul de ce classeur ont le format général où :

• Nombres positifs sont affichés tels quels (alignés à gauche)
• Nombres négatifs sont affichés avec un signe moins (et alignés à gauche)
• Zéro est affiché comme 0 (et aligné à gauche)
• Texte est affiché tel quel (aligné à droite)

Et si vous entrez une date, elle est affichée en fonction de votre paramètre régional (au format JJ-MM-AAAA ou MM-JJ-AAAA).

Ainsi, par défaut, le formatage des nombres de la cellule est :

Général;Général;Général;Général

Où chaque type de données est formaté en général (et suivez les règles que j'ai mentionnées ci-dessus, selon qu'il s'agit d'un nombre positif, négatif, 0 ou texte)

Maintenant, le paramètre par défaut est correct, mais comment changer le format. Par exemple, si je veux que mes nombres négatifs s'affichent en rouge ou que mes dates s'affichent dans un format spécifique, comment dois-je procéder.

Utilisation de la liste déroulante Format dans le ruban

Vous pouvez trouver certains des formats de nombres couramment utilisés dans l'onglet Accueil du groupe Nombre.

C'est une liste déroulante qui affiche de nombreux formats couramment utilisés que vous pouvez appliquer à une cellule en sélectionnant simplement la cellule, puis en sélectionnant le format dans cette liste.

Par exemple, si vous souhaitez convertir des nombres en pourcentages ou des dates converties au format de date courte ou au format de date long, vous pouvez facilement le faire en utilisant les options de la liste déroulante.

Utilisation de la boîte de dialogue Formater les cellules

Boîtes de dialogue de mise en forme des cellules dans lesquelles vous bénéficiez d'une flexibilité maximale lorsqu'il s'agit d'appliquer des formats à une cellule dans Excel.

Il contient déjà de nombreux formats prédéfinis que vous pouvez utiliser, ou si vous souhaitez créer votre propre format de nombre personnalisé, vous pouvez également le faire.

Voici comment ouvrir la boîte de dialogue Format de cellule;

1. Cliquez sur l'onglet Accueil
2. Dans le groupe Numéro, cliquez sur l'icône du lanceur de boîte de dialogue (la petite flèche inclinée en bas à droite du groupe).

Cela ouvrira la boîte de dialogue Formater les cellules. À l'intérieur, vous trouverez tous les formats dans l'onglet Nombre (avec la possibilité de créer des formats de cellule personnalisés)

Vous pouvez également utiliser le raccourci clavier suivant pour ouvrir la boîte de dialogue de formatage des cellules :

CONTROL + 1 (toutes les touches Control puis appuyez sur la touche 1).

Si vous utilisez Mac, vous pouvez utiliser CMD + 1

Voyons maintenant quelques exemples pratiques dans lesquels nous allons créer nos propres formats de nombres personnalisés.

Exemples d'utilisation de la mise en forme de nombres personnalisés dans Excel

Voyons maintenant quelques exemples pratiques dans lesquels nous allons créer nos propres formats de nombres personnalisés.

Masquer une valeur de cellule (ou Masquer toutes les valeurs d'une plage de cellules)

Parfois, vous devrez peut-être masquer le contenu de la cellule (sans masquer la ligne ou la colonne contenant la cellule).

Un cas pratique de ceci est lorsque vous créez un tableau de bord Excel et qu'il y a quelques valeurs que vous utilisez dans le tableau de bord mais que vous ne voulez pas que les utilisateurs le voient. vous pouvez donc simplement masquer ces valeurs

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez masquer toutes les valeurs numériques :

Voici comment procéder à l'aide d'un formatage personnalisé dans Excel :

1. Sélectionnez la cellule ou la plage de cellules dont vous souhaitez masquer le contenu de la cellule
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

   ;;;

Comment cela marche-t-il?

Le format de quatre types différents de types de données est divisé par des points-virgules au format suivant :

;;;

Dans cette technique, nous avons supprimé le format et spécifié uniquement le point-virgule, ce qui indique que nous ne voulons pas de formatage pour les cellules. Ceci est une indication pour Excel de masquer tout contenu présent dans la cellule.

Notez que même si cela masque le contenu de la cellule, il est toujours là. Ainsi, si vous avez masqué des nombres, vous pouvez toujours les utiliser dans les calculs (ce qui en fait une excellente astuce pour les tableaux de bord et les rapports Excel).

Afficher les nombres négatifs en couleur rouge

Par défaut, les nombres négatifs s'affichent avec un signe moins (ou entre parenthèses dans certains paramètres régionaux).

Vous pouvez utiliser un formatage numérique personnalisé pour modifier la couleur des nombres négatifs et les faire apparaître en rouge.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez mettre en évidence toutes les valeurs négatives en rouge.

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez la plage de cellules où vous souhaitez afficher les nombres négatifs en rouge
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

Général;[Rouge]-Général

Le format ci-dessus fera apparaître vos cellules avec des valeurs négatives en rouge.

Comment cela marche-t-il?

Lorsque vous spécifiez uniquement deux formats comme format de nombre personnalisé, le premier est pris en compte pour les nombres positifs et 0, et le second est pris en compte pour les nombres négatifs (la valeur suivante par défaut devient le format général).

Dans le format ci-dessus que nous avons utilisé, nous avons spécifié la couleur que nous voulons que ce format prenne entre crochets.

Ainsi, bien que les nombres négatifs prennent le format général, ils sont affichés en rouge avec un signe moins.

Si vous souhaitez que les nombres négatifs s'arrêtent en rouge entre parenthèses (au lieu du signe moins), vous pouvez utiliser le format ci-dessous :

Général;[Rouge](Général)

Ajouter du texte à Numbers (tels que Millions/Billions)

Une chose étonnante à propos de la mise en forme personnalisée des nombres est que vous pouvez ajouter un préfixe ou un suffixe à un nombre, tout en conservant le nombre d'origine dans la cellule.

Par exemple, je peux faire apparaître « 100 » comme « 100 millions de dollars », tout en ayant la valeur d'origine dans la cellule afin que je puisse l'utiliser dans les calculs.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez afficher ces nombres avec un signe dollar et le mot million devant.

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez le jeu de données où vous souhaitez ajouter le texte aux nombres
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

$Général "Millions"

Notez que ce format de nombre personnalisé n'affectera que les nombres. Si vous saisissez du texte dans la même cellule, il apparaîtra tel quel.

Comment cela marche-t-il?

Dans cet exemple, au format général, j'ai ajouté le mot million entre guillemets. tout ce que j'ajoute entre guillemets doubles au format est affiché tel quel dans la cellule.

J'ai également ajouté le signe dollar avant le format général afin que tous mes nombres affichent également le signe dollar avant le nombre.

Remarque : si vous vous demandez pourquoi le signe dollar n'est pas entre guillemets doubles alors que le mot Million est entre guillemets doubles, c'est parce qu'Excel reconnaît le dollar comme un symbole souvent utilisé dans la mise en forme personnalisée des nombres et ne vous oblige pas pour y ajouter des guillemets doubles. Vous pouvez continuer et ajouter des guillemets doubles au signe dollar, mais lorsque vous fermez la boîte de dialogue de formatage des cellules et l'ouvrez à nouveau, vous remarquerez qu'Excel l'a supprimé

Chiffres et texte de déguisement

Avec la mise en forme des nombres personnalisée, vous pouvez évaluer un maximum de deux conditions et, en fonction de ces conditions, vous pouvez appliquer une mise en forme à la cellule.

Par exemple, supposons que vous ayez une liste de scores pour certains étudiants, vous souhaitez afficher le texte Réussite ou Échec en fonction du score (où tout score inférieur à 35 doit afficher « Échec », sinon il doit afficher « Réussite ».

Vous trouverez ci-dessous les étapes pour afficher les scores en tant que réussite ou échec au lieu de la valeur à l'aide d'un format numérique personnalisé :

1. Sélectionnez l'ensemble de données où vous avez les scores
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

   [<35]"Échec";"Réussite"

Vous trouverez ci-dessous à quoi ressembleraient vos données après avoir appliqué le format ci-dessus.

Notez que cela ne change pas la valeur dans les cellules. La cellule continue d'avoir les scores en valeurs numériques tout en affichant « Réussite » ou « Échec » en fonction de la valeur de la cellule.

Comment cela marche-t-il?

Dans cet exemple, la condition doit être spécifiée entre crochets. la mise en forme personnalisée des nombres évalue ensuite la valeur dans la cellule et applique le format en conséquence.

Ainsi, si la valeur dans la cellule est inférieure à 35, le nombre est masqué et le texte « Echec » est affiché à la place, en ajoutant tous les autres cas, « Réussite » est affiché.

De même, si vous souhaitez convertir une plage de cellules contenant des 0 et des 1 en VRAI et FAUX, vous pouvez utiliser le format ci-dessous :

[=0]"FAUX";[=1]"VRAI"

N'oubliez pas que vous ne pouvez utiliser que deux conditions dans le formatage numérique personnalisé. Si vous essayez d'utiliser plus de deux conditions, votre message vous indiquera qu'il ne peut pas accepter ce format.

Masquer le texte mais afficher les chiffres

Parfois, lorsque vous téléchargez vos données à partir de bases de données, car elles sont vides, elles sont remplies de valeurs telles que Non disponible ou NA ou -

Avec la mise en forme personnalisée des nombres, vous pouvez masquer toutes les valeurs de texte tout en conservant les nombres tels quels. cela garantit également que vos données d'origine sont intactes (comme vous ne supprimez pas la valeur du texte, vous la cachez simplement)

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous où vous souhaitez masquer toutes les valeurs de texte :

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez le jeu de données où vous avez les données
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

Général; -Général; Général;

Notez que j'ai utilisé le format général pour les nombres. Vous pouvez utiliser n'importe quel format (comme 0, 0.#, #0.0%). Notez également qu'il y a un signe négatif (-) avant le deuxième format général, car il représente le format des nombres négatifs.

Comment cela marche-t-il?

Dans cet exemple, puisque nous voulions masquer toutes les valeurs de texte, nous n'avons tout simplement pas spécifié le format pour cela.

Et puisque nous avons spécifié le format pour les trois autres types de données (nombre positif, nombre négatif et zéro), Excel a compris que nous avions volontairement laissé le format de la valeur de texte vide et qu'il ne devrait rien afficher si une cellule a un texte chaîne de caractères.

Afficher les nombres en pourcentages (%)

Avec la mise en forme personnalisée des nombres, vous pouvez également modifier les nombres et les faire apparaître en pourcentages.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez convertir ces nombres pour afficher la valeur en pourcentage équivalente :

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez les cellules où vous avez les nombres (que vous souhaitez convertir en pourcentage)
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

0%

Cela changera les nombres en leurs pourcentages. Par exemple, il passe de 0,11 à 11 %.

Si tout ce que vous voulez faire est de convertir un nombre en sa valeur de pourcentage équivalente, une meilleure façon de le faire serait d'utiliser l'option de pourcentage dans la boîte de dialogue de formatage des cellules.

La mise en forme personnalisée peut être utile lorsque vous souhaitez davantage de contrôle sur la manière dont vous souhaitez afficher la valeur en pourcentage.

Par exemple, si vous souhaitez afficher du texte supplémentaire avec le pourcentage, tout ce que vous voulez afficher des pourcentages négatifs en rouge, il est préférable d'utiliser un formatage personnalisé

Afficher les nombres dans une unité différente (millions en milliards ou grammes en kilogrammes)

Si vous travaillez avec de grands nombres, vous souhaiterez peut-être les afficher dans une unité différente. par exemple, si vous disposez des données de ventes de certaines entreprises, vous souhaiterez peut-être afficher les ventes en millions ou en milliards (tout en conservant la valeur de vente d'origine dans les cellules)

Heureusement, vous pouvez facilement le faire avec un formatage numérique personnalisé.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez afficher ces valeurs de vente en millions.

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez les cellules où vous avez les numéros
2. Ouvrez la boîte de dialogue Format de cellule (raccourci clavier Ctrl + 1)
3. Dans l'onglet Numéro, cliquez sur Personnaliser
4. Saisissez le format suivant dans le champ Type :

0.0,,

Ce qui précède vous donnerait le résultat comme indiqué ci-dessous (valeurs en millions dans un chiffre après la virgule) :

Comment cela marche-t-il?

Dans le format ci-dessus, un zéro avant la virgule indique qu'Excel doit afficher le nombre entier (exactement tel qu'il est dans la cellule) et un 0 après la virgule indique qu'il ne doit afficher qu'un seul chiffre significatif après la virgule.

Donc 0.0 serait toujours un nombre avec un chiffre après la virgule.

Lorsque vous ajoutez une virgule après le 0,0, les trois derniers chiffres du nombre seront supprimés. Par exemple, 123456 deviendrait 123,4 (les trois derniers chiffres sont supprimés, et comme il doit afficher 1 chiffre après la virgule, il affiche 4)

Si vous y réfléchissez, cela revient à diviser le nombre par 1000. Donc, si vous voulez afficher un nombre dans l'unité "millions", vous devez ajouter 2 virgules, afin de supprimer les 6 derniers chiffres.

Et si vous souhaitez également afficher le mot million ou l'alphabet M après le nombre, utilisez le format ci-dessous

0.0,, "M"