Keyboard shortcuts, core formulas, and collaboration reference for Google Sheets.
| Category | Function | Syntax | Example | Notes |
|---|---|---|---|---|
| Sheets-only | QUERY | QUERY(data, query, [headers]) | =QUERY(A1:E,"select B,sum(E) where C='West' group by B order by sum(E) desc",1) | The most powerful function in Sheets. SQL-like filtering, grouping, and sorting in one formula. See the QUERY tab for full clause syntax. |
| Sheets-only | ARRAYFORMULA | ARRAYFORMULA(expression) | =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) | One formula in row 2 covers the whole column forever — no dragging, and new rows are handled automatically. The IF(A2:A="","",…) guard stops it filling blank rows with zeros. |
| Sheets-only | IMPORTRANGE | IMPORTRANGE(url, "Sheet!A1:D") | =IMPORTRANGE("1AbC...xyz","Data!A1:D100") | Live link to another spreadsheet. Requires a one-time "Allow access" click the first time — it returns #REF! until you do. |
| Sheets-only | GOOGLEFINANCE | GOOGLEFINANCE(ticker,[attr],[start],[end]) | =GOOGLEFINANCE("NASDAQ:GOOG","price") | Live and historical market data. Delayed up to 20 minutes and explicitly not for trading use, but fine for tracking. |
| Sheets-only | IMPORTHTML / IMPORTXML | IMPORTHTML(url,"table",index) | =IMPORTHTML("https://example.com","table",1) | Scrapes a table or list from a public web page. Fragile by nature — it breaks whenever the source page's markup changes. |
| Sheets-only | SPARKLINE | SPARKLINE(data,[options]) | =SPARKLINE(B2:M2,{"charttype","column"}) | A miniature chart inside one cell. Types: line, bar, column, winloss. |
| Lookup | XLOOKUP | XLOOKUP(key, lookup_range, result_range, [missing]) | =XLOOKUP(A2,Staff!A:A,Staff!C:C,"Not found") | Available in Sheets too. Searches any direction, exact match by default, with built-in not-found handling. |
| Lookup | VLOOKUP | VLOOKUP(key, range, index, [is_sorted]) | =VLOOKUP(A2,$D$2:$F$99,3,FALSE) | Always pass FALSE. The default TRUE assumes sorted data and returns wrong answers without warning. |
| Lookup | INDEX + MATCH | INDEX(range, MATCH(key, range, 0)) | =INDEX(C:C,MATCH(A2,B:B,0)) | Works in any direction and survives column insertion, unlike VLOOKUP. |
| Array | FILTER | FILTER(range, condition1, ...) | =FILTER(A2:D,C2:C="West",D2:D>1000) | Multiple conditions are AND by default (comma-separated). Use + between conditions for OR. |
| Array | UNIQUE / SORT | UNIQUE(range) | =SORT(UNIQUE(B2:B)) | The standard recipe for a self-maintaining dropdown source or a distinct-values list. |
| Array | FLATTEN | FLATTEN(range) | =UNIQUE(FLATTEN(A2:E20)) | Collapses a 2-D range into a single column. Combined with UNIQUE it de-duplicates an entire block at once. |
| Array | SPLIT / JOIN | SPLIT(text, delimiter) | =SPLIT(A2,",") | SPLIT spreads across columns; TEXTJOIN merges back with a separator and blank-skipping. |
| Aggregate | SUMIFS / COUNTIFS | SUMIFS(sum_range, range1, crit1, ...) | =SUMIFS(D:D,B:B,"West",C:C,">="&DATE(2026,1,1)) | Note the ">="& concatenation pattern when comparing to a date or cell reference. |
| Aggregate | SUBTOTAL | SUBTOTAL(code, range) | =SUBTOTAL(109,D2:D) | 109 sums only visible rows — the correct total to display above a filtered range. |
| Aggregate | COUNTUNIQUE | COUNTUNIQUE(range) | =COUNTUNIQUE(B2:B) | Distinct count in one step — Excel has no direct equivalent without a Pivot or array trick. |
| Logic | IFS / IFERROR | IFS(test1,val1,...) | =IFS(A2>90,"A",A2>80,"B",TRUE,"C") | End with TRUE,default to catch everything else, or unmatched values return #N/A. |
| Text | REGEXEXTRACT / REGEXMATCH | REGEXEXTRACT(text, regex) | =REGEXEXTRACT(A2,"[\w.]+@[\w.]+") | Full regex support built in — no Excel equivalent without VBA. Also REGEXREPLACE for substitution. |
| Text | TEXT | TEXT(value, format) | =TEXT(A2,"yyyy-mm-dd") | Converts to formatted text. The result no longer sorts or calculates as a number. |
| Clause | Syntax | Example | Notes |
|---|---|---|---|
| select | select A, C, E | "select A,C where C>100" | Column letters, not names. select * returns everything. |
| where | where <condition> | "select * where B='West' and D>500" | Text needs single quotes inside the double-quoted query. Supports and, or, not. |
| group by | group by A | "select B,sum(E) group by B" | Every non-aggregated column in select must appear in group by — same rule as SQL. |
| order by | order by A desc | "select * order by D desc limit 10" | The classic "top 10" recipe when paired with limit. |
| limit / offset | limit 10 offset 5 | "select * limit 25" | Caps returned rows. Useful for dashboards that should never grow unbounded. |
| label | label sum(E) 'Total' | "select B,sum(E) group by B label sum(E) 'Revenue'" | Renames output headers. Without it you get ugly headers like "sum Amount". |
| format | format D '#,##0.00' | "select A,D format D '$#,##0'" | Applies number formatting inside the query result. |
| aggregates | sum() avg() count() max() min() | "select B,avg(D),count(A) group by B" | Multiple aggregates in one query are fine. |
| date literals | where C > date '2026-01-01' | "select * where C >= date '2026-01-01'" | Dates need the date keyword and ISO format — a very common source of silent failure. |
QUERY recipes
Top 10 by revenue
Group, aggregate, sort, cap, and rename the header — all in one cell. Replaces a PivotTable for a fixed report.
Dynamic criteria from a cell
Concatenate a cell into the query string: "select * where B='"&F1&"'". Now F1 becomes a live filter control.
Combine with IMPORTRANGE
=QUERY(IMPORTRANGE(url,"A:F"),"select Col2,sum(Col5) group by Col2",1) — note that imported ranges use Col1/Col2 instead of letters.
Exclude blank rows
where A is not null keeps trailing empty rows out of results when you reference an open range like A2:A.
Pivot inside QUERY
Add pivot C to turn distinct values in column C into output columns — a genuine cross-tab with no PivotTable.
| Group | Action | Windows | Mac | Notes |
|---|---|---|---|---|
| Navigate | Jump to edge of data | Ctrl + Arrow | ⌘ + Arrow | Finds the last populated row/column instantly. |
| Navigate | Select to edge | Ctrl + Shift + Arrow | ⌘ + Shift + Arrow | Selects a whole data column without grabbing empty rows below. |
| Navigate | Move to next sheet | Alt + ↓ / ↑ | ⌥ + ↓ / ↑ | Cycles worksheet tabs. |
| Navigate | Open search-the-menus | Alt + / | ⌥ + / | Type any command name and run it — the fastest way to reach a menu item you can't find. |
| Entry | Fill down / right | Ctrl + D / Ctrl + R | ⌘ + D / ⌘ + R | Copies from above/left into the whole selection. |
| Entry | Insert date / time | Ctrl + ; / Ctrl + Shift + ; | ⌘ + ; | Static value, unlike TODAY() which recalculates daily. |
| Entry | Toggle absolute reference | F4 | Fn + F4 | Cycles A1 → $A$1 → A$1 → $A1 while editing a formula. |
| Entry | Insert new row above | Ctrl + Alt + = | ⌘ + ⌥ + = | With a full row selected first. |
| Format | Paste values only | Ctrl + Shift + V | ⌘ + Shift + V | Strips formulas and formatting — the fix for a formula that breaks when moved. |
| Format | Toggle filter | Ctrl + Shift + L | ⌘ + Shift + L | Adds or removes the filter row. |
| Format | Clear formatting | Ctrl + \ | ⌘ + \ | Resets a messy pasted block to plain formatting in one keystroke. |
| Format | Strikethrough | Alt + Shift + 5 | ⌘ + Shift + X | Common for lightweight to-do lists in a sheet. |
| Collab | Insert comment | Ctrl + Alt + M | ⌘ + ⌥ + M | Type @email inside to assign it as a task. |
| Collab | Open version history | Ctrl + Alt + Shift + H | ⌘ + ⌥ + Shift + H | Every change is recorded — the real undo for collaborative edits. |
| Formula | Show all formulas | Ctrl + ~ | ⌃ + ~ | Audit view — reveals hardcoded values hiding among formulas. |
| Formula | Wrap in ARRAYFORMULA | Ctrl + Shift + Enter | ⌘ + Shift + Enter | Auto-wraps the current formula in ARRAYFORMULA() rather than typing it. |
Choose the narrowest access that works
Share → specific people beats "anyone with the link". If you must use a link, set it to Viewer and grant Editor individually. Restricted-by-default is the safe posture.
Lock the formula columns
Data → Protect sheets and ranges → select the calculated columns → "Only you" can edit. Collaborators can fill in their input cells but can't accidentally paste over your formulas. This single step prevents most shared-sheet damage.
Add data validation to input cells
Data → Data validation → List from a range (point it at a UNIQUE() output so it maintains itself). Set "Reject input" rather than "Show warning" when the value genuinely matters downstream.
Use comments and assignment instead of a side channel
Ctrl+Alt+M, then type @someone@company.com to assign the comment as a task. They get an email, and the item shows as open until resolved — far more reliable than a Slack message that scrolls away.
Turn on notifications for changes
Tools → Notification settings → get an email when anyone edits, immediately or as a daily digest. Essential for a sheet other teams write into.
Name versions at milestones
File → Version history → Name current version. A named checkpoint before a big restructure is far easier to find later than hunting through timestamps.
Publish read-only rather than sharing edit access
File → Share → Publish to web gives a view-only URL or embeddable HTML that doesn't require a Google account. The right answer for a public dashboard — and it can be limited to a single sheet.
Automation options, weakest to strongest
Data validation + conditional formatting
Prevents bad input and highlights anomalies. Always the first thing to reach for — most "we need automation" requests are really this.
Notification rules
Tools → Notification settings. Alerts on any edit or on form submission, without writing anything.
Apps Script
Extensions → Apps Script. Write custom functions callable from cells, add menu items, and set time-driven or on-edit triggers. The real automation layer.
Sheets API / connectors
For pushing data in from other systems on a schedule. Prefer this over IMPORTHTML scraping when a real API exists.
Gotchas
- 10 million cells per spreadsheet is the hard ceiling — and performance degrades well before that.
- Volatile functions (
NOW,TODAY,RAND,GOOGLEFINANCE) recalculate constantly and are the usual cause of a sluggish sheet. - Many
IMPORTRANGEcalls are slow — consolidate into one import of a wide range rather than a dozen narrow ones. - Delete unused rows and columns. An empty grid still counts toward the cell limit.
- If a sheet genuinely needs millions of rows, it belongs in BigQuery, not Sheets. Sheets can query BigQuery via Connected Sheets.
- It requires an explicit one-time permission grant. Until someone clicks "Allow access", it returns #REF! with no other explanation.
- Permission is granted per source-file/destination-file pair, by a user who has access to both.
- It imports values only — no formatting, no notes, no conditional formatting comes across.
- If the source is deleted or its sharing changes, every dependent sheet silently breaks. Avoid deep chains of files importing from each other.
- In some locales the argument separator is a semicolon rather than a comma:
=SUM(A1;A2). - File → Settings → Locale controls this, plus date format and currency. It's a per-file setting, not per-user.
- Copying a formula between differently-configured files is a common and confusing source of parse errors.
- Sheets-only functions (QUERY, ARRAYFORMULA, IMPORTRANGE, SPARKLINE, REGEX*) have no Excel equivalent and break on export to .xlsx.
- Excel files opened in Sheets keep working, but VBA macros do not run — Apps Script is a rewrite, not a conversion.
- For files that must live in both worlds, stick to the common function set and avoid the Sheets-only headline features.
Tips worth adopting
A single ARRAYFORMULA in row 2 covers the entire column and handles new rows automatically. It's faster to calculate and impossible for a collaborator to break by deleting one row's formula.
Right-click a cell → View more cell actions → Get link to this cell. Sends someone to the exact row you're discussing rather than "it's somewhere in column F".
Data → Filter views creates a personal filter that doesn't change what collaborators see. A normal filter changes the view for everyone — a frequent annoyance on shared sheets.
Data → Data connectors → BigQuery lets you pivot and chart billions of rows using the familiar Sheets interface, with nothing actually stored in the spreadsheet.
Insert → Function → Help me organize, or the "Help me" AI prompt in newer builds, will draft a formula from a plain-English description. Verify the output — but it's a fast starting point for gnarly nested logic.
Stop making "Budget_v3_FINAL_v2" duplicates. File → Version history keeps everything, shows who changed what, and restores any point in time.