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.
On this page7
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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “apply accounting format in excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. 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. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “apply accounting format in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
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.