While working in Excel, we can simplify how we present large numbers and display the values in thousands or in millions. All we have to do is make some adjustments in the format.
Figure 1. Final result: Thousands or millions format
Format in thousands
We want to convert the values in column B to thousands by following these steps:
- Select the cells we want to format, C3:C8
- Right click and select Format Cells or press Ctrl + 1
Figure 2. Format Cells option
- In the Format Cells dialog box, select Custom in Number tab
- In the text box for Type:, enter 0, “K”
Figure 3. Custom Format Cells dialog box
The values in column C are now displayed in thousands, with a “K” to indicate thousands. We can opt to remove the K by simply entering “0,” in the text box for Type.
Figure 4. Output: Format in thousands
Note that the values are still intact and the same as shown in the formula bar, where C3 still has the value “102,345”. Only the format is changed, where all numbers are presented as thousands.
Format in millions
In the same manner, we can convert numbers into millions.
- In the Custom window of Format Cells, we enter 0,, “M” in the text box for Type:
Figure 5. Custom Format for Millions
All numbers are now converted into the Millions number format. Note in cell D8 where the number is rounded off to the nearest millions but only as displayed. The original value of “14,698,781” still remains the same.
Figure 6. Output: Millions Format
By customizing the format of numbers, we can easily convert thousands to millions and vice versa. We can even format numbers in billions by using the format 0,,, “B”. We can further navigate the Custom Format feature to learn several other ways to present our data.