Excel Custom Number Format: How to Create and Apply It

How-to basics · Updated November 20, 2026

To set 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 without changing the value stored in the cell, so you can display numbers as currency, percentages, dates, IDs, or numbers with units while keeping them usable in calculations.

You will need a workbook containing the cells you want to format and a format code that matches the display you want. The examples below work in desktop Excel for Microsoft 365 on Windows. The same basic custom-format options are available in Excel for Mac, though the shortcut can differ; use the Format > Cells menu if needed. Excel for the web may display formats already in a workbook, but desktop Excel is the reliable place to create or edit custom codes.

What You’ll Need

  • An Excel workbook with the target cells selected or ready to select.
  • A clear idea of how the values should appear, including decimal places, symbols, and any text such as units.
  • Excel for Microsoft 365 on Windows to create or edit the custom format. You do not need to change the underlying values or use a formula.

Step-by-Step

  1. Select the cells. Click a cell or drag across a range. To format a whole column, click its letter; to select nonadjacent ranges, hold Ctrl while selecting each range. Formatting applies to the selected cells, not automatically to other cells that you fill or add later.
  2. Open Format Cells. Press Ctrl+1 on Windows. Alternatively, go to Home > Number and select the small dialog box launcher at the lower-right of the Number group. In Excel for Mac, select Format > Cells, then open the Number tab if it is not already active.
  3. Choose the Custom category. In the Format Cells dialog, select the Number tab and then Custom in the Category list. The Type box shows a format code. Excel may show codes available in the workbook or codes associated with the selected cells; you can choose one as a starting point or type your own.
  4. Enter a code for the display you want. Click in Type, replace the existing code, and enter a valid custom format. For example, enter #,##0.00 to show a number with a thousands separator and two decimal places, or 00000 to show an integer with at least five digits. The Sample area previews the result for the active cell.
  5. Check the preview and apply it. Confirm that the sample looks right, then select OK. If the preview is not available or the cell is blank, test the format on a cell that contains a representative value. The code affects appearance; for example, a value of 42 formatted as 00000 displays as 00042 but remains the number 42.
  6. Verify the result in the worksheet. Check more than one cell if the range contains different kinds of values. Make sure dates, negative values, zeros, and text display as intended. If you need to revise the format, reopen Format Cells > Number > Custom, edit the code in Type, and select OK again.

Useful format-code building blocks

Excel custom formats use placeholders and symbols to decide what appears. The digit placeholder 0 forces a digit into that position, including a zero when the value has fewer digits. The # placeholder displays a digit only when needed, so optional leading or trailing zeros are not shown. The ? placeholder reserves space for a digit or a blank, which can help line up decimal points in a column.

  • 0.00 displays two decimal places, padding with zeros when necessary. For example, 7 displays as 7.00.
  • #,##0 displays whole numbers with a thousands separator, such as 12500 as 12,500. The separator character used in a code can depend on regional settings.
  • 0.0% displays a percentage with one decimal place. A stored value of 0.256 displays as 25.6%. Applying a percentage format scales the display; it does not rewrite the stored value.
  • mmmm d, yyyy displays a date such as January 5, 2026. Excel stores dates as serial numbers, so this format works as expected only when the cell contains a valid Excel date value.
  • #,##0.0" kg" appends a space and the literal unit kg, so 1250.5 displays as 1,250.5 kg. The unit is display text, not part of the value.

Text placed in a format code usually needs quotation marks so Excel treats it literally. In the dialog’s Type box, type ordinary quotation marks around the text, as in 0" items". A backslash can also make the next character literal, such as 0\! to display an exclamation mark after a number. When quoting a code in an article or other HTML, the quotation marks may be shown as entities; enter normal quotation marks in Excel.

Using separate formats for positive, negative, zero, and text

A custom code can contain up to four sections separated by semicolons, in this order: positive numbers; negative numbers; zero values; text. For example, #,##0.00;[Red](#,##0.00);0.00;@ shows positive numbers with two decimals, negative numbers in red parentheses, zero as 0.00, and text as entered. The @ placeholder represents the cell’s text. If you provide only one section, Excel generally uses that format for all numbers; if you provide two or three sections, the sections have different meanings, so do not add semicolons casually.

For simple cases, use one section rather than building a complex code. If you do use separate sections, test a positive number, a negative number, zero, and a text entry. Color names such as [Red] are supported in a format section, but they do not replace conditional formatting when you need rules that depend on a range of values or other cells.

Common Mistakes

  • Expecting a format to change the stored value. A custom format only changes display. A number formatted with a currency symbol is still numeric, and the symbol is not included in a sum. Conversely, adding a unit with a custom format does not convert the measurement or calculate it.
  • Using a number format to restore digits Excel already discarded. Excel keeps only 15 significant digits in numeric values. If a long account, product, or tracking ID has more digits, formatting it cannot recover digits lost during entry. Store such identifiers as text before entering or importing them.
  • Typing a leading zero and assuming it will remain. Excel commonly removes leading zeros from numeric entries. Apply a fixed-width format such as 00000 to display them, or store the entry as text if the value is an identifier and should not be calculated.
  • Confusing a date format with a date conversion. A date-looking format cannot turn arbitrary text into a real date. If a cell contains text such as 2026-01-05, it may need to be converted to a valid date value before a date format can display it as intended.
  • Using m without checking its meaning. In Excel date and time codes, m can represent a month or minutes depending on context. Use a clear date pattern such as mmm d, yyyy for month names, and test time patterns carefully when minutes are involved.
  • Including a decimal point without the intended placeholders. Codes such as 0.## show up to two decimal places but omit unnecessary trailing zeros, while 0.00 always shows two. Choose based on whether aligned, fixed decimals matter.

Tips

  • Start with the closest built-in format. In Format Cells, select a built-in category such as Number, Date, or Percentage first if it is close to your goal, then select Custom and adjust the Type code. This reduces the chance of creating a code that does something unexpected.
  • Use a custom unit instead of typing the unit into every cell. A code such as 0.0" cm" keeps the stored values numeric, so calculations still work. Use a space inside the quoted text when you want a gap between the number and unit.
  • Use formats consistently across a column. A range with mixed display formats can be hard to scan. Select the entire relevant range and apply one format, then make exceptions only where they genuinely communicate something different.
  • Keep a copy of codes that are hard to recreate. Excel saves custom formats in the workbook. Copying a formatted cell to another workbook can carry its format with it, but it can also bring unwanted formatting. Keep a note of important codes or use Format Painter carefully.
  • Check regional settings when separators look wrong. Excel may use a comma for decimals and a period for thousands, or the reverse, depending on system and regional settings. If a code behaves differently on another computer, review the relevant locale and test the display rather than assuming the punctuation is universal.
  • Use conditional formatting for value-dependent styling. Custom number formats are best for consistent display rules such as parentheses for negatives. If you want cells to change color based on thresholds or other rules, use Home > Conditional Formatting instead.

Troubleshooting

The code appears in the cell instead of the formatted result. Confirm that you entered the code in Format Cells > Number > Custom > Type, rather than directly into the worksheet. If the cell itself is set to Text, Excel may treat entries as text; change the cell format to General or the intended number category, then re-enter or convert the value as appropriate.

The display does not change after selecting OK. Check that the intended cells were selected and that they contain numeric values where the code expects numbers. If the cell contains text that looks like a number, a numeric custom format may not affect it. You can inspect the formula bar and try a known numeric value in a test cell to distinguish a value problem from a code problem.

You see ###### instead of a value. This usually means the column is too narrow for the formatted result, especially for dates, times, or numbers with many digits. Widen the column by dragging the boundary between column headings or double-clicking that boundary to AutoFit. If the value is a negative date or time, use a different display approach because Excel may not show it as an ordinary date or time.

A date displays as a number or the wrong month. Excel dates are stored as serial numbers. Apply the intended date code if the underlying value is a real date; if it remains a serial number, test a date format such as mmm d, yyyy. Check whether the source is text, and remember that m can mean month or minute in time-related codes.

The format works in one workbook but not another. Custom formats belong to a workbook and may not be present in a different file. Copy a correctly formatted cell into the destination workbook, or recreate the code through Format Cells. Also check whether the destination is being opened in Excel for the web or another spreadsheet application, since support and display can vary.

The zeros or decimal places are not what you expected. Use 0 where a digit must appear and # where it should be optional. For fixed decimal places use a fixed number of zeros after the decimal point, such as 0.000; to allow up to three places without padding, use 0.###. Confirm that you are testing with a value that has enough digits to show the difference.

Frequently Asked Questions

Does a custom number format change the value in Excel?
No. It changes how the existing value appears. Calculations continue to use the stored number, date, or time, not the symbols or text added by the format.

Can I create a custom format for leading zeros?
Yes. Select the cells, open Format Cells, choose Number > Custom, and enter a code such as 00000 for a five-digit display. The cell still stores a number. For identifiers that should never be treated as numbers, store them as text instead.

Can I add a currency symbol without changing the number?
Yes. A format code can show a currency symbol while keeping the cell numeric. For example, a code like $#,##0.00 displays a dollar sign and two decimals. Choose the right currency and regional convention for your workbook; the symbol is not a currency conversion.

How do I remove a custom number format?
Select the cells and choose a built-in category in Format Cells, such as General or Number, then select OK. This changes the display format; it does not delete the values. Selecting General may reveal the underlying serial number for a date.

Can custom number formats show text such as kg or units?
Yes. Put literal text in quotation marks in the Type code, such as 0.0" kg". The displayed unit is not stored in the cell and does not perform a unit conversion.

Will my custom format work in Excel for the web?
Excel for the web can display many formats saved in a workbook, but its options for creating or editing custom codes may be more limited than desktop Excel. Create or revise the code in desktop Excel when you need predictable results, then check how the workbook appears in the web version.

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.