KutoolsforOffice — One Suite. Five Tools. Get More Done.

How to format numbers in thousands, millions or billions in Excel?

AuthorXiaoyangLast modified

Large numbers can make an Excel worksheet difficult to read, especially when you are working with sales reports, financial statements, budgets, website traffic, or other datasets containing values in the thousands, millions, or billions.

For example, displaying 1,250,000 as 1.25M makes a report more compact and easier to understand. Excel provides several ways to shorten large numbers without manually changing every value. Excel provides several ways to shorten large numbers without manually changing every value. You can use a custom number format to preserve the original numbers, apply a formula to display different suffixes automatically, or use Kutools for Excel for a faster, code-free solution.

To display large numbers in a shorter form, apply one of the following custom number formats:

Display unitFormat CodeExample result
Thousands0.0,"K"125,000 → 125.0K
Millions0.0,,"M"8,500,000 → 8.5M
Billions0.0,,,"B"3,200,000,000 → 3.2B

Display Large Numbers in Thousands, Millions, or Billions Separately

Automatically Display Numbers in K, M, or B Based on Value

Convert K, M, and B Abbreviations to Full Numbers with a Formula

Which Method Should You Use?

Tips for Formatting Large Numbers in Excel

Frequently Asked Questions

Conclusion

Display Large Numbers in Thousands, Millions, or Billions Separately

Use this approach when all numbers in a range should use the same unit. For example, you may want an entire sales column displayed in millions or a website traffic column displayed in thousands.

 

Method 1: Use a Custom Number Format

A custom number format changes only how a number appears. It does not change the actual value stored in the cell.

For example, the value 1,250,000 can be displayed as 1.3M, while Excel still stores the original number and can continue using it in calculations.

  1. Select the cells containing the large numbers.
  2. Press Ctrl + 1 to open the Format Cells dialog box.
  3. Select Custom in the Category list. And enter the following format in the Type box:

    ✓ Format numbers in thousand: 0.0, "K"

    ✓ Format numbers in millions: 0.0,,"M"

    ✓ Format numbers in billions: 0.0,,,"B"


    set options in the Format Cells dialog box
  4. Click OK.

Result: The numbers are now displayed in thousands, millions, or billions, as shown in the following screenshot:
numbers are now displayed in thousands, millions, or billions

Custom format summary

You can adjust the number of decimal places by changing the zeros in the format code.

Display unitCustom number formatExample result
Thousands0.0,"K"125,000 → 125.0K
Millions0.0,,"M"8,500,000 → 8.5M
Billions0.0,,,"B"3,200,000,000 → 3.2B
Thousands without decimals0,"K"125,000 → 125K
Millions without decimals0,,"M"8,500,000 → 9M
Billions without decimals0,,,"B"3,200,000,000 → 3B
Note: Formats without decimal places round the displayed result to the nearest whole number, while the original value stored in the cell remains unchanged.

Advantages

  • The original numeric values remain unchanged.
  • Formulas such as SUM, AVERAGE, and MAX continue to work.
  • No helper column is required.
  • The format can be applied quickly to an entire range.

Limitation

The same unit is applied to every selected cell. If you apply the millions format to 50,000, for example, the cell will display 0.1M rather than 50K.

 

Method 2: Use Kutools for Excel

Remembering exact custom number format codes can be inconvenient, especially when you frequently switch between thousands, millions, billions, decimal places, currencies, and negative-number styles.

The Number Format Helper in Kutools for Excel provides a visual way to create and apply number formats without manually entering complex format codes.

  1. Select the cells containing the numbers you want to format.
  2. Click Kutools > Format > Number Format Helper, as shown in the following screenshot:
    enable Number Format Helper
  3. In the Number Format Assistant dialog box, configure the following options:
    • Select Number Unit from the Category box.
    • Choose the required format from the Format list, such as Thousands K Integer, Thousands K One Decimal, Millions M Integer, Millions M One Decimal, Billions B Integer, or Billions B One Decimal.
    • Check how the selected numbers will appear in the Preview pane.

    configure the options
  4. Click OK to apply the selected format.

Result: Kutools applies the selected thousands, millions, or billions format to the entire range without requiring you to enter custom number format codes manually.

Advantages of Using Kutools for Excel

  • No need to remember custom number format codes.
  • Quickly apply a number format to an entire selected range.
  • Easily switch between thousands, millions, and billions.
  • Choose the required number of decimal places.
  • Preview the formatted results before applying the changes.
  • The original numeric values remain unchanged and can still be used in calculations.

Automatically Display Numbers in K, M, or B Based on Value

When a worksheet contains values of different sizes, applying the same number unit to every cell may not produce clear results. For example, thousands are better displayed with K, millions with M, and billions with B. To make the data easier to read, you can use the following methods to automatically select the appropriate abbreviation based on each number’s value.

 

Method 1: Use a Custom Number Format

A conditional custom number format allows Excel to display each value automatically in thousands, millions, or billions according to its size.

This method changes only how the numbers appear in the worksheet. The original numeric values remain unchanged and can still be used in calculations.

    1. Select the cells containing the numbers you want to format.
    2. Press Ctrl + 1 to open the Format Cells dialog box.
    3. Select Custom in the Category list, and then enter the following format code in the Type box:
      [>=1000000000]0.0,,,"B";[>=1000000]0.0,,"M";0.0,"K"

      Tip: The format code works as follows:

      • Values of at least 1,000,000,000 are displayed in billions, such as 1.5B.
      • Values of at least 1,000,000 are displayed in millions, such as 8.5M.
      • All remaining values are displayed in thousands, such as 125.0K.

set options in the Format Cells dialog box

  1. Click OK to apply the format.

Result: Excel automatically displays each value with the appropriate K, M, or B unit based on its size. Only the display format changes, so the original values remain available for formulas and calculations.
Excel automatically displays each value with the appropriate K, M, or B unit based on its size

 

Method 2: Use a Formula to Display K, M, or B Automatically

When your worksheet contains values of different sizes, a formula can automatically determine whether each number should be displayed with K, M, or B.

Unlike a custom number format, this method returns the abbreviated result in a separate cell. It is useful for reports, labels, dashboards, and summary tables where the displayed unit needs to update dynamically according to the original value.

  1. Suppose the original number is in cell A2. Select cell C2 and enter the following formula: 
    =IF(ABS(A2)>=1000000000,TEXT(A2/1000000000,"0.0")&"B",
IF(ABS(A2)>=1000000,TEXT(A2/1000000,"0.0")&"M",
IF(ABS(A2)>=1000,TEXT(A2/1000,"0.0")&"K",
TEXT(A2,"0"))))
  2. Press Enter to return the abbreviated result.
  3. Drag the fill handle down to apply the formula to the remaining rows.
    Use a Formula to Display K, M, or B Automatically

How the formula works

  • If the absolute value is at least 1,000,000,000, the formula divides it by one billion and adds B.
  • If the absolute value is at least 1,000,000, the formula divides it by one million and adds M.
  • If the absolute value is at least 1,000, the formula divides it by one thousand and adds K.
  • If the value is below 1,000, the formula displays the original number without an abbreviation.
  • The ABS function checks the absolute value, allowing both positive and negative numbers to be classified correctly.

Limitation of the Formula

Because K, M, or B is joined to the result, the formula returns text rather than a numeric value. These results cannot be directly summed or averaged without additional conversion.

 

Method 3: Use Kutools for Excel to Display K, M or B Automatically

Kutools for Excel can automatically display each number in thousands, millions, or billions according to its value, eliminating the need to create complex nested formulas or custom number formats. It provides a quick and user-friendly way to format mixed-size values while keeping the original numbers unchanged and available for calculations.

  1. Select the numbers you want to format.
  2. Click Kutools > Format > Number Format Helper。
  3. In the Number Format Assistant dialog box:
    • Select Number Unit from the Category box;
    • Then, choose Auto K/M/B.
    • Check the converted results in the Preview pane

    set options in the kutools feature
  4. Click OK.

Result: Kutools automatically applies the appropriate K, M or B format to each number in the selected range.
Use Kutools for Excel to Display K, M or B Automatically

Kutools for Excel

Kutools for Excel includes 300+ powerful features to simplify complex tasks and improve your productivity in Excel.

With tools like Number Format Helper, you can apply professional number formats more easily and quickly without remembering complicated format codes or formulas.

Convert K, M, and B Abbreviations to Full Numbers with a Formula

When numbers are stored with abbreviations such as K, M, or B, Excel usually treats them as text, which prevents them from being used directly in calculations.

You can use a formula to convert values such as 1.5K, 2M, or 3B into their full numeric equivalents—1,500, 2,000,000, and 3,000,000,000—while leaving values that are already numeric unchanged.

  1. Select a blank cell and enter the following formula: 
    =IF(ISTEXT(A2),10^(LOOKUP(RIGHT(A2),{"B","K","M"},{9,3,6}))*LEFT(A2,LEN(A2)-1),A2)
  2. Press Enter. Select the formula cell and drag the fill handle down to apply the formula to the remaining rows. The abbreviated value will be converted into its full numeric value.
    Convert K, M, and B Abbreviations to Full Numbers with a Formula

How the formula works

1. Extract the abbreviation

RIGHT(A2) extracts the last character from A2, such as K, M, or B.

2. Find the corresponding exponent

LOOKUP(RIGHT(A2),{"B","K","M"},{9,3,6}) matches the abbreviation and returns the corresponding exponent:

  • B returns 9.
  • K returns 3.
  • M returns 6.

3. Create the multiplier

10^... converts the exponent into the required multiplier:

  • 10³ = 1,000
  • 10⁶ = 1,000,000
  • 10⁹ = 1,000,000,000

4. Extract the numeric portion

LEFT(A2,LEN(A2)-1) removes the final abbreviation and extracts the numeric portion. For example, it converts 1.5K to 1.5.

5. Calculate the full number

The extracted number is multiplied by the corresponding multiplier. Therefore, 1.5K becomes 1,500.

6. Preserve existing numeric values

IF(ISTEXT(A2),...,A2) checks whether A2 contains text. If it does, the abbreviation is converted; if A2 already contains a numeric value, the original value is returned unchanged.

Which Method Should You Use?

The best method depends on whether you want to change only the display, create a separate reporting column, or convert text abbreviations into actual numbers.

MethodAdvantagesDisadvantages
Custom Number FormatKeeps values numeric and requires no helper columnFormat codes can be difficult to remember and have limited flexibility
FormulaAutomatically applies K, M, or B based on each valueRequires a helper column and usually returns text
Kutools for ExcelFast, easy, and requires no formulas or format codesDownload Kutools for Excel Now!

Tips for Formatting Large Numbers in Excel

1

Keep the Original Values

Custom number formatting does not reduce or round the actual stored number. A cell displaying 2.5M may still contain the complete value 2,500,000.

You can confirm the original value by selecting the cell and checking the Formula Bar.

2

Choose a Consistent Unit

For comparisons, it is often better to display every number using the same unit.

For example, a financial report may be easier to compare when all values are shown in millions:

0.8M2.5M125.0M

Using a mixture of K, M, and B is more compact, but readers may need extra time to compare the values.

3

Keep Enough Precision for Your Report

Displaying 1,249,000 as 1.2M is compact, but it removes visible detail. For financial or analytical reports, consider displaying two decimal places or keeping the exact value in a separate column.

4

Add the Unit to the Column Heading

To avoid confusion, include the unit in the heading when the entire column uses the same scale.

Examples:

✓ Revenue (USD, millions)

✓ Website Visits (thousands)

✓ Market Value (billions)

This is especially important when a report is printed or shared with other users.

Frequently Asked Questions

1 Does formatting a number as K, M, or B change its actual value?

No. A custom number format changes only the displayed appearance. For example, a cell containing 1,500,000 may display 1.5M, but Excel still stores 1,500,000.

You can confirm this by selecting the cell and checking the value in the Formula Bar.

2 Can formatted numbers still be used in calculations?

Yes. Numbers formatted with a custom number format remain numeric and can be used in SUM, AVERAGE, PivotTables, charts, and other calculations.

Note: Formula results that join a suffix using &"K", &"M", or &"B" are text.
3 How do I remove the K, M, or B display format?

Select the cells, press Ctrl + 1, and choose General or Number in the Format Cells dialog box.

The full values will be displayed again.

4 Can I use these methods in charts and PivotTables?

Yes. Custom number formats can be applied to worksheet cells, PivotTable values, data labels, and chart axes. However, some chart elements may require the format to be configured separately in the chart formatting pane.

5 Why does a value such as 999,999 display as 1000.0K instead of 1.0M?

A fixed thousands format always divides the value by 1,000, so it does not automatically switch to millions after rounding. To display values according to their rounded size, use this conditional custom format instead:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

With this format, 999,999 is displayed as 1.0M instead of 1000.0K.

Conclusion

Formatting large numbers as thousands, millions, or billions can make Excel reports cleaner, more compact, and easier to understand.

A custom number format is the best choice when you want to shorten the display without changing the underlying values. A formula offers greater flexibility when each number needs to be evaluated individually, while Kutools for Excel provides a faster and easier solution for applying these formats without remembering complex codes.

Choose the method that matches your dataset, reporting requirements, and preferred workflow.