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

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.

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.

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”

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.

Read More:  How to Apply Custom Number Format in Excel 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).

Step 2: Type the format_text Argument

• To enter a thousand units (K), type (#,##0,) in format_text

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

• After entering the TEXT function, write (& “K”) in the end.

• In the code (#,##0,), a single comma indicates the Thousand unit (K).

• Finally, press Enter to see the results.

Step 4: Copy the Formula

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

Read More: How to Add Number with Text in Excel Cell with a Custom Format

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.

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.

Step 3: Get the Results

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

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.

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

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.

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).

Step 3: Type the Letter ‘M’ to Indicate the Million Unit

• After Inserting the TEXT function, write (& “M”) in the end.

Step 4: Get the Result

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

Step 5: Copy the Formula

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

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

Step 1:

• Type the following formula.

Step 2:

• Then, press Enter.

Step 3:

• Apply the AutoFill to fill the blank.

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

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

• How to Apply Custom Format Cells in Excel
• How to Format Number to Millions in Excel
• How to Add Text after Number with Custom Format in Excel

<< Go Back to Custom Number Format | Number Format | Learn Excel