Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Excel has an entire language for displaying values just the way you need them. Don't see your format in the list? Let's look at how to roll your own!
There are no related exercise files for this lesson.
Understanding Custom Number Formats
Custom number formatting syntax.
When to use
Understanding the encoding of a custom number format.
Global syntax of custom number formats
- A custom number can hold up to four different formats, separated by semi-colons
- The general setup is: positive format; negative format; zero format; text format
- You may provide between 1 and 4 formats, but they always read from the left side
- If a value doesn’t meet a category, the program will evaluate it against the next format
- If no format is found that supports the data, it will default to the first format
- 0 returns a number (if there is one to fill the space) or a 0
- # returns a number (if there is one to fill the space) and is suppressed if not
- , separates thousands and millions with commas (if the number goes that high)
- “ “ returns anything between the quotes as text
- ? pads from the right to align decimals
Reserved Date Characters
- d will return the day
- m will return the month
- y will return the year
- h will return the hour
- m will return the minute
- s will return the second
- AM/PM will convert to a 12 hour clock from a 24 hour clock
- single character versions (d, m, y) will return the numeric value, with leading zeros suppressed
- dual character versions (dd, mm, yy) will return the numeric value, forcing leading zeros
- triple character versions (ddd, mmm) will return 3 digit abbreviations of day or month names
- quad character versions (dddd, mmmm) will return full day or month names
Hints & tips
- If you want to use a reserved character (like m) in a number format, wrap it in quotes
Lesson notes are only available for subscribers.