Vinai Prakash

To show Excel numbers in thousands or millions, select the cells, press Ctrl+1, choose Custom and type #,##0,"K" for thousands or #,##0.0,,"M" for millions. Each trailing comma divides the displayed number by 1,000, so 23,600,000 shows as 23.6M. Only the display changes. The real value stays in the cell, so your formulas, charts and pivot tables still work.

In this guide you’ll get copy-paste format codes for K, M and B, a way to switch automatically between K and M, how to handle negatives and small numbers, the TEXT function alternative, and what to do in charts and pivot tables.

Quick reference: custom format codes for K, M and B

Type any of these into Format Cells > Number > Custom > Type. The examples use 23,614,500 (and 2,350,000,000 for billions).

The rule: one comma after the digit placeholders = thousands, two = millions, three = billions. The text in quotation marks is just a label.

How to display numbers in thousands (K) in Excel

1. Select the cell or range you want to change.
2. Press Ctrl+1 (Mac: Cmd+1 or Ctrl+1) to open Format Cells. You can also right-click and choose Format Cells, or use the small arrow in the Number group on the Home tab.
3. On the Number tab, click Custom.
4. In the Type box, replace General with:

   #,##0,"K"

5. Click OK. 23,000 now shows as 23K.

If you type 0, "K" with a space before the quotation mark, you get a space in the result (“23 K”). Leave the space out for “23K”.

How to display numbers in millions (M) in Excel

1. Select the numbers and press Ctrl+1.
2. Choose Custom.
3. Type this in the Type box:

   #,##0.0,,"M"

4. Click OK. 23,600,000 now shows as 23.6M.

Use 0,,"M" if you want whole millions only (24M), or add more zeros after the decimal point for more detail. Excel rounds the display, not the value, so 23,614,500 shown as 23.6M is still 23,614,500 in the formula bar.

Show K for thousands and M for millions automatically

If one column mixes small and large numbers, a single code gives awkward results (4,500 shows as 0.0M). A conditional format fixes that by choosing the unit for each cell:

[>=1000000]#,##0.0,,"M";[>=1000]#,##0.0,"K";0

Excel checks the conditions from left to right. Values of 1 million or more show in M, values from 1,000 up show in K, and anything smaller shows as a plain number. So 2,350,000 becomes 2.4M, 8,400 becomes 8.4K and 650 stays 650.

To add billions, use this version:

[>=1000000000]#,##0.0,,,"B";[>=1000000]#,##0.0,,"M";#,##0.0,"K"

Here everything below 1 million falls into the K section, so 650 shows as 0.7K.

Two limits to know about. First, a custom format allows at most two conditions plus a final “everything else” section. That is why this stops at B, M and K. Second, negative numbers do not meet >= conditions, so they fall into the last section and are not scaled the way you expect. If your data has big negatives, use Conditional Formatting with separate rules, which has no two-condition limit.

Format negative numbers and small values

A custom format can hold up to four sections, separated by semicolons: positive, negative, zero and text. To show negative millions in red brackets:

#,##0.0,,"M";[Red](#,##0.0,,"M");0

Now -23,600,000 shows as (23.6M) in red, and zero shows as plain 0.

For small values, use 0 rather than # as the last placeholder before the commas. With #,,"M", a value of 400,000 displays as just “M” because the # hides the zero. With 0.0,,"M", it shows 0.4M.

Use the TEXT function instead (and when not to)

If you need the result as text inside another cell, such as a chart title or a sentence, use TEXT:

=TEXT(A2,"#,##0.0,,")&"M"

For thousands, use =TEXT(A2,"#,##0,")&"K". You can also divide first: =TEXT(A2/1000000,"0.0")&"M".

Microsoft notes that TEXT always returns text, not a number, which makes it harder to use in later calculations. So keep your original numbers in one column, and use TEXT only in a separate “label” cell. For ordinary reports, a custom number format is the better choice because the cell stays a number.

Actually converting to thousands or millions (not just display)

Sometimes you want the cell value itself to change, for example to turn 23,600,000 into 23.6 before exporting. A number format will not do that. Instead:

1. Type 1000000 (or 1000) in an empty cell and copy it.
2. Select your numbers, then choose Paste Special > Values > Divide.

Or use a formula such as =A2/1000000. To go from millions to thousands, multiply by 1,000 instead. Label the column “in millions” so nobody misreads it.

Show K and M in charts and pivot tables

Pivot tables. Right-click a value, choose Value Field Settings > Number Format > Custom and paste the same format code. The pivot then keeps the format when you refresh or rearrange it. New to pivots? See our Excel pivot table tutorial.

Charts. A chart normally follows the format of its source cells, so the K or M format carries across to axis labels and data labels. If it does not, open Format Axis (or Format Data Labels), go to Number, pick Custom, paste your code and untick Linked to source if it is ticked. Value axes also have a Display Units setting (Hundreds, Thousands, Millions and more) that removes the zeros and adds a label such as “Millions”. For more chart ideas, see creating beautiful Excel charts for business presentations.

Excel for Mac and Excel for the web

• Mac: Format Cells opens with Cmd+1 or Ctrl+1, and the Custom codes above work the same way.
• Excel for the web: custom format support in the browser is more limited than in the desktop app. If you cannot find the Custom option, use Open in Excel to open the workbook in the desktop app and apply the format there.
• Versions: Microsoft’s custom number format article covers Excel for Microsoft 365, 2024, 2021, 2019 and 2016.

Handy for this and many other tasks: our list of Microsoft Excel shortcuts.

Common mistakes and troubleshooting

• The number shows as “M” only, or as 0M. The value is under 1 million. Use 0.0,,"M" with a decimal, or the K/M conditional format above.
• Quotation marks missing. Always put the letter in double quotes. An unquoted M can be read as a month or minute code, and Excel may reject or change your format.
• Curly “smart” quotes. If you paste a code from a web page or Word and it fails, retype the quotation marks in Excel.
• It shows ######. The column is too narrow. Widen it, or reduce the decimal places.
• Totals look off by 0.1. The cells are rounded for display only. A visible 23.6M + 12.4M may total 36.1M because the hidden digits are still added.
• Nothing changed. The “number” may be text. Check whether it is left-aligned, then convert it to a real number first.
• Want to remove the format? Select the cells and choose General in the Number Format box on the Home tab.

Download the practice file

Try each format on real numbers: Number_formatting_in_K_or_M.xlsx. It has a few scenarios for converting large numbers into thousands or millions.

Frequently Asked Questions

How do I format numbers in millions in Excel?

Select the cells, press Ctrl+1, choose Custom and enter #,##0.0,,"M". The two commas divide the display by one million and the quoted M is added as a label. The cell value does not change.

What custom format turns 1,000,000 into 1M?

Use 0,,"M". The two commas scale the display by one million, so 1,000,000 shows as 1M and 2,500,000 shows as 3M. For 2.5M instead, use 0.0,,"M". It is a custom number format, not a formula, so the cell keeps its full value.

How do I add M to numbers in Excel?

Use a custom number format with the letter in quotation marks, such as 0,,"M". Do not type the M into the cell, because that turns the number into text. If you only need the M in a formula result, use =TEXT(A2,"0.0,,")&"M".

How do I show numbers in thousands (K) in Excel?

Use #,##0,"K". One comma scales the display by 1,000, so 23,000 appears as 23K.

How do I show $M or $K in Excel?

Add the dollar sign at the start of the format: $#,##0.0,,"M" gives $23.6M and $#,##0,"K" gives $23K.

How do I convert thousands to millions in Excel?

If the numbers are already in thousands, divide by 1,000 with a formula or Paste Special > Divide to get millions. If they are in full units and you only want them to look like millions, use the two-comma format.

How do I show millions with two decimals?

Use #,##0.00,,"M". Add or remove zeros after the decimal point to change the precision, or use the Increase Decimal button on the Home tab.

Does the custom format change the actual value?

No. It changes only how the number looks. Formulas, sorting and charts use the full original value, which you can see in the formula bar.

Can I show K, M and B in the same column?

Yes, with a conditional format such as [>=1000000000]#,##0.0,,,"B";[>=1000000]#,##0.0,,"M";#,##0.0,"K". Excel allows two conditions plus a default section.

Next, try how to learn advanced Excel fast. Spend more time analysing your numbers, and less time counting zeros.