The Custom Number Formatting feature in Excel is all about making data appear in a specific way.
Most users are familiar with formatting presets like the “Long Date” style, the “Currency”, style, and the “Text” style just to name a few.
But what many users are not aware of is that you can supplement existing data with additional text and numbers.
Imagine a spreadsheet where the users enter a mileage value and the text “miles” is displayed next to the number in the same cell.
Using our previous example’s data, we can select the data and press CTRL-1 to open the Number Formatting dialog box.
Selecting the category CUSTOM, we can define the formatting of our choosing.
If we want to place the letters “ID “ (with a space at the end) we would enter the text in double-quotes and follow the text with a “#” character (no quotes).
If you want to implement thousands separators or decimal points for fractions, you can use of codesets listed in the same window.
Clicking OK reveals the newly formatted data.
The great advantage to Custom Number Formatting is that the numbers remain numbers and can be utilized in calculations. Looking back at the mileage example, it is still possible to calculate the total and average miles of the set.
If you need to remove the custom formatting from the data, you can set the cell’s style to GENERAL.
“But what about formatting a text entry?”
We can see that by placing the text “Tom” in a previously formatted cell, the custom formatting does not carry over.
This is because we defined a custom formatting rule for the numbers (more specifically, the positive numbers) but not for the text.
We can update the Custom Number Formatting to have a rule for the numbers as well as the text.
“ID ”#;;;“ID ”
The three semicolons are there to act as placeholders as we are not defining formatting for negative values or zeroes.
NOTE: For a detailed explanation of the Custom Number Formatting codes along with practical examples, check out the link below.