Add a Custom Number Format in Excel: Steps & Examples

How-to basics · Updated November 19, 2026

To add a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, enter a format code in Type, and select OK. A custom format changes how a value looks—not the value stored in the cell—so you can add symbols, leading zeros, or clearer date and number displays without changing the underlying data.

Why use a custom number format?

Excel’s built-in formats cover common needs such as currency, percentages, dates, and decimals. A custom format is useful when those options do not display information the way a report or worksheet requires. For example, a product code stored as the number 721 can display as 00721, while remaining the number 721 for calculations.

Because formatting affects only appearance, a formula that refers to a formatted cell still uses its original numeric value. If a cell contains 0.25 and you apply a percentage format, Excel displays 25%, but a formula using that cell still works with 0.25. This distinction is central to using custom formats safely: they can make a sheet easier to read, but they do not convert, round, or otherwise rewrite the data.

  • Use custom formats to add units such as kg or hours without typing the unit into every cell.
  • Display negative values in parentheses or in a different color.
  • Show consistent decimal places, dates, or elapsed time.
  • Make numeric identifiers appear with leading zeros when they are short and suitable for numeric storage.

How to add a custom format in Excel

  1. Select the cell or range to format. You can select non-adjacent cells with Ctrl-click on Windows.
  2. Press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, open the Number Format menu and choose More Number Formats.
  3. On the Number tab, choose Custom in the Category list.
  4. Choose an existing code in the Type list as a starting point, or type a new code in the Type box.
  5. Check the Sample preview, then select OK. If Excel rejects the code, review its punctuation and section separators.

In Excel for Mac, select the cells and press Command+1 to open Format Cells; the dialog may look slightly different, but choose the custom number format category and enter the code in its format field. Excel for the web has a different interface and may not expose every desktop formatting option in the same way. If you need to create or troubleshoot a more advanced code, open the workbook in the desktop version of Excel.

Understand the parts of a format code

Custom number formats use placeholders and symbols to tell Excel what to display. The three most useful placeholders are 0, #, and ?. A zero forces a digit to appear, even when the value has no digit in that position. A number sign displays a digit only when one is present. A question mark also allows optional digits, but reserves space for missing digits so decimal places can line up vertically.

  • 0.00 displays two decimal places, so 4.5 appears as 4.50.
  • 0.## displays up to two decimal places without adding unnecessary trailing zeros, so 4.5 appears as 4.5 and 4 appears as 4.
  • #,##0 displays a thousands separator and no decimal places, so 12500 appears as 12,500.

Other symbols have specific jobs. A comma can group digits, while a comma placed after the last digit placeholder scales the displayed number by 1,000 for each trailing comma. A period marks the decimal position in the format code used by your regional settings. The percent sign displays a value as a percentage by multiplying its displayed value by 100. Regional settings can affect which characters Excel expects for decimal and grouping separators, so a code copied from another locale may need adjustment.

To show text after a number, put the text in quotation marks inside the format code. For example, 0" kg" displays 12 as 12 kg. The cell still contains the number 12, so it can be added or averaged normally. You can also use a backslash to make the following character literal, but quoting a short text label is often easier to read.

Useful custom format examples

Try these codes in the Type box. The example results assume the cell contains the stated value; the code changes the display, not the stored value.

  • 00000: the value 721 displays as 00721. This is useful for fixed-width numeric codes, such as some short inventory IDs.
  • $#,##0.00: the value 1234.5 displays as $1,234.50. The currency symbol and separators may appear differently under other regional settings.
  • 0.0%: the value 0.256 displays as 25.6%. Do not use this if the cell already contains 25.6 and you intend to display 25.6%; that value would display as 2560.0%.
  • 0" units": the value 8 displays as 8 units. The word is a label; it does not turn the cell into text.
  • #,##0.0,: the value 12500 displays as 12.5, with the trailing comma scaling the displayed amount to thousands. Add a quoted label, such as #,##0.0,"K", to display 12.5K.
  • (#,##0): the value 1250 displays as (1,250). This example applies the parentheses to positive values; use separate sections to make parentheses apply only to negatives.

For identifiers such as ZIP codes, account numbers, or product codes, consider whether the data is truly a quantity. If you need to preserve leading zeros in imported data, or if an identifier can exceed Excel’s 15-digit numeric precision, store it as text rather than relying on a number format. Formatting can add visual zeros, but it cannot recover digits that Excel has already rounded or preserve zeros that were lost when data was imported.

Format positive, negative, zero, and text values

A custom format can contain up to four sections separated by semicolons. In the basic pattern, the sections apply in this order: positive numbers; negative numbers; zero; text. For example, #,##0;[Red](#,##0);-;@ displays positive values with grouping, negative values in red parentheses, zero as a dash, and text as entered. The @ placeholder represents the cell’s text.

You do not have to supply all four sections. With one section, the same pattern applies to numbers. With two, the first applies to positive numbers and zero, and the second to negative numbers. With three, the sections cover positive, negative, and zero values. Use semicolons carefully: an omitted section still has a position, and an unexpected extra separator can make a code behave differently than intended.

Color names in square brackets, such as [Red], can set a display color in a custom format. Conditions can also be written in brackets, for example to apply a section only when a value is below zero. These options are useful for simple display rules, but they are not a substitute for Conditional Formatting when colors or styles must respond to more complex conditions or update independently of the number format.

Custom formats for dates and time

Excel stores dates as serial numbers and times as fractions of a day. A date or time format tells Excel how to show that stored value. For instance, a value representing November 3, 2026 could display as 11/3/2026, Nov 3, 2026, or Tue, Nov 3, 2026 using formats such as m/d/yyyy, mmm d, yyyy, or ddd, mmm d, yyyy. The displayed order and punctuation may vary with regional settings.

Time codes use h for hours, m for minutes or months, and s for seconds. Excel interprets m as minutes when it appears in a time context, such as next to hours or seconds; otherwise it generally represents a month. To show a time with an AM or PM marker, use a pattern such as h:mm AM/PM. To show elapsed hours beyond 24—for example, a total duration of 31 hours—use [h]:mm. Without the square brackets, the hours can wrap around and display as 7:00 rather than 31:00.

If a date code displays a number such as 46329 instead of a calendar date, the cell may simply be using a General or numeric format. Apply a date format to display the serial number as a date. If the value was imported as text, however, changing its number format alone may not convert it into a real Excel date; conversion may be required first.

Choose between formatting and changing the data

Use a custom number format when you want a different presentation while keeping the value available for arithmetic, sorting, and charting as a number. Use actual text when the content is an identifier, a label, or a value whose exact characters matter more than numeric calculations. For example, displaying a number as 00721 can be convenient for a report, but storing a part number as text is often safer if it may contain letters or leading zeros in source data.

Custom formats also differ from formulas that create display text. A format leaves the cell’s numeric value intact; a formula using the TEXT function returns text. That can be useful for composing a sentence or label, but the result is no longer a numeric value for ordinary arithmetic unless it is converted back. Choose based on what downstream formulas, exports, and other users need to do with the cell.

Common problems and how to fix them

  • The format has no effect: Check whether the cell contains a number or a number stored as text. A numeric format will not turn text into a number. You can inspect alignment and the formula bar, or test the cell in a calculation.
  • Leading zeros disappear: Apply a code such as 00000 to a numeric value, or store the identifier as text if it must retain exact characters. If zeros were removed from text during import, a format alone may not restore the intended original data.
  • A percentage is much too large: Remember that a percent format multiplies the displayed value by 100. If the cell contains 25.6, the result is 2560%, not 25.6%.
  • A date shows as a number: Apply an appropriate date format if the underlying value is a date serial. If the entry is text, convert the data rather than only changing its appearance.
  • The code works on one computer but not another: Decimal and thousands separators depend on regional settings, and some symbols may vary by locale. Review the code in the target workbook’s Excel environment.
  • Cells show hashes: A row of hash marks commonly means the column is too narrow for the displayed value. Widen the column or choose a shorter format; it does not usually mean the value has been erased.

If you need to remove a custom format, select the cells and choose a different number format, such as General, from the Number Format menu. This changes how the cells display; it does not clear their contents. To remove only custom styling while preserving the values, use the appropriate clear-formatting option rather than deleting the cell contents.

Frequently Asked Questions

Does a custom number format change the value in Excel?

No. It changes how the existing value is displayed. Calculations continue to use the stored number, unless the cell contains text or you replace the value with a different result.

How do I add leading zeros without changing a number?

Select the cells, open Format Cells with Ctrl+1, choose Custom, and enter a code such as 00000 for a five-digit display. If the identifier must preserve exact characters or may exceed 15 digits, store it as text instead.

Can I add words or units after a number?

Yes. Put the label in quotation marks in the format code, such as 0" days". A value of 5 then displays as 5 days while remaining numeric.

Why does Excel show a number instead of a date?

Dates are stored as serial values, so a cell in General format may show the serial number. Apply a date format. If the entry is text rather than a date value, convert it to a real date first.

Can I use a custom number format in Excel for the web?

Excel for the web can display many number formats, but its controls and support for custom codes can differ from desktop Excel. For advanced codes or a missing Custom option, open the workbook in Excel for Microsoft 365 on Windows or Mac.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.