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

Did you know that formulas are written differently depending on where in the world you’re located? For example, the syntax in the US is different to that in Italy.

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.
Continue reading Explaining syntax differences in your formulas due to your Google Sheets location

How to use the Google Sheets Filter function: a guide to get you started right now

The Google Sheets FILTER function is a powerful function we can use to, well, filter our data.

Filter function in Google Sheets

Suppose we want to retrieve all values above a certain threshold? Or values that were greater than average? Or all even, or odd, values?
Continue reading How to use the Google Sheets Filter function: a guide to get you started right now

How to use Google Sheets: A Beginner’s Guide

How to use Google SheetsHow to use Google Sheets: A Beginner’s Guide

This tutorial will help take you from an absolute beginner, or basic user, of Google Sheets through to a confident, competent, intermediate-level user.

Google Sheets is a hugely powerful tool, for everything from digital marketing to finance modeling, from project management to statistical analysis, in fact, just about any activity involving the recording and analysis of data.

And if you’re (relatively) new, it really pays dividends to learn how to use Google Sheets correctly. This tutorial will help you transition from newbie to ninja in short order!

If you’re new to Google Sheets, then I recommend you start from the beginning of this article.

However, if you’ve used Sheets before, feel free to skip sections 1 and 2, and begin with the Data and basic formulas section.

A template is available for copying to your Drive, to accompany this tutorial:

Want a copy of the template from this tutorial?
Click here to create your own copy >>

In addition, various advanced resources are listed for you to take things a step further. Look for this logo: Advanced Resource


Continue reading How to use Google Sheets: A Beginner’s Guide

2017 in review and a look forward to 2018

Happy New Year to you all!

spacex launch

Max Q‘ is a term used in rocketry to denote the moment during a rocket flight when aerodynamic stress on the rocket’s airframe is at it’s maximum. It’s one of the early milestones of any flight, and one of the most dangerous to boot. Astronauts and engineers both breathe a sigh of relief when the rocket passes this point. Engines, which have been throttled back to 60 – 70% of capacity during this phase, are once again opened up and the great fire-breathing, bone-rattling tin can accelerates rapidly upwards again, on its way to space.

Well, the summer of 2017 felt like ‘max Q’ for our family. We had a second baby boy in late May (he’s brilliant!), relocated from DC to Florida in early July, sold a house and bought a house, prepared for our first hurricane in August (that was stressful!) and had a to-do list longer than our arm, in fact, all our arms combined.

Through this, I did what I could, when I could, to keep the ball rolling with my business. I launched my second course, Data Cleaning and Pivot Tables in Google Sheets, in June, although almost all of the work was completed prior to my son’s arrival in May.

Since October, the throttle has been fully open again on the business and things are moving along nicely. I launched my third course, Advanced Formulas 30 Day Challenge, in mid December, and had over 1k students sign up in the first week.

2017 highlights

Continue reading 2017 in review and a look forward to 2018

Data Studio Connector fields get an update

The engineers at Google continue to roll out updates to Data Studio at a relentless and impressive pace.

Back in September they released community connectors, which allow you to connect any web service with an API to Data Studio for reporting.

Yesterday, they released an update which gives developers much more control over the connector fields in Data Studio, by allowing developers to define Data Types and Semantic Types with more granularity, in your Apps Script code.

Additionally, developers can now embed calculated fields into the connector’s schema too, so it’s not left up to the user to figure this out.

This is a huge improvement as it obviates the need for the end user to select the correct field settings (for example, which aggregation to use) and should therefore make it easier for users to build accurate reports.

Data Type and Semantic Type features in the connector fields

Continue reading Data Studio Connector fields get an update