Excel custom number formatting codes control how values appear without changing the values stored in cells. In Microsoft 365 for Windows, select the cells, press Ctrl+1, choose Number > Custom, enter a code in the Type box, and select OK. The code can change the display of numbers, dates, times, and text—for example, showing 0.25 as 25% or 7 as 0007.
What custom number formats do
A custom number format is a display rule attached to a cell. The underlying value remains numeric or textual, so calculations, sorting, and most comparisons continue to use the stored value rather than its appearance. A cell containing 0.25 can therefore display as 25% while a formula still uses 0.25.
This distinction is useful when you want a consistent presentation without changing data. For example, a number can be displayed with a currency symbol, a date can use a shorter date style, and an account code can show leading zeros. A custom format does not convert a number into text or change the value saved in the workbook.
That also means formatting is not a substitute for data cleanup. If an ID must truly be stored as text, a numeric format that adds zeros only changes how it looks in the worksheet. The difference can matter when exporting data, copying it into another program, or using it as an identifier.
How format sections work
A custom format can contain up to four sections separated by semicolons. When no conditions are used, Excel assigns those sections in this order: positive numbers; negative numbers; zero; text. You can omit sections, but their positions still matter.
0.00uses one section for all numbers and displays two decimal places.0.00;(0.00)displays positive numbers normally and negative numbers in parentheses.0.00;(0.00);-also displays zero as a dash.0.00;(0.00);-;@adds a text section that displays text as entered.
The fourth section uses @ as a placeholder for the cell’s text. If a format has fewer than four sections, text generally displays using the standard text behavior. Sections are not separate instructions for different columns: each cell uses the applicable part of the same format according to its value.
When conditions are included, the first two sections can test values instead of simply meaning positive and negative. For example, [>=1000]#,##0;[<0][Red]#,##0;0;@ displays values of 1,000 or more with a thousands separator, colors negative values red, displays other numeric values as zero-decimal numbers, and leaves text to the fourth section. Conditions belong in square brackets. Use conditions when a format needs thresholds that do not match the usual positive, negative, and zero categories.
Number placeholders and common symbols
The three main digit placeholders are 0, #, and ?. They all reserve positions for digits, but they behave differently when the value has fewer digits than the format asks for.
0displays a zero where no digit exists. The code00000displays the number 42 as 00042.#displays a digit when one exists but does not add unnecessary zeros. The code0.##can display 3.5 as 3.5 rather than 3.50.?reserves space for a missing digit, which can align decimal points or fractions visually. It may show a blank space where a zero would otherwise appear.
A period in a code marks the decimal position, and commas can add thousands grouping: #,##0 displays 12500 as 12,500. A comma at the end of a format scales the displayed number by 1,000 without changing the stored value; for example, 0.0, displays 12500 as 12.5. Two trailing commas scale by 1,000,000. This is different from a comma between digit placeholders, which groups thousands.
The percent sign displays a number multiplied by 100. Thus 0% displays 0.25 as 25%, while 0.0% displays it as 25.0%. Scientific notation uses E, as in 0.00E+00. Fraction formats such as # ?/? display suitable decimal values as fractions; the number of question marks and slash placeholders affects the displayed fraction’s precision.
Formatting dates and times
Excel stores dates as serial numbers and times as fractions of a day. Date and time formats make those values readable; they do not change the serial value. For example, yyyy-mm-dd can display a date as 2026-11-03, while ddd, mmm d can display it as Tue, Nov 3. The exact date shown depends on the cell’s stored value.
Common date tokens include d for the day, m for the month, and y for the year. Repeating a token changes the display: dd pads a one-digit day with a leading zero, while mmmm spells out the month name. For times, h and m represent hours and minutes, and s represents seconds. For example, h:mm AM/PM uses a 12-hour clock.
The letter m is context-sensitive: it means minutes when Excel interprets it as part of a time, commonly next to an hour or before seconds; otherwise it means month. A format such as h:mm displays hours and minutes, while yyyy-mm displays year and month. For totals that can exceed a day, use elapsed-time brackets: [h]:mm can display 27 hours as 27:00 instead of wrapping to 3:00.
Date displays can also depend on regional settings, including the language used for month or weekday names and the characters used as decimal or date separators. A code copied from another computer may therefore look different. If a date appears as a number, confirm that the cell contains a valid Excel date value and apply a date format; formatting text that merely resembles a date does not always convert it into a date.
Literals, colors, and conditions
To include explanatory text or punctuation in a format, put text in double quotation marks or use a backslash to escape a character. For instance, 0 "items" displays 6 as 6 items, and 0\! displays an exclamation mark after a number. The word or symbol is part of the display; it is not added to the cell’s stored value.
Square brackets have special uses. They can specify a color, a condition, or an elapsed time unit. Supported named colors include [Red], [Blue], [Green], [Black], [White], [Yellow], [Cyan], and [Magenta]. A color code changes the displayed font color, not the cell fill. Conditions such as [>100] or [<=0] select a section based on the value.
For example, [Red](#,##0);#,##0;0 shows negative values in red with parentheses, positive values with grouping, and zero as 0. A custom number format is not a full replacement for Conditional Formatting: it offers limited display rules and does not, by itself, change borders, fills, or other cell styles based on complex criteria.
Apply or edit a custom code
- Select the cells or range you want to format.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, choose Custom in the Category list.
- Choose an existing code in Type to modify it, or type a new code in the box.
- Check the Sample preview, then select OK.
On Excel for Mac, Command+1 opens Format Cells; the Number tab and Custom category are the corresponding choices. In Excel for the web, existing formats can be displayed, but the full custom-format editing experience may be limited. Open the workbook in desktop Excel when you need to enter or precisely edit a code.
Custom formats are saved with the workbook, and a format added to one workbook is not automatically added to every other workbook. If a code behaves unexpectedly, reopen Format Cells and inspect the exact Type entry. Excel may also adjust separators or code details to match regional settings.
Practical examples and choices
Choose a code based on what readers need to see, while remembering that the underlying value remains unchanged. These examples illustrate typical uses:
- Fixed-width number:
00000displays 42 as 00042. This is suitable for presentation, but store the value as text if the leading zeros are essential to the actual identifier. - Currency-like display:
$#,##0.00;($#,##0.00);-displays two decimals, puts negative values in parentheses, and shows zero as a dash. The symbol is only a display choice; it does not convert currencies. - Compact large values:
0.0,displays 12500 as 12.5, a useful shorthand when a nearby heading explains that values are in thousands. - Units:
0.0 "kg"displays 2.5 as 2.5 kg. The cell still contains a number that can be used in arithmetic. - Different positive and negative styles:
+#,##0;[Red]-#,##0;0adds a plus sign to positive values, colors negative values red, and displays zero plainly.
For identifiers such as postal codes, product codes, or account numbers, decide whether the value is a quantity or a label. If arithmetic is not meaningful and every digit—including leading zeros—must persist across exports, storing the identifier as text is usually more dependable than relying only on a display code.
Common problems and limitations
A custom format can make a cell look different without fixing its contents. A date-looking string may still be text; a number formatted as a percentage may still have an unexpected underlying magnitude; and a leading-zero format does not make a numeric ID safe from every export or import process. Click the cell and inspect the formula bar or use a suitable test to check what is actually stored.
Other frequent issues include:
- Too many decimal places: the format controls displayed precision, not necessarily the value used in calculations. Changing the format does not round the stored number.
- Unexpected dates: Excel date interpretation can vary with regional settings, and date formats cannot turn arbitrary text into valid dates.
- Wrong commas or periods: separators may follow the computer’s regional settings, so a copied code may be interpreted or displayed differently.
- Codes that do not appear to save: the format may have been applied to a different range, or the entry may be invalid. Recheck the selected cells and the Type box.
- CSV differences: CSV files store text values rather than Excel cell-format rules, so a custom display such as leading zeros may not travel with the file.
Custom formats also have limits: they are not formulas, do not alter values, and cannot create arbitrary conditional designs. If the desired result needs to change the actual text, use a formula or store the data appropriately. If a display rule needs complex colors or fills based on values, Conditional Formatting is often a better fit.
Frequently Asked Questions
How do I add leading zeros in Excel without changing the number?
Apply a format such as 00000 to display a five-digit appearance. The stored value remains numeric, so if the zeros must be part of an identifier in exports or other systems, store the identifier as text instead.
What does the semicolon mean in an Excel number format?
It separates format sections. Without conditions, the sections correspond to positive numbers, negative numbers, zero, and text, in that order. A format can use fewer sections when some categories do not need special display rules.
Does a custom number format change the value in a cell?
No. It changes only the display. A value formatted as a percentage, date, or rounded-looking number remains its original stored value for calculations unless you separately change the value or use a formula.
How can I show negative numbers in parentheses?
Use a separate negative-number section, such as #,##0;(#,##0). The first section formats positive values and the second formats negative values.
Why does m mean month in one format and minute in another?
Excel interprets m according to its date-and-time context. Near hour or second tokens it can mean minutes; in a date context it means month. Use a clear code such as h:mm for time or yyyy-mm for year and month.