An Excel custom number format changes how a cell’s value looks without changing the value Excel stores or uses in calculations. Open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter a format code; the cheat sheet below covers the syntax and common examples.
How custom number formats work
A number format is a display instruction, not a conversion. If a cell contains the number 0.25, a percentage format can show 25%, while the stored value remains 0.25. Formulas and charts generally continue to use the underlying value. To convert a value into different data, use a formula or another data operation rather than a number format.
Excel custom formats use up to four sections separated by semicolons, in this order: positive numbers; negative numbers; zero; text. For example, #,##0;[Red](#,##0);"-";"Text: "@ displays positive whole numbers with grouping, negative numbers in red parentheses, zero as a dash, and text with a prefix. You do not have to provide all four sections: a single section applies to all numbers, and a second section normally distinguishes positive and negative values.
0is a required digit placeholder. Excel displays a zero when no digit exists in that position.#is an optional digit placeholder. It suppresses unnecessary zeros.?is an optional digit placeholder that leaves space for a suppressed zero, helping align decimal points or fractions.@represents text when you include a text section.
For instance, 0.00 always displays two decimal places, whereas 0.## displays up to two and omits trailing zeros. With a value of 4.5, the first displays 4.50 and the second displays 4.5. A format cannot restore precision that was never stored: it only controls the displayed digits and rounding.
Quick number, decimal, and percentage formats
These codes are useful starting points. Enter them in the Type box under the Custom category in Format Cells. The result depends on the cell’s actual value, not on what the code is intended to represent.
0displays a whole number, including a zero for a blank digit position.#,##0displays whole numbers with a thousands separator, such as 12500 as 12,500 in a typical English-language setting.#,##0.00displays two decimal places and groups thousands.0.##displays up to two decimal places without forced trailing zeros.0.0%displays a value such as 0.256 as 25.6%. Excel scales the displayed value by 100 for percentage formats.0.00E+00displays a number in scientific notation, useful when values are very large or very small.# ?/?displays a value as a fraction where Excel can represent it suitably; the question marks help line up the parts.
A comma can group thousands or scale a number, depending on its position. In #,##0, commas separate digit groups. A comma after the final digit placeholder divides the displayed value by 1,000: 0.0, shows 1,250,000 as 1,250.0. Two trailing commas scale by 1,000,000. These codes change only the display, so label a scaled figure clearly if readers might mistake it for the full amount.
Negative numbers, zeros, colors, and conditions
Use semicolon-separated sections to make negative values and zeros easier to read. For example, #,##0;(#,##0);"-" displays 1,200 as 1,200, -1,200 as (1,200), and zero as a dash. Parentheses are a familiar accounting-style convention; they do not make a negative value positive.
You can add a color in square brackets, such as [Red], [Blue], or [Green]. For example, 0.00;[Red](0.00);"-" shows negative amounts in red and zero as a dash. Excel also recognizes standard color names and indexed colors such as [Color10]. Color availability and appearance can vary with the workbook’s theme and display settings, so do not rely on color alone to convey meaning.
Conditions in brackets let a section apply only when a value meets a test. For example, [>=1000]0.0,"K";0.0 shows values of 1,000 or more in thousands with a K suffix, while other values use one decimal place. A custom format can use up to two condition tests; when conditions are used, remaining values use the remaining section. Keep the code readable and check boundary values such as exactly 1,000 and just below it.
Number-format colors and conditions are not a replacement for Conditional Formatting. A custom format controls a cell’s display based on its value, but it does not create rules that compare cells, add icons, or apply data bars. Use Home > Conditional Formatting when you need visual rules that respond to ranges or more complex criteria.
Dates and times: useful codes and common traps
Excel stores dates and times as serial values, so a date format changes the display of a number that Excel recognizes as a date. Common codes include m/d/yy, mmm d, yyyy, dddd, mmmm d, and h:mm AM/PM. For example, mmm d, yyyy can display a date as Nov 3, 2026, while dddd, mmmm d displays its weekday and full month name.
Letter case and position matter. In date formats, m means month in most contexts, but in a time expression it can mean minutes when it appears beside hours or seconds. Thus h:mm displays hours and minutes; mm:ss displays minutes and seconds. If a format gives an unexpected month or minute, inspect the surrounding tokens rather than assuming the cell value is wrong.
For durations longer than one day, use square brackets around the elapsed-time unit: [h]:mm can show a total of 36 hours as 36:00 instead of wrapping the clock display at 24 hours. Similarly, [m]:ss displays total elapsed minutes. This is useful for work totals or run times, but it does not change how the duration is calculated.
Regional settings affect how dates and separators appear. A code that looks familiar on one computer can display with different date ordering or punctuation under another locale. If a date appears as a serial number, check that the cell contains a valid Excel date value and that the date format was applied; formatting text that merely looks like a date will not always turn it into a usable date.
Text, identifiers, and literal characters
Custom formats can add labels or punctuation around a number. Put literal text in double quotation marks, as in 0.00 "kg", which displays 12.5 as 12.50 kg. To show a character that Excel otherwise interprets as formatting syntax, quote it or escape it with a backslash where appropriate. For example, 0\% displays a literal percent sign without scaling the value, unlike 0%, which treats the value as a percentage.
A custom format can also help display fixed-length numeric identifiers. The code 00000 displays the number 42 as 00042, while the underlying value remains 42. This is useful for numeric codes whose leading zeros are for presentation. If the identifier must preserve leading zeros as actual characters—for importing, matching, or exporting, for example—store it as text instead. Formatting cannot change a numeric value into text.
To display text with a prefix, use the fourth section and the @ placeholder. For instance, General;General;General;"Ref: "@ displays text as Ref: followed by the cell contents. Test formats on both numeric and text cells: a format designed for numbers may not produce the intended result for text values.
How to create or edit a custom format
- Select the cell or range. If you want to format a whole column, select the column before applying the code.
- Press Ctrl+1 on Windows to open Format Cells. On Mac, use Command+1 to open the formatting dialog.
- Choose the Number tab, then Custom. Select a close existing format or enter a code in the Type box.
- Check the Sample preview and choose OK. Test the result with representative positive, negative, zero, text, or date values as applicable.
Custom formats are saved with the workbook and can be reused from the Custom list. On Mac, the Format Cells dialog may look different, but the number-format concepts and semicolon section order are the same. Excel for the web can display many formats already stored in a workbook, but use the desktop app if you need to create or edit a custom format and the web interface does not offer the required controls.
Choosing between a custom format and other tools
Use a custom number format when the value should remain numeric but needs a clearer presentation—for example, adding decimal places, showing negatives in parentheses, or displaying a date in a different style. This keeps ordinary arithmetic and sorting based on the underlying numbers.
Use a formula when you need to create a genuinely different value or text result. For example, a format can show a number with a unit label while leaving it numeric; a formula that joins a value and a unit produces text, which may not behave like a number in later calculations. Use Conditional Formatting for rules that need to highlight cells based on comparisons or visual indicators. Use text storage for identifiers whose characters, including leading zeros, are part of the data itself.
One frequent misconception is that a format changes precision. Applying 0 to 3.7 displays 4, but it does not replace the stored value with 4. A formula using that cell can still use 3.7. If you need to round the actual result, use an appropriate rounding formula or data transformation and verify the intended calculation separately.
Common problems and ways to troubleshoot
- The code appears as text: Open Format Cells and enter the code in the Custom Type box; typing a format code directly into a worksheet cell enters cell content, not formatting instructions.
- Leading zeros disappear: Apply a zero-padded format such as
00000for display, or store the identifier as text if the zeros must be part of the data. - A date code shows a number: Confirm the cell contains an Excel date serial rather than date-looking text, then apply a date format.
- Percentages are 100 times too large or small: Check the stored value. A value of 0.25 displays as 25% under
0%; a stored value of 25 displays as 2,500%. - Commas or decimal points differ: The display follows regional settings. Validate the result on the computers or files where the workbook will be used.
- The display is ####: The column may be too narrow for the formatted value. Widen it or choose a shorter format; the hashes do not automatically mean the value is damaged.
Frequently Asked Questions
Does a custom number format change the value in Excel?
No. It changes how the value is displayed. Calculations continue to use the stored value unless you separately change it with a formula or data operation.
How do I show a zero as a dash?
Use a zero section in a custom format, such as 0;-0;"-". For grouped numbers and parenthesized negatives, use #,##0;(#,##0);"-".
How do I keep leading zeros in Excel?
For display only, apply a format such as 00000 to show five digits. If the zeros must be stored as part of an identifier, enter or convert the identifier to text instead.
What does the semicolon mean in an Excel format code?
It separates the sections for positive numbers, negative numbers, zero, and text, in that order. You can use fewer than four sections when you do not need separate display rules for every type.
Can I use a custom number format in Excel for the web?
Excel for the web can show many formats created in desktop Excel, but its controls may not let you create or edit every custom format. Open the workbook in Excel for Microsoft 365 on Windows or Mac when you need the full Custom format dialog.
Related
- Close Excel Shortcut: Windows and Mac Keys
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
- Excel Drop-Down Lists: How to Create and Manage Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut Key to Go to the Last Row in a SheetIn Excel for Microsoft 365 on Windows, press Ctrl+Down Arrow to jump to the edge of the current…
- Excel Power Query: Combine Columns Without Losing DataIn Excel for Microsoft 365 on Windows, concatenate columns in Power Query by selecting them in the Power…
- How to Change Pane View in OutlookTo change the pane view in Outlook, open View, choose Layout if your version shows it, then select…
- How to Make a Brochure in Microsoft Word, Step by StepTo make a brochure in Microsoft Word, start with a brochure template or set a blank document to…
- Delete Blank Rows in Excel All at OnceTo delete fully blank rows in Excel all at once, add a temporary helper column, mark rows with…
- How to Double Space in Word and Google DocsTo double space text, select it and choose 2.0 line spacing: in Word, go to Home > Line…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.