Introduction
Number formats control how numbers are displayed in Excel.
The key benefit of number formats is that they change how a number looks without changing any data.
They are a great way to save time in Excel because they perform a huge amount of formatting automatically.
As a bonus, they make worksheets look more consistent and professional.
Video:What is a number format
What can you do with custom number formats?
Where can you use custom number formats?
Many areas in Excel support number formats.
What is a number format?
A number format is a special code to control how a value is displayed in Excel.
Where can you find number formats?
On the home tab of the ribbon, you’ll find a menu of built-in number formats.
General is default
By default, cells start with the General format applied.
The display of numbers using the General number format is somewhat “fluid”.
Note: As you enter data, Excel will sometimes change number formats automatically.
For example, if you enter a valid date, Excel will change to “Date” format.
If you enter a percentage like 5%, Excel will change to Percentage, and so on.
Excel will show a small preview of the code applied to the first selected value above the input area.
Note: Custom number formats live in a workbook, not in Excel generally.
you’re able to use the Delete button to delete custom formats you no longer need.
Warning: there is no “undo” after deleting a custom number format!
Structure and Reference
Excel custom number formats have a specific structure.
Escaping characters
Some characters won’t work correctly in a custom number format without being escaped.
The escape character in custom number formats is the backslash ().
Pound sign (#)is a placeholder for optional digits.
When a number has fewer digits than # symbols in the format, nothing will be displayed.
For example, the custom format #.## will display 1.15 as 1.15 and 1.1 as 1.1.
)is used to align digits.
)is a placeholder for the decimal point in a number.
Comma (,)is a placeholder for the thousands separators in the number being displayed.
It can be used to define the behavior of digits in relation to the thousands or millions digits.
Asterisk (*)is used to repeat characters.
The character immediately following an asterisk will be repeated to fill the remaining space in a cell.
Underscore (_)is used to add space in a number format.
The character immediately following an underscore character controls how much space to add.
At (@)- placeholder for text.
This is a visual effect only; actual values are not modified.
Number formats for ELAPSED TIME
Elapsed time is a special case and needs special handling.
By using square brackets, Excel provides a special way to display elapsed hours, minutes, and seconds.
Number formats for COLORS
Excel provides basic support for colors in custom number formats.
Color names must appear in brackets.
The first image shows all 56 colors on a standard white background.
The second image shows the same colors on a gray background.
Note the first 8 colors shown correspond to the named color list above.
you could howeveruse a formulato display feet together with inches.
If you have data that contains phone numbers with hard-coded punctuation (parentheses, hyphens, etc.)
you will need toclean the telephone numbers firstso that they only contain numbers.
Hide all content
you might use a custom number format to hide all content in a cell.
