Google Sheets Formulas Cheat Sheet
Covers Google Sheets lookup, aggregation, QUERY, and ARRAYFORMULA functions for building dynamic, spreadsheet-based data analysis.
1 PageBeginnerMar 12, 2026
Lookup & Aggregation Formulas
Core formulas for finding and summarizing values.
javascript
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")=SUMIFS(C:C, A:A, "US", B:B, ">100")=COUNTIFS(A:A, "US", B:B, ">100")=UNIQUE(A2:A100) // list distinct values=SORT(A2:C100, 3, FALSE) // sort a range by column 3 descending=IMPORTRANGE("sheet_url", "Sheet1!A:C")
QUERY & ARRAYFORMULA
Run SQL-like queries and apply formulas across whole ranges at once.
javascript
=QUERY(A1:D100, "SELECT A, SUM(D) WHERE B = 'US' GROUP BY A ORDER BY SUM(D) DESC", 1)// ARRAYFORMULA applies a formula to an entire range at once=ARRAYFORMULA(IF(A2:A100="", "", B2:B100*1.1))// Combine with SPLIT/JOIN=SPLIT(A2, ",")=JOIN(", ", A2:A10)// Reusable custom function with LAMBDA=MAP(A2:A10, LAMBDA(x, x * 2))
Key Functions
Functions worth knowing beyond basic arithmetic.
- VLOOKUP/XLOOKUP- Look up a value in a range and return a corresponding value from another column
- QUERY- Runs a Google Visualization API SQL-like query directly on a range
- ARRAYFORMULA- Applies a formula across an entire range, returning a spilled array of results
- IMPORTRANGE- Pulls data from another Google Sheets spreadsheet by URL and range
- UNIQUE / SORT / FILTER- Dynamic array functions that return deduplicated, sorted, or conditionally filtered ranges
- LAMBDA- Defines a custom reusable function using =LAMBDA(param, formula), which can be named via Name Manager
Named LAMBDA Helper Functions
Define reusable custom functions once in Name Manager, then call them like built-ins across the sheet.
javascript
// Define in Data > Named functions as MARGIN_PCT(revenue, cost):=LAMBDA(revenue, cost, (revenue - cost) / revenue)// Call it anywhere:=MARGIN_PCT(B2, C2)// BYROW / BYCOL apply a LAMBDA across each row or column=BYROW(A2:C100, LAMBDA(row, SUM(row)))=BYCOL(A1:D1, LAMBDA(col, COUNTA(col)))// SCAN accumulates a running value across an array=SCAN(0, C2:C100, LAMBDA(acc, x, acc + x))
Apps Script Custom Function
A bound script exposing a custom sheet function and a simple onEdit trigger for automation.
javascript
/** * Converts a raw score to a letter grade. * @param {number} score The numeric score. * @customfunction */function LETTERGRADE(score) { if (score >= 90) return "A"; if (score >= 80) return "B"; if (score >= 70) return "C"; return "F";}function onEdit(e) { const sheet = e.range.getSheet(); if (sheet.getName() === "Scores" && e.range.getColumn() === 3) { const grade = LETTERGRADE(e.value); sheet.getRange(e.range.getRow(), 4).setValue(grade); }}
Advanced QUERY Patterns
Pivot-style aggregation and cross-sheet joins expressed as a single QUERY string.
javascript
// Pivot: regions as columns, months as rows=QUERY(A1:D1000, "SELECT B, SUM(D) WHERE A IS NOT NULL GROUP BY B PIVOT C", 1)// Emulate a join by combining two ranges with IMPORTRANGE + QUERY=QUERY({IMPORTRANGE("sheet_url", "Orders!A:C"), IMPORTRANGE("sheet_url", "Customers!A:B")}, "SELECT Col1, Col2, Col5 WHERE Col1 IS NOT NULL", 1)// Date bucketing inside QUERY=QUERY(A1:C1000, "SELECT YEAR(A), MONTH(A)+1, SUM(C) GROUP BY YEAR(A), MONTH(A) LABEL YEAR(A) 'Year'", 1)
Error Handling & Performance
Techniques for keeping large formula-heavy sheets fast and robust.
- IFERROR / IFNA- Wrap volatile lookups to return a fallback instead of #N/A or #REF! propagating downstream
- IMPORTRANGE caching- Each IMPORTRANGE call re-fetches on open; consolidate to as few calls as possible per external sheet
- Avoid whole-column ARRAYFORMULA- A:A ranges force recalculation over 10M+ empty rows; bound ranges like A2:A5000 keep formulas responsive
- Protected ranges- Data > Protect sheets and ranges locks formula cells from accidental edits while leaving input cells open
- Named ranges- Data > Named ranges gives formulas readable references (Sales_2024) that survive row/column inserts
- Iterative calculation- File > Settings > Calculation > enable to support intentional circular formulas (e.g. amortization loops)
Connected Sheets & App Integration
Pulling live BigQuery data and pushing script-driven updates to Slack via Apps Script.
javascript
// Connected Sheets: query a BigQuery table directly in a cell=QUERY(BIGQUERY, "SELECT region, SUM(amount) FROM `project.dataset.sales` GROUP BY region")// Apps Script: post a summary to a webhook on a schedulefunction postDailySummary() { const sheet = SpreadsheetApp.getActiveSheet(); const total = sheet.getRange("D2:D1000").getValues().flat().reduce((a, b) => a + (b || 0), 0); UrlFetchApp.fetch("https://hooks.example.com/notify", { method: "post", contentType: "application/json", payload: JSON.stringify({ text: `Daily total: $${total.toFixed(2)}` }) });}
Pro Tip
Use FILTER() or QUERY() instead of legacy array-entered formulas when you need dynamic, spillable results - both automatically resize as source data grows, without needing to drag-fill formulas down the sheet.
Was this cheat sheet helpful?
Explore Topics
#GoogleSheetsFormulas#GoogleSheetsFormulasCheatSheet#DataScience#Beginner#LookupAggregationFormulas#QUERYARRAYFORMULA#KeyFunctions#Named#Functions#Databases#MachineLearning#CheatSheet#SkillVeris
