Excel custom number formats let you change how a cell’s value looks without changing the value itself. Select the cells, press Ctrl+1 on Windows, choose Number > Custom, enter a format code, and select OK; the same number can then appear as a date, a percentage, a rounded amount, or text with leading zeros while remaining available for calculations in its original form.
What custom number formats do
A number format controls the display of a value in a cell. It does not rewrite the stored number. For example, if cell A1 contains 427 and you apply the format code 00000, Excel displays 00427. A calculation using A1 still uses the number 427.
This distinction is useful when a value needs a particular presentation but must remain numeric. You can show a sales figure with a currency symbol, hide decimal places on a report, add a unit label, or display a date in a consistent style. It is also the source of a common misunderstanding: formatting a number to look like text does not turn it into text. If a value is an identifier that should never be calculated, storing it as text may be a better choice.
Custom formats are useful for presentation, not data cleanup. They will not fix an incorrect value, add missing digits to the underlying data, or change how a formula calculates. A format can make a worksheet easier to read while preserving the original data for sorting, arithmetic, and charts.
Where to create and apply a format
In Excel for Microsoft 365 on Windows, select one or more cells and press Ctrl+1 to open Format Cells. Choose the Number tab, select Custom in the Category list, and type a code in the Type box. You can also reach the Format Cells dialog from the small dialog launcher in the Home tab’s Number group. On a Mac, Command+1 opens Format Cells. Excel for the web can display many formats, but its controls for creating and editing custom codes may be more limited; use desktop Excel when you need the full Custom category.
The sample formats shown in the Type list can be a useful starting point. You can modify a listed code or enter a new one. The format applies to the selected cells, and the code becomes available in that workbook’s custom format list. Applying a format to a range is generally easier than repeatedly setting it cell by cell.
To reuse a format elsewhere, you can copy a formatted cell and use Paste Special > Formats, or use Format Painter. These methods copy appearance settings, so check that they do not overwrite other formatting you intended to keep. If you share a workbook, the custom code is stored with it; the recipient does not have to install a separate format file.
How the four format sections work
A custom number format can contain as many as four sections separated by semicolons. In the usual arrangement, the sections describe positive numbers, negative numbers, zero, and text, in that order. The sections are optional, but their position matters.
#,##0.00uses one section for all numeric values.#,##0.00;(#,##0.00)uses the first section for positive numbers and zero, and the second for negative numbers.#,##0.00;[Red](#,##0.00);-gives positive values two decimals, shows negative values in red parentheses, and displays zero as a dash.0;[Red]-0;0;@includes a fourth section, which uses the text placeholder@for text values.
With only two sections, the second is for negative numbers; it does not mean “negative numbers and text.” Text is normally displayed as entered unless a text section is specified. When a format has four sections, the fourth section is the one for text. A section containing a dash or other literal can make a zero look blank or marked, but it does not remove the zero from the cell.
Excel also allows conditions in the first two sections, such as [>=1000]. A format like [>=1000]0.0;0.00 displays values of 1,000 or more with one decimal place and other values with two. Conditional sections are different from the ordinary positive/negative/zero arrangement: Excel evaluates the conditions, and an optional third numeric section can handle values that do not meet either condition. Test a conditional code with values on both sides of each boundary.
Placeholders, separators, and useful examples
The most common numeric placeholders are 0, #, and ?. A zero displays a digit even when the value has no digit in that position. A number sign displays a digit when one is present but does not add an unnecessary zero. A question mark reserves space for an absent digit, which can help line up decimals. These characters control display; they do not set a value’s precision.
0.00always shows two decimal places, so 8 appears as 8.00.0.##shows up to two decimal places without requiring trailing zeros, so 8.5 appears as 8.5 and 8 appears as 8.#,##0adds a thousands separator and displays no decimal places.00000pads a number to at least five displayed digits; 427 appears as 00427.0.0%displays a value as a percentage multiplied by 100; 0.125 appears as 12.5%.0.0 "kg"appends the literal label kg, so a value of 3.2 appears as 3.2 kg.
A comma between digit placeholders groups thousands. A comma at the end of a numeric pattern scales the displayed number by 1,000, and two trailing commas scale it by 1,000,000. For example, 0.0, displays 12500 as 12.5. This scaling is only visual: the stored value remains 12500. It can be useful in a compact report, but label the units clearly so readers do not mistake thousands for individual units.
To include text such as a unit or punctuation, put it in double quotation marks in the format code, as in 0.0 "hours". A backslash can also make the next character display literally. Use these techniques rather than assuming every letter is treated as ordinary text, because some characters have special formatting meanings.
Formatting dates, times, and elapsed time
Excel stores dates and times as numeric serial values, then uses a date or time format to display them. A date format such as ddd, mmm d, yyyy can show a date as a weekday abbreviation, month abbreviation, day, and four-digit year. The underlying serial value stays the same, so the date can still be compared or used in calculations.
In date codes, d and dd show the day as one or two digits; ddd and dddd show abbreviated or full weekday names. Similarly, mmm and mmmm show abbreviated or full month names, while yy and yyyy control the year display. The letter m can mean month or minute depending on context. In a code such as h:mm, it represents minutes; near a day or year code, it represents a month. Check a sample cell when building a date-and-time pattern.
For a duration, use square brackets around the elapsed-time unit. For example, [h]:mm can display a total of 27 hours as 27:00. Without brackets, an hours format commonly wraps around after 24 hours, which is appropriate for a time of day but misleading for a total duration. A format changes how the serial value is shown; it does not turn a date into a duration or repair text that merely looks like a date.
Colors, conditions, and literal display choices
A format section can include a color name in square brackets, such as [Red], or a condition such as [<0]. For instance, a negative-number section can put a value in parentheses and color it red. The color applies to the cell’s displayed number, not its fill color, and it is not the same feature as Conditional Formatting. Conditional Formatting is usually better when a cell’s appearance should respond to a rule across a changing range or when you need to format fills, fonts, or other visual properties.
Some codes include a star or underscore for alignment effects. The asterisk repeats the next character to fill available space; the underscore reserves space equal to the next character. These can help align symbols in an accounting-style display, but they are less important for everyday formats and can look confusing when copied into a narrow column. Use them only when the layout benefit is clear.
What formatting changes—and what it does not
Displayed rounding is not the same as rounding the stored value. If a cell contains 2.678 and the format shows two decimal places, Excel displays 2.68, but calculations generally continue to use 2.678. That matters in totals: a column can show rounded values whose displayed sum differs slightly from a total calculated from the full underlying values. If the calculation itself must use rounded numbers, use an appropriate rounding formula or change the data, rather than relying on a number format alone.
Likewise, a custom format that displays a telephone number, product code, or leading zeros does not make the cell’s content text. This can be convenient for numeric identifiers of fixed length, but it is not always safe. Very long identifiers can exceed Excel’s reliable numeric precision, and identifiers may contain letters or meaningful leading zeros. Store those as text when preserving each character matters. A custom format is best when the underlying value is genuinely numeric and the added display is only a view.
Custom formats also do not change the value used by a chart or copied into a formula. If you need a formatted result as text for a label or export, Excel’s TEXT function can create text from a value and a format code, but that result is no longer numeric for ordinary arithmetic. Keep the original numeric cell if later calculations are needed.
Common problems and how to diagnose them
- The code appears literally. Check whether the cell is stored as text or whether the format was entered with an unintended text section. Select the cell and inspect the formula bar and Format Cells category.
- Leading zeros disappear. Confirm that the cell contains a number and that the number of zero placeholders matches the desired minimum width. For variable-length IDs that must retain typed zeros, use text instead.
- A date becomes a number. The value may be a valid date serial displayed with a General or numeric format. Apply a date format; if it remains unchanged, the cell may contain text rather than a date value.
- Negative values or zero look wrong. Count the semicolon-separated sections and remember their order. A three-section format assigns its third section to zero.
- A displayed total seems inconsistent. Compare the shown decimals with the underlying values. The format may hide fractions that still contribute to the calculation.
When a format behaves unexpectedly, test it on a few known values: a positive number, a negative number, zero, a date if relevant, and text if the code has four sections. Also check regional settings. Decimal and thousands separators, date conventions, and currency symbols can differ by locale, so a code shared between computers may not look identical in every environment.
Frequently Asked Questions
Do custom number formats change the actual value?
No. They change how Excel displays the value. Formulas and calculations continue to use the underlying value unless you separately change it.
Can I use a custom format to add leading zeros?
Yes. Apply a code such as 00000 to numeric values that should display with at least five digits. For codes where each typed character must be preserved, including long IDs, store the data as text instead.
How do I show zero as a dash?
Use a three-section code with the zero section set to a dash, such as #,##0.00;(#,##0.00);-. The underlying zero remains in the cell.
Can a custom format round a number for calculations?
No. It can round the display, but not the stored value used by formulas. Use a rounding formula when calculations must use rounded results.
Can I copy custom formats to another workbook?
Yes. Copy cells and paste formats, or use Format Painter, then check the destination cells. The copied appearance may include other formatting as well, so review the result if the destination has its own styling.
Related
- Simple Checklist Template Excel: Build a Useful One
- Simple Inventory Tracking in Excel: A Practical Setup
- Excel Insert Row Shortcut: Windows, Mac, and Tips Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Creating a Flowchart in Word: Steps and Practical TipsTo create a flowchart in Microsoft Word for Microsoft 365 on Windows, choose Insert > Shapes, add shapes…
- Excel File Format Corrupted? Causes, Fixes, and RecoveryIf Excel says a file format or file extension is invalid, or a workbook will not open, the…
- How to Make a Poster from PowerPoint SlidesTo make a poster from PowerPoint slides, create a slide at the poster’s final page size, arrange the…
- Password Protect an Excel Document From OpeningYes. In Excel for Microsoft 365 on Windows, open the workbook and choose File > Info > Protect…
- Present PowerPoint in Teams With Notes: How It WorksYes. Microsoft Teams can present a PowerPoint while you privately view your speaker notes by using PowerPoint Live:…
- Find Same Names in Two Excel ColumnsTo find whether names in column A also appear in column B, enter =IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Not found") in C2, assuming…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.