To create 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 appears—not the value stored in the cell—so it can show numbers as dates, percentages, IDs, or rounded figures without changing the underlying data.
What Excel custom number formats do
A number format is a display rule for a cell. Excel still stores the original number, date serial, time, or text; the format controls how that content is shown. For example, a cell containing the number 7 can display as 7.00, 007, 700%, or Jul 7 if the code and underlying value call for those displays. The formula bar generally continues to show the underlying value rather than the formatted appearance.
This distinction matters when calculating. If a cell contains 0.25 and you apply a percentage format, Excel displays 25% while retaining 0.25 for arithmetic. A custom format is therefore different from a formula that converts data, and different from replacing the value with typed text. Formatting is useful for presentation while preserving numeric behavior for totals, sorting, and calculations.
Excel includes built-in formats for common needs, but a custom format lets you specify details such as leading zeros, parentheses for negative values, a dash for zero, or a particular date layout. A workbook can also contain a format code that you can reuse in other cells.
How to enter or edit a custom format
- Select the cells you want to format. To apply a format to a whole column, select the column; to affect only a range, select that range.
- Press Ctrl+1 on Windows to open Format Cells. Alternatively, use Home > Number group and select the small dialog box launcher, then choose the Number tab.
- Select Custom in the Category list. Choose an existing code to edit it, or enter a new code in the Type box.
- Check the Sample preview, then select OK. If Excel rejects the code, check its punctuation, quotation marks, and section separators.
On a Mac, Command+1 opens Format Cells; the dialog and available codes are similar, though the interface can vary slightly by Excel release. Excel for the web offers common number-format controls, but some custom-format editing options may differ from the desktop app. If a workbook relies on a specialized code, check its appearance in the version and platform where it will be used.
To reuse a format, select another cell and choose the custom code from the Number Format list or reopen Format Cells and select it from the custom format list. Custom codes are associated with the workbook. If you copy cells between workbooks, Excel may also bring along formatting, and the destination workbook can acquire that custom format.
Reading the format-code symbols
Most custom format codes use a small set of symbols to decide what digits appear. The key digit placeholders are:
0displays a digit even when the value has no digit in that position. Use it when you need a fixed number of decimal places or leading zeros.#displays a digit only when needed. Optional trailing decimal zeros are not shown.?reserves space for an insignificant digit, which can help align decimal points or fractions in a column.
For instance, 0.00 shows 3 as 3.00, while #.## can show 3.5 as 3.5 and 3 as 3. The comma can group thousands, as in #,##0. A comma after the final digit placeholder scales the displayed number by 1,000: 0, displays 125000 as 125. Two trailing commas scale by a million, so 0.0,, displays 1250000 as 1.3. These commas affect display only; they do not divide the stored value.
A percent sign multiplies the displayed value by 100. Thus, a value of 0.125 shown with 0.0% appears as 12.5%. For scientific notation, a code such as 0.00E+00 can display a large or small value in exponential form. Test a code on representative values before applying it to a whole report, especially where rounding could obscure meaningful precision.
Use sections for positive, negative, zero, and text
A format can contain as many as four sections separated by semicolons. In the usual arrangement, they describe positive numbers, negative numbers, zero, and text, in that order. For example, #,##0.00;[Red](#,##0.00);"–";@ displays positive numbers with two decimals, negative numbers in red parentheses, zero as a dash, and text as entered. The @ placeholder stands for the cell’s text.
You do not have to define all four sections. With two sections, the first generally controls positive numbers and zero, and the second controls negative numbers. With one section, the same pattern is used for positive and negative values, with negatives distinguished by a minus sign. Because section rules can be less obvious when conditions are introduced, a quick test with a positive number, a negative number, zero, and text is a practical check.
Color names such as [Red] can be included in a section. Excel also recognizes other named colors, including [Blue], [Green], and [Yellow]. Colors in a format are a display choice, not a substitute for conditional formatting when colors need to depend on changing rules or comparisons between cells.
Conditions and literal text
Conditions let a section apply only to values that meet a test. For example, [>=1000]0.0,"K";0 displays values of at least 1,000 in thousands with a K suffix, while other values use the second section. Here, 2500 appears as 2.5K and 750 appears as 750. A format can use conditions such as greater than, less than, or equal to comparisons. Excel permits conditions in the first two sections; when conditions are used, the format’s section behavior differs from the ordinary positive/negative/zero sequence, so test both matching and nonmatching values.
To show fixed words or punctuation, enclose literal text in double quotation marks. For example, 0.00 "kg" displays 4.5 as 4.50 kg while preserving the numeric value. A backslash can also make the next character display literally. Do not add words directly to a code without quoting or escaping them, because characters such as E, M, or D may have special meaning in number formats.
Useful examples for everyday spreadsheets
- Leading-zero codes:
00000displays the number 73 as 00073. This works well for fixed-width numeric identifiers, but it does not change the stored number. - Readable amounts:
$#,##0.00;[Red]($#,##0.00);"–"displays dollar amounts with two decimals, uses red parentheses for negatives, and shows zero as a dash. Choose a currency symbol appropriate to the workbook’s context. - Compact thousands:
0.0,"K"displays 12500 as 12.5K. The comma scales the display, and the quoted K labels the result. The underlying value remains 12500. - Fixed date appearance:
dd-mmm-yyyycan display a valid Excel date as 03-Nov-2026. Dates are stored as serial values, so this code changes their appearance, not the date itself. - Elapsed hours:
[h]:mmcan show a duration longer than one day, such as 37:30. The square brackets allow total elapsed hours instead of resetting the hour display at 24.
These examples assume the cells contain appropriate numeric or date values. A format cannot turn text such as a typed product code into a true number or repair an invalid date. For identifiers where leading zeros are meaningful, storing the value as text may be a better choice if the data must retain those characters independently of its display.
Date and time codes have special rules
In date formats, d represents the day, m the month, and y the year. Repeating a letter can change the display: dd gives a two-digit day, while mmmm gives the full month name. In time formats, h represents hours and s seconds. The letter m can mean either month or minute; in a time pattern, its position alongside hours or seconds helps Excel interpret it as minutes. For example, h:mm displays hours and minutes, while m/d/yy displays a date.
For durations, use bracketed units when totals may exceed a normal clock cycle. [h]:mm:ss can display cumulative hours, minutes, and seconds rather than rolling over after 24 hours. This is useful for elapsed work time or processing durations. A regular time format such as h:mm is appropriate for a time of day, but may be misleading for a multi-day duration.
Date displays can also depend on regional settings. A code that uses month-first ordering may be familiar in one locale and confusing in another. Prefer an unambiguous display such as dd-mmm-yyyy in shared reports, and verify that the cell contains an actual date rather than a text string that merely looks like one.
Common problems and misconceptions
- The value appears as ####. The column may be too narrow for the formatted value. Widen the column or choose a shorter format. In some date or time cases, hashes can also indicate a negative date/time value that Excel cannot display normally.
- A leading zero disappears. The value is numeric and the current format does not request that zero. Apply a fixed-width code such as
00000, or store the entry as text if the characters are identifiers rather than quantities. - The displayed result seems rounded. A format can hide decimal places without rounding the stored value. Calculations typically continue to use the underlying precision. If a calculation itself must round, use an appropriate rounding formula rather than relying on appearance alone.
- The format does not turn text into a date or number. Custom formatting changes how compatible stored values appear. It does not parse arbitrary text or convert a value’s data type.
- The custom appearance vanishes in a CSV file. CSV stores plain text values, not Excel’s cell formatting rules. Exported content may therefore show the underlying values rather than the workbook’s styled presentation.
Another common misconception is that a custom format protects sensitive data. It does not: hiding digits with a display format does not remove them from the workbook. Use appropriate data handling and access controls rather than relying on formatting to conceal information.
How to choose the right format
Start with the meaning of the data, not just the look you want. A quantity should remain a number if people will total or compare it. An account code or postal code may be better treated as text if arithmetic has no meaning and every character must remain fixed. For dates, decide whether the audience needs a compact numeric form or a month name that reduces ambiguity. For money, select decimals and symbols that suit the report’s purpose, and avoid formats that imply a precision the data does not support.
Before applying a code broadly, test values at the boundaries: zero, a negative value, a value with several decimal places, a large value, and a text entry if the range mixes data types. Check that the displayed rounding is acceptable, the columns fit, and the result is understandable without relying on color alone. If users will filter, copy, export, or open the workbook in different spreadsheet apps, keep the code straightforward and verify its appearance in the destination environment.
Frequently Asked Questions
Does a custom number format change the actual cell value?
No. It changes the display, while the underlying value remains available for calculations. For example, a percentage format can display 0.25 as 25% without changing 0.25.
How do I add leading zeros in Excel without changing the value?
Select the cells, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter a code such as 00000. A value of 73 then displays as 00073. If the zeros are essential characters in an identifier, consider storing the entry as text instead.
Can I use a custom number format to round a value?
A format can display fewer decimal places, making a value look rounded, but it does not ordinarily change the stored precision. Use a rounding calculation if the value used in later calculations must itself be rounded.
How do I show zero as a dash?
Use a zero section in a multi-section format. For example, 0;-0;"–" displays positive numbers normally, negative numbers with a minus sign, and zero as a dash.
Why does Excel show hashes instead of my date or number?
First widen the column; the formatted result may not fit. If hashes remain for a date or time, check whether the value is valid and whether a negative date/time result is being displayed.
Related
- How to Ungroup Excel Windows on the Windows 11 Taskbar
- How to Ungroup Rows in Excel With a Keyboard Shortcut
- How to Undo in Excel With a Keyboard Shortcut on Windows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Print Area: Where to Find, Set, and Clear ItIn Excel for Microsoft 365 on Windows, find Print Area on the Page Layout tab: choose Page Layout…
- Track Changes in Word Anonymously Without Exposing NamesTo track changes in Word without showing your personal name, first set Word’s author information to a neutral…
- How to Insert a Gray Line in Word: Shapes and BordersTo insert a gray line in Microsoft Word, go to Insert > Shapes > Line, draw the line,…
- Camping Meal Planner Spreadsheet: Plan Meals and Pack FoodA camping meal planner spreadsheet helps you map meals to each day, estimate how much food to bring,…
- How Long Does Outlook Archive Keep Emails?Outlook does not set one universal expiration date for archived emails. Messages in an Archive folder generally stay…
- Excel Cell Value Based on Another Cell?To make an Excel cell show a value based on another cell, enter a formula in the result…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.