Google Sheets Formulas Cheat Sheet
Master Google Sheets with our comprehensive formulas reference guide. From basic math to powerful Google-exclusive functions like QUERY, ARRAYFORMULA, and IMPORTRANGE — find syntax, examples, and expert tips for every essential formula.
FILTER()
Google-Specific ⭐Returns only the rows in a range that meet specified conditions
Syntax:
=FILTER(range, condition1, [condition2], ...)Examples:
=FILTER(A1:C10, B1:B10>50) Rows where column B > 50
=FILTER(A:C, A:A="North", C:C>100) Multiple conditions (AND)
=FILTER(A:A, (B:B="Y")+(C:C="Y")) OR condition using addition
Note: ⭐ Google Sheets exclusive; results spill automatically into adjacent cells
SORT()
Google-Specific ⭐Sorts the rows of a range by one or more columns
Syntax:
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...)Examples:
=SORT(A1:C10, 1, TRUE) Sort by first column ascending
=SORT(A1:C10, 2, FALSE) Sort by second column descending
=SORT(A1:C10, 1, TRUE, 2, FALSE) Sort by column 1 then 2
Note: ⭐ Google Sheets exclusive; spills automatically; pair with FILTER for dynamic tables
QUERY()
Google-Specific ⭐Runs a SQL-like query on your data using Google Visualization API query language
Syntax:
=QUERY(data, query, [headers])Examples:
=QUERY(A1:D100, "SELECT A, B WHERE C > 100") Select columns where C>100
=QUERY(A:D, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC") Sum by group, sorted
=QUERY(A:C, "SELECT * WHERE B = '" & E1 & "'") Dynamic filter using cell value
Note: ⭐ Google Sheets exclusive; one of the most powerful functions for data analysis
Google Sheets Mastery Guide
Beginner
Start with calculations and basic data organisation
Intermediate
Master lookups and Google-specific power features
- • VLOOKUP, INDEX-MATCH
- • FILTER, UNIQUE, SORT
- • ARRAYFORMULA basics
- • IMPORTRANGE cross-sheet data
Advanced
QUERY language, regex, and live data imports
- • QUERY with SQL-like syntax
- • REGEXEXTRACT, REGEXREPLACE
- • IMPORTHTML, IMPORTXML
- • GOOGLEFINANCE live data
📊 What is Google Sheets?
Google Sheets is a free, cloud-based spreadsheet application from Google, available as part of Google Workspace. First launched in 2006, it has grown into a powerful alternative to Microsoft Excel — and in several areas surpasses it, with unique features like real-time collaboration, built-in QUERY language, live data imports, and formulas for translating text and fetching stock prices directly in a cell.
🚀 Core Capabilities
- ✓ Real-time collaboration: Multiple users edit simultaneously
- ✓ Cloud storage: Auto-saves to Google Drive
- ✓ QUERY formula: SQL-like data analysis in a cell
- ✓ Live data imports: IMPORTHTML, IMPORTXML, GOOGLEFINANCE
- ✓ ARRAYFORMULA: Apply formulas to entire columns instantly
- ✓ Apps Script: JavaScript-based automation and macros
💼 Google Sheets Exclusive Functions
- ⭐ QUERY: SQL-like language for filtering and aggregating data
- ⭐ ARRAYFORMULA: Array operations without Ctrl+Shift+Enter
- ⭐ IMPORTRANGE: Pull data live from another spreadsheet
- ⭐ GOOGLETRANSLATE: Translate text between 100+ languages
- ⭐ GOOGLEFINANCE: Live stock prices and financial data
- ⭐ SPARKLINE: Mini charts directly inside a cell
⚡ Google Sheets vs. Excel
Google Sheets Advantages
- • Free to use with a Google account
- • Real-time multi-user collaboration
- • QUERY, ARRAYFORMULA, FILTER, UNIQUE built-in
- • Live web data imports (IMPORTHTML, IMPORTXML)
- • Version history with per-cell tracking
- • Works in any browser, no install needed
Excel Advantages
- • More powerful PivotTables and data model
- • Better performance with very large datasets
- • VBA macros for complex automation
- • More chart types and formatting options
- • Power Query for ETL operations
- • Industry standard in finance and enterprise
Google Sheets Pro Tips
ARRAYFORMULA shortcut: Press Ctrl+Shift+Enter in a cell to automatically wrap your formula with ARRAYFORMULA — applies it to the entire column instantly.
QUERY SQL-like syntax: Use SELECT, WHERE, GROUP BY, ORDER BY, LIMIT just like SQL — e.g. QUERY(A:D, "SELECT A, SUM(D) GROUP BY A")
IMPORTRANGE cross-sheet tip: Paste the full Google Sheets URL as the first argument. You only need to grant access once — after that, data syncs live between spreadsheets automatically.
FILTER with OR logic: Use + between conditions
(not OR()) — e.g. FILTER(A:B, (A:A="X")+(B:B="Y"))
Master Google Sheets formulas with practice. Bookmark this page for quick reference! ⭐ marks Google-exclusive functions.