Formatting reference
Excel custom number formats guide
One set of format codes, two places to use them: inside the TEXT() function, or in Format Cells → Custom. This guide lists every code, what it does, and a working example you can paste straight into a cell.
How to apply a format
The same format code works in two places — inside a formula, or in the Format Cells dialog.
1 In a formula
value — the number, date, or time to formatformat_code — a pattern below, always in quotes
2 From the menu
General
Number
Currency
Date
Custom
Type:
#,##0.00
Paste any code from the tables below — no quotes needed here.
Right-click a cell → Format Cells → Custom, or press Ctrl + 1
Format codes
Grouped by what they format — with a working example for each you can paste straight in.
01
These codes control how digits, commas, and decimals are displayed.Number formatting
| Code | What it does | Example | Result | |
|---|---|---|---|---|
| 1 | 0 | Forces Excel to show a number in this exact spot. If your number is too short, Excel will fill the empty space with a zero. | TEXT(123, "00000") | 00123 |
| 2 | # | Shows a number only if it actually exists. It will not add extra, unnecessary zeros if your number is short. | TEXT(4.5, "#.##") | 4.5 |
| 3 | ? | Acts just like the # symbol, but instead of doing nothing, it adds an invisible space. This is used to make decimals or fractions line up perfectly in a column. | TEXT(4.5, "0.0?") | 4.5 |
| 4 | / | Formats the number as a fraction instead of a decimal. | TEXT(0.5, "# ?/?") | 1/2 |
| 5 | $ | Displays a currency symbol. You can also use €, £, or any other currency symbol. | TEXT(123, "$0") | $123 |
| 6 | . | Determines exactly where the decimal point goes in your number. | TEXT(12, "0.00") | 12.00 |
| 7 | , | Adds a comma to separate thousands, making large numbers easier to read. | TEXT(50000, "#,##0") | 50,000 |
| 8 | ,, | Hides the thousands and hundreds at the end of a very large number, turning millions into a shorter, easier to read number. | TEXT(1500000, "0.0,,") | 1.5 |
| 9 | E+ / E- | Converts massive numbers into scientific notation (e.g., 1.5 x 10^6), which shrinks them down so they fit easily in tight cells. | TEXT(1500000, "0.00E+00") | 1.50E+06 |
| 10 | % | Multiplies your number by 100 and adds a percentage sign to the end. | TEXT(0.25, "0%") | 25% |
| 11 | 0;(0) | Up to 3 sections separated by semicolons set the format for positive; negative; zero values in turn. | TEXT(-5, "0;(0)") | (5) |
02
Dates in Excel are just normal numbers behind the scenes. These codes tell Excel how to display them as days, months, and years.Date formatting
| Code | What it does | Example | Result | |
|---|---|---|---|---|
| 1 | m | Displays the month as a simple number from 1 to 12. | TEXT("1/5/2024", "m") | 1 |
| 2 | mm | Displays the month as a two-digit number. It will add a zero to the front of single-digit months. | TEXT("1/5/2024", "mm") | 01 |
| 3 | mmm | Displays the month as a short three-letter word, such as Jan, Feb, or Mar. | TEXT("1/5/2024", "mmm") | Jan |
| 4 | mmmm | Displays the full name of the month, such as January or February. | TEXT("1/5/2024", "mmmm") | January |
| 5 | mmmmm | Displays only the first letter of the month, such as J for January or F for February. | TEXT("1/5/2024", "mmmmm") | J |
| 6 | d | Displays the day of the month as a simple number from 1 to 31. | TEXT("1/5/2024", "d") | 5 |
| 7 | dd | Displays the day as a two-digit number, adding a zero to the front if necessary. | TEXT("1/5/2024", "dd") | 05 |
| 8 | ddd | Displays the day of the week as a short three-letter word, such as Mon or Tue. | TEXT("1/5/2024", "ddd") | Fri |
| 9 | dddd | Displays the full name of the day of the week, such as Monday or Tuesday. | TEXT("1/5/2024", "dddd") | Friday |
| 10 | yy | Displays the year as a two-digit number, cutting off the century. | TEXT("1/5/2024", "yy") | 24 |
| 11 | yyyy | Displays the full four-digit year. | TEXT("1/5/2024", "yyyy") | 2024 |
| 12 | dd-mmm-yy | Combine codes with any separators you like to build a full custom date. | TEXT("1/5/2024", "dd-mmm-yy") | 05-Jan-24 |
Date and time letters are language-dependent in some locales.
03
Similar to dates, times are just numbers to Excel. Use these codes to pull out hours, minutes, and seconds.Time formatting
| Code | What it does | Example | Result | |
|---|---|---|---|---|
| 1 | h | Displays the hour as a simple number from 0 to 23. | TEXT("8:05 AM", "h") | 8 |
| 2 | hh | Displays the hour as a two-digit number, adding a zero to the front if necessary. | TEXT("8:05 AM", "hh") | 08 |
| 3 | m | Displays the minute as a simple number. (Note: Must be used right after hours or right before seconds, otherwise Excel thinks you mean months.) | TEXT("8:05 AM", "h:m") | 8:5 |
| 4 | mm | Displays the minute as a two-digit number, adding a zero to the front if necessary. | TEXT("8:05 AM", "h:mm") | 8:05 |
| 5 | s | Displays the second as a simple number. | TEXT("8:05:09 AM", "h:mm:s") | 8:05:9 |
| 6 | ss | Displays the second as a two-digit number, adding a zero to the front if necessary. | TEXT("8:05:09 AM", "h:mm:ss") | 8:05:09 |
| 7 | AM/PM | Forces Excel to use a 12-hour clock and adds the letters AM or PM to the end of the time. | TEXT("14:30", "h:mm AM/PM") | 2:30 PM |
| 8 | [h]:mm | Brackets show elapsed hours past 24 — useful for totalling hours worked. | TEXT(1.5, "[h]:mm") | 36:00 |
04
You can also use custom formatting to add spaces, align your data, or automatically attach words to your text.Text & spacing
| Code | What it does | Example | Result | |
|---|---|---|---|---|
| 1 | @ | Represents the original text you typed into the cell. You can add extra words before or after this symbol to automatically surround your text. | TEXT("Smith", "Mr. @") | Mr. Smith |
| 2 | * | Forces the very next character you type to repeat over and over until it fills the entire width of the cell. | TEXT(123, "*-0") | ------123 |
| 3 | _ | Creates a small blank space that is exactly as wide as the character you type immediately after it. It is usually used to line up positive and negative numbers perfectly. | TEXT(123, "_-0") | 123 |
| 4 | " " | Allows you to attach specific words directly to your number. Anything wrapped in quotation marks will appear exactly as you typed it. (Note: When using the TEXT formula, you must double up the quotes inside.) | TEXT(123, "0 ""Units""") | 123 Units |
05
These special codes change the physical color of the font, or apply a format only when a rule is met. They work in the Format Cells menu only — not inside a TEXT formula.Colors & conditions
| Code | What it does | Example | Result | |
|---|---|---|---|---|
| 1 | [Color] | Changes the color of the text. You must type the name of the color inside square brackets at the very start of the code.Format Cells only | TEXT(123, "[Red]0") | 123 |
| 2 | [Condition] | Tells Excel to apply a specific format only if the number matches a rule you set, such as being greater than or less than a specific amount.Format Cells only | TEXT(150, "[>100]""High"";""Low""") | High |