How to abbreviate large numbers in Excel (K, M, B)

Method 1: a custom number format (best for numbers you still calculate with)

A comma placed after the last digit placeholder in a number format scales the display down by 1,000. Two commas scale by a million, three by a billion. The letter in quotes is just text that is shown after the number. So 0.0,,"M" displays 1,250,000 as 1.3M, and 0.0,"K" displays 45,300 as 45.3K.

Nothing about the value changes. The formula bar still shows 1250000, SUM still adds the full amounts and sorting still works on the real numbers. That is why this is the method to use on any column you total, chart or filter.

Useful variants: $0.0,,"M" for currency ($1.3M), 0,"K" for whole thousands (45K), #,##0.0,"K" to keep a thousands separator on large K values (12,480.0K) and 0.0,,,"B" for billions (3.9B).

1. Select the revenue cells, B2:B8.
2. Press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells.
3. On the Number tab, choose Custom.
4. In the Type box, type 0.0,,"M" and click OK.
5. To reuse it later, it now sits at the bottom of the Custom list in this workbook.

One format that switches between K and M

A single code applies one scale to every cell, so 45,300 formatted with 0.0,,"M" shows as 0.0M. Custom formats accept up to two conditions in square brackets, which is enough for a K/M switch: [>=999950]0.0,,"M";[>=999.95]0.0,"K";0. Millions get M, thousands get K, and anything smaller is shown as a plain number.

Two limits come with it. There is no room for a third condition, so billions show as thousands of millions (3870.0M) unless you use a separate B format on those cells. And negative values fail both tests and drop to the last section, so -2,460,000 is not abbreviated. If your column has losses or billions, use the formula in the next sections instead.

The odd-looking 999950 is deliberate. With a cut-off of 1000000, 999,950 would be formatted as K and rounded to 1000.0K. Starting the M section at 999,950 shows it as 1.0M.

Method 2: TEXT, when you need the abbreviation as text

For chart titles, dashboard labels or sentences like "Revenue: $1.3M", build the text with a formula. Divide by the scale first, then let TEXT round to one decimal and add the letter. Excel's TEXT also accepts the comma codes from Method 1 (next section), but dividing makes the formula easier to read and check.

To join it into a sentence: ="Revenue: $"&TEXT(B2/1E6,"0.0")&"M". Keep the original number in its own cell and point the label at it, so the label updates when the figure does.

The same label with TEXT's comma codes

TEXT understands the scaling commas too, so you can skip the division and hand it the format code from Method 1. Inside a formula the quotes around the letter have to be doubled, because the whole code is already in quotes: "0.0,,""M""" is the code 0.0,,"M".

Both forms give the same text. =TEXT(B3,"0.0,""K""") returns 45.3K, =TEXT(B4,"0.0,,,""B""") returns 3.9B and =TEXT(B8,"#,##0,""K""") returns 12,480K. Pick the divide-first form if other people will edit the sheet, since B2/1E6 says what it does; pick the comma form when you are copying a code you already use as a number format.

Method 3: one formula that picks K, M or B

When the numbers range from hundreds to billions, let IFS choose the scale by checking the biggest one first. ABS makes the test work on negative values too, so a loss of 2,460,000 still gets M. TRUE as the last test catches everything under 1,000 and shows it as is.

Fill it down beside the numbers and Brightpath shows 45.3K, Northwind 1.3M, Corner Cafe 820 and Fjordsoft -2.5M. IFS needs Excel 2019 or later; in older versions nest IF the same way: =IF(ABS(B4)>=1E9,...,IF(ABS(B4)>=1E6,...)).

Rounding and negatives: the cases that look wrong

The rounding trap: Oakline's 999,950 is under a million, so the IFS formula picks K, and 999.95 rounded to one decimal is 1000.0K. Move each cut-off down to the point where rounding reaches the next unit: 999,950 for M, 999,950,000 for B and 999.5 for K. LET keeps the formula readable by naming the value and its absolute size once.

Negatives: TEXT keeps the minus sign, giving -2.5M. For accounting-style brackets, give TEXT a second section for negative numbers: =TEXT(B6/1E6,"0.0""M"";(0.0""M"")") returns (2.5M). The same two-section idea works in a custom number format: 0.0,,"M";(0.0,,"M").

Two format-code details. Put the letter in quotes (or after a backslash, as in 0.0,,\M): an unquoted M or m is read as a month or minute code. And if you try 0.# to drop the trailing .0, Excel shows whole numbers with a dangling decimal point, so 2,000,000 becomes "2.M". Stick with 0.0 for a consistent look.

Which to pick: format or TEXT

A number format changes only what you see. A TEXT result is text, so Excel stops treating it as a number. Column C holds the TEXT versions, and SUM ignores text completely. Sorting goes wrong too: text sorts character by character, so 12.5M lands above 3.9B and 45.3K, and 820 ends up last.

Use a custom number format on every cell you calculate with, sort or chart. Use TEXT only for display text built from a number. For chart axes you often need neither: in Format Axis > Axis Options, set Display units to Thousands or Millions, and Excel scales the axis and adds a units label.