Why you should be using the QUERY function

Discover how the QUERY function is going to change how you work

‍

If we’ve said it once, we’ve said it a thousand times - in our line of work automation is key. Often faced with a heavy workload, finance teams in start-ups and scale-ups are going to need to improve process efficiency to keep up as the company grows. With Google Sheets fast becoming the favoured tool amongst such teams, where work is generally online, highly collaborative and shared with outside clients, it’s arguably the first place you should look to optimise.

Automation in the finance function

‍

For a myriad of reasons, teams often don’t decide to make use of great tech, software and training out there to aid with automation. Among the many motives behind the reluctance include

💸 It’s viewed as too expensive

🤖 It removes the human element

⌛ It’s complicated and takes too much time

‍

But this way of thinking is behind the times! 

1. There’s a huge selection out there, including free and cheap options that are tailored to different start-ups. 🧧
2. Automation is all about freeing up time from monotonous tasks so that you can focus on other more important areas. Rather than spending hours on payroll, you can spend that extra time rethinking your forecasting strategy. Humans will still be needed! 🧑
3. Software and tech are becoming ever more user-friendly. I’m sure even your grandma could have a go at using ChatGPT. With Google at our fingertips, we have access to a wealth of educational resources. Putting in the time to learn now could save you hours down the line. ⏳

The power of the QUERY function

‍

Where better to look to improve and automate tasks than the bread and butter of our sector - Google Sheets. From employee information to customer sales, making sense of rows and rows of data is a task many of us are familiar with. 

As much as we resist the stereotypes, the reality is we are formula pros. However, even we can be limited by our tools. Ever had a mess of filters and functions scattered throughout your sheet? Tired of having to copy and paste them and then go back in and update them when you realise you wrote A3:35 instead of A3:36? We know your pain. 😞

💥 Enter the QUERY function. 💥

Dubbed by some as the most powerful feature of Google Sheets, it’s something you can start learning and using today. No need to download any program or extension. Just start typing it in your formula box.

What exactly does it do? The QUERY function lets you manipulate data in Google Sheets. Use it to analyse, aggregate, filter or transform data sets. It’s particularly useful if you want to visualise specific subsets from your data - a bit like a pivot table and filter combined. 💪

If you’ve ever worked with SQL (check out our SQL crash course blog if you haven’t), it’s going to be even easier for you to get your head around it. But even if you’re a complete beginner, it’s still going to be simple to get the basics down. Luckily for us anglophones, it uses descriptive English words like ORDER BY and SELECT.

Advantages

‍

The advantages of this powerful tool are endless.

Here are but a few:

➡️ QUERY datasets update in real time, refreshing your spreadsheets too. 

➡️ You can reference your QUERY in other Google applications like Slides. If you update your QUERY dataset, it will automatically do so in the other applications too.

➡️ You can omit the columns that you don’t want to appear in your result.

➡️ Use one single QUERY rather than individual formulas, making your work neater and avoiding mistakes that may arise from copying and pasting.

➡️ Once you’ve written a QUERY for a dataset, you can use it again and again, just edit it as necessary.

A quick intro

‍

Let’s take a quick look at how it’s structured.

Google Sheets query function takes 3 arguments:

=QUERY(data, query, [headers])

1. The data range you want to analyse 👾

2. The query you want to run (enclosed in quotations "") which uses the clauses we looked at in the last lesson ⚒️

3. An optional number to say how many header rows there are in your data 🤯

‍

Let's break down an example:

=QUERY(A1:D20,"SELECT A, D",1)

‍

What will this QUERY function do?

👾 First you have the data range of the data you want to analyse A1:D20

⚒️ Then you have the query statement using the SELECT clause. This query will select data in Columns A & D

🤯 Finally the header numbers tells your formula that our data has a single header row. As mentioned above, this is optional data and can be omitted. If omitted, the number of headers will be automatically determined by Sheets.

‍

As mentioned, your QUERY can perform multiple tasks. Let's take a look at building on our previous example:

=QUERY(A1:D20,"SELECT COUNT(A,D)",1)

Just as above, it would select columns A & D but this time it will return the count of elements in them (skipping over null cells)

Free course

‍

This is but a glimpse of the capabilities of the QUERY function. With our latest course, we’ve saved you time and energy by collating just the necessary beginner info to discover its use in the finance function. Forget having to wade through the sea of information online. See where you can implement it today and even have a go yourself with our set of realistic sales data. 

You'll get to:

💪 Discover how powerful it can be to the finance function

✍️ Learn how it's structured including its syntax, clauses and aggregation functions

🤿 Take a deep dive into each of the clauses with examples and tasks to practice with

🧠 Test your knowledge with an extended technical challenge

‍

Sign up today. Get a grasp of the QUERY function and it will become your new obsession, sumifs will be a sordid affair of the past.