An Excel number custom format code is a set of characters that controls how a cell’s value looks without changing the value stored in the workbook. In Microsoft Excel for Microsoft 365 on Windows, open Format Cells with Ctrl+1, choose the Number tab, select Custom, and enter or choose a code in the Type box.
What a custom number format does
A custom format changes the display of a number, date, time, or text entry. For example, a cell containing the number 0.25 can display as 25%, and a cell containing 7 can display as 007. In both cases, the stored value remains unchanged: Excel can still use it in calculations as 0.25 or 7.
This distinction is useful when a worksheet needs readable, consistent presentation but its data must remain numeric. A custom format is not a formula, and it does not turn a number into text. It also does not alter the value copied into another program as raw data, although copying and pasting with formatting may carry the appearance along.
- Use a format code to show a consistent number of decimal places.
- Use a format code to show leading zeros in IDs or phone-number-like entries.
- Use a format code to display negative values in parentheses or in a color.
- Use a format code to show dates, elapsed time, percentages, or abbreviated large numbers.
Where to enter a custom format code
On Windows, select the cells, press Ctrl+1, and choose Number > Custom. You can also use Home > Number group dialog launcher, then choose Custom in the Format Cells dialog. Select an existing code to modify it, or type a new one in the Type box; the Sample area previews how the active cell will look. Select OK to apply the format.
On Excel for Mac, select the cells and press Command+1 to open Format Cells, then choose Custom. The dialog layout may differ slightly from Windows. Excel for the web can show many formats applied in desktop Excel, but the ability to create or edit custom codes may be limited. If the Custom option you need is unavailable, open the workbook in the desktop app.
To reuse a code, it is often enough to apply it to another range through the same dialog. Custom formats are stored with the workbook, rather than being a universal setting that automatically follows a user to every new workbook. If a code is important for a team template, save it in the template or share the workbook containing it.
How the four format sections work
A custom number format can contain up to four sections separated by semicolons. The sections apply, in order, to positive numbers, negative numbers, zero values, and text. You do not have to specify all four: a single section applies to all numbers, while two sections generally separate positive and zero values from negative values.
0.00;[Red](0.00);-;@displays positive numbers with two decimals, negative numbers in red parentheses, zero as a dash, and text as entered.0.00;(0.00)displays positive numbers with two decimals and negative numbers in parentheses; zero uses the first section’s format.Generaluses Excel’s general display for the value.
In the first example, the third section replaces the display of numeric zero with a dash; it does not replace zero with a different value. The fourth section uses the at sign, @, as a placeholder for text. A custom format can therefore make a report easier to scan, but it cannot make a blank cell behave like zero or change what a calculation returns.
Sections can also contain conditions, such as [>=1000]0.0,"K";0. This displays values of 1,000 or more in thousands with one decimal place and a K suffix, while other numeric values use the second section. When conditions are used, Excel evaluates the specified conditions rather than relying only on the usual positive/negative order. Conditional formats have syntax limits, so keep the code simple and check its preview with values on both sides of the condition.
Number placeholders and punctuation
The three most useful digit placeholders are 0, #, and ?. They all reserve a position for a digit, but they handle insignificant zeros differently.
0forces a digit to appear. The code000displays 7 as 007.#displays a digit when needed but suppresses extra zeros. The code0.##can show 4.5 as 4.5 rather than 4.50.?suppresses an insignificant zero while reserving its space, which can help align decimal points or fractions.
A period in a number format marks the decimal position, and a comma commonly groups thousands: #,##0 displays 12500 as 12,500 in a locale that uses commas for grouping and a period for decimals. Excel may display separators according to the computer’s regional settings, so a code that appears with different punctuation in another region may still be applying the same kind of format.
A comma placed after the last digit placeholder scales the displayed number by 1,000 for each comma. For example, 0.0, displays 12500 as 12.5. This scaling affects only the display. It is different from using a comma between digit placeholders to group thousands.
Useful codes for common tasks
These examples can be entered in the Type box. The precise currency symbol and punctuation may depend on regional settings.
- Fixed decimals:
0.00displays 8 as 8.00. - Thousands separator:
#,##0displays 12500 as 12,500 in a matching locale. - Percentage:
0.0%displays the stored value 0.256 as 25.6%. The percent sign also scales the displayed value by 100. - Leading zeros:
00000displays 42 as 00042. This is suitable for numeric codes when arithmetic is not needed to interpret the digits. - Negative amounts:
$#,##0.00;[Red]-$#,##0.00displays negative values with a minus sign and red font, subject to Excel’s currency and color handling. - Scientific notation:
0.00E+00displays very large or small numbers in scientific form. - Elapsed hours:
[h]:mm:sscan show a duration longer than 24 hours without wrapping back to zero hours. - Date:
mmm d, yyyycan display a valid Excel date as an abbreviated month, day, and four-digit year.
For a phone number pattern, 000-000-0000 can display a ten-digit numeric value with separators. However, phone numbers and identifiers are usually better stored as text: they are labels, not quantities, and can include country codes, extensions, or leading zeros. A number format can supply leading zeros for display, but it cannot restore digits that were lost before the value was entered.
Dates, times, and special characters
Excel stores dates and times as serial values, so a custom format can change how a valid date or time appears without changing its underlying serial value. For a date, dd means a two-digit day, mmm an abbreviated month name, and yyyy a four-digit year. For times, hh:mm shows hours and minutes. In a format code, m can mean month or minute: near hour or second markers it represents minutes; in a date context it represents months. If an output looks unexpected, inspect the surrounding date and time markers.
Ordinary characters can be included as literals. Put literal text in quotation marks, as in 0.0" kg", or use a backslash before a character to display it literally. A semicolon separates format sections, so it is not simply ordinary punctuation in a code. The underscore character reserves space equal to the width of the character that follows, and the asterisk repeats the following character to fill the cell. These options can help align accounting-style displays, though they are usually unnecessary for everyday tables.
Codes are interpreted in context: a string that looks like a date format does not convert arbitrary text into an Excel date. If a cell contains text such as 20261103, applying a date format may not produce a real date. Convert or enter the data as a valid date first, then choose the display format.
How to choose the right code
Start with the meaning of the data, not just the appearance you want. If a value represents a measurement or amount, retain a numeric value and use a format to control its presentation. If it represents an identifier, consider storing it as text so that Excel does not perform arithmetic on it or discard meaningful leading zeros.
- Decide whether the cell contains a number, a date/time value, or text.
- Choose the required display: decimal places, grouping, units, sign, or date pattern.
- Use
0where a digit must always appear and#where extra zeros should be hidden. - Preview the code with representative values, including zero, a negative number, and a large or small value where relevant.
- Check that the displayed value is not misleading. A rounded display may hide precision still used in calculations.
For example, formatting 12.345 as 0.0 displays 12.3, but the stored value remains 12.345. A sum using that cell still uses the underlying precision unless the workbook’s calculation or formula explicitly rounds it. Do not treat the displayed number as proof that the stored value has been rounded.
Common problems and how to fix them
The format seems to do nothing. Check whether the cell contains text rather than a number. Number formats generally affect numeric values and valid date/time serials, not text that merely resembles them. Also confirm that the selected cells, rather than a different range, received the format.
Leading zeros disappear. A numeric value does not retain zeros at the start, so use a zero-placeholder format such as 00000 for a fixed-width display. If the digits are an identifier that must remain exactly as entered, store them as text instead.
A date displays as a number. The cell may have General or a numeric format applied, or its contents may not be a valid date. Apply a date format to a valid date value; changing the appearance alone cannot reliably convert text into a date.
A time total wraps after 24 hours. Use elapsed-hour brackets, for example [h]:mm, rather than only h:mm. The brackets tell Excel to show accumulated hours rather than a time of day that cycles each day.
The result shows hashes. A row of hash marks often means the column is too narrow to show the formatted value. Widen the column or choose a shorter display. It does not usually mean that the underlying value has been erased.
A code looks different on another computer. Regional settings can affect date order, decimal symbols, thousands separators, and currency display. Confirm both the code and the workbook’s locale context before assuming the data changed.
Frequently Asked Questions
What is the difference between a number format and a formula?
A number format changes the cell’s appearance; a formula calculates or returns a value. Formatting 0.25 as a percentage displays 25%, but the cell still contains 0.25.
How do I show a leading zero in Excel?
For numeric values of a fixed width, apply a custom format such as 00000. For an identifier that must preserve its exact characters, set the cells to Text before entering the data or enter the value as text.
Can a custom number format round a value?
It can round the displayed result to the selected decimal places, but it does not change the stored number. Use a rounding formula if the calculated value itself must be rounded.
Can I use a custom format to add words or units?
Yes. Put literal text in quotation marks, for example 0.0" kg". The unit is added to the display; it does not become part of the numeric value.
Why does my custom format show hash marks?
The column may be too narrow for the formatted value. Widen it or use a shorter format, then check the value again.
Do custom formats change the workbook’s data?
No. They change how supported values appear, not the underlying cell contents. That is why a displayed percentage, abbreviated number, or leading-zero code can still behave differently from the characters shown on screen.
Related
- Excel Green Color Code: RGB, Hex, and Theme Shades
- Excel German Number Format: Commas, Dates, and Currency
- Excel General Number Format: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: Get a List of Unique ValuesWith source values in A2:A, enter =UNIQUE(A2:A) in an empty cell to return each distinct value once. This…
- Excel Import CSV as Text: Keep Every Value UnchangedTo import a CSV into Excel as text, use Data > From Text/CSV, choose the file, select Transform…
- Excel: Hide Duplicates Based on One Column in a ListTo hide entire rows when a value repeats in one column, add a helper column, mark the first…
- Excel Solver: GRG Nonlinear vs. Simplex LPUse Simplex LP when the objective and every constraint are linear in the changing cells; use GRG Nonlinear…
- Excel: How to Paste in Filtered Cells OnlyTo put the same value in visible rows of a filtered Excel range, select the target cells, choose…
- How to Add Footnotes in Word (and Format Them)To add a footnote in Word, click where its reference number should appear, then choose References > Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.