Tom’s Tutorials For Excel: Formatting Numbers for Thousands or Millions
When you have a range of large numbers that can be difficult to comprehend because of their size, it helps to make the range more readable by formatting the numbers as thousands or millions.
The first step in formatting the cells is to select the range, right-click the selected range, and click Format Cells from the popup menu to show that Format Cells dialog box. Alternatively, with the range selected, from your keyboard you could press Alt+O+E
.
To format the numbers in thousands, and with an indicative “K” suffix:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter #,##0,K
• Click OK.
Here is the result of that custom format for Thousands.
To format the numbers in millions, with an indicative “M” suffix and a decimal for fun:
• Click onto the Number tab.
• In the Category list, select Custom.
• In the Type field, enter #,##0.0,,\M
• Click OK.
Here is the result of that custom format for Millions, with an optional single decimal.
Dear Tom,
While using these no. formats with TEXT function, are K & M required to be wrapped within quotation marks?
I tried doing it but failed to get the result. It is showing Value error.
I don’t understand the premise of your question. This is a format example without needing any functions. Why use the TEXT function to accomplish what this example shows how to do without the TEXT function.