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.
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: 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, ")
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’ 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).
- Finally, press Enter to see the results.
Step 4: Copy the Formula
- Use the AutoFill tool to copy the formula to the rest cells.
Similar Readings
- How to Add Number with Text in Excel Cell with a Custom Format
- How to Add Text after Number with Custom Format in Excel
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.
=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
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.
