Format Numbers in Thousands and Millions in Excel

When you are dealing with large numbers where precision is less important than readability, there are a few methods you can use to display numbers in a way that they are easy to digest.

Adding thousands separator

The first quick way to change the format in which the number is displayed is to add the thousands separator.

You can find it in Home >> Number group.

This command will automatically add the decimal part to the end o the number if we used it on an integer or rounds up to two digits after the decimal point.

If you want to get rid of the decimal part, you can click the Decrease Decimal command that is located in the same group.

Now, the numbers are formated into groups of three numbers:

Using shortcut

If you want to add a thousands separator to a single number, you can do it by adding a comma after the first character, but before the third one from the end. So the number should consist of at least 4 digits.

Place a comma between any of these numbers.

Press Enter. Now, the numbers are already separated. Notice that there is no decimal value added to the number.

Using a free plugin for separator

Another way to quickly add the thousands separator is to use the Excel SEO plugin.

After you add it, navigate to SEO >> Humanize.

Click to button to expand the menu. The last of them adds the thousands separator to a number.

With this command, you can add the thousands separator to multiple numbers at once. If a number has a decimal part, it’s not taken into consideration.

This is what it looks like:

Custom format for thousands (K) or millions (M)

Another way to display numbers in a way that increases redibility is to add metric prefixes such as K, M, G, etc.

Name	Symbol	Decimal	Word
kilo	K	1 000	thousand
mega	M	1 000 000	million
giga	G	1 000 000 000	billion

To add a symbol to a number you have to use custom formatting (Home >> Number >> Number Format >> More Number Formats…).

You can also access the Format Cells window using the Control + 1 keyboard shortcut.

On the first tab (Number) there are multiple categories. We are interested in the last one: Custom.

Here, you can add your formatting. First, let’s add the “K” symbol for thousands (both positive and negative):

You can modify the formatting for millions (notice additional comma):

And for billions:

The problem with this formatting is that it lacks precision, especially for smaller numbers. We can easily fix that by modifying the number formatting. For thousands it looks like this:

You can add more zeros after the dot to add more precision.

The problem with this approach is that no matter how big is the number, it always uses the same letter.

Automatically match the letter to the number

The easier approach is to use the plugin to automatically match a letter to a number. If the number is between -1000 and 1000 then it doesn’t format the number at all, if it’s lower than -1000 and greater than 1000 it adds “K”, etc.

There are options for integer and three options for floating points.

This is what the previous example looks like with the plugin.