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
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#Covers#Functions#Databases#MachineLearning#CheatSheet#SkillVeris
