Show Thousands as K in Excel (3 Easy Ways)

When you work with large figures in Excel, for instance, financial numbers, displaying thousands as K can make the data more readable. For example, it is easier to read 334K than 334,703.

In this tutorial, I will show you ways to show thousands as K in Excel ranges and charts.

Table of Contents

Show Thousands as K in Cells

I will show you ways to show thousands as K in Excel ranges in this section,

Method #1: Using Custom Number Formatting

You can use custom number formatting to display thousands as K in Excel. The formatting only changes the way numbers are displayed but does not change the actual numbers stored in cells.

Let’s say you have the dataset below and want to format the numbers to show thousands as K:

Here’s how to do it:

Select the cell range with the values you want to format as thousands.

Open the Home tab and click the dialog box launcher in the bottom right corner of the Number group to open the Format Cells dialog box.

Alternatively, you can launch the Format Cells dialog box by pressing CTRL + 1.

On the Number tab of the Format Cells dialog box, select the Custom category on the Category list box, enter the custom format 0,”K” on the Type box, and click OK.

enter the custom format to show numbers in thousands in K

The above steps display thousands as K in the target range, as shown below:

Note: This only changes the way you see the number in the cell, and not the actual numbers in the cell. So if you’re using these cells in calculations and formulas, this wouldn’t affect it.

Does not change the actual number — Cell value isn’t changed as shown in the formula bar

Explanation of the Custom Format

The custom format 0,”K” displays numbers in thousands with the letter ‘K’ appended, denoting thousands.

Here’s how it works:

0 – The zero represents the number of digits Excel displays after the formatting converts the number to thousands. For instance, 116250 becomes 116 and 25984 becomes 26. The zero ensures that at least one digit is shown even if the number is less than a thousand. You can sometimes use a ‘#’ instead of a zero, but if the target number is less than a thousand, Excel will display a blank cell.
Comma(,) – A single comma indicates dividing the number by a 1000 or moving the decimal point three digits to the left.
K – This is the letter you want appended to the formatted number.

Note: You can use the custom format 0.0,”K” if you want Excel to show the number with one decimal place and 0.00,”K” to show the number with two decimal places.

If you want to remove the custom formatting, select the numbers, open the Number Format drop-down menu on the Number group, and select General on the list.

Method #2: Using the TEXT Function

You can use the TEXT function to display thousands as K in Excel. The TEXT function converts a value to text in a specified number format.

Suppose you have the dataset below and want to use the TEXT function to show thousands as K.

Below is the formula that can do the job for you:

=TEXT(B2,"0,")&"K"

Enter the formula in cell C2 and copy it down the column:

Notice that the results of the formula are text strings and therefore cannot be used in numeric calculations.

If you want to replace the original values in column B with the formatted ones in column C, copy the text strings and paste them as values.

Explanation of the Formula

=TEXT(B2,"0,")&"K"

The formula takes a number in cell B2, uses the format code 0, to format it to display in thousands, and appends the letter ‘K’ to indicate thousands.

Note: I have explained the format code 0, in the section ‘Explanation of Custom Formatting’ in Method #1.

Method #3: Using the MROUND Function

You can use the MROUND function to round a number to a specified multiple and append a ‘K’ to the result.

Suppose you have the dataset below and want to use the MROUND function to show thousands as K.

You can use the formula below:

=MROUND(B2/1000,1)&"K"

Enter the formula in cell C2 and copy it down the column:

Note: While the MROUND function returns a numeric value, appending a ‘K’ to the result converts it to a text string, which cannot be used in numeric calculations.

If you want to replace the original values with the formatted ones, copy the text strings and paste them as values.

Explanation of the Formula

=MROUND(B2/1000,1)&"K"

Here’s a breakdown of the formula:

B2/1000 – Divides the value in cell B2 by 1000 converting it into thousands.
MROUND(B2/1000,1) – Rounds the result from the division to the nearest whole number. The MROUND function rounds a number to the nearest specified multiple. Here, it rounds to the nearest whole number (multiple of 1).
&”K” – Concatenates the rounded number with the letter ‘K,’ turning the result into a text string representing thousands.

If you want to display a number with one decimal place, you can modify the formula as follows:

=MROUND(B2/1000,0.1)&"K"

Similarly, to display a number with two decimal places, you can use the formula below:

=MROUND(B2/1000,0.01)&"K"

Also read: Round Up to the Nearest Whole Number in Excel (Formulas)

Show Thousands as K in Charts

You may need to show thousands as K on your Excel charts to shorten the values on the axes and data labels to make the chart more readable.

Let’s say you have the chart below with the axes and data label values in General number format and want to display thousands on the axis and data labels as K.

You can use the steps below to display thousands on the data labels as K:

Click on one label to select all labels.
Right-click the selection and choose Format Data Labels on the shortcut menu.

The step above opens the Format Data Labels pane on the right of the Excel window.

On the Format Data Labels task pane, select the Number category to expose its options, enter 0,”K” on the Format Code box, and click the Add button.

Enter the custom code to show thousand in K

The above step displays thousands as K on the data labels, as shown below:

Similarly, to format the numbers on the y-axis, right-click the values on the axis, select Format Axis on the shortcut menu to open the Format Axis pane on the right of the Excel window, and follow step 3 above.