Skip to content

How Location Affects Formula Syntax in Google Sheets

The formula syntax (like a comma used as the argument separator for functions) may be different depending on your location? For example, the formula syntax in the United States is different compared to Italy.

This article explains the syntax differences that occur based on your Google Sheets location. This is the location you’re working in, assuming your Google Account settings match your Google Sheets settings (which they should by default).

Why Is This Important?

If you’ve ever copied a copied a formula exactly as it should be, but then couldn’t get it to work, it’s probably because you’ve run into a syntax issue due to your Google Sheets location.

This article shows you the differences and will hopefully help you translate seamlessly when sharing or learning about Google Sheets in different locations.

Location Differences

For most of the world, aside from Europe, you write decimals with a decimal point notation (for example $2.50) and in Google Sheets, your functions will use a comma as the argument separator.

I’m currently based in the United States, so my Google account is set to a United States location. As a result, my Google Sheets spreadsheets all default to the United States location.

However, for countries using decimal comma separators (for example €2,50), which is most of the European countries and a select few others, the syntax for formulas is slightly different, as explained below.

So, ask yourself – where are you based and how do you write your decimal numbers? Then read the different sections below for guidance on how your formulas are written.

Locations Using Decimal Points

This includes most Non-European countries such as the United States, UK, and Australia.

The formula syntax will follow this structure.

  • Decimals are denoted by a decimal point (a period)
  • Arguments in formulas are separated by a comma
  • Horizontal data in curly-brace arrays are separated by a comma

Locations Using Decimal Commas

This includes most European countries.

The formula syntax will follow this structure.

  • Decimals are denoted by a comma
  • Arguments in formulas are separated by a semi-colon
  • Horizontal data in curly-brace arrays are separated by a back-slash

How to View and Change Your Google Sheets Location

To view and/or change your Google Sheets Location, go to File > Settings.

In the window that appears, you can see and change the location for that specific spreadsheet. Keep in mind that this setting will only apply to this specific spreadsheet and not others.

Formula Examples

Here are a few examples that demonstrate the differences. The changes are colored red.

Decimal Point Notation (e.g. US)

=SUM(A1:A10 , C1:C10 , E1:E10)

Decimal Comma Notation (e.g. Italy)

=SUM(A1:A10 ; C1:C10 ; E1:E10)

A comma is used as an argument separator for the first formula and a semi-colon is used as an argument separator for the second formula.

 

Decimal Point Notation (e.g. US)

=SPARKLINE( B4:B12 , { "charttype","bar" ; "color1","green" } )

Decimal Comma Notation (e.g. Italy)

=SPARKLINE( B4:B12 ; { "charttype"\"bar" ; "color1"\"green" } )

A comma is used as an argument separator for the first formula and a semi-colon is used as an argument separator for the second formula. Furthermore, a comma is used to separate the related pairs in the array, while a slash is used instead in the second formula.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top