Bhubon Costa
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Format a Number in Thousands K and Millions M in Excel
In the sections that follow, we will demonstrate four appropriate ways to format numbers with units of thousand (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).
Read More: Excel Custom Number Format 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, ")
Step 2: Type the format_text Argument
- To enter the thousand units (K), type (#,##0,) in format_text
=TEXT(B5,"#,##0, ")
Step 3: Type the Letter ‘K’ for Indicating 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).
- 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 Custom Cell Format Number with Text in Excel (4 Ways)
Similar Readings
- How to Apply Accounting Number Format in Excel! (Easy way)
- Remove Leading Zeros in Excel (7 Easy Ways + VBA)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- How to Convert Number to Percentage in Excel (3 Quick Ways)
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 Demical 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 (5 Ways)
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,")
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,, ")
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"
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.
=TEXT(B5,"#,##0.000,,") & "M"
Step 2:
- Then, press Enter.
Step 3:
- Apply the AutoFill to fill the blank.
Read More: Custom Number Format: Millions with One Decimal in Excel (6 Ways)
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.
The Exceldemy staff will get back to you as soon as possible.
Stay with us and continue to learn.
