Custom Number Formats in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
A custom number format changes how a number looks without changing its value. Select the cells, press Ctrl+1 (Mac: ⌘1), choose Custom and type a code in the Type box, such as 00000 for leading zeros, #,##0,"K" for thousands or 0 "kg" for units. Formulas still use the real number underneath.
Key takeaways
- Open Format Cells with Ctrl+1 (Mac: ⌘1), choose Custom, and type the code in the Type box.
- 0 always shows a digit, # shows a digit only when needed, and a comma adds thousands separators.
- Text in double quotes, like 0 "kg", adds a label but keeps the cell a real number.
- A format can have four sections: positive; negative; zero; text.
- Formatting changes only the display. Use the TEXT function when you need the formatted result as text.
On this page+
A custom number format in Excel lets you decide exactly how a number looks: leading zeros on IDs, sales shown as 1.2M, a "kg" label after weights, or negative numbers in red. The value in the cell doesn't change, so formulas keep working. This guide explains how the codes work and gives you formats you can copy.
What is a custom number format?#
A number format is a set of instructions for displaying a value. Excel's built-in formats (Currency, Percentage, Date) cover the basics, which our guide to formatting numbers as currency, percent and dates explains. A custom format is one you write yourself with a short code.
The key point: formatting changes the display, not the value. If a cell holds 0.256 and shows 26%, formulas use 0.256. You can always see the real value in the formula bar. The More formatting lesson shows this with dates and comma style.
How to create a custom number format#
- Select the cells.
- Press Ctrl+1 on Windows or ⌘1 on a Mac to open Format Cells. You can also right-click and choose Format Cells.
- On the Number tab, click Custom at the bottom of the Category list.
- Type your code in the Type box. The Sample area shows a preview.
- Click OK.
Your new code is saved in the list for that workbook, so you can reuse it. A quick start is to pick a built-in format first, then click Custom: the Type box shows that format's code, ready to edit.
The building blocks of format codes#
These characters do most of the work:
| Code | What it does | Value | Format | Shows |
|---|---|---|---|---|
0 | A digit that always shows, padding with zeros | 7.5 | 0.00 | 7.50 |
# | A digit that shows only when needed | 7.5 | #.## | 7.5 |
, (between digits) | Thousands separator | 1234567 | #,##0 | 1,234,567 |
, (at the end) | Divides by 1,000 per comma | 1234567 | #,##0, | 1,235 |
. | Decimal point | 0.5 | 0.0 | 0.5 |
% | Multiplies by 100 and adds % | 0.256 | 0.0% | 25.6% |
"text" | Shows the text as typed | 12 | 0 "kg" | 12 kg |
@ | Stands for text typed in the cell | North | "Region: "@ | Region: North |
A few characters, such as $, -, +, (, ) and a space, show without quotes. Everything else that you want to show as text should go in double quotes.
Useful custom formats you can copy#
| You want | Format code | Value | Shows as |
|---|---|---|---|
| Thousands with K | #,##0,"K" | 1234567 | 1,235K |
| Millions with one decimal | 0.0,,"M" | 1234567 | 1.2M |
| Units after the number | #,##0.0 "kg" | 1234.5 | 1,234.5 kg |
| Plus sign for positives | +#,##0;-#,##0;0 | 1234 | +1,234 |
| Negatives in red brackets | #,##0;[Red](#,##0) | -1234 | (1,234) in red |
| Five-digit ID with leading zeros | 00000 | 42 | 00042 |
Leading zeros are a common need: an employee ID of 42 shown as 00042. The format keeps the cell a real number, so you can still sort and look it up as a number. If the ID must be stored with its zeros, for example to match a text code in another system, store it as text instead.
The four sections: positive, negative, zero, text#
A custom format can have up to four parts, separated by semicolons:
positive ; negative ; zero ; textFor example, #,##0;[Red]-#,##0;"-";@ shows positives normally, negatives in red with a minus sign, zeros as a dash, and text unchanged. If you write only one section, it applies to every number. With two, the first is for positives and zeros, and the second is for negatives.
Two handy tricks:
- Hide zeros:
0;-0;;@(the zero section is empty). - Hide everything:
;;;hides every value in the cell. The value still shows in the formula bar, so this is not a way to protect data. To stop people changing cells, see how to lock cells and protect a sheet.
Colour names go in square brackets at the start of a section: [Red], [Blue], [Green], [Black] and a few others. You can also add a condition, like [>=1000]#,##0,"K";0, to show values of 1,000 and above in thousands.
Custom date and time formats#
Dates in Excel are numbers (days since 1 January 1900), which is why the same date can look so different. Our article on why Excel stores dates as numbers explains the system.
| Code | Shows | Example for 3 Feb 2025 |
|---|---|---|
d / dd | Day number | 3 / 03 |
ddd / dddd | Day name | Mon / Monday |
m / mm | Month number | 2 / 02 |
mmm / mmmm | Month name | Feb / February |
yy / yyyy | Year | 25 / 2025 |
Combine them freely: ddd d mmm yyyy shows Mon 3 Feb 2025. For times, h:mm shows hours and minutes. To show total hours above 24, for example on a timesheet, use [h]:mm: a total of 27 hours 30 minutes shows as 27:30 instead of 3:30.
Custom formats vs the TEXT function#
A number format changes the display but keeps the number. The TEXT function uses the same codes but returns text, which you need when you join a number to words:
="Due "&TEXT(B2,"ddd d mmm")This gives "Due Mon 3 Feb". Without TEXT you would get "Due 45691", the date's serial number. The TEXT and VALUE lesson has you build sentences like this, and our guide to the TEXT function and its format codes has more examples.
Use a number format when the cell will be calculated, sorted or charted. Use TEXT only when the result is a label. Before printing a formatted report, our guide to printing an Excel sheet neatly covers fit to page and titles.
Practise it#
Try the TEXT version of a date format in the exercise on this page. Then practise date and comma formats in a live spreadsheet, and use the TEXT and VALUE lesson to put format codes inside formulas.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Format codes also work inside the TEXT function. B2 holds a shift date. In C2, show it as a short day and date, like Mon 3 Feb, with =TEXT(B2,"ddd d mmm").
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Label | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 |
Tip: ddd gives the short day name, d the day number and mmm the short month name. The code goes in double quotes.
Practise this in a lesson
Frequently asked questions
How do I create a custom number format in Excel?
Select the cells and press Ctrl+1 (⌘1 on a Mac). On the Number tab choose Custom, type your code in the Type box, check the Sample preview and click OK.
How do I keep leading zeros in Excel?
Apply a custom format with as many zeros as the code length, such as 00000 for five digits. The number 42 then shows as 00042, while the cell still holds 42.
How do I show numbers in thousands or millions?
End the code with a comma for each thousand you want to divide by: #,##0,"K" shows 1234567 as 1,235K, and 0.0,,"M" shows it as 1.2M. The value in the cell doesn't change.
Does a custom number format change the value?
No. It only changes what you see. Formulas, totals and lookups use the full value stored in the cell, which you can see in the formula bar.
How do I hide zeros or values with a number format?
Use 0;-0;; to hide zeros, because the empty third section is the zero format. Three semicolons (;;;) hide every value, though it still shows in the formula bar.