Spreadsheet Practice

How to show numbers in thousands (K) and millions (M) in Excel

Formula (Excel and Google Sheets)

=IF(B2>=1000000,TEXT(B2,"0.0,,")&"M",IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0")))

For a text label, pick the unit with IF and let TEXT scale the number: commas at the end of a format divide by 1,000 each, as in TEXT(B2,"0.0,,")&"M".

Result on the example
one result per row in C2, filled down to C6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

How it works

A comma at the end of a number format divides by 1,000, and two commas divide by 1,000,000, so "0.0,," turns 1,250,000 into 1.3. The IF picks the right scale and adds the letter. The result is text, which is what labels, titles and joined sentences need.

=IF(B2>=1000000,TEXT(B2,"0.0,,")&"M",IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0")))

Result"1.3M"

  1. B21,250,000

    The value in B2.

  2. B2>=1000000TRUE

    The >= operator tests at least.

  3. TEXT(B2,"0.0,,")"1.3"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueB21,250,000
    • format_text"0.0,,"
  4. TEXT(B2,"0.0,,")&"M""1.3M"

    The & operator joins as text.

  5. B2>=1000TRUE

    The >= operator tests at least.

  6. TEXT(B2,"0.0,")"1250.0"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueB21,250,000
    • format_text"0.0,"
  7. TEXT(B2,"0.0,")&"K""1250.0K"

    The & operator joins as text.

  8. TEXT(B2,"0")"1250000"not used

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueB21,250,000
    • format_text"0"
  9. IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0"))"1250.0K"not used

    IF: Returns one value if a test is true, another if false.

    • logical_testB2>=1000TRUE
    • value_if_trueTEXT(B2,"0.0,")&"K""1250.0K"
    • value_if_falseTEXT(B2,"0")"1250000"not used
  10. IF(B2>=1000000,TEXT(B2,"0.0,,")&"M",IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0")))"1.3M"

    IF: Returns one value if a test is true, another if false.

    • logical_testB2>=1000000TRUE
    • value_if_trueTEXT(B2,"0.0,,")&"M""1.3M"
    • value_if_falseIF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0"))"1250.0K"not used

In Google Sheets

The same formula works in Google Sheets: =IF(B2>=1000000,TEXT(B2,"0.0,,")&"M",IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0"))). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(B2>=1000000,TEXT(B2,"$0.0,,")&"M",IF(B2>=1000,TEXT(B2,"$0.0,")&"K",TEXT(B2,"$0")))$1.3M, $48.5K, $3.4M…Different resultThe same with a dollar sign in each format. "$1.3M", "$48.5K" and so on.

Common mistakes

  • The labels are text, so charts and SUM can't use them. To keep real numbers, use a custom number format instead: [>=1000000]0.0,,"M";[>=1000]0.0,"K";0.
  • Values just under a million, like 999,960, round up to "1000.0K". Test against 999,950 if that matters.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Show numbers in thousands (K) and millions (M)

In C2, show the value as a short label: millions with M, thousands with K, one decimal, and smaller numbers as they are. Fill down to C6.

Questions

How do I show thousands without a decimal?

Use TEXT(B2,"0,")&"K": 48,500 becomes "49K".

Does this work in Google Sheets?

Yes. TEXT with trailing commas works the same in Google Sheets.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.