To create a custom number format in Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose the Number tab, select Custom, enter a format code in Type, and click OK. The format changes how a value looks, not the value stored in the cell, so calculations can still use the original number.
Where to create a custom format
Custom formats are useful when Excel’s built-in choices do not show information the way you need—for example, when you want a fixed number of decimal places, parentheses around negative amounts, or a dash for zero. You can create a new code or select and adapt a code already listed in the Custom category.
- Select the cell or range you want to format. You can also select a whole column or row.
- Open Format Cells by pressing Ctrl+1, or use Home > Number group > dialog box launcher.
- On the Number tab, select Custom from the Category list.
- Type a code in the Type box, or choose an existing code and edit it.
- Check the sample preview, then select OK.
On Excel for Mac, select the cells and press Command+1 to open Format Cells; then choose Custom and edit Type. Ribbon labels and dialog layout can vary slightly between builds. Excel for the web can display many formats, but creating or editing custom codes is not available in every web experience. If you cannot find the Custom type editor, open the workbook in the desktop app.
How custom number formats work
A format code is a set of instructions for displaying a cell’s contents. It does not convert the value, round it permanently, or change how a calculation works. If a cell contains 12.345 and the format shows one decimal place, the cell still contains 12.345; a formula that refers to it uses that underlying value. This distinction matters when totals appear slightly different from the sum of displayed, rounded figures.
Excel’s main placeholders are 0, #, and ?. A zero forces a digit to appear, a number sign displays a digit only when needed, and a question mark reserves a character space for an insignificant digit. For instance, 00000 shows the number 73 as 00073, while #,##0 shows 12345 as 12,345. The decimal point and comma can also control displayed precision and grouping.
A format code can contain up to four sections separated by semicolons. These sections can define appearances for positive numbers, negative numbers, zero values, and text, in that order. You do not have to use all four. With one section, the same instructions apply to all numbers; with two, the first generally covers positive numbers and zero while the second covers negatives; with three, the sections distinguish positive, negative, and zero values. A fourth section can define text display.
Useful codes and practical examples
Try these examples in the Type box. Select a cell containing a representative value first so you can inspect the preview and confirm that the result matches your intent.
0displays a whole number with no decimal places. Excel rounds the displayed value; it does not remove the stored decimal places.0.00always displays two decimal places, including trailing zeros. A value of 4.5 appears as 4.50.#,##0.00displays a thousands separator and two decimal places. For example, 12500.5 appears as 12,500.50 in a locale using a comma for grouping and a period for decimals.0.0%displays a number as a percentage with one decimal place. A stored value of 0.256 appears as 25.6%; a stored value of 25.6 appears as 2,560.0% because percent formatting multiplies the display by 100.00000pads a numeric value with leading zeros to five positions. The number 73 appears as 00073, but a six-digit value remains six digits.#,##0.00;[Red](#,##0.00);"-"displays positive values normally, negative values in red parentheses, and zero as a dash. The quoted dash is literal text in the display.0.0,,"M"scales a number by one million for display and adds M. A value of 1250000 appears as 1.3M because the format rounds the displayed result to one decimal place.
In a number format, a comma between digit placeholders can group thousands, as in #,##0. One or more commas after the last digit placeholder instead scale the displayed number by thousands for each comma. That scaling affects display only. Quoted text adds a literal label, while a backslash can escape a following character. Use these features carefully: a label such as M does not make a value a true text description or change its units in calculations.
Formatting negatives, zeros, and text
Semicolon sections let one code handle common presentation rules without changing values or adding extra columns. For example, #,##0;[Red](#,##0);"-" displays whole-number positives with grouping, negative values in red parentheses, and zero as a dash. The color name in brackets is part of the format code. Excel supports a limited set of named colors; if you need more flexible rules, use conditional formatting instead.
To add a label after every number, place the text in quotation marks, such as 0 "items". The number 6 then appears as 6 items but remains numeric. The fourth section is for text; the @ placeholder represents the cell’s text. For example, 0;[Red](0);"-";@ formats the first three number cases and displays text as entered. Keep literal labels in quotation marks so Excel does not interpret their characters as format instructions.
Custom formatting does not create missing data. If a cell is empty, a numeric format will not make it a numeric zero. Likewise, formatting a number with leading zeros is different from storing an identifier as text: 00000 makes 73 look like 00073, but the underlying value remains 73. For codes where the zeros are meaningful and must be preserved when data is exported or imported, store the values as text or use an appropriate import setting.
Dates, times, and elapsed time
Excel stores dates and times as serial numbers, so a custom date or time format changes the display of a numeric value rather than its underlying storage. Common examples include m/d/yy for a compact date, mmm d, yyyy for a month name and four-digit year, and h:mm AM/PM for a 12-hour clock. The same date can therefore appear in different ways in different cells without changing the date value.
Letter case and context matter in date codes. In Excel format codes, m can represent a month or minutes; when it follows an hour code or appears in a time context, it represents minutes. For example, h:mm displays hours and minutes, while mmm displays an abbreviated month name. If a date appears as a number, apply a date format; if a code intended for minutes shows a month, review the surrounding time tokens.
For durations that can exceed 24 hours, use square brackets around the elapsed-hours token: [h]:mm or [h]:mm:ss. Without brackets, hours normally cycle through a clock day, so a 26-hour duration can display as 2:00 rather than 26:00. This format is helpful for elapsed time, but it does not calculate a duration for you; the cell still needs to contain a valid time or duration value.
Choosing the right format for the job
Choose a format based on what readers need to understand, not merely on how many characters fit on screen. Fixed decimals suit prices or measurements that must appear consistently; optional decimals can make variable-precision results more compact. Percent formatting is appropriate when the stored value is a proportion such as 0.25 for 25%, but applying it to a value already entered as 25 changes its displayed scale.
For identifiers such as postal codes, product codes, and account numbers, first decide whether the data is truly numeric. If arithmetic is meaningful, a numeric value with a custom zero-padding format may be suitable. If the characters are an identifier and leading zeros must survive copying, exporting, or importing, text storage is often safer. Number formats control appearance inside Excel; other programs may not retain the same display rules when data is transferred.
Use the built-in Currency or Accounting formats when their standard choices meet your needs, and use a custom format when you need a specific combination of symbols, decimals, or negative-number styles. A custom code can include a currency symbol as a literal, such as "$"#,##0.00, but symbols, decimal marks, and grouping conventions depend on regional settings. If a workbook is shared internationally, verify how it appears under the recipients’ locale settings.
Common problems and how to fix them
- The code appears literally in the cell. Check that you entered it in Format Cells > Number > Custom > Type, rather than typing it into the worksheet. Confirm that the cell is not being treated as text.
- Leading zeros disappear. A numeric cell normally drops typed leading zeros. Apply a code such as
00000to display a fixed width, or store the identifier as text if the zeros must be part of the data itself. - A date or time looks wrong. Confirm that the cell contains a valid Excel date/time value and check whether
mmeans month or minutes in that context. For durations over one day, use[h]. - Values do not line up with the display. A custom format may hide decimals or scale a number with commas. Select the cell and inspect the formula bar to check the stored value.
- A format works on one computer but looks different elsewhere. Regional settings can affect date order, decimal symbols, and digit grouping. Use unambiguous date displays where possible and test shared workbooks in the intended locale.
A frequent misconception is that a format code changes the data. It does not: changing 0.00 to 0 hides decimal places but does not round the stored value for later calculations. If you need an actual rounded result, use an appropriate rounding formula in a separate calculation; do not rely on a display format to alter the math.
Custom formats versus other Excel tools
Use a custom number format when the same value should remain numeric but appear differently—for example, a quantity followed by a label, a negative value in parentheses, or a date shown with a month name. Use conditional formatting when the appearance should depend on a rule or should be visually highlighted across a range. Conditional formatting is easier to maintain for thresholds that change, such as highlighting all values below a target.
Use a formula or a separate text column when you need to build a new text result rather than simply change a cell’s appearance. A custom format does not produce a different value for copying into another system, nor does it add literal characters to the formula bar. If downstream software needs the formatted characters as actual text, create text explicitly or export through a process that preserves the required representation.
Custom formats are stored with the workbook and can be reused by applying them to other cells in that workbook. If a colleague opens the file in an application that does not support the same format features, the display may differ even though the stored numeric values remain. For important reports, check both the worksheet view and any exported PDF or data file.
Frequently Asked Questions
Can I create a custom number format without changing the value?
Yes. A custom number format changes the cell’s display, while the underlying value remains the same. For example, formatting 73 with 00000 displays 00073, but Excel still treats the value as 73 for arithmetic.
How do I show a dash instead of zero in Excel?
Select the cells, open Format Cells with Ctrl+1, choose Custom, and use a three-section code such as #,##0;(#,##0);"-". The third section controls zeros. The dash is only a display choice; the cell still contains zero.
How do I keep leading zeros in Excel?
For numeric values that only need to look padded, apply a format such as 00000. For identifiers whose leading zeros must remain part of the stored content, enter or import them as text. A display format alone does not turn a number into text.
Can custom number formats add words or symbols?
Yes. Put literal text in quotation marks, as in 0 "days", or include a symbol as a literal in the format code. The words and symbols change the display, not the numeric value or its units in a calculation.
Why does my custom format disappear when I send the file?
Custom formats are saved in the workbook, but another application or a conversion process may not interpret them identically. Share the original workbook when recipients need to retain the format, and check the result after exporting to a different file type.
Related
- Excel Conditional Formatting Based on Color: How It Works
- Conditional Formatting in Excel: Compare Dates
- Comparison Spreadsheet Template Excel: Setup and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Model Budget Spreadsheet: How to Build a Monthly BudgetA model budget spreadsheet is a reusable worksheet for comparing planned income and outgoings with what actually happens.…
- Excel Reference Table in a Data Validation ListYes. In Excel for Microsoft 365 on Windows, convert the source values into an Excel Table, define a…
- Google Sheets IFS Function: Syntax, Examples, and TipsGoogle Sheets’ IFS function checks conditions in order and returns the value paired with the first condition that…
- How to Find Interquartile Range in ExcelTo find the interquartile range (IQR) in Excel, subtract the first quartile from the third quartile. With your…
- Add Conditional Formatting in Excel: Rules and TipsTo add conditional formatting in Excel for Microsoft 365 on Windows, select the cells you want to format,…
- How to Delete a Blank First Page in Microsoft WordTo delete a blank first page in Microsoft Word, show formatting marks, remove any empty paragraphs or page…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.