How to Format a Number in Thousands K and Millions M in Excel?

Get FREE Advanced Excel Exercises with Solutions!

While working with large numbers, it’s not easy to visualize data with thousands and millions. That’s why you may need to put units for better visualization. In this tutorial, we will show you how to format a number in thousands K and millions M in Excel.


How to Format a Number in Thousands K and Millions M in Excel: 4 Suitable Ways

In the sections that follow, we will demonstrate four appropriate ways to format numbers with units of thousands (K) and millions (M). To begin, we will utilize the Format Cell option, followed by the TEXT function to complete the work.

Sample Data


1. Utilize Custom Format Cell to Format Number in Thousands K in Excel

To utilize the Format Cell option, follow the steps below.

Step 1: Selection of the Cells

  • Select the cells.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 2: Insert Code in the Format Cells Box

  • Press Ctrl + 1 to open the Format Cells dialog box.
  • Click on the Custom.
  • In the Type box, type the following code.

#,##0 “K”

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 3: Get the Results

  • Press Enter to see the numbers formatted with the unit in (K) and divide each cell value by 1000 to convert the values into K units.

Utilize Custom Format Cell

Read More: Excel Custom Number Format with Multiple Conditions


2. Apply TEXT Function to Format Number in Thousands K in Excel

You can use the TEXT function to format numbers with units. To do so, follow the simple steps outlined below.

Step 1: Insert the TEXT Function

  • In the value argument, type the cell number (B5).
=TEXT(B5,"#,##0, ")

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 2: Type the format_text Argument

  • To enter the thousand units (K), type (#,##0,) in format_text
=TEXT(B5,"#,##0, ")

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 3: Type the Letter ‘K’ to Indicate the Thousand Unit

  • After entering the TEXT function, write (& “K”) in the end.
=TEXT(B5,"#,##0, ") & "K"
  • In the code (#,##0,), a single comma indicates the Thousand unit (K).

Suitable Ways to Format Number in Thousands K and Millions M in Excel

  • Finally, press Enter to see the results.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 4: Copy the Formula

  • Use the AutoFill tool to copy the formula to the rest cells.

Suitable Ways to Format Number in Thousands K and Millions M in Excel


Similar Readings


3. Utilize Custom Format Cell to Format Number in Millions M in Excel

To apply the millions (M) unit, follow the instructions below.

Step 1: Selection of the Cells.

  • Firstly, select the cells.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 2: Insert Code in the Format Cells Box

  • To open the Format Cells dialog box, press Ctrl + 1.
  • Then, click on the Custom.
  • Type the following code, in the Type.

#,##0,, “M”

  • (#,##0,,), the double comma indicates units to millions.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 3: Get the Results

  • Finally, press Enter to get the units in millions (M).

Suitable Ways to Format Number in Thousands K and Millions M in Excel


Notes: Look that, the numbers in the million units (M) are shown in the round figure as there are no decimal places. To increase the decimal places, follow the steps below.

Step 1:

  • Click on the Increase Decimal option from the Number.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

  • As shown in the image below, click three times on the Increase Decimal to add three decimal places.

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Read More: How to Apply Number Format in Millions with Comma in Excel


4. Apply TEXT Function to Format Number in Millions M in Excel

We can also apply the TEXT function to format numbers in Millions (M) as we applied for the thousands (K). Follow the outlined procedures below to accomplish the task.

Step 1: Insert the TEXT Function

  • Type the cell number (B5), in the value.
=TEXT(B5,")

Suitable Ways to Format Number in Thousands K and Millions M in Excel

Step 2: Type the format_text Argument

  • Type (#,##0,,) in format_text argument to enter the million units (M). Here, (#,##0,,) double comma indicates the million unit (M).
=TEXT(B5,"#,##0,, ")

 

Sample Data

Step 3: Type the Letter ‘M’ for Indicating the Million Unit

  • After Inserting the TEXT function, write (& “M”) in the end.
=TEXT(B5,"#,##0,, ") & "M"

Sample Data

Step 4: Get the Result

  • Consequently, press Enter to see the result in the million units (M).

Sample Data

Step 5: Copy the Formula

  • To copy the formula to the blank cells, Use the AutoFill.

Sample Data


Notes: To increase the decimal places to three digits, follow the instructions below.

Step 1:

  • Type the following formula.
=TEXT(B5,"#,##0.000,,") & "M"

Sample Data

Step 2:

  • Then, press Enter.

Sample Data

Step 3:

  • Apply the AutoFill to fill the blank.

Sample Data

Read More: Custom Number Format: Millions with One Decimal in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Finally, I hope you now understand how to format a number in thousands K and millions M in Excel. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.


Related Articles

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo