Google Sheets Functions to Speed Up Your Work

This is the first post on this website and I would like to begin the blog with my 3 top functions on Google Sheets that will speed your work up by a lot.

Query

This is probably my favourite, but one which I use with caution as too many of them in a sheet will slow your sheet down (or potentially breaking it).

What this QUERY formula does is to "query" or summarise or segment your data based on what you like it to be.

The possible ways to segment or summarise your data is almost limitless and is solely dependent on how you query it.

This query takes two required arguments:

=QUERY([your data to query], [your SQL-like query])

For the first query, you just need to highlight the data you want to query. The second part looks scary but it's actually quite simple.

I will write another post about this but here's a simple explainer.

The query, based on Google Visualization API Query Language, usually starts with a "SELECT" statement. Following that, you add the columns (alphabets in capitals) that you would like to be selected, separated by commas.

=QUERY(A:G, "SELECT A, B, D, E, G")

What you did with the formula above is selecting only 5 of the 7 available ones (in your first argument), leaving out columns C and F.

Following that, you can do a filter ("WHERE") and order ("ORDER BY") to simplify your data. For example, let's say you want to filter to rows where column A says "chair" and order by column D descending. The following query lets you do that.

=QUERY(A:G, "SELECT A, B, D, E, G WHERE A CONTAINS 'Chair' ORDER BY D DESC")

As you can guess, this query is very useful for data exploration, filtering, segmenting, reporting and much more.

There's more to this amazing function that I'm sure to cover in following posts. Here is the documentation to the QUERY function for your exploration!

ArrayFormula

Though not strictly a formula, this is extremely useful to slim down the load on your Google Sheets.

FYI this formula is available for MS Excel as well.

Arrayformula is used when you want to process data and outputs out as a range of values, instead of just one cell.

Based on that premise, we can save on the number of cells with formulae in them, saving plenty of processing power and memory.

For example, let's say you want to join the numbers in column A to the string values in column B pair-wise and output in column C.

To do so, you can either key in the following formula in each cell in column C from row 1 - 11

=A1&B1

Or, you can use arrayformula like this

=ArrayFormula(A1:A11&B1:B11)

First, you need to array the input (i.e. A1:A11 instead of just A1) as it makes sense to input an array if you're expecting an array output. Then, click Ctrl + Shift + Enter (Cmd + Shift + Enter on a Mac) when confirming your formula

Okay, this seems like a very useless example, but some of you might already have as to how this can improve your work especially when you're working with a huge dataset.

Here's an application of the ArrayFormula to create a powerful, flexible multi-market calendar aggregator to keep track of upcoming holidays for multiple countries.

 

Google Translate and Detect Language

What else can I say about Google Translate that hasn't been said? It's one of the greatest inventions Google gave us and do you know that you can access it directly from Google Sheets?

This feature is especially useful to me when I need to translate a huge amount of multi-language keywords or ad copies to a specific language e.g. Japanese.

You need 3 arguments to use this function:

=GOOGLETRANSLATE(text, [source_language], [target_language])

You can set [source_language] as "auto" to auto-detect the language, or you can force it as one using the language code (e.g. "id" to force it as Bahasa Indonesia instead of Malay/Bahasa Malaysia).

To translate a whole bunch of multi-language keywords to Japanese, use this:

=GOOGLETRANSLATE(text, "auto", "ja")

Another great function is DETECTLANGUAGE, where you can use Google Translate's technology to identify the language of the word.

=DETECTLANGUAGE(text_or_range)

This is especially useful when you have a huge load of raw, dirty, uncleaned data and you just want to filter to English. You can use this formula and then filter to only those that says "en".

As you can see, it's not perfect.

 

Bonus! Text Join / Join

There are many other functions that I will likely cover in future posts. But here's one that's useful too.

Remember earlier when we want to join Column A and Column B for the arrayformula example? Instead of separating the values by "&", there's a dedicated formula for that.

You can use TEXTJOIN or JOIN. The main difference is TEXTJOIN allows for detection of blank columns.

So it's more useful for fluid data (i.e. data that is constantly updated or streamed) when the number of rows is flexible.

With these two formulae, you can add separators/delimitors that separate the column data.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

 

I really hope these have been helpful and I will be sharing more in future posts. So follow me on Twitter to get updated about future posts!