If an Excel custom number format is not working, first confirm the selected cells contain numbers or dates rather than text, then check the format code in Format Cells. In Microsoft 365 for Windows, select the cells, press Ctrl+1, choose Number > Custom, correct the code, and select OK; formatting changes how a value looks, not the value itself.
How custom number formats work
A custom number format is a display instruction applied to a cell. The underlying value remains available for calculations. For example, a value of 7.5 can display as 7.50 with 0.00, while Excel still uses 7.5 in formulas. This distinction explains many apparent failures: a format cannot turn text into a number, calculate a missing digit, or change the stored value.
Excel format codes use placeholders and optional sections. The main placeholders are:
0displays a digit even when the value has no digit in that position, adding zeroes where needed.#displays a digit only when needed; it does not add insignificant zeroes.?reserves space for an insignificant digit, which can help align decimal points.@represents text in a format section for text values.
Semicolons separate sections for positive numbers, negative numbers, zero, and text, in that order. For instance, 0.00;[Red](0.00);"—" displays positive values with two decimal places, negative values in red parentheses, and zero as a dash. The fourth section, if included, controls text. If a code has fewer than four sections, Excel applies the available sections according to its format rules; it does not mean every type of value has a separate section.
Start with the cell value, not the appearance
Click a cell that looks wrong and inspect the formula bar. A number may appear left-aligned, have a small green warning marker, or show an apostrophe in the formula bar—clues that it is stored as text. Text that looks like a number will generally not respond to a numeric custom format, and arithmetic may treat it differently too. Imported data, copied web content, and CSV files are common sources of text-formatted numbers.
To convert a simple text number, use the warning icon and choose Convert to Number when Excel offers that option. Alternatively, select the affected range and use Data > Text to Columns, then finish the wizard with General selected. For a formula-based conversion, with the text value 1250 in A2, enter =VALUE(A2) in another cell; the result is the numeric value 1250. Check that the conversion is appropriate for your decimal and thousands separators, especially when the source data was created in a different regional setting.
Do not convert identifiers automatically. An account number, postal code, or product code may look numeric but be intended as text. If leading zeroes were already removed, a custom format such as 00000 can display them for a fixed-width code, but it does not restore them in the underlying value. If the identifier has variable length or is used as text elsewhere, store it as text instead.
Check the format code and its sections
Open Format Cells with Ctrl+1, choose Number > Custom, and inspect the Type box. Confirm that the code is valid and that the value falls into the section you intended. A semicolon in the wrong place can send negative values, zeroes, or text to a different display rule. For example, a format with only a positive and negative section does not provide a dedicated zero section.
Literal characters, such as labels or punctuation, often need quotation marks in a custom format. A format like 0.00" kg" displays a number followed by “ kg” while leaving the cell numeric. Special characters can also be escaped with a backslash in many format codes. If a code was copied from a website or another workbook, retype its quotation marks and symbols in the Type box: typographic quotation marks or an unexpected character can make the code behave differently from the intended syntax.
For a threshold-based format, Excel supports conditions in custom number formats, but the syntax is more specialized than ordinary placeholders. If the result is not obvious, test the code on a few sample values in a blank area before applying it to a large range. A custom format is not a substitute for conditional formatting when the goal is to apply visual rules based on more complex criteria or to compare cells.
Make sure the selected cells are the ones being formatted
A correct code has no effect on cells that were not selected. Before opening Format Cells, check the highlighted range and the Name Box to the left of the formula bar. If you selected a whole column, verify that the data is actually in that column and that a neighboring column is not the one displayed in the report. Also check whether a filter or hidden rows made it difficult to see which cells were included.
If you set a format while the active cell is inside a selection, the format normally applies to the selected cells, but merged cells, pasted formats, or a later edit can make the result confusing. Select one problem cell, open Format Cells, and verify its Number category and Type code. You can then compare it with a cell that displays correctly. The Format Painter can copy formatting, but it may also copy other formatting attributes, so applying the specific number format through Format Cells is often more controlled.
Understand dates, times, and elapsed time
Excel stores dates as serial numbers and times as fractions of a day. A value that appears as 45,000, for example, may display as a date after a date format is applied; the format has not changed the number. Conversely, a date-looking entry stored as text will not behave like a real Excel date. Check the formula bar and test whether the value can be used in date calculations before assuming a format code is the only issue.
Date codes are context-sensitive. In a date format, m or mm usually means month; alongside hours or seconds, it can mean minutes. A code such as h:mm shows a time of day, but a duration longer than 24 hours may wrap around as a clock time. For accumulated hours, use an elapsed-time format such as [h]:mm. That displays 27 hours as 27:00 rather than resetting to 3:00.
If dates display as a row of number signs, the column may simply be too narrow to show the formatted result. Widen the column or use Home > Format > AutoFit Column Width. Number signs can also appear when a date or time calculation produces a value that Excel cannot display, such as a negative date or time under the workbook’s date system. Check the value and formula before changing the format.
Check regional settings and decimal separators
Number formats can depend on regional conventions. A comma may be a grouping separator in one locale and a decimal separator in another, while a period may have the opposite role. Excel generally interprets and displays separators according to its regional settings, so a code copied from a computer with different settings may not produce the expected result. Review the example shown in the Format Cells dialog and try a simple code such as 0.00 in a test cell.
Currency and date formats are especially likely to vary across locales. If a workbook is shared internationally, avoid assuming that every recipient will see the same separator or date order. A date displayed as 03/04/2026 can be ambiguous to a reader even when Excel stores a valid date. Use an unambiguous format such as yyyy-mm-dd when the display needs to be clear across regions.
Look for another rule changing the appearance
Conditional formatting can make a cell look different from the ordinary number format. Open Home > Conditional Formatting > Manage Rules and check which rules apply to the selected cells. Review each rule’s range, priority, and formatting. If a rule includes a number format, it may affect how the value appears while the rule is active. Edit or remove the rule only if it is not needed; otherwise, adjust the custom format so the two are consistent.
Also check whether the cell is part of a table or was copied from another range. Table styles commonly affect fills and fonts, while pasted cell formatting can replace a number format. To see whether the problem is tied to the cell’s existing style, test the custom code in an empty cell with a known numeric value. If it works there, the original cell likely has a different value, rule, or formatting history rather than a defective code.
Differences in Excel for Mac and the web
On Excel for Mac, select the cells and open Format Cells with Command+1; then choose the number-format options and Custom where available. The exact dialog appearance can differ slightly from Windows, but the same core format-code concepts apply. Keyboard shortcuts can vary with keyboard settings, so use the Format Cells command from the menus if the shortcut does not open the dialog.
Excel for the web has a more limited interface than the desktop application, and available custom-format controls can depend on the current web version. Try Home > Number Format and look for the option to create or edit a custom format. If the required code cannot be entered or the display does not match the desktop workbook, open the file in Excel for Microsoft 365 on Windows or Mac and inspect Format Cells > Custom. A custom number format is workbook formatting, not a separate formula, so opening the file in another compatible Excel version should not convert the displayed text into the underlying value.
A quick troubleshooting sequence
Work through these checks in order; each one rules out a common cause without changing the data unnecessarily:
- Select one affected cell and inspect the formula bar to determine whether it contains a number, date serial, formula result, or text.
- Press Ctrl+1 and review the Number category and Type code. Confirm the code has the intended sections and literals.
- Test the same code in a blank cell containing a known number. If it works there, focus on the original cell’s value or rules.
- Check for text storage, conditional formatting, insufficient column width, and regional separator differences.
- Apply the correction to a small range first, verify both the display and calculations, then extend it to the rest of the data.
This approach helps avoid a common mistake: repeatedly editing the format code when the real problem is that the cell contains text. It also preserves the distinction between changing a value and changing how Excel presents it.
Frequently Asked Questions
Why does my custom number format show no change?
Most often, the cell contains text instead of a number, the code is invalid, or the format was applied to a different range. Inspect the formula bar, then test the code on a known numeric value in a blank cell.
Why does Excel show asterisks or number signs instead of my value?
For a date or number format, the column may be too narrow. Widen it or use Home > Format > AutoFit Column Width. If the cell is a date or time, also check for a negative or otherwise un-displayable result.
Can a custom format add leading zeroes?
Yes. A code such as 00000 displays a numeric value with at least five digits, adding leading zeroes when needed. The underlying value remains numeric, and this does not recover digits that were not part of a fixed-width identifier unless the format supplies them for display.
Why did my date format turn a number into a date?
Excel dates are stored as serial numbers. Applying a date format tells Excel to display the serial as a calendar date; it does not change the stored number. Select General or another number format to see a more direct representation of the value.
Will a custom number format change the value used in calculations?
No. It changes the display, not the stored value. A formula such as TEXT can create a text result that looks formatted, but that result is text rather than the original numeric value, so use cell formatting when you need the result to remain numeric.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Data Validation Stop Error: What It MeansExcel’s Data Validation Stop error alert blocks an invalid value entered directly into a validated cell and offers…
- IFERROR VLOOKUP Not Working? Find and Fix the ErrorIf IFERROR with VLOOKUP is not working, first check that the formula is structured correctly and that VLOOKUP…
- Excel Won’t Insert a Page Break? Fix the Common CausesIf Excel won’t insert a page break, select the row below or column to the right of where…
- Jupyter Notebook: How to Split a Cell and Fix Common IssuesTo split a Jupyter Notebook cell, click inside it where you want the break, then choose Edit >…
- Excel Column Is a Number Instead of a Letter? Fix ItIf Excel shows column headings as 1, 2, 3 instead of A, B, C, turn off R1C1 reference…
- How to Fix Mail Merge in Word: Fields, Data, and PrintingTo fix mail merge in Word, check that your recipient list has clear column headers and valid records,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.