Excel custom number formatting uses format codes made from placeholders, symbols, and optional sections separated by semicolons. A code can change how a number, date, time, or text value looks in a cell without changing the value stored there; for example, 00000 displays the number 123 as 00123.
Where to enter a custom format
In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, and choose Number > Custom in the Format Cells dialog. You can also open the dialog box launcher in Home > Number. Type a code in the Type box and select OK. To make a copy of an existing format before editing, select it in the list and modify its code.
On a Mac, Command+1 opens Format Cells, although the dialog’s appearance and navigation can differ slightly. Excel for the web has a different interface and may not offer the same complete custom-format editing experience as desktop Excel. If a code is difficult to create or modify there, open the workbook in the desktop app. The syntax described below applies to Excel, but symbols such as decimal and thousands separators can appear differently under different regional settings.
The four-section syntax
A custom format can contain as many as four sections, with semicolons separating them. In the standard arrangement, they apply in this order:
- First section: positive numbers.
- Second section: negative numbers.
- Third section: zero values.
- Fourth section: text.
For example, 0.00;(0.00);"Zero";@ displays positive numbers with two decimal places, negative numbers in parentheses, zero as the word Zero, and text as entered. The @ symbol stands for the cell’s text. If a format has only one section, Excel generally applies it to all numbers. With two sections, the first applies to positive numbers and zero, and the second to negative numbers. With three, the sections distinguish positive, negative, and zero values.
Sections are optional, but their positions matter. A blank section means that category is displayed as blank. For instance, 0;-0;;@ leaves zero values visually empty while showing positive numbers, negative numbers, and text. The cell still contains zero; only its display is hidden.
Number placeholders: 0, #, and ?
The three core digit placeholders control how many digits Excel displays. They are similar, but they handle unused positions differently:
0displays a digit when present and adds a zero when needed. The format00000makes 123 appear as 00123.#displays a digit when present but does not add an unnecessary zero. The format##leaves a value of zero without a displayed digit.?reserves a blank space for an absent digit. This can help align values or fractions vertically.
To show thousands separators and two decimal places, use #,##0.00. A value such as 12345.6 appears as 12,345.60 in a locale that uses a comma for grouping and a period for decimals. In other locales, Excel may display different separator characters. The placeholders still express the intent: group digits, require at least one integer digit, and show two decimal places.
Percentages, scaling, and fractions
A percent sign in a number format multiplies the displayed number by 100 and adds the percent symbol. Thus, the format 0.0% displays an underlying value of 0.256 as 25.6%. It does not convert the stored value into 25.6; the underlying value remains 0.256. This distinction matters if the value is later used in a calculation.
A comma placed after the digit placeholders can scale a displayed value by thousands. For example, 0, displays 1250000 as 1250, while 0.0,, displays it as 1.3. The latter rounds to one decimal place after scaling by one million. These codes change the display, not the stored amount, so they should not be used when readers might mistake a shortened display for the actual value.
Fraction formats can display decimal values as fractions. For example, # ?/? can display 1.25 as 1 1/4. A question-mark placeholder can leave alignment spaces where a digit is absent. The number of placeholder digits affects how Excel represents the fraction; a format with more denominator placeholders can show finer fractions. For financial or scientific work, keep the actual numeric value and use a suitable display format rather than replacing the number with typed text.
Dates, times, and elapsed time
Excel stores dates and times as numeric values, so custom number formats can display those values as calendar dates, clock times, or durations. Common date and time codes include mmm d, yyyy for a month abbreviation, day, and four-digit year, and h:mm AM/PM for a 12-hour clock. A value’s appearance depends on its stored serial value and the selected format; changing a date’s format does not change the date itself.
The letter m can mean month or minute. In a date context, it usually means month; near an hour or seconds code, it can mean minutes. For example, h:mm shows hours and minutes, whereas mmm shows a month abbreviation. Square-bracket forms such as [h], [m], and [s] show elapsed hours, minutes, or seconds rather than cycling through clock units. A duration of 27 hours can therefore be shown with [h]:mm as 27:00 instead of wrapping to 3:00.
Excel can interpret dates differently across regional settings, and a string that looks like a date may be stored as text instead of a date serial. Applying a date format does not turn text into a real date. If changing the format has no effect, check whether the cell contains a numeric date value or text.
Literals, colors, and conditions
To include explanatory text or punctuation in a format, put literal text in quotation marks. For example, 0.0 "kg" displays 3.5 as 3.5 kg while keeping the cell numeric. A backslash can make the next character literal, and some characters can be included directly depending on the format. Quotation marks are usually the clearest option for suffixes and labels.
Square brackets have several special uses. They can specify colors, conditions, or elapsed-time units. A code such as [Red]0.00 displays the number in red. Excel also recognizes color names and indexed colors such as [Color10]. A condition can direct a section to values meeting a test, for example [>=1000]0.0,"K";0, which displays 1250 as 1.3K and uses the second section for other numeric values. Custom formats can use up to two conditions; if conditions are specified, Excel uses the matching section or the remaining section as the fallback. Test formats on positive, negative, and boundary values to make sure they cover the cases you intend.
Choosing a format for a real task
Start by deciding what the reader needs to see, then choose a code that preserves useful precision and makes the value’s meaning clear. A format is not a substitute for a label or a data type. For example, showing a value as a percentage does not prove it was entered as a percentage, and adding a currency symbol does not identify the currency reliably in every region.
- For identifiers: use a zero-padded format such as
00000when the identifier is numeric and has a fixed display width. If leading zeros are meaningful data, storing the identifier as text may be more appropriate. - For measurements: use a suitable decimal count and a quoted unit, such as
0.0 "cm". This keeps the value numeric for sorting and calculations. - For reports: use separate positive and negative sections when parentheses, colors, or a minus sign improve readability. Avoid hiding zeros if readers need to distinguish zero from missing data.
- For large totals: use scaling only when the heading or nearby note clearly states the unit, such as thousands or millions.
What custom formats can and cannot do
Custom number formatting is a presentation feature. It does not change the underlying number, perform a calculation, round the stored value, or replace conditional formatting. A displayed value of 1.3K may still be 1250 in the cell, and another formula will use the underlying value. To make a lasting data conversion, use an appropriate formula or data-cleaning step rather than relying on the display code.
Custom formats also have limits. They cannot reliably make a number behave like text, and a number format cannot add arbitrary words based on complex logic. Conditional formats are more suitable when appearance depends on a rule that goes beyond the basic number-format conditions. If the format code is used to hide information, remember that hiding it visually is not security: the value remains in the worksheet and can be revealed by changing the format or examining the cell.
Common problems and how to fix them
- The code appears in the cell instead of changing the value: confirm that the format was entered in Format Cells > Custom, not typed into the worksheet as cell content. Check that the cell is not being treated as text.
- A date format has no effect: the cell may contain text rather than an Excel date value. Convert the text to a date value before applying the date format.
- Separators look wrong: decimal and grouping symbols vary by regional settings. Excel may display the locale’s separators even if the code was entered with familiar punctuation.
- A negative number or zero looks unexpected: inspect the order and count of semicolon-separated sections. Test a positive value, a negative value, zero, and text after changing the code.
- The value seems rounded or shortened: the display may be rounded or scaled even though the stored value is unchanged. Select the cell and inspect the formula bar or increase the displayed decimal places to check.
Frequently Asked Questions
Does a custom number format change the value in a cell?
No. It changes only how Excel displays the value. Calculations continue to use the stored number, date serial, or text.
How do I show leading zeros in Excel?
Apply a format with required zero placeholders, such as 00000, to display a number with at least five digits. If the zeros are part of an identifier’s actual data, consider storing the identifier as text instead.
Can a custom format hide zero values?
Yes. In a three-section number format, leave the zero section empty, as in 0;-0;;@. The zero remains in the cell even though it is not visible.
Why does m mean month in one format and minute in another?
Excel interprets m from its context. It generally means month in a date pattern and minute when used with hours or seconds. Use a test value and inspect the result when building a combined date-and-time code.
Can I use a custom format to turn a number into text?
No. A format can display a suffix or arrange digits, but the cell remains numeric. If another system requires text, convert the value to text separately and consider the consequences for sorting and calculations.
Related
- Conditional Formatting for Errors in Excel: How to Do It
- Contact List Excel Example and Setup Guide
- Excel Conditional Formatting Number Formats Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Page in Word Without Losing Text SafelyTo delete an unwanted page in Word, show formatting marks if it is blank, remove the extra text,…
- Employee Work Schedule Template for Google SheetsTo make an employee work schedule template in Google Sheets, create a spreadsheet with columns for date, employee,…
- Excel Packing List Template: What to Include and How to UseAn Excel packing list template is a reusable worksheet for recording what is packed, how many packages or…
- Excel Can’t Delete a Named Range? Causes and FixesYes, you can delete a named range in Excel for Microsoft 365 on Windows: choose Formulas > Name…
- Excel Worksheet Password Remover: Safe Ways to UnlockAn Excel worksheet password remover is usually a tool or method for taking protection off a sheet, but…
- How to Make a Small Booklet in Microsoft WordTo make a small booklet in Microsoft Word, open the Page Setup options, choose the Book fold layout,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.