In Excel: select the cells, press Ctrl+1 ▸ Number ▸ Custom, and type a format code — for example 0.0,,"M" to show 12,400,000 as 12.4M.
Select the cells, press Ctrl+1 (⌘1 on Mac) and choose the Number tab.
Pick the closest built-in format first — it fills the Custom box with a working code to edit.
Choose Custom and edit the code: 0 forces a digit, # hides an absent one, "text" adds literal characters.
Add a second section after a semicolon for negatives, a third for zero, a fourth for text.
Press Ctrl+1 ▸ Custom on another cell and paste the code, or use the Format Painter, to reuse it.
What this does
A number format changes how a value is DISPLAYED and never what it is: the cell still holds 12400000, so every formula that reads it still gets 12400000. A format code has up to four sections separated by semicolons — positive;negative;zero;text — and you can supply just the first, or the first two. The building blocks are 0 (a digit, padded with zero), # (a digit, blank if absent), ? (a digit, blank but aligned), a comma as thousands separator, and quotation marks around any literal text. A comma at the END of the code divides the display by a thousand, so 0.0,, shows millions. Colours go in square brackets: [Red]. And the Accounting format is not merely currency — it aligns the currency symbol at the left edge of the cell, aligns the decimal points, and shows zero as a dash, which is what makes columns of it readable. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “custom number format”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a workspace setting that makes a big sheet comfortable to navigate useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Phone numbers stored as the digits 2125550147: Ctrl+1 ▸ Custom ▸ (000) 000-0000 displays (212) 555-0147 while the cell still contains the number, so sorting and lookups keep working. Revenue in millions: 0.0,,"M" turns 12400000 into 12.4M. Positive and negative in one code: #,##0;[Red](#,##0) shows 1,250 and (1,250) in red. Custom formats let one column serve both the reader and the formulas — a readable 12.4M on screen with the exact 12,400,000 still available to every calculation that touches it. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. The short version of “custom number format”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Expecting a format to change the value — the cell still holds the full number, which is why a "rounded" column can still fail to add up on screen.
- Formatting phone numbers that were entered as TEXT: a number format has no effect on text, and leading zeros mean they probably are text.
- Using a custom format to hide decimals and then quoting the displayed figure as if it were exact.
- Reaching for the currency format when the column needs Accounting, which is what aligns the symbols and decimal points.
- Building a format with three commas expecting billions and getting a blank-looking cell, because each trailing comma divides by another thousand.
Frequently asked questions
How do I create a custom number format in Excel?
Select the cells, press Ctrl+1, choose Number ▸ Custom and type a format code such as #,##0.00 or 0.0,,"M".
How do I format phone numbers in Excel?
Ctrl+1 ▸ Custom ▸ (000) 000-0000 for US numbers. If the cells contain text rather than digits, convert them to numbers first or the format will do nothing.
What is the accounting number format?
A currency format that left-aligns the currency symbol at the cell edge, lines up the decimal points, and shows zero as a dash. Home ▸ Number ▸ Accounting, or Ctrl+Shift+$ for plain currency.
How do I show numbers in millions?
Use the custom code 0.0,,"M" — two trailing commas divide the display by a million, and the underlying value is unchanged.
What do the semicolons in a format code mean?
They separate up to four sections: positive;negative;zero;text. Supplying fewer sections is fine, and an empty section hides that case entirely.
Other ways people ask this
On the way here you may have searched this as “custom format number excel”, “excel number format codes”, “excel custom number format millions” and “excel custom number format with text” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “custom format number excel”, “excel number format codes”, “excel custom number format millions” all point at the one operation explained on this page, which is why they all lead here.