Excel displays “Excel cannot use the number format you typed” when it cannot interpret a custom number-format code, often because of a syntax error or a mismatch with regional settings. In Excel for Microsoft 365 on Windows, press Ctrl+1, open Number > Custom, correct the code or choose a built-in format, and select OK; this changes how a value looks, not the value stored in the cell.
What the error means
A number format is a set of instructions for displaying a cell’s underlying value. Excel may show a date as 3-Nov-2026, a percentage as 25%, or a number as 00127 without changing the stored value. The warning means Excel did not accept the format code you entered. It does not usually mean the workbook or the cell’s data is damaged.
The issue most often appears in the Custom format box in the Format Cells dialog. A format copied from a website, another spreadsheet program, or a workbook created under different regional settings may use characters Excel does not recognize in that context. A small typo, such as a missing quotation mark or an extra section separator, can also make the code invalid.
First check whether you are trying to change the display or the underlying data. If you want a number to appear with a currency symbol, leading zeros, or a date layout, formatting may be enough. If you want to change the value itself—for example, turn text that looks like a date into a real Excel date—you need to convert the data rather than just choose a number format.
How custom number formats work
Custom formats use special characters called placeholders and instructions. For example, 0.00 displays at least one digit before the decimal point and two digits after it. A value of 8.5 appears as 8.50. The format affects the display; the underlying value remains 8.5.
Excel can use up to four format sections, separated by semicolons. In a simple format, the sections apply in this order: positive numbers, negative numbers, zero, and text. For example, 0.00;[Red]-0.00;0;@ displays positive values with two decimal places, negative values in red with a minus sign, zero as 0, and text as entered. The fourth section uses @ as the text placeholder.
You do not need four sections for most tasks. A single section applies to all numeric values, while two or three sections can distinguish positive values, negative values, and zero. Conditional formats can also use conditions in the first sections, but their rules are more restrictive; if a code contains conditions, check that its sections and conditions follow Excel’s custom-format syntax.
Common placeholders and instructions include:
0displays a digit or a zero when no digit is present in that position.#displays a digit when one is present, without forcing an extra zero.?reserves space for a digit, which can help align decimal points.%displays a value as a percentage; for example,0.0%displays 0.125 as 12.5%.@represents text in a format section for text values.
Enter and check a custom format
On Windows, select the cells, press Ctrl+1, and choose Number > Custom. Select a code in the Type box to use as a starting point, or enter your own. Choose OK to apply it. If Excel shows the warning, return to the Type box and check the code before trying again. Starting from a similar built-in format is often easier than typing a complete code from scratch.
- Make a note of the original value or copy the cells if you are unsure what the format is meant to do.
- Open Format Cells with Ctrl+1 and select the Number tab, then Custom.
- Enter a small, simple code first, such as
0.00, and apply it. - Add one feature at a time—such as a color, a literal label, or a negative-number section—so you can identify which addition causes the error.
- Check how several values display, including a negative number, zero, and text if the format is intended to handle them.
On Excel for Mac, select the cells and press Command+1 to open Format Cells; the Number and Custom options are similar, although the dialog layout may differ. Excel for the web has fewer options for creating or editing complex custom formats than the desktop app. If a format code is rejected or the needed controls are unavailable in the browser, open the workbook in desktop Excel and use Format Cells.
Syntax details that commonly cause rejection
Literal text in a custom format should be enclosed in quotation marks or escaped with a backslash. For example, to append a unit, use 0.00 "kg", which can display 3.5 as 3.50 kg. Unquoted letters or punctuation can be interpreted as format instructions rather than as ordinary text, so quoting the label is a safer choice. In this article, quotation marks inside a code example are written as HTML entities; enter ordinary quotation marks in Excel.
Check punctuation carefully. A semicolon separates format sections, so an unintended semicolon may split the code into an extra section. Brackets have specific uses, including colors such as [Red] and conditions such as [>100]. A bracket that is missing its partner, or a condition placed in an unsupported position, can invalidate the code. Avoid adding spaces or characters copied from formatted web text unless they are intentional.
Date and time codes have their own conventions. For example, mmm d, yyyy can display a date with an abbreviated month, day, and four-digit year in a compatible locale. The letter m can mean month or minute depending on its position in a date/time format. If a value is displaying a month where you expected minutes, review the surrounding hour and second instructions; the problem may be interpretation rather than an invalid code.
Be aware that a code can be valid but produce a surprising result. For example, 00000 makes the number 127 display as 00127. It does not turn that number into text or permanently add zeros to its value. If you need to preserve an identifier exactly as typed—including leading zeros—store it as text, or use a suitable import method, rather than relying only on a display format.
Common causes and practical fixes
- Wrong decimal or thousands separators: A code such as
#,##0.00uses comma grouping and a period decimal point. Regional settings may use different separators, so enter the version appropriate to your Excel setup. On Windows, Excel’s system-separator setting is under File > Options > Advanced. Changing it can affect number entry and display throughout Excel, so do not change it just to repair one cell unless you intend that broader effect. - Unquoted text: To add a label such as kg or units, put it in quotation marks, as in
0 "units", rather than assuming Excel will treat arbitrary letters as plain text. - Too many sections: Count the parts separated by semicolons. A custom number format supports at most four sections, with the fourth used for text in the standard section arrangement.
- Copied characters: Curly quotation marks, nonbreaking spaces, or typographic dashes copied from a document may not behave like the plain characters expected in a format code. Retype the code directly in the Type box using ordinary keyboard characters.
- A format meant for another program: Other spreadsheet tools may support different tokens or syntax. Translate the code to Excel’s conventions instead of assuming a format string will transfer unchanged.
- Text stored in a cell: Applying a numeric format will not convert text such as
'00127into the number 127. Check the value and its alignment or formula-bar contents; if it is text, convert it separately when appropriate.
Diagnose whether the format or the value is the problem
Try applying a built-in format first, such as Number, Date, or Percentage from the Number Format controls on the Home tab. If the built-in format works, the value is likely usable and the custom code is the issue. If the cell still looks unexpected, check whether the value is stored as text, whether the date is a real Excel date serial value, or whether the column is too narrow to show the full display.
You can also test a custom format on one sample cell rather than an entire range. For instance, enter a numeric value in a blank cell and apply 0.00. If that succeeds, build up the desired format incrementally. This separates a syntax problem from issues caused by mixed data types in the original range. Remember that number formatting cannot display a numeric-looking text value as though it were a number until the data is converted.
If the code is accepted but the display seems wrong, select the cell and inspect the formula bar. The formula bar generally shows the stored value or formula rather than the formatted appearance. Also check whether a different format is applied to only some cells in the range. Pasting cells can bring formatting with them, so a range may not have one consistent format even when its contents appear similar.
When to use a different approach
Use a built-in number format when it meets the need; it is easier to understand and less vulnerable to syntax or locale differences. Use a custom format when you need a specific display such as a fixed number of decimal places, a visual negative-number style, or a unit label. Use text conversion when the characters themselves must be preserved, such as product codes with leading zeros. Use a formula or data-cleaning operation when the underlying value must actually change.
One important distinction is that a custom format is not a data-conversion tool. A percentage format scales the displayed number, so a stored value of 0.25 appears as 25%, while a stored value of 25 appears as 2500%. Likewise, a date format only makes sense when the cell contains a value Excel can interpret as a date. If the outcome matters for calculations, sorting, or exports, verify the underlying values as well as the appearance.
Frequently Asked Questions
How do I fix Excel cannot use the number format you typed?
Open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Number > Custom, and check the code for unmatched quotes or brackets, extra semicolons, and regional separator differences. Try a simple format first, then add instructions gradually.
Why does Excel reject a custom number format copied from the internet?
The code may use syntax from another spreadsheet application, regional separators that differ from yours, or typographic characters such as curly quotation marks. Retype it with ordinary characters and adjust it to Excel’s custom-format conventions.
Can number formatting add leading zeros permanently?
No. A format such as 00000 can display 127 as 00127, but the stored numeric value remains 127. Store the entry as text if the leading zeros must be part of the value, such as in an identifier.
Why does my custom format work on one computer but not another?
Regional settings can affect decimal and grouping separators, and Excel may interpret some date details according to the computer’s locale. Use a format appropriate to the destination Excel setup, and check the displayed result after opening the workbook on another system.
Will changing the number format change a formula or its result?
No. A number format changes the appearance of the formula result, not the formula or its calculated value. If the calculation itself is wrong, inspect the formula and inputs rather than changing the display format.
Related
- Contact List Excel Sheet: How to Build and Use One
- Contact List Excel Spreadsheet: Create and Manage It
- Conditional Formatting for Errors in Excel: How to Do It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets SUMIF: Syntax, Examples, and Common FixesTo add values in Google Sheets only when matching cells meet a condition, use SUMIF. For example, if…
- How to Change View Settings in the New Outlook AppTo change how email looks in the new Outlook for Windows, open the View tab and adjust options…
- Box Plot in Excel: Create, Format, and Read Box PlotsTo create a box plot in Excel for Microsoft 365 on Windows, arrange each group’s numeric observations in…
- Clear Outlook Cache on iOS: Safe Steps That WorkTo clear Outlook’s local cache on an iPhone or iPad, open Outlook, tap your profile icon, tap the…
- Google Sheets FILTER Formula: How Do You Use It?The Google Sheets FILTER formula returns only the rows or columns that meet conditions you specify. For example,…
- Excel GIS Data Analysis: Maps, Methods, and Limits ExplainedExcel can organize, check, summarize, and map geographic data, but it is not a full geographic information system…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.