1. QUERY: SQL inside a spreadsheet
QUERY lets you filter, group and sort with a SQL-like language. It is the fastest way to build a live summary of a sales sheet.
=QUERY(A1:D, "select B, sum(D) where C = 'Paid' group by B order by sum(D) desc", 1)2. IMPORTRANGE: pull data from another file
Centralize data from several team spreadsheets. The first use requires clicking "Allow access" once.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID", "Sales!A1:D")3. ARRAYFORMULA: one formula for the whole column
Instead of dragging a formula down 5,000 rows, write it once. New rows are calculated automatically, which prevents the classic "formula missing on the last rows" error.
=ARRAYFORMULA(IF(B2:B="", , B2:B * C2:C))4–6. FILTER, UNIQUE and SORT
FILTER(A2:D, D2:D > 1000) returns matching rows live; UNIQUE(B2:B) builds a clean list; SORT(range, column, FALSE) orders it. Combined, they replace most manual filtering.
7–8. SPLIT and REGEXEXTRACT for text
SPLIT(A2, " ") separates first and last names. REGEXEXTRACT(A2, "[\w.]+@[\w.]+") pulls an email out of a free-text cell — handy after copying data from emails or forms.
9–10. IFERROR and GOOGLEFINANCE
Wrap fragile formulas in IFERROR(formula, "") to keep dashboards clean, and use GOOGLEFINANCE("CURRENCY:EURUSD") for an up-to-date exchange rate in quotes and invoices. Remember that Google Sheets uses commas or semicolons as separators depending on the spreadsheet locale (File > Settings).