Google Sheets Formulas: QUERY, ARRAYFORMULA & More

Google Sheets-Specific Functions

While Google Sheets supports most standard Excel functions like VLOOKUP, SUMIF, and IF, it also offers a set of unique functions that take advantage of its cloud-native architecture. These include QUERY for SQL-like data analysis, ARRAYFORMULA for applying formulas to entire columns, IMPORTRANGE for cross-spreadsheet references, web import functions, and built-in regex support.

These functions are often the reason people choose Google Sheets over Excel for specific workflows. Understanding them unlocks capabilities that would require VBA macros or Power Query in Excel.

QUERY Function

QUERY is arguably the most powerful function in Google Sheets. It lets you run SQL-like queries against your spreadsheet data — selecting columns, filtering rows, sorting, grouping, and aggregating — all within a single formula.

Syntax

=QUERY(data, query, [headers])

• data — The range of cells to query (e.g., A1:E100 or a named range).
• query — A string written in Google Visualization API Query Language, which resembles SQL.
• headers — Optional. The number of header rows in your data (default is auto-detected).

The query string uses column letters (A, B, C, etc.) to reference columns within the data range. Note that these refer to the first, second, and third columns of the data range — not necessarily spreadsheet columns A, B, and C.

Example 1: SELECT and WHERE

Return the Name (column A) and Sales (column C) for all rows where the Region (column B) is "West":

=QUERY(A1:C100, "SELECT A, C WHERE B = 'West'")

This returns a filtered table with only two columns. Single quotes are used for text values inside the query string.

Example 2: ORDER BY

Return all data sorted by Sales (column C) in descending order, limited to the top 10 results:

=QUERY(A1:C100, "SELECT * ORDER BY C DESC LIMIT 10")

The LIMIT clause works just like in SQL. Combined with ORDER BY, you can easily create top-N leaderboards.

Example 3: GROUP BY with aggregation

Calculate total sales per region:

=QUERY(A1:C100, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'")

The LABEL clause renames the aggregated column header in the output. QUERY supports SUM, AVG, COUNT, MAX, and MIN aggregate functions.

ARRAYFORMULA

ARRAYFORMULA lets you apply a formula to an entire column or range with a single cell entry. Instead of dragging a formula down hundreds of rows, you write one ARRAYFORMULA and it automatically processes every row.

Syntax

=ARRAYFORMULA(array_formula)

Example 1: Multiply two columns

Calculate the total for every row by multiplying Quantity (column B) by Price (column C):

=ARRAYFORMULA(B2:B * C2:C)

Place this in D2 and it fills the entire column D with results. When new rows are added, the formula automatically covers them because the ranges are open-ended (no row limit).

Example 2: Conditional ARRAYFORMULA with IF

Apply a conditional label to every row — mark orders over 1000 as "High" and the rest as "Normal":

=ARRAYFORMULA(IF(D2:D > 1000, "High", "Normal"))

ARRAYFORMULA combined with IF is one of the most common patterns in Google Sheets. It eliminates the need to copy formulas down to new rows manually.

IMPORTRANGE

IMPORTRANGE pulls data from another Google Sheets spreadsheet into your current one. This is essential for building dashboards that aggregate data from multiple source sheets, or for sharing specific data between teams without giving access to the entire workbook.

Syntax

=IMPORTRANGE(spreadsheet_url, range_string)

Example

Import cells A1 through D50 from the "Sales" sheet in another spreadsheet:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A1:D50")

The first time you use IMPORTRANGE with a new source spreadsheet, Google Sheets will ask you to grant access. Once authorized, the data updates automatically. Note that IMPORTRANGE has a limit on the total amount of data you can import across all formulas in a single spreadsheet.

IMPORTDATA, IMPORTHTML & IMPORTXML

Google Sheets can pull data directly from the web into your spreadsheet. These functions are unique to Sheets and have no Excel equivalent.

IMPORTDATA

Imports data from a CSV or TSV file at a given URL:

=IMPORTDATA("https://example.com/data.csv")

IMPORTHTML

Imports a table or list from an HTML page. Specify whether you want a "table" or "list" and which occurrence (1 for first, 2 for second, etc.):

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries", "table", 1)

IMPORTXML

Imports data from an XML or HTML page using an XPath query:

=IMPORTXML("https://example.com/page", "//h2")

These import functions refresh periodically (typically every one to two hours). They are excellent for building live dashboards that pull from external data sources without any scripts or add-ons.

REGEXEXTRACT, REGEXMATCH & REGEXREPLACE

Google Sheets has built-in regular expression functions — something Excel does not offer natively. These are invaluable for parsing, validating, and cleaning text data.

REGEXEXTRACT

Extracts the first substring that matches a regular expression pattern:

=REGEXEXTRACT(text, regular_expression)

Example 1: Extract email domain

Extract the domain from an email address in cell A2:

=REGEXEXTRACT(A2, "@(.+)")

The capturing group (.+) after the @ symbol returns everything after the @ sign. For "user@example.com", this returns "example.com".

REGEXMATCH

Returns TRUE or FALSE based on whether the text matches a pattern. Useful for data validation.

Example 2: Validate phone number format

Check if a value in A2 matches a US phone number pattern (e.g., 555-123-4567):

=REGEXMATCH(A2, "^\d3-\d3-\d4$")

This returns TRUE for correctly formatted phone numbers and FALSE otherwise. You can combine this with conditional formatting to highlight invalid entries.

REGEXREPLACE

Replaces all occurrences of a pattern with a replacement string:

Example 3: Clean non-numeric characters

Remove all non-digit characters from a phone number so "555-123-4567" becomes "5551234567":

=REGEXREPLACE(A2, "[^\d]", "")

The pattern [^\d] matches any character that is not a digit, and replaces each match with an empty string.

UNIQUE, SORT & FILTER

Google Sheets was one of the first spreadsheet platforms to offer dynamic array functions. These functions return multiple results that automatically spill into adjacent cells.

UNIQUE

Returns only the distinct values from a range, removing duplicates:

=UNIQUE(A2:A100)

The result spills downward, listing each unique value once. This is perfect for building dropdown lists or summary categories from raw data.

SORT

Returns a sorted copy of a range. You can sort by any column, ascending or descending:

=SORT(A2:C100, 3, FALSE)

This sorts the data by the third column in descending order (FALSE = descending, TRUE = ascending). The original data is unaffected — SORT returns a new sorted array.

FILTER

Returns only the rows that meet one or more conditions:

=FILTER(A2:C100, B2:B100 = "West", C2:C100 > 1000)

This returns all rows where region is "West" AND sales exceed 1000. Multiple conditions are separated by commas (AND logic). For OR logic, use the + operator between conditions. FILTER is often preferred over QUERY for simpler filtering tasks because the syntax is more straightforward.

Google Sheets vs Excel — Key Differences

While Google Sheets and Excel share many functions, there are important differences to be aware of when switching between platforms or sharing files.

FormulaAI automatically generates the correct syntax for your chosen platform, so you do not need to memorize these differences. Select "Google Sheets" or "Excel" before converting, and the AI handles the rest.

Related Guides

• VLOOKUP Guide — The classic lookup function for all spreadsheet platforms
• SUMIF Guide — Conditional summing in Excel and Google Sheets
• IF Formula Guide — Logical formulas and nested conditions
• FormulaAI FAQ — Common questions about the app