Excel reference

Excel custom number format codes, explained

Q: How do I create a custom number format in Excel?

Select the cells, press Ctrl+1, choose Custom on the Number tab, and type the code in the Type box. The Sample line shows the result before you click OK.

Q: How do I show numbers in thousands or millions in Excel?

End the code with one comma per thousand: #,##0,"K" for thousands, 0.0,,"M" for millions. The cell keeps the full number, so totals stay exact.

Q: How do I show negative numbers in red with brackets?

Use #,##0.00;[Red](#,##0.00). The part after the semicolon is used for negatives, and its brackets replace the minus sign.

Q: How do I hide zeros in Excel?

Use a format with an empty third section, such as 0;-0;;@. The zeros are still there for formulas. File > Options > Advanced also has a setting to hide zeros on a whole sheet.

Quick answerA custom number format has up to four sections separated by semicolons: positive;negative;zero;text. In each one, 0 shows a digit or a zero, # shows a digit only if needed, a comma groups thousands or (at the end) divides by 1,000, and text goes in "quotes". Apply one with Ctrl+1 > Number > Custom, or use the same code in TEXT(). Formats change only how a value looks; the value underneath stays the same.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Item	Amount
2	Revenue	1234567
3	Refund	-1234.5
4	Adjustments	0
5	Order date	Nov 5, 2026
6	Hours logged	1.25

Download this sheet as an Excel practice file (.xlsx)

Where custom formats live

Select the cells.
Press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
On the Number tab, choose Custom at the bottom of the list.
Type the code in the Type box, check the Sample above it, and click OK.

The codes are the same in the TEXT function, which is how every example on this page is shown: TEXT(value,"code") returns what the cell would display. The difference is that a format keeps the number a number, while TEXT turns it into text that SUM ignores.

The four sections: positive;negative;zero;text

With one section, the code is used for every number, and negatives get a minus sign. With two, the second is for negatives, and its code replaces the minus sign, which is how brackets work. A third section is for zero, and a fourth for text typed into the cell. Leave a section empty to hide that kind of value.

=TEXT(B3,"#,##0.00;(#,##0.00)")

Result: (1,234.50)

The refund uses the second section, so it shows in brackets with no minus sign: (1,234.50).

=TEXT(B4,"#,##0;-#,##0;""-""")

Result: -

Zero uses the third section, a dash in quotes. Inside a formula each quote in the code is doubled.

=TEXT(B4,"0;-0;;@")

Result: "" (empty text, so the cell looks blank)

An empty third section hides zeros: the cell looks blank but still holds 0.

Digit placeholders: 0 vs # vs ?

0 always shows a digit, padding with zeros: 00000 shows 742 as 00742. # shows a digit only when there is one, so #.00 shows 0.5 as .50 with no leading zero. ? works like # but leaves a space where a digit is missing, which lines decimal points up in a column. Extra decimals are rounded, never cut off.

=TEXT(742,"00000")

Result: 00742

Five zeros force five digits: 00742. Use it for ZIP codes and fixed-length IDs.

=TEXT(2,"0.##")

Result: 2.

A trap: with no decimals to show, # leaves the point behind, so 2 shows as 2. with a dot.

Thousands separators and scaling commas

A comma between digit placeholders, as in #,##0, adds thousands separators. A comma after the last placeholder divides the display by 1,000: one comma for thousands, two for millions, three for billions. Combined with a letter in quotes it gives dashboard-style K and M numbers that still add up, because the cell keeps the full value.

=TEXT(B2,"#,##0,""K""")

Result: 1,235K

1,234,567 shown in thousands: 1,235K.

=TEXT(B2,"$#,##0.0,,""M""")

Result: $1.2M

Two trailing commas divide by a million: $1.2M.

Colors and conditions

A color name in square brackets at the start of a section colors that kind of number: [Red], [Blue], [Green], [Magenta], [Cyan], [Yellow], [White], [Black], or [Color1] to [Color56] from the workbook palette. TEXT ignores colors, since text has no color, so in the table below the color is only described.

A condition in square brackets, such as [>=1000], replaces the usual positive and negative rules. You can use up to two conditions plus a final section for everything else, so [>=1000000]0.0,,"M";[>=1000]0.0,"K";0 shows any number as M, K or plain. For more than that, use conditional formatting with a number format, or IFS with TEXT in a formula.

Text in formats: quotes, backslash and @

Put words in double quotes: 0 "pcs" shows 12 as 12 pcs and the cell still holds the number 12, so totals keep working. A single character can be escaped with a backslash instead, as in 0\x for 4x. A few characters need no quotes at all, such as $ - + / ( ) : and the space.

@ stands for text typed into the cell. In the fourth section, ;;;"Note: "@ puts a label in front of any text entry. A format can add text around a value, but it cannot change the value itself: to calculate with text, use formulas.

=TEXT(12,"0 ""pcs""")

Result: 12 pcs

12 pcs. In the Format Cells box you type 0 "pcs" with the quotes as they are; inside TEXT each one is doubled.

Date codes: d, m and y

d is the day (5), dd the day with a leading zero (05), ddd the short weekday (Thu) and dddd the full weekday (Thursday). m, mm, mmm, mmmm and mmmmm give 11, 11, Nov, November and N. yy is 26 and yyyy is 2026.

m is also minutes. Excel reads m or mm as minutes when it comes right after h or hh, or right before ss; otherwise it is the month. Date codes are language-specific: a workbook made in a German or French version of Excel may use different letters, such as JJJJ for the year.

=TEXT(B5,"dddd, mmmm d, yyyy")

Result: Thursday, November 5, 2026

The order date written out in full: Thursday, November 5, 2026.

=TEXT(B5,"ddd")

Result: Thu

Just the short weekday, Thu. Handy for a weekday column next to a date column.

Time codes: h, mm, ss, AM/PM and [h]

h and hh are hours, mm minutes and ss seconds. Add AM/PM (or am/pm, A/P) for a 12-hour clock; without it Excel uses 24 hours.

Square brackets show elapsed time instead of clock time: [h]:mm keeps counting past 24 hours, [mm]:ss past 60 minutes, and [ss] gives total seconds. Use them for timesheets and durations. Without the brackets, a 30-hour total shows as 6:00, because the clock has rolled over to the next day.

=TEXT(B6,"[h]:mm")

Result: 30:00

1.25 days logged shows as 30:00 hours.

=TEXT(B6,"h:mm")

Result: 6:00

The same value without brackets shows 6:00, the time of day after 30 hours. The classic timesheet mistake.

52 ready-to-copy format codes

Copy a code into Format Cells > Custom. The Shows as column is what Excel displays for the value beside it, produced here with the same code in the engine’s TEXT function; the advanced codes at the end are described in words.

Numbers

Code	Value	Shows as	Use it for
`0`	1234.5	1235	Whole number, rounded
`0.00`	1234.5	1234.50	Always two decimals
`#,##0`	1234.5	1,235	Thousands separator, no decimals
`#,##0.00`	1234.5	1,234.50	The standard accounting look
`#.00`	0.5	.50	No zero before the point
`0.##`	0.5	0.5	Up to two decimals, only if needed
`0.##`	2	2.	The catch: the point stays when there are no decimals
`#,##0,`	1234567	1,235	In thousands (one trailing comma divides by 1,000)
`#,##0,"K"`	1234567	1,235K	Thousands with a K
`0.0,,"M"`	1234567	1.2M	Millions with an M
`$#,##0.0,,"M"`	1234567	$1.2M	Money in millions
`0.0,,,"B"`	1234567890	1.2B	Billions with a B

Negatives and zero

Code	Value	Shows as	Use it for
`#,##0.00;(#,##0.00)`	-1234.5	(1,234.50)	Negatives in brackets
`#,##0.00;[Red](#,##0.00)`	-1234.5	(1,234.50)	Negatives in red brackets (the color shows in the cell, not in TEXT)
`#,##0;-#,##0;"-"`	0	-	A dash instead of 0
`0;-0;;@`	0	(blank)	Hide zeros (empty third section)
`;;;`	1234.5	(blank)	Hide everything: the value is still there

Percent and change

Code	Value	Shows as	Use it for
`0%`	0.256	26%	Whole percent (multiplies by 100)
`0.0%`	0.256	25.6%	Percent with one decimal
`+0.0%;-0.0%;0.0%`	0.125	+12.5%	Change with a plus sign
`+0.0%;-0.0%;0.0%`	-0.04	-4.0%	The same code on a fall
`▲0.0%;▼0.0%`	-0.04	▼4.0%	Arrows instead of signs

Currency and units

Code	Value	Shows as	Use it for
`$#,##0.00`	1234.5	$1,234.50	Dollars and cents
`$#,##0`	1234.5	$1,235	Whole dollars
`$#,##0.00;-$#,##0.00`	-1234.5	-$1,234.50	Minus before the dollar sign
`#,##0.00 "USD"`	1234.5	1,234.50 USD	Currency code after the number
`0 "pcs"`	12	12 pcs	A unit label that keeps the number a number
`0.0 "kg"`	12.5	12.5 kg	Unit with a decimal

Codes and IDs

Code	Value	Shows as	Use it for
`00000`	742	00742	Fixed length with leading zeros (ZIP codes)
`"ID-"0000`	42	ID-0042	Prefix and padding
`(000) 000-0000`	5551234567	(555) 123-4567	US phone number
`000-000-000`	123456789	123-456-789	Grouped account number

Dates

Code	Value	Shows as	Use it for
`m/d/yyyy`	Nov 5, 2026	11/5/2026	US short date
`mm/dd/yyyy`	Nov 5, 2026	11/05/2026	US date with leading zeros
`dd/mm/yyyy`	Nov 5, 2026	05/11/2026	Day first (UK, Europe, Australia)
`yyyy-mm-dd`	Nov 5, 2026	2026-11-05	ISO date, sorts correctly as text
`d-mmm-yy`	Nov 5, 2026	5-Nov-26	Compact date
`mmm d, yyyy`	Nov 5, 2026	Nov 5, 2026	Readable short date
`dddd, mmmm d, yyyy`	Nov 5, 2026	Thursday, November 5, 2026	Full date with weekday
`ddd`	Nov 5, 2026	Thu	Short weekday
`mmm yyyy`	Nov 5, 2026	Nov 2026	Month and year
`mmmmm`	Nov 5, 2026	N	Month initial (chart axes)
`"Week of "mmm d`	Nov 5, 2026	Week of Nov 5	Text and a date together

Times

Code	Value	Shows as	Use it for
`h:mm AM/PM`	Nov 5, 2026 6:30:15 PM	6:30 PM	12-hour clock
`h:mm:ss AM/PM`	Nov 5, 2026 6:30:15 PM	6:30:15 PM	12-hour clock with seconds
`hh:mm`	Nov 5, 2026 6:30:15 PM	18:30	24-hour clock
`yyyy-mm-dd hh:mm`	Nov 5, 2026 6:30:15 PM	2026-11-05 18:30	Timestamp
`ddd h AM/PM`	Nov 5, 2026 6:30:15 PM	Thu 6 PM	Weekday and hour
`[h]:mm:ss`	1.25 days + 40 seconds	30:00:40	Total hours past 24 (timesheets)
`h:mm:ss`	1.25 days + 40 seconds	6:00:40	The catch: without [ ] hours restart after 24
`[mm]:ss`	1:04:37 (a lap)	64:37	Total minutes and seconds
`mm:ss`	1:04:37 (a lap)	04:37	The catch: without [ ] the hour is dropped

Advanced (described, not rendered here)

Code	Value	Use it for
`[>=1000000]0.0,,"M";[>=1000]0.0,"K";0`	Shows 2,500,000 as 2.5M, 1,500 as 1.5K and 950 as 950. Conditions in square brackets replace the usual positive;negative split; at most two conditions plus a fallback.	K and M in one code
`[<0]"Overdue";0 "days left"`	Shows a negative number of days as the word Overdue and anything else as, for example, 12 days left.	A label for a condition
`@ "units"`	Shows text typed into the cell followed by " units". The @ stands for the cell’s text and only works in the text section.	Text placeholder
`# ?/?`	Shows 1.75 as 1 3/4. Use # ??/?? for two-digit denominators or # ?/8 to force eighths.	Fractions
`0.00E+00`	Scientific notation: 1234567 shows as 1.23E+06.	Very large or small numbers
`#,##0.00_);(#,##0.00)`	The _) adds a space the width of a bracket, so positive numbers line up with bracketed negatives.	Aligned accounting columns
`0*-`	The * repeats the next character to fill the cell width: 42 followed by dashes to the edge.	Fill the cell
`??0.00`	? reserves a space for a digit, so decimals line up in a column even when the numbers have different widths.	Align decimal points

Common mistakes

Expecting the format to change the value

A cell showing 1.2M still holds 1234567, and one showing 26% still holds 0.256. Round with ROUND if the stored value must change.

Using TEXT in a column you need to add up

TEXT returns text, so SUM skips it. Format the cells instead and keep TEXT for labels and sentences.

Writing mm for minutes on its own

mm alone is the month. Put it after h (h:mm) or before ss (mm:ss), or use [mm] for total minutes.

Single quotes in TEXT

Inside a formula a quote in the code must be doubled: =TEXT(A2,"0 ""pcs"""). In the Format Cells box you type 0 "pcs" as it is.

FAQ

How do I create a custom number format in Excel?

Select the cells, press Ctrl+1, choose Custom on the Number tab, and type the code in the Type box. The Sample line shows the result before you click OK.

How do I show numbers in thousands or millions in Excel?

End the code with one comma per thousand: #,##0,"K" for thousands, 0.0,,"M" for millions. The cell keeps the full number, so totals stay exact.

How do I show negative numbers in red with brackets?

Use #,##0.00;[Red](#,##0.00). The part after the semicolon is used for negatives, and its brackets replace the minus sign.

How do I hide zeros in Excel?

Use a format with an empty third section, such as 0;-0;;@. The zeros are still there for formulas. File > Options > Advanced also has a setting to hide zeros on a whole sheet.

What is the difference between 0 and # in a number format?

0 always shows a digit, adding zeros where needed, so 00000 shows 742 as 00742. # only shows digits that exist, so #.00 shows 0.5 as .50.

How do I show hours over 24 in Excel?

Use [h]:mm. The square brackets show total elapsed hours, so 30 hours shows as 30:00 instead of 6:00.

Functions on this page

Related guides

More Excel reference

Updated November 5, 2026