Explaining syntax differences in your formulas due to your Google Sheets location

Did you know that formulas are written differently in the US versus Italy? (And that’s just one example.)

This post explores the syntax differences that occur based on your Google Sheets location, i.e. the location you’re working in, assuming your Google settings match (which they would by default).

Formula syntax based on Google Sheets location
What wizardry is this? Either this format will look utterly normal to you, or it won’t.

If you’ve ever copied a template but been unable to get it working, or simply not understood a formula, then it’s possible you’ve run into this syntax issue due to Google Sheets location.

This handy guide will show you the differences and hopefully help you translate seamlessly when sharing Sheets in different locations.

For the most of the world, aside from Europe, you write decimals with a decimal point notation (for example $2.50) and your formulas will use commas to separate the different parts.

I’m currently based in the US, my Google account is set to a US location, so all the articles and template downloads on this site use this notation. (Incidentally, I’m from the UK originally, but since they use the same decimal notation there, formulas in my Google Sheets are the same regardless.)

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 now where you’re based and how you write your decimal numbers, and then see the different sections below for guidance on how your formulas are written.

How to change Google Sheets location

Before getting to the nitty-gritty of formula syntax, let’s first see where we set the location.

You can specify your Google Sheets location under the File menu:

File > Spreadsheet settings...

which brings up settings pane, where you can specify the Google Sheets location:

Settings to change Google Sheets location

It’s done on a per-Sheet basis, so you can have different locations set for different projects if you require.

Syntax Rules

Locations using decimal points

For locations using periods (or points or dots or whatever you call them) to denote decimal separators (most non-European countries including US, UK, Australia), the syntax will follow this structure:

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

Locations using decimal commas

For locations using commas to denote decimal separators (most European countries), the syntax will follow this structure:

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

Of course, the best way to understand these rules is to see them in action, so without further ado, here you go!

Formula examples showing change with Google Sheets location

The following formulas show the different syntaxes side-by-side. Any components that change are highlighted in red.

Basic FILTER example

A) Decimal point notation (e.g. US):

=FILTER( A1:B10 , A1:A10 = "apple" )

B) Decimal comma notation (e.g. Italy):

=FILTER( A1:B10 ; A1:A10 = "apple" )

In this example, the comma is changed to a semi-colon.

Date Formula example

This formula example shows how to extract the first and last dates of the current month.

A) Decimal point notation (e.g. US):

=EOMONTH( TODAY() , - 1 ) + 1
=EOMONTH( TODAY() , 0 )

B) Decimal comma notation (e.g. Italy):

=EOMONTH( TODAY() ; - 1 ) + 1
=EOMONTH( TODAY() ; 0 )

In this example, the comma is changed to a semi-colon.

VLOOKUP Formula example

A) Decimal point notation (e.g. US):

=VLOOKUP( F1 , A1:C10 , 2 , FALSE )

B) Decimal comma notation (e.g. Italy):

=VLOOKUP( F1 ; A1:C10 ; 2 ; FALSE )

Again, the comma is changed to a semi-colon.

Curly-brace Array Formula example

A) Decimal point notation (e.g. US) use a comma within the curly-braces array notation to create arrays of data in the same row, like this:

= { 1 , 2 }

and a semi-colon to create data arrays in the same column, like so:

= { 1 ; 2 }

which give the outputs, respectively:

Array formula syntax based on Google Sheets location

B) Decimal comma notation (e.g. Italy) use a back slash within the curly-braces array notation to create arrays of data in the same row, like this:

= { 1 \ 2 }

The vertical orientation does not change, and still uses the semi-colon to create data arrays in the same column, like so:

= { 1 ; 2 }

which give the outputs, respectively:

Array formula syntax based on Google Sheets location

So let’s see an application of this array syntax rule with the SPARKLINE formula.

SPARKLINE example

A) Decimal point notation (e.g. US):

=SPARKLINE( A1:A10 , { "charttype","column" ; "color","green" } )

B) Decimal comma notation (e.g. Italy):

=SPARKLINE( A1:A10 ; { "charttype"\"column" ; "color"\"green" } )

There are two things happening here:

i) Inside of the curly braces { ... } we apply the array syntax rules, so the comma changes to a back-slash, but the semi-colons do not need to change.

{ "charttype","column" ; "color","green" }

changes to:

{ "charttype"\"column" ; "color"\"green" }

ii) Outside of the curly braces, in the main part of the formula, where the arguments are separated by a comma in the decimal point notation, they’re separated by a semi-colon in the decimal comma notation. (I realize that sentence may be somewhat confusing! What’s happening is this:

=SPARKLINE( A1:A10 , { ... } )

changes to:

=SPARKLINE( A1:A10 ; { ... } )

And that’s as complex as it gets.

If you can remember your rules and check whether you’re operating inside of a curly brace array or not, then you should be able to convert formulas between different locations.

Default currency, date, and number formatting

Since we’re on the location topic, it’s worth spending a couple of minutes seeing what else changes.

The default currency of the Sheet will match the currency that is used by the location set for the Sheet. So if you’re Sheet is set to US, you’ll get the Dollar sign ($) when you use the currency formatter. In Europe, you’ll get the Euro sign (€), whilst in the UK, you’ll get the Pound sign (£).

Similarly the default date format will match the location, notably that US dates will appear with the Month preceding the Day, so MM/DD/YYYY, whereas everywhere else will go with the Day first, so DD/MM/YYYY. (Incidentally, this took me a long time to get used to when I first arrived in the States!)

And finally, number formatting follows the decimal point or decimal comma style that we’ve discussed all the way through this post, so $0.99 versus €0,99

Further reading

Google documentation on changing a spreadsheet’s location

Google documentation on using Arrays in Sheets

The full list of decimal comma countries is here.

Further reading on FILTER function

Further reading on Array Formulas

Further reading on SPARKLINES

If you’re into formulas, be sure to check out my free Google Sheets course: Advanced Formulas 30 Day Challenge

Leave a Reply

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