Custom Number Format Display Errors — Complete Fix
The hash symbols, the wrong sign display, the format that "should" work but doesn't — here's how the four-section syntax actually works.
Table of Contents
- 1. The ##### Symbol Is Not an Error
- 2. Interactive Demo: Four-Section Format Builder
- 3. Understanding the Four-Section Syntax
- 4. The Single-Section Trap
- 5. Common Custom Format Codes Reference
- 6. Conditional Formatting Within Number Formats
- 7. The Text Section Quirk
- 8. Frequently Asked Questions
1. The ##### Symbol Is Not an Error
Seeing a row of hash symbols (#####) instead of your expected number is one of the most common points of confusion, but it's not actually a formatting mistake — it means the column is simply too narrow to display the fully formatted value at its current width. The underlying value and format are both completely correct; Excel is just refusing to truncate or wrap a number in a way that could be misread. Widen the column (drag its right border, or double-click the border for auto-fit) to resolve this instantly.
2. Interactive Demo: Four-Section Format Builder
Build a format code section by section and see exactly how each part applies to different value types.
3. Understanding the Four-Section Syntax
A complete custom number format code can define up to four distinct sections, separated by semicolons, applied in this fixed order:
POSITIVE;NEGATIVE;ZERO;TEXTEach section controls exactly how that category of value is displayed. You don't have to define all four — Excel applies sensible defaults to any section you omit, but understanding this structure is essential for diagnosing why a format behaves unexpectedly for a specific value type.
4. The Single-Section Trap
If your custom format contains no semicolons at all — just a single section — that one format is applied uniformly to every value, positive, negative, or zero, without any automatic sign handling:
0.00 — applies to ALL values identically; a negative number displays as e.g. -125.50 using the default minus sign
#,##0.00;[Red](#,##0.00) — now positive and negative are handled separately, with negatives in red parenthesesIf negative numbers are displaying strangely (unexpected sign placement, missing color coding you expected), check whether your format code actually has the semicolon-separated negative section defined, or whether it's relying on the single default section for everything.
5. Common Custom Format Codes Reference
| Format code | Displays |
|---|---|
#,##0 | 1,250 (thousands separator, no decimals) |
0.00% | 12.50% (percentage with 2 decimals) |
$#,##0.00 | $1,250.00 (currency) |
0"K" | Displays a number followed by a literal "K" — useful for thousands shorthand |
[Red]-0.00;[Green]0.00 | Negative numbers in red, positive in green |
mm/dd/yyyy | 09/08/2026 style date display |
6. Conditional Formatting Within Number Formats
Custom number formats support basic color coding using bracketed color names like [Red], [Green], [Blue], and a few others, directly embedded within the relevant section. This is purely visual and happens at the formatting layer — it doesn't change the underlying value the way a Conditional Formatting rule might, and it's limited to Excel's small predefined set of named colors rather than arbitrary custom RGB values.
7. The Text Section Quirk
The fourth section (text) is the least commonly used but occasionally causes confusion — it only applies when the cell's underlying value is genuinely text, not a number formatted to display as if it were text. The @ symbol within this section acts as a placeholder representing the original text content, allowing you to wrap it with additional literal text (like a prefix or suffix) without altering the actual stored value.
8. Frequently Asked Questions
Why does my custom number format show ##### instead of a number?
This means the column is too narrow to display the formatted value at its current width, not that the format code itself is broken. Widen the column, either by dragging its border or double-clicking the border to auto-fit, to reveal the correctly formatted value.
What are the four sections of a custom number format code?
A full custom format code can define up to four sections separated by semicolons, in this order: format for positive numbers, format for negative numbers, format for zero, and format for text. Any section can be omitted, and Excel applies default behavior for unspecified sections.
Why does my custom format show the wrong value for negative numbers?
If only one format section was defined without semicolons separating positive, negative, and zero sections, Excel applies that same single format to all values regardless of sign, which can produce a misleading display for negative numbers if the format wasn't designed with negatives in mind.
Related Guides
Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.