How to Format Currency in Google Sheets (An Easy Guide)

In this article, we will show you how to add and format currency values in Google Sheets.

Currency is just another numerical value that conveys a different meaning than a regular number. While it can perform all the mathematical calculations that a number can, what sets it apart is the symbolism attached to it.

So, it is crucial that you know how to mat currencies to convey the right value and meaning.

How to Format Currency in Google Sheets

How to Include or Add a Currency Format to a Numerical Value

To show our examples, we have created the following worksheet:

example dataset to format currency in google sheets

Here we can see the Salary column containing the numerical values.

On its own, we can’t evaluate the worth of these numbers. However, if we are to add currency symbols, it will be much clearer as to their worth.

So, to add currency to these numbers, select the cells first. Then navigate to the Format as currency icon in the toolbar. It is represented as a dollar ($) symbol:

the format as currency icon in google sheets

Clicking on this icon will add the default currency, USD ($) in this case, to the selected cells.

using the format as currency option to apply currency in google sheets

What we’ve applied here is the standard currency format, which includes two decimal places.

Alternative: More Format Options

Alternatively, we can also navigate to the More formats option in the toolbar to apply currency format to the numbers in Google Sheets.

The More formats option not only includes currency formats but also other number formats.

navigating to the more formats option in google sheets toolbar

Selecting the Currency rounded option will apply the currency format, but without the decimal places.

applying the currency rounded format in google sheets

Another way to access these options is from the Format tab of Google Sheets:

Format > Number > Currency

currency format options can also be found in the format tab of google sheets

No matter which way you choose, you will have successfully applied a currency format over the number values in Google Sheets.

Formatting to Change Currency Symbols in Google Sheets

The default currency symbol that is applied to the numbers is the USD ($). However, it is more than likely that everyone won’t use this currency for their worksheet.

So, how do we change the currency symbol?

How do we apply different currencies like Pounds (£) or Yen (¥)?

Thankfully, it is quite easy. Google Sheets provides us with an entire menu of currencies to select from called Custom currency.

We can get to this option from either the Format tab:

Format > Number > Custom currency

Or from the More formats icon in the toolbar:

More formats > Custom currency

navigating to the custom currency option from more formats option

Clicking on the Custom currency option will open the Custom currencies window where you can either input the currency symbol or browse through all the currencies that are available and select one:

searching through different currencies in the custom currencies window

Select a currency option to apply it to the selected cells. In our example, we have converted USD ($) to British Pound Sterling (£).

using custom currencies to apply the pound currency format in google sheets

Format Currency to Include Lakh and Crore Comma Separators in Google Sheets

Some currencies, especially those around the Indian subcontinent, do not follow the same comma separator conventions as that most Western countries.

For example, the Indian Rupee (₹) follows the…

  • Lakh (equivalent to a hundred thousand: 1,00,000)
  • Crore (equivalent to ten million: 1,00,00,000)

… currency comma separators.

This is quite different from the thousands’ comma separators that we see after every 3 digits for other calculations.

However, even if we apply the Indian Rupee as the currency in Google Sheets, the comma separators do not update:

wrong comma separators

To format in the lakh and crore comma separators, it is best to use the Custom number format feature of Google Sheets instead.

After selecting the cells, you can find this option from either the Format tab or the More formats icon from the toolbar:

navigating to the custom number format option in google sheets

All we have to do now is input the custom formula to include the comma separators in their correct position.

The formula is:

[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0

custom number format formula for lakh and crore separators

Formula Breakdown:

  • [$₹]##,##0: For the first ten thousand digits, we’ve only added the currency symbol.
  • [$₹][>99999]##\,##\,##0: For numbers greater than 99999, we’ve included a comma separator. This acts as the Lakh comma separator.
  • [$₹][>9999999]##\,##\,##\,##0: For values greater than 9999999, we’ve added another comma separator. This acts as the Crore comma separator.
  • There are actually three format formulas here, each separated by a semicolon (;).

Clicking on Apply will give us the following result:

formatting currency to include custom comma separators in google sheets

Note that this custom formula is for only positive currency values.

For negative currency values, you can use the following custom formula:

[$₹][<-9999999]##\,##\,##\,##0;[$₹][<-99999]##\,##\,##0;[$₹]##,##0

Learn More: How to Add Commas to Numbers in Google Sheets

How to Format Currency in Google Sheets from a Mobile Device (Android/iOS)

With handheld devices becoming as powerful as they are, Google did not sit idly by and went on to integrate Sheets into these. Quite seamlessly if I may add.

Most basic functions can be used in this Google Sheets mobile application. Whether you are using an Android or Apple device, the experience is virtually the same.

So, let’s see how we can add a currency format to cells from the Google Sheets mobile app:

Step 1: Select the cell or the range of cells you want to format. Tap on the cell once to select the cell, then tap and drag to select multiple cells.

Step 2: After selecting the cells, tap on the Format icon located at the top of the worksheet. See image:

selected cells and format icon in google sheets mobile app

Step 3: Move to the Cell tab. Scroll down to find the Number format option.

the number format option under the cell tab

Step 4: Here you’ll find all the available number formats, including a few currency formats.

currency formatting options

Note: If you want a different currency than the default USD ($), choose the More currencies option.

Step 5: For this example, we have selected the Currency (rounded) option. The result:

apply currency format from the google sheets mobile app

Final Words

Adding a new currency format in Google Sheets is as easy as clicking a button. This simplicity is carried forward also when you will look to further customize these formats to other currencies.

However, formulas might be required when trying to change the default formatting, for example, changing the positions of comma separators. Even then, the process is fairly straightforward.

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

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com 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

OfficeWheel
Logo