Google Sheets shares most basic functions with Excel, but it offers some that stem from being a web-based, collaborative application. Some have no direct Excel equivalent, others only have one in the most recent versions. Knowing them lets you make real use of the tool, instead of treating it as Excel in a browser. Depending on the interface language some function names may appear translated; here I use the original names.
QUERY: interrogating data with SQL-like syntax
QUERY is probably the most powerful function in Google Sheets. It takes a range and a string describing what to extract, with a syntax inspired by SQL:
=QUERY(A1:E5000, "select B, sum(E) where C = 'North' group by B order by sum(E) desc", 1)
This formula returns, for each agent (column B), the total of the amounts (column E) in the North area, sorted from highest to lowest. The third argument says how many header rows there are.
The main clauses are:
| Clause | Purpose |
|---|---|
select |
which columns to return |
where |
which rows to keep |
group by |
how to group |
order by |
how to sort |
limit |
the maximum number of rows |
label |
how to rename the result headers |
A few quirks worth knowing:
- columns are referred to by letter (
A,B) when the source is a range; - text in a condition goes in single quotes:
'North'; - dates need a specific syntax:
where A >= date '2026-01-01'; - QUERY decides each column's type from the prevailing values: in a column with numbers and text mixed, the minority values may be treated as empty. One more reason to keep data consistent.
With QUERY you can replace a pivot table with a formula, and the result updates automatically when the data changes.
IMPORTRANGE: data from another file
=IMPORTRANGE("URL or ID of the sheet", "Sales!A1:E5000")
IMPORTRANGE brings data from another Google sheet into a file. The first time, the program asks you to authorise the connection between the two files; after that, updates are automatic. It is very useful for building a central summary fed by several sheets filled in by different people.
Combined with QUERY it becomes even more effective, because it lets you import only the rows you need:
=QUERY(IMPORTRANGE("ID", "Sales!A1:E5000"), "select Col2, sum(Col5) group by Col2", 1)
Note that when the source is the result of another function, columns are referred to as Col1, Col2 and so on, not by letter.
Bear in mind: anyone who can edit the destination file sees the imported data, even without access to the source file. Before linking two files, consider who they are shared with.
ARRAYFORMULA: one formula for the whole column
Before dynamic arrays, ARRAYFORMULA was Google Sheets' way of applying a formula to a whole range:
=ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))
Written once in D2, it calculates the product for every row, including those added in future. The condition on an empty A2:A avoids filling the column with zeros all the way to the bottom of the sheet. It is especially useful in sheets linked to a Google Form, where each response adds a row and formulas copied by hand would not reach the new rows.
SPLIT and JOIN
=SPLIT(A2, ";") divides text into several cells by a separator; =JOIN(", ", A2:A10) does the reverse. They are more direct than the combinations of text functions needed in older Excel versions.
Regular expressions
Google Sheets includes three functions based on regular expressions:
REGEXMATCH(text, pattern)checks whether the text matches the pattern;REGEXEXTRACT(text, pattern)extracts the matching part;REGEXREPLACE(text, pattern, replacement)replaces it.
For example, =REGEXEXTRACT(A2, "[A-Z]{2}\d{6}") pulls out of a free-text description a code made of two capital letters and six digits. For anyone who knows regular expressions, they are a far more powerful data-cleaning tool than traditional text functions.
Other useful functions
- IMPORTHTML and IMPORTXML import tables or elements from public web pages; useful for data that changes often, but they depend on the page's structure and stop working if it changes.
- GOOGLETRANSLATE translates the content of a cell; handy for a first understanding, not for texts to be published.
- SPARKLINE draws a small chart inside a cell, useful in summary panels.
Collaboration and history
Although they are not functions, two features change the way you work: version history, which lets you see who changed what and restore an earlier state, and the ability to protect specific ranges while leaving others editable. In my work, when a sheet is filled in by several people, I use QUERY and IMPORTRANGE in a separate read-only file: the data stays where it is entered, the summary stays clean.
In short
- QUERY interrogates data with SQL-like syntax and can replace a pivot.
- IMPORTRANGE links several files; watch who can see the imported data.
- ARRAYFORMULA applies a formula to entire columns, ideal with Google Forms.
- SPLIT, JOIN and the REGEX functions make cleaning text easier.
- Version history and protected ranges make collaboration safe.
