Excel Custom Number Formats: Thousands, Units, and Red Negatives
Custom number formats change how a value looks without changing the value. Here are format codes for thousands, numbers with units, red negatives, and dates with weekdays.
2026-11-10
Key points
- 1Set them in Ctrl + 1 → Number → Custom
- 2Only the look changes; the value and calculations stay the same
- 3Semicolons separate formats for positive numbers, negative numbers, and zero
How to set a format
- 1
Select the cells to format
- 2
Open Format Cells with Ctrl + 1 (⌘ + 1 on a Mac)
- 3
On the Number tab, choose Custom under Category
- 4
Enter a format code in Type and click OK
Basic symbols
| Symbol | Meaning | Example | 1234.5 shows as |
|---|---|---|---|
0 | A digit; missing digits show as 0 | 0.00 | 1234.50 |
# | A digit; missing digits are not shown | #.# | 1234.5 |
, | Thousands separator | #,##0 | 1,235 |
"text" | Shows the text as is | 0" pcs" | 1235 pcs |
; | Separates positive, negative, and zero formats | 0;-0;"-" | 1235 |
Show values in thousands or millions
Each comma at the end of a format code hides three more digits.
| View | Format code | 1234567 shows as |
|---|---|---|
| Thousands | #,##0, | 1,235 |
| Millions | #,##0,, | 1 |
| Millions with 1 decimal | #,##0.0,, | 1.2 |
The cell value doesn't change
Even when it shows 1,235, the cell still holds 1234567. Totals and other calculations use the original value, so there's no need to divide by 1000 and retype.
Add units
Typing "12 pcs" into a cell makes it text, so it can't be summed. Enter only the number and add the unit with a format.
| View | Format code | Example |
|---|---|---|
| Pieces | #,##0" pcs" | 1,200 pcs |
| Dollars | "$"#,##0 | $18,000 |
| % (enter 0.125) | 0.0% | 12.5% |
| Pad with leading zeros | 000 | 007 |
| Add text before a name | "Dear "@ | Dear Smith |
Change how negatives and zero appear
Semicolons let you set formats for positive numbers, negative numbers, zero, and text, from left to right.
| View | Format code | -1200 shows as |
|---|---|---|
| Negatives in red | #,##0;[Red]-#,##0 | -1,200 |
| Negatives in parentheses | #,##0;(#,##0) | (1,200) |
| Negatives in red parentheses | #,##0;[Red](#,##0) | (1,200) |
| Zero as "-" | #,##0;-#,##0;"-" | -1,200 (0 shows as "-") |
Change how dates and weekdays appear
| View | Format code | Nov 10, 2026 shows as |
|---|---|---|
| Date | m/d/yyyy | 11/10/2026 |
| Month and day | mmm d | Nov 10 |
| With weekday | m/d (ddd) | 11/10 (Tue) |
| Weekday only | dddd | Tuesday |
When a date turns into a number
If a date shows as a number like 46336, choose a date format again to fix it. Excel stores dates as serial numbers.
FAQ
QI set a format but nothing changed.
Number formats don't apply to numbers stored as text. Use the green triangle in the top-left corner of the cell to convert them to numbers.
QCan I use my custom format in other workbooks?
Format codes are saved per workbook. In another workbook, copy the formatting or enter the code again.
QCan I delete a custom format?
Select it in the Custom list and click Delete.
Related guides and practice
Readable Spreadsheet Drill →
36 exercises that fix hard-to-read tables in a virtual Excel
Building a table →
8 steps from data entry to finishing touches
Merge Cells →
Merging and unmerging, and centering without merging
Conditional Formatting →
Colors based on values: whole rows, multiple conditions, weekends
Hide Zeros →
Whole sheet, some cells, or a formula, and when to use each
Border →
Choosing lines, double lines under totals, removing stubborn lines