Format Numbers in Thousands or Millions as K and M

Often, we encounter the need to show numbers in the scale of thousands or millions, represented as K and M, respectively. This practice proves helpful when showing data on charts and carries substantial significance within financial reports, amplifying comprehension and insights. Whether commas separate your thousands (,) or decimals (.), you will learn a straightforward technique of formatting numbers in thousands and millions to show as K and M, respectively.

Show Thousands as K (Comma Separator)

Let's say you have some numbers in thousands separated by commas in the range B3:B10.

To display those values as K, follow these steps:

1. Select the range B3:B10.
2. Right-click to open the context menu.
3. Click on Format Cells… in the menu. The Format Cells dialog box will open.
4. Navigate to the Number tab in the dialog box.
5. Choose Custom from the Category list.
6. Enter “0, “k”” to show numbers in thousands as K in the Type section of the Format Cells dialog box.
7. Enter “0,, “M”” to show numbers in millions as M in the Type section.
8. Click OK. With the code 0,”k” in the Type section of the Format Cells dialog box, the values will display as k, and if the numbers are linked to a chart, the chart will also update.

Note that formatting numbers only change how the numbers are displayed in cells within your spreadsheet but doesn't change the actual numerical value. The inherent numerical values can be seen when the cell is selected and viewed through the formula bar.

Thousands as K (Decimal Separator)

In some regions, decimals are commas, and commas are decimals. For example, in Sweden, decimals are commas, and vice versa. Hence, a Swedish-locale Excel separates values in thousands with decimals and decimal values with commas. When faced with decimal-separated thousands or millions, you must ensure that your Excel uses decimal as the default separator, or you can manually change it.

To change Excel’s default separators, follow these steps:

1. With your Excel file open, go to Files and click Options.
2. Choose Advanced in the Excel Options window.
3. Uncheck the Use System Separators checkbox.
4. Manually input the required separator for decimals and thousands in the boxes below.
5. Click OK.

Now that you've ensured that your Excel uses the suitable separators for numbers, follow the steps above to show numbers in thousands as K and in millions as M by simply entering the code “0.”K”” in step 6 and “0..”K”” in step 7 for thousands and millions, respectively. Then click OK.

Format Chart values Axis in K or? M

To make charts more presentable, you will need to show values in thousands and millions using the symbols K and M, respectively.

Let's consider the column chart below:

To format the chart value axis, follow these steps:

1. Select the Vertical (values) Axis.
2. Right-click to open the mini toolbar.
3. Choose Format Axis from the mini-toolbar. The Format Axis pane will open to the right of your Excel window.?
4. Click the Number arrow to expand it.
5. Enter “0,”K””? or “0.”K”” for thousands depending on your locale, in the Format Code box.
6. Enter “0,,”M”” or “0..”M”” for millions depending on your locale to format millions as M.
7. Click Add.

Now, your value axis will be updated accordingly.

In Conclusion

The steps outlined in this article are a straightforward way to format numbers in thousands as K and millions as M for both comma or decimal separators. This practice enhances financial reports and insights.

Thank you for reading. Please share your thoughts in the comments section.

Look forward to the next month's edition. Bye!