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.

Graphical user interface, application

Description automatically generated

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.

Table, calendar

Description automatically generated

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

Graphical user interface, application

Description automatically generated

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

Table

Description automatically generated

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.

Table

Description automatically generated

Place a comma between any of these numbers.

Timeline

Description automatically generated

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

Table

Description automatically generated

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.

Graphical user interface, application

Description automatically generated with medium confidence

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

Graphical user interface, text, application

Description automatically generated

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:

Table

Description automatically generated

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.

Graphical user interface

Description automatically generated

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:

Table

Description automatically generated

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.

Table

Description automatically generated

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.

Graphical user interface, text, application

Description automatically generated

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

Table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.