A Microsoft Excel custom number format changes how a cell’s value looks 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 format code in the Type box, and select OK.
Custom formats are useful when standard choices such as Currency, Percentage, or Date do not show information the way you need. They can add units, preserve leading zeros in a display, show negative values in parentheses, or make large figures easier to scan. The key distinction is that formatting changes appearance, not the underlying data.
What a custom number format does
Excel stores a cell’s contents as a number, date serial value, text string, or another underlying type. A number format tells Excel how to present a numeric value on screen and in printed output. For example, if a cell contains the number 0.25, a percentage format can display it as 25%, while the stored value remains 0.25. Changing the format does not calculate a new value or turn the cell into text.
This makes custom formats valuable for reports and data entry templates. A quantity can display with a unit such as “kg,” a project code can display with leading zeros, and a negative amount can be visually distinguished from a positive one. Because the cell still contains its original value, Excel can continue to sort, add, compare, and chart it as a number.
Formatting is not the same as replacing a value. If a number is displayed as 1.2, the cell might still contain 1.23456; the extra digits are simply not shown. To inspect the stored value, select the cell and look at the formula bar, or increase the displayed decimal places.
How format codes are structured
A custom format can have 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. You do not have to supply all four sections.
0.00applies one display pattern to numbers, including positive and negative values.0.00;[Red](0.00)gives positive values two decimal places and shows negative values in red parentheses.0.00;[Red](0.00);-adds a dash for zero.0.00;[Red](0.00);-;@also specifies that text should display as entered.
The symbols in a format code act as instructions. The most commonly used digit placeholders are 0, #, and ?. A zero placeholder forces a digit to appear, including a zero when there is no corresponding digit. A number sign displays a digit when one is present but does not force extra zeros. A question mark reserves a character space for a missing digit, which can help align decimal points or fractions.
For instance, the code 00000 displays the number 42 as 00042. By contrast, ##### does not add leading zeros. These are display instructions: the value remains 42. If those zeros are part of an identifier that must be preserved when data is exported or transferred, consider storing the identifier as text instead.
How to create and apply a custom format
- Select one cell or a range of cells that should share the format.
- On Windows, press Ctrl+1 to open Format Cells. You can also use the dialog launcher in the Home tab’s Number group.
- Open the Number tab and select Custom in the Category list.
- Choose a similar existing format if one is available, then edit its code in the Type box, or type a new code.
- Select OK and check several representative cells, including zero, negative, or unusually large values if those cases occur in your data.
Excel may add a newly created custom format to the workbook’s available formats. The exact list can grow as formats are created or copied in, so it is worth editing a suitable existing code rather than making many nearly identical versions. To adjust a format later, select a cell using it, reopen Format Cells > Number > Custom, edit the Type entry, and confirm.
In Excel for Mac, select the cells and open Format Cells with Command+1, then use the Number tab and Custom category. Ribbon layout and keyboard behavior can vary with settings and releases, so the Format Cells dialog is the reliable place to enter and review codes. Excel for the web has its own interface and may not expose every desktop formatting control in the same place; for complex custom formats, use the desktop app if the needed option is unavailable.
Useful examples for everyday spreadsheets
Custom formats can solve common presentation problems without extra formulas or helper columns. The examples below assume the cells already contain appropriate numeric values.
- Show a fixed number of decimal places:
0.00displays 3 as 3.00 and 3.456 as 3.46. The second value is rounded for display, not changed. - Show thousands separators:
#,##0displays 1250000 as 1,250,000 in a typical English-language setting. Regional settings may use different separators. - Display negative values in parentheses:
#,##0;(#,##0)displays -2500 as (2,500). - Append a unit:
0.0 "kg"displays 4.2 as 4.2 kg. Quotation marks mark kg as literal text, rather than a formatting instruction. - Show a percentage:
0.0%displays 0.125 as 12.5%. Percentage formatting scales the displayed value by 100; it does not multiply the stored value. - Show a phone-number pattern:
(000) 000-0000can display a ten-digit numeric entry in that pattern. It is a visual mask, not a validation rule, and is not suitable for every country or numbering plan. - Display a date clearly:
mmm d, yyyycan display a valid Excel date as Nov 3, 2026, according to the cell’s date value and regional settings. - Show elapsed hours:
[h]:mmdisplays a duration as total hours and minutes, including totals greater than 24 hours.
In custom formats, commas can have two different roles. Between digit placeholders, they usually mark thousands grouping; commas placed after a number placeholder can scale a value by thousands for display. For example, 0.0,, "M" displays 1250000 as 1.3 M after rounding. The underlying value remains 1250000. Test scaling codes with realistic numbers, because they can make a report appear to contain smaller values than the cells actually hold.
Dates, times, and elapsed time
Excel generally stores dates as serial numbers and times as fractions of a day, then uses a number format to display them as calendar dates or clock times. A custom date code can change the presentation without changing the date. Common date elements include d for day, m for month, and y for year. Repeating a letter changes the presentation: for example, mmmm displays a full month name, while mm displays a two-digit month.
The letter m can mean either month or minute, depending on its position in a time format. Near hours or seconds, it is interpreted as minutes; in a date pattern, it represents the month. A time format such as h:mm AM/PM displays a 12-hour clock. For durations, use square brackets around the hour token: [h]:mm. Without brackets, a time total can wrap around after 24 hours, which may make a long duration look misleading.
A format cannot turn ordinary text into a real date. If a date-looking entry is stored as text, applying a date code may have no visible effect. Confirm that Excel recognizes the entry as a date before troubleshooting the format itself. Date interpretation can also depend on regional settings, especially for entries such as 4/5/2026, which may be read differently across locales.
Choosing placeholders, literals, and colors
Choose placeholders according to what readers need to see. Use 0 where fixed digits are important, such as a standard number of decimal places or a padded display. Use # when unnecessary zeros should disappear, as in 0.##, which can show 2.5 as 2.5 and 2 as 2. Use ? when aligning values with different digit counts is more important than a compact display.
To include words or symbols that Excel might otherwise interpret as code, enclose literal text in quotation marks, as in 0 "items". Some punctuation can be entered directly; a backslash can also make the next character display literally. Use the format preview and test the code rather than assuming a symbol will behave the same way in every regional configuration. Currency symbols and separators can depend on the system’s locale.
Color names can be included in brackets in a section, such as [Red]. Excel supports a set of named colors for custom formats, but color should not be the only way important information is conveyed. A reader viewing a printout, a different theme, or a workbook with accessibility needs may not distinguish the color reliably. Pair color with a clear convention, such as parentheses for negative amounts.
Custom formats compared with formulas and text
Use a custom number format when the goal is to change only the display. Use a formula when you need to calculate a different result, combine values, or produce text for an export. The distinction matters because a formatted number remains numeric, while a formula that returns a text string may no longer behave like a number in calculations or sorting.
For example, displaying a number with a unit using 0 "kg" keeps the value numeric, so it can still be summed. Typing “4.2 kg” directly into the cell creates text, which typically cannot be added as a numeric quantity without conversion. Conversely, if an external system requires the literal characters including leading zeros, a number format alone may not be enough: the receiving system may read the underlying value rather than Excel’s displayed appearance.
Custom formats also differ from conditional formatting. A number format can provide sections and some condition-based display rules within its code, but conditional formatting is the better tool for rules that change cell appearance based on other cells, formulas, or more elaborate criteria. Conditional formatting can change fills, borders, and font styles; a custom number format focuses on how the cell’s value is represented.
Common problems and how to fix them
- The code appears as text: Confirm that you entered it in Format Cells > Number > Custom, not into the worksheet cell itself. Also check that the cell is not formatted as Text before entry.
- Leading zeros disappear: The numeric value does not store them. A code such as
00000can show them for a fixed-width display, but use text for identifiers that must retain the zeros outside Excel. - A date format does nothing: The entry may be text rather than a date serial value. Convert or re-enter it as a recognized date, then apply the format.
- Unexpected decimals or rounding appear: The format controls display precision. Increase decimal places or inspect the formula bar before concluding that the stored value changed.
- Commas, periods, or dates look different: Excel follows regional conventions for separators and date interpretation. A code created on one system may display differently on another.
- Large values show hash marks: The column may be too narrow to display the formatted value. Widen the column or use a more compact format; the hash marks do not usually mean the value has been erased.
- The format is missing in another workbook: Custom formats are associated with workbooks. Copying cells or moving data between files can affect which custom formats are available, so check the destination workbook’s Format Cells dialog.
One common misconception is that a custom format changes the value itself. It does not. If a value needs to be rounded for a later calculation, use an appropriate rounding formula or change the data through a deliberate calculation; merely displaying fewer decimal places does not remove the hidden precision.
Frequently Asked Questions
How do I open Custom Number Format in Excel?
Select the cells, press Ctrl+1 on Windows, choose Number > Custom, and edit the Type box. On Mac, Command+1 opens Format Cells; select the Number tab and Custom category.
Can a custom number format add text without changing a number into text?
Yes. Put literal text in quotation marks in the code, such as 0 "days". The displayed result can include the word days while the cell remains numeric for calculations.
Does a custom format round the value in the cell?
No. It may round the displayed digits, but the underlying value retains its precision. Use a calculation if the value itself must be rounded.
How do I show leading zeros in Excel?
For a fixed-width numeric display, apply a code such as 00000. If the zeros are part of an identifier that must travel unchanged to another system, store the identifier as text instead.
Can I use a custom format to make a cell accept only certain values?
No. A number format affects appearance, not data-entry rules. Use Data Validation when you need to restrict what users can enter, and check the entered values separately if the workbook has special requirements.
Saving and sharing custom formats
Custom formats are useful workbook-level presentation tools, but they should be documented when they carry meaning that is not obvious. A code that turns 1250000 into 1.3 M, for example, is convenient only if readers know the displayed number is scaled. Add a clear column heading or note such as “Values in millions” when that interpretation matters.
Before sharing a workbook, check how important formats appear in the destination environment and whether the recipients need the underlying values or only the displayed report. If the workbook will be converted to CSV or imported into another application, remember that file formats that store plain text data generally do not preserve Excel’s custom display instructions. For reliable exchanges, provide explicit headings, units, and data in the structure the receiving system expects.
Related
- Leave Calendar Template in Excel: Build or Download
- Laundry List Template in Excel: Build and Use One
- Kids Chore List Excel Template: Build a Simple Tracker
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert a Row in a QuickBooks Journal Entry CorrectlyTo insert a row in a QuickBooks journal entry, open the entry and add a line: in QuickBooks…
- How to Make 2 Columns in Word for Part of a PageTo make only part of a page two columns in Word, select the text, then choose Layout >…
- Excel Yes or No Drop-Down: Create and Customize ItYes. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation > Data…
- Find and Replace Duplicates in Excel: Safe MethodsTo find duplicates in Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules…
- How to Change the View in Outlook Sent ItemsTo change how sent messages appear, open the Sent Items folder and use Outlook’s view controls: in classic…
- Google Sheets English Date Format: Set Dates CorrectlyTo display dates in English in Google Sheets, select the date cells and choose Format > Number >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.