5 astuces de format personnalisé Excel dont vous ne saviez pas que vous aviez besoin

Les formats numériques dans Microsoft Excel définissent comment les données sont affichées dans votre feuille de calcul sans modifier les valeurs sous-jacentes. En plus d'utiliser des formats de nombres pour présenter les données en tant que dates, heures, pourcentages et devises, vous pouvez créer un format de nombre plus spécifique à l'aide de la mise en forme des numéros personnalisés.

Avant de vous montrer quelques exemples, voici comment fonctionnent les formats de numéro personnalisés.

Dans Excel, chaque cellule a son propre format de numéro, que vous pouvez consulter en sélectionnant une cellule et en regardant le groupe de numéros dans l'onglet Home sur le ruban.

L'une des options de format que vous pouvez appliquer lorsque vous cliquez sur la flèche vers le bas est «personnalisée». Les formats de numéro personnalisés sont définis dans le champ de type dans la section personnalisée de la boîte de dialogue des cellules de format, que vous pouvez lancer en appuyant sur Ctrl + 1.

Le code que vous devez insérer ici suit une commande stricte:

POSITIVE;NEGATIVE;ZERO;TEXT

Ainsi, le premier argument dicte comment les nombres positifs sont formatés, la seconde comment les nombres négatifs sont formatés, le troisième comment les zéros sont formatés et le quatrième comment le texte est formaté. Notez comment chaque type de valeur est séparé par un point-virgule.

Maintenant que vous savez comment fonctionne le format de numéro personnalisé, voici quelques choses intéressantes que vous pouvez faire avec.

1

Masquer les valeurs des cellules

Si vous souhaitez masquer le contenu d'une cellule tout en préservant sa valeur, la meilleure façon est d'utiliser le formatage des numéros personnalisés.

Par exemple, cette carte thermique illustre les positions à partir desquelles une équipe a marqué les buts de cette saison. Les différentes tons de couleur – créées par des échelles de couleur de formatage conditionnel – rendent ce clair sans avoir besoin d'afficher les nombres correspondants.

De même, les scores de ce tableau sont calculés à l'aide de nombres qui ne détiennent aucune valeur numérique, et les barres de données de mise en forme conditionnelle racontent toute l'histoire. Ainsi, les données seraient moins déroutantes si les chiffres étaient complètement cachés.

Ici, lorsque j'imprime cette fiche d'information des employés, je souhaite cacher le salaire sans supprimer le chiffre.

Si vous êtes dans une position similaire, sélectionnez d'abord les cellules dont vous souhaitez masquer les valeurs, puis cliquez sur le lanceur de boîte de dialogue « Number Format » dans le coin inférieur droit du groupe numérique dans l'onglet Home sur le ruban Excel. Alternativement, appuyez sur Ctrl + 1.

À ce stade, n'oubliez pas que le code que vous entrez dans le champ de type de la catégorie personnalisée suit cet ordre:

POSITIVE;NEGATIVE;ZERO;TEXT

Puisque tu veux tous Les données dans les cellules sélectionnées pour être cachées, vous n'avez pas besoin de saisir des règles de formatage pour l'un des quatre types de valeurs – juste les trois semi-virgules qui les séparent:

;;;

Lorsque vous cliquez sur « OK », les valeurs dans les cellules sélectionnées disparaîtront comme par magie, même si les données sont toujours là.

Étant donné que les données sont toujours attribuées à cette cellule, même si elles sont cachées, vous pouvez toujours les référencer dans des formules comme vous le feriez lorsque les données sont visibles.

Voici comment la carte thermique que je vous ai montrée ressemble plus tôt avec les chiffres cachés.

2

Remplacez les zéros par un tableau de bord

Imaginez que vous avez créé cette feuille de calcul Excel qui suit le nombre d'apparitions que chaque joueur a faite cette saison.

Cependant, vous n'êtes pas satisfait de l'apparence de la table en désordre, donc une chose que vous voulez faire pour résoudre ce problème est de transformer tous les zéros en tirets.

Tout d'abord, sélectionnez toutes les cellules contenant des valeurs numériques et appuyez sur Ctrl + 1 pour lancer la boîte de dialogue des cellules du format. Ensuite, dans le champ de type de la catégorie personnalisée, Type:

#;;"-";

où

• Le symbole de hachage (#) indique à Excel que vous voulez que des valeurs positives soient affichées sous forme de nombres,

• L'argument pour les valeurs négatifs est laissé de côté, car il n'y en aura jamais dans cet ensemble de données,

• « – » (un tiret à l'intérieur des guillemets) dit à Excel de transformer les zéros en tirets, et

• L'argument texte est omis, car il n'y a pas de texte dans les cellules sélectionnées.

Maintenant, les zéros sont remplacés par des tirets, ce qui rend le tableau plus cher sans donner l'impression que vous avez oublié de peupler ces cellules.

De plus, toutes les formules qui font référence aux cellules affectées fonctionnent toujours comme prévu.

3

Transformez les valeurs en texte en fonction d'une condition

Une autre façon d'utiliser le formatage des numéros personnalisés dans Excel consiste à transformer les nombres en texte représentatif en fonction des conditions que vous définissez.

Disons que vous souhaitez transformer les scores globaux des élèves dans la colonne des résultats de ce tableau en le mot « passer » si les valeurs sont 165 ou plus, ou le mot « échouer » si les valeurs sont inférieures à 165.

Tout d'abord, sélectionnez les cellules de la colonne que vous souhaitez formater et appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue du format de numéro.

Avant de taper le code pertinent dans le champ de type de la catégorie personnalisée, gardez à l'esprit que les conditions doivent être placées entre crochets. Si vous ne le faites pas, les caractères conditionnels que vous insérez dans le résultat de la cellule. Donc, type:

(>=165)"Pass";"Fail"

Dans ces cas, il vous suffit de saisir deux arguments, séparés par un point-virgule. Le premier est le formatage des valeurs qui répondent à la condition, et la seconde est le formatage de ceux qui ne le font pas. De plus, vous n'avez pas besoin de répéter la condition dans le deuxième argument.

Voici ce que vous obtenez lorsque vous cliquez sur « OK ».

4

Nombres de couleurs basés sur leurs valeurs

Le format de numéro personnalisé dans Excel vous permet de définir la couleur du texte dans les cellules sélectionnées en fonction de leurs valeurs. De plus, pour les huit couleurs principales (noir, blanc, rouge, vert, bleu, jaune, magenta et cyan), vous n'avez pas à vous souvenir de code complexe – vous pouvez simplement taper le nom de la couleur.

Il n'y a que deux choses à noter lors de l'utilisation du formatage des numéros personnalisés à cet effet – les couleurs doivent être le premier argument pour chaque type de données, et ils doivent être placés entre crochets.

Cet exemple de feuille de calcul montre si les employés ont atteint l'objectif de 400 ventes. Vous voulez que tous les nombres positifs de la colonne C soient verts, les nombres négatifs rouges et Zeros Blue.

Sélectionnez toutes les cellules de la colonne du résultat, appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue des cellules du format et dans le champ de type de la catégorie personnalisée, type:

(Green)#;(Red)#;(Blue)0;

Étant donné que les valeurs négatives seront formatées en rouge, je n'ai pas ajouté le symbole moins à l'argument négatif.

Lorsque vous cliquez sur « OK », le formatage est appliqué aux cellules sélectionnées.

Si vous souhaitez accéder à plus de couleurs que les huit standard, au lieu de taper le nom de la couleur, saisissez le code couleur en fonction de ce graphique:

5

Répéter les personnages de leaders

Une astuce de format de numéro personnalisé final dans Excel consiste à remplir une cellule avec un certain caractère avant ou après la valeur cellulaire.

Que vous souhaitiez insérer une ligne de signature, une ligne de date ou une ligne pointillée qui relie un numéro de chapitre à un numéro de page dans une table des matières, le formatage du numéro personnalisé vous a le dos!

Dans cet exemple, disons que vous souhaitez répéter un soulignement après le mot « signature » pour créer une ligne de signature.

Pour ce faire, sélectionnez la cellule A1 et appuyez sur Ctrl + 1 pour lancer la boîte de dialogue des cellules du format. Ensuite, dans le champ de type de la catégorie personnalisée, Type:

@*_

où le symbole @ représente la valeur du texte dans la cellule (dans ce cas, « signature »), et l'astérisque dit à Excel de répéter le caractère qui suit (dans ce cas, un soulignement).

Si le personnage de premier plan ou de fuite est avant ou après une valeur numérique (plutôt que le texte), utilisez le signe de hachage (#) au lieu du signe @.

C'est ce que vous obtenez lorsque vous appuyez sur Entrée.

D'un autre côté, vous pouvez utiliser la même méthode pour répéter un caractère leader. Ici, dans la cellule B2, vous voulez une ligne pointillée avant « Page 1. »

Ainsi, cette fois, dans le champ de type de la catégorie personnalisée, Type:

*.@

avec la combinaison d'astérisque placée avant L'espace réservé au texte (@).

Voici le résultat lorsque vous appuyez sur Entrée.

L'avantage de l'utilisation de cette méthode est que si vous modifiez la largeur de la colonne, les instances du caractère répété ajustent pour remplir la cellule – sans cesse de diriger ou de suivre.

---

Si vous souhaitez aller plus loin, vous pouvez utiliser la mise en forme du numéro personnalisé pour afficher un symbole, comme ▲ ou ▼ – avec ou au lieu d'une valeur numérique. De plus, vous pouvez colorer ces symboles afin que vos données soient encore plus claires.