Microsoft Excel custom number format codes control how a cell’s value appears without changing the value stored in the cell. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, enter a code in Type, and select OK.
What custom number formats do
A number format is a display rule. For example, a cell can contain the number 0.25 but display it as 25%, or contain 45292 and display it as a date. The underlying value remains numeric, so Excel can still calculate with it. This differs from converting a value to text: custom formatting changes appearance, not the stored data.
Excel includes built-in formats for common needs such as dates, percentages, and currency. A custom format lets you specify details those choices may not offer, such as showing negative amounts in parentheses, adding a label after a number, or displaying zero as a dash. Custom formats are useful when a consistent presentation matters, but they should not conceal information that readers need to interpret the data correctly.
How a format code is organized
A format code can contain up to four sections separated by semicolons. In the usual arrangement, the sections apply to positive numbers, negative numbers, zero, and text, in that order. For example, #,##0;(#,##0);-;@ displays positive whole numbers with commas, negative numbers in parentheses, zero as a dash, and text as entered.
- One section: applies the same format to numbers; text is generally displayed as text.
- Two sections: the first applies to positive numbers and zero, and the second applies to negative numbers.
- Three sections: the sections apply to positive numbers, negative numbers, and zero.
- Four sections: the fourth section specifies how text is displayed. The
@symbol stands for the text value.
Sections can also use conditions and colors, which changes how Excel assigns formats. A code is not a formula: it does not calculate a new result or alter a cell’s value. It only tells Excel how to render that value in the worksheet and in many printed or exported views.
Number placeholders and common symbols
The most important placeholders are 0, #, and ?. They determine how digits are displayed, especially when a value has fewer digits than the format requests.
0displays a digit when present and adds a zero when needed. The code00000displays 42 as 00042.#displays a digit when present but does not add unnecessary zeroes. The code0.##displays 3.5 as 3.5 and 3 as 3.?reserves space for a digit or a blank, which can help align decimal points or fractions visually.
Separators and symbols add other display choices. For instance, #,##0 displays 1234567 as 1,234,567 in a typical English-language setting. The actual grouping and decimal separators can vary with the computer’s regional settings. A period in a code is the decimal placeholder, while a comma can group thousands or scale a number, depending on where it appears.
To show thousands or millions without writing all the digits, use a scaling comma: 0, displays 1250000 as 1250, and 0,, displays it as 1. The number is still 1250000, so the format should make the scale clear. A percent sign multiplies the displayed value by 100: 0.0% displays 0.256 as 25.6%. Scientific notation can be displayed with a code such as 0.00E+00.
Useful examples for everyday worksheets
These examples illustrate how small changes to a code can make a report easier to scan. Enter the code in the Custom Type box; do not enter it in a worksheet cell as if it were a formula.
0.00shows two decimal places, including trailing zeroes. A value of 4.5 appears as 4.50.#,##0.00;(#,##0.00);-shows two decimals, encloses negative amounts in parentheses, and replaces zero with a dash.0 "items"adds the literal word “items” after a number, so 6 appears as 6 items. The quotation marks tell Excel to display the word literally.000-000can display a six-digit number such as 123456 as 123-456. This is a presentation choice, not a way to preserve leading zeroes in data imported as numbers.# ?/?displays a value as a fraction where practical. Excel chooses a fraction representation based on the format and the value.
Be careful with identifiers such as postal codes, account numbers, or product IDs. If leading zeroes are meaningful, storing the identifier as text is often safer than storing it as a number and relying on a display format. A format may make a numeric identifier look right in the sheet, but copying it into another application or exporting it can expose the underlying numeric value.
Formatting dates, times, and elapsed time
Excel stores ordinary dates as serial numbers and times as fractions of a day. A custom date format changes how those values appear; it does not turn arbitrary text into a date. Common date codes include d for the day number, dd for a two-digit day, ddd for an abbreviated weekday, mmmm for a full month name, and yyyy for a four-digit year. For example, ddd, mmm d, yyyy can display a date with a weekday and abbreviated month.
The letter m is context-sensitive: it represents a month in a date, but it can represent minutes in a time. Excel interprets it as minutes when it is used in a time context near hours or seconds. A code such as h:mm AM/PM displays a time with an AM or PM marker, while hh:mm:ss includes seconds. Date separators such as the slash may display according to regional settings.
For totals that can exceed one day, use elapsed-time brackets. The format [h]:mm can display a total of 27 hours as 27:00; ordinary h:mm displays the hour on a clock and can wrap after 24 hours. Similarly, [m]:ss is useful for a total number of minutes. If a date or time looks unexpected, check that the cell contains a real numeric date/time value and that the workbook’s date system is appropriate; changing the display code alone cannot repair an incorrectly stored value.
Adding a custom format in Excel
On Excel for Microsoft 365 for Windows, use this sequence:
- Select the cells or range to format.
- Press Ctrl+1 to open Format Cells. You can also use the Number group’s dialog launcher on the Home tab.
- Choose Custom in the Category list.
- Select an existing code in Type to edit it, or type a new code in the Type box.
- Check the Sample preview, then select OK.
On Excel for Mac, select the cells and press Command+1 to open Format Cells; the dialog’s Custom category is the place to enter the code. In Excel for the web, custom formats made in desktop Excel can be displayed, but the web formatting controls do not offer the full desktop custom-format authoring experience. Open the workbook in the desktop app if you need to create or edit a detailed code.
Custom formats are generally stored with the workbook, so a code added to one file is not automatically available in every other workbook. For a reusable workbook, apply the format to a template or copy a formatted cell into the destination file. Preview the result with representative positive, negative, zero, date, and text values before distributing the workbook.
Conditions, colors, and literal characters
Square brackets can add color names such as [Red] or conditions such as [>=1000]. A simple conditional code can apply one display rule to values meeting a threshold and another to the remaining values. Excel custom number formats support up to two conditions in a format code; when using conditions, test values on both sides of each boundary to confirm the intended result. Conditions affect display only, not sorting, filtering, or the stored number.
To display a character that Excel might otherwise interpret as part of the format, enclose literal text in double quotation marks, as in 0 "kg", or use a backslash to escape an individual character. The underscore character reserves horizontal space equal to the next character, and the asterisk repeats the next character to fill available width. These alignment characters can be helpful in accounting-style layouts, but excessive spacing can make a worksheet harder to read on different screen sizes.
Common problems and how to avoid them
- The value looks rounded: a format may hide decimal places without rounding the stored value. Increase the displayed decimals to inspect it. If a calculation must actually round, use an appropriate rounding formula rather than relying on formatting.
- A date code shows a number: the cell may contain a serial date but be formatted as General or Number. Apply a date format, and verify that the original entry was recognized as a date.
- Leading zeroes disappear: a numeric value does not retain typed leading zeroes. Use a text value for identifiers or a format such as
00000if the value is genuinely numeric and only the display needs padding. - The code appears in the cell: the entry may have been typed into the worksheet rather than the Type box, or the cell may be treated as text. Open Format Cells and enter the code under Custom.
- A format works on one computer but looks different elsewhere: separators, currency symbols, and date conventions can depend on regional settings. Check the workbook on the target system and prefer an unambiguous date presentation when sharing across regions.
A frequent misconception is that a custom format permanently changes the data. It does not: a value formatted as a percentage is still stored as its underlying decimal, and a displayed dash may still represent numeric zero. Formulas and charts normally use the underlying value. If you need the visible representation as actual text for an export or label, create text deliberately and consider the consequences for later calculations.
Frequently Asked Questions
Do custom number formats change the value in a cell?
No. They change how Excel displays the value. Calculations generally continue to use the stored number, date serial, or text.
How do I show negative numbers in parentheses?
Open Format Cells with Ctrl+1 on Windows, choose Custom, and use a two-section code such as #,##0;(#,##0). Add a third section if you also want a special display for zero.
Can a custom format hide zero values?
Yes. In a three-section code, the third section controls zero. Leaving that section empty can suppress its display, though hiding values may confuse readers; a dash is often clearer.
Can I use a custom format to add text after a number?
Yes. Put literal text in quotation marks, as in 0 "days". The cell remains numeric, so the displayed word is not part of the stored value.
Why does Excel show a format code differently on another computer?
Regional settings can affect date and number separators, and some display conventions vary by locale. For shared files, test the workbook in the expected environment and choose clear, familiar formats.
Related
- Excel Keyboard Shortcuts to Resize Rows and Columns
- Keyboard Shortcut to Insert a Checkbox in Excel (Windows)
- Excel Shortcut to Collapse a Group
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 Keys for Data Entry: A Practical GuideFor data entry in Excel for Microsoft 365 on Windows, use Tab to move right, Enter to move…
- Change PowerPoint Slide Size to A2 (Portrait or Landscape)To change a PowerPoint presentation to A2, open Design > Slide Size > Custom Slide Size on Windows,…
- Outlook View Changes: Can You Lock the Layout?Outlook does not provide a universal “Lock View” switch that makes a mail layout impossible to change. You…
- How to Add a Subject Line to a Word Mail Merge EmailTo add a subject line to a Word email merge, finish preparing your message, then choose Mailings >…
- How to Make a Fillable Form in Microsoft Word Step by StepTo make a fillable form in Microsoft Word, turn on the Developer tab, add content controls such as…
- Excel Day Counter: Count Days Between DatesWith a start date in A2 and an end date in B2, enter =B2-A2 to count the days…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.