How to Add Commas to Numbers in Google Sheets

It can be crucial to add commas to numbers in Google Sheets in the correct position to convey the meaning behind the digits.

Whether it be a currency, number systems from different regions of the world, or just simply stating a custom magnitude, commas in numbers can impact a lot.

Google Sheets already has measures in place with the most common formats in the default menu. But it also allows users to customize numerical values as much as they want.

How to Add Commas to Numbers in Google Sheets

To show the methods today, we have created the following set of numbers:

The numbers are random and have quite a high range. This is to better show how default and custom number formatting will impact the values.

These digits are already in a number format as we can see that they are aligned to the right.

All we have to do now is format these numbers to add commas among them.

Use Default Number Formats in Google Sheets

First, we will show the default way to add commas to a number in Google Sheets. (Click here to move to the custom number formatting method)

We start by selecting the cells that contain the numbers. Then click on the More Formats icon on the spreadsheet toolbar:

This will open a menu that shows all the possible number formats that are applicable. What we are looking for is the default Number format.

Select cells with numbers > More formats > Number

See how it shows the comma in the number value in the preview?

Alternatively, we can also access this menu from the Format tab of Google Sheets.

Format > Number > Number

Selecting the default number format will add a comma to every thousand values (every 3 digits after decimal from the right) of the number in Google Sheets.

This comma is also known as the thousands separator.

However, this default number format also adds a decimal point with two decimal values at the end of the number. But you can easily remove them using the Decrease Decimal Place icon from the toolbar.

As you have noticed, the comma separator is added after every thousand, million, billion, and so on. This is the most common type of comma separator for numbers and thus earned its place as a default option.

As such, the default currency format also follows the same convention:

Format Numbers as Text Using Formula

An alternative to adding commas to a number while also not impacting the source values is to utilize the TEXT function of Google Sheets.

The TEXT function syntax:

TEXT is primarily used to format numbers, making it the perfect function to add commas to them.

Important: The TEXT function converts the number to a text value.

For example, the formula to add commas to a number using TEXT is:

The hash (#) represents a number value whose insignificant zero (0) value will be ignored. Inputting a comma between the hashes will automatically add a thousands separator (comma) to the number.

You can also add decimal points or currency symbols in this way:

But notice that the values generated by the TEXT function are left-aligned. This means that these values are in the text format.

Get Lakh and Crore Values by Adding Commas to Numbers in Google Sheets (Custom Number Format)

By default, Google Sheets follows a numbering convention that contains the thousands separator. So, at thousand, millions, billions, and so on, we get a comma added to the number.

But many regions, especially Southern Asia, have different placements of commas after thousands. Which are:

• 1,00,000: Known as Lakh and is equivalent to hundred-thousand.
• 1,00,00,000: Known as Crore and is equivalent to ten-million.

So, we have commas after every two digits after the thousands’ comma.

In such cases, we have no other way but to create a custom number format for ourselves.

But not just any custom format, but a format that is dependent on the number value.

Here we have arranged a few numbers with increasing digits where we will add commas to the Lakh and Crore separators:

Step 1: Select the numbers and navigate to Custom number format from the Format menu (from either the toolbar or the Format tab).

More formats > Custom number format

Step 2: Let’s do a test first: We will first add a comma to the number for thousands and lakhs. The custom format is:

As you can see, although it’s darkened as we are in the custom number format window, we have successfully added a comma to the thousands place and the lakhs place.

There are two conditions here separated by a semicolon (;) (Note: You can add more conditions by using this separator).

• The Thousands Comma Condition is ##,##0
• The Lakhs Comma Condition is [>99999]##\,##\,##0

Where a comma will be added when the number value goes over 99999.

So, following this convention, the Crore Comma condition will be [>9999999]##\,##\,##\,##0.

Making our custom number format:

Step 3: Clicking on Apply will give us the following result:

Include Negative Numbers and Currency Symbols

The custom format we showed in the previous section only works for positive numbers.

To only accommodate for negative values, we simply have to make an update to the value section of the formula, which are [>9999999] and others.

The new custom format to add commas for negative lakh and crore numbers in Google Sheets is:

So, to conclude, to add custom commas to positive numbers in Google Sheets:

To add custom commas to negative numbers in Google Sheets:

Extra: To add currency symbols to the custom formula:

Note: We have used the symbol for BDT in this case. You can replace it with any currency symbol.

Final Words

That concludes all the ways we can use to add commas to numbers in Google Sheets. While generally, we can use the default options provided by the application, knowing how to create a custom number format to add commas can be indispensable.

Feel free to leave any queries or advice you might have for us in the comments section below.