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:

dataset of numbers to add commas to in google sheets

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.

number values in google sheets 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:

selecting the numbers and navigating to the more formats icon

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

navigating to the number format to apply to the cells

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

alternative way to format values from the format tab

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.

selecting a number format will add commas to the numbers 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.

removing decimal places from the default number format

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:

selecting the currency format al adds commas to the numbers in google sheets

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(number, format)

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:


using the text function to add commas to numbers in google sheets

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:

adding a currency symbol and decimal places to the text function

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

the text function converts the number to 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:

second sample dataset with a range of numbers

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

navigating to the custom number format option

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:


adding thousand and lakh separators as a custom number format

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:


the custom number format to add crore, lakh and thousand comma separators

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

adding commas to crore, lakh, and thousand separator in numbers in google sheets

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:


custom number format for adding commas to negative numbers

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:


using custom number format to add commas to positive, negative, and currency numbers in google sheets

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.

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply