Last Updated: November 21, 2025
Unique Google Sheets Formulas
| Formula | Description | Example |
|---|---|---|
QUERY
|
SQL-like data queries |
=QUERY(A:D, "SELECT A, B WHERE C > 100 ORDER BY D")
|
IMPORTRANGE
|
Import data from another sheet |
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10")
|
ARRAYFORMULA
|
Apply formula to entire array |
=ARRAYFORMULA(A2:A * B2:B)
|
FILTER
|
Filter data by conditions |
=FILTER(A:C, B:B>100, C:C="Active")
|
UNIQUE
|
Get unique values |
=UNIQUE(A:A)
|
SORT
|
Sort data |
=SORT(A:C, 2, FALSE)
|
SORTN
|
Top N sorted items |
=SORTN(A:B, 10, 0, 2, FALSE)
|
IMPORTXML
|
Import XML/HTML data |
=IMPORTXML("url", "//div[@class='price']")
|
IMPORTHTML
|
Import tables from web |
=IMPORTHTML("url", "table", 1)
|
IMPORTFEED
|
Import RSS/Atom feeds |
=IMPORTFEED("feed_url", "items", TRUE, 10)
|
IMPORTDATA
|
Import CSV data |
=IMPORTDATA("csv_url")
|
GOOGLETRANSLATE
|
Translate text |
=GOOGLETRANSLATE(A1, "en", "es")
|
GOOGLEFINANCE
|
Stock/finance data |
=GOOGLEFINANCE("NASDAQ:GOOGL", "price")
|
IMAGE
|
Insert image from URL |
=IMAGE("url", 1)
|
SPARKLINE
|
Mini charts in cells |
=SPARKLINE(A1:A10, {"charttype","column"})
|
QUERY Function Syntax
| Clause | Description | Example |
|---|---|---|
SELECT
|
Choose columns |
SELECT A, B, C
or
SELECT *
|
WHERE
|
Filter rows |
WHERE B > 100 AND C = 'Active'
|
ORDER BY
|
Sort results |
ORDER BY D DESC
|
GROUP BY
|
Group and aggregate |
GROUP BY A
|
PIVOT
|
Pivot table |
PIVOT C
|
LIMIT
|
Limit results |
LIMIT 10
|
OFFSET
|
Skip rows |
OFFSET 5
|
LABEL
|
Column headers |
LABEL A 'Name', B 'Total'
|
FORMAT
|
Number format |
FORMAT B 'currency'
|
| Aggregates | SUM, AVG, COUNT, MIN, MAX |
SELECT A, SUM(B) GROUP BY A
|
| Operators | =, !=, <, >, <=, >= |
WHERE B >= 100
|
| Logic | AND, OR, NOT |
WHERE A='X' OR B>50
|
| Text Match | CONTAINS, STARTS WITH, ENDS WITH |
WHERE A CONTAINS 'test'
|
| Date Functions | YEAR, MONTH, DAY |
WHERE YEAR(A) = 2024
|
Array Formulas
| Formula | Description | Example |
|---|---|---|
ARRAYFORMULA
|
Apply to entire range |
=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))
|
Array Math
|
Operations on arrays |
=ARRAYFORMULA(A2:A10 * B2:B10)
|
ROW
|
Generate row numbers |
=ARRAYFORMULA(ROW(A2:A)-1)
|
SEQUENCE
|
Number sequence |
=SEQUENCE(10, 1, 1, 1)
|
FLATTEN
|
Convert range to column |
=FLATTEN(A1:C10)
|
SPLIT
|
Split text to columns |
=SPLIT(A1, ",")
|
JOIN
|
Join array with delimiter |
=JOIN(", ", A1:A10)
|
TRANSPOSE
|
Flip rows/columns |
=TRANSPOSE(A1:C10)
|
Conditional Array
|
IF with arrays |
=ARRAYFORMULA(IF(A2:A>100, "High", "Low"))
|
SUMPRODUCT
|
Array multiplication sum |
=SUMPRODUCT(A2:A10, B2:B10)
|
Advanced Lookup & Reference
| Formula | Description | Example |
|---|---|---|
VLOOKUP
|
Vertical lookup |
=VLOOKUP(A2, Data!A:D, 3, FALSE)
|
XLOOKUP
|
Modern lookup (new) |
=XLOOKUP(A2, B:B, C:C, "Not Found")
|
INDEX + MATCH
|
Flexible lookup |
=INDEX(C:C, MATCH(A2, B:B, 0))
|
INDIRECT
|
Dynamic references |
=INDIRECT("Sheet"&A1&"!B2")
|
OFFSET
|
Reference from offset |
=OFFSET(A1, 2, 3, 5, 1)
|
GETPIVOTDATA
|
Extract pivot data |
=GETPIVOTDATA("Sales", A1, "Region", "East")
|
CHOOSECOLS
|
Select specific columns |
=CHOOSECOLS(A:D, 1, 3)
|
CHOOSEROWS
|
Select specific rows |
=CHOOSEROWS(A1:D10, 1, 3, 5)
|
Text & String Functions
| Formula | Description | Example |
|---|---|---|
REGEXMATCH
|
Test regex pattern |
=REGEXMATCH(A1, "[0-9]{3}-[0-9]{4}")
|
REGEXEXTRACT
|
Extract by regex |
=REGEXEXTRACT(A1, "@(.+)")
|
REGEXREPLACE
|
Replace by regex |
=REGEXREPLACE(A1, "[0-9]", "X")
|
SPLIT
|
Split by delimiter |
=SPLIT(A1, ",")
|
JOIN
|
Join with delimiter |
=JOIN(", ", A1:A10)
|
TEXTJOIN
|
Join with options |
=TEXTJOIN(", ", TRUE, A1:A10)
|
TRIM
|
Remove extra spaces |
=TRIM(A1)
|
CLEAN
|
Remove non-printable chars |
=CLEAN(A1)
|
SUBSTITUTE
|
Replace text |
=SUBSTITUTE(A1, "old", "new")
|
PROPER
|
Title case |
=PROPER(A1)
|
Date & Time Functions
| Formula | Description | Example |
|---|---|---|
TODAY()
|
Current date |
=TODAY()
|
NOW()
|
Current date/time |
=NOW()
|
DATE
|
Create date |
=DATE(2024, 12, 25)
|
EDATE
|
Date plus months |
=EDATE(A1, 6)
|
EOMONTH
|
End of month |
=EOMONTH(A1, 0)
|
NETWORKDAYS
|
Workdays between dates |
=NETWORKDAYS(A1, B1, Holidays)
|
WORKDAY
|
Workday after X days |
=WORKDAY(A1, 30)
|
DATEDIF
|
Date difference |
=DATEDIF(A1, B1, "D")
|
WEEKDAY
|
Day of week (1-7) |
=WEEKDAY(A1)
|
TEXT
|
Format date as text |
=TEXT(A1, "YYYY-MM-DD")
|
Keyboard Shortcuts
| Shortcut (Windows/Chrome) | Shortcut (Mac) | Action |
|---|---|---|
Ctrl + C/V/X
|
⌘ + C/V/X
|
Copy/Paste/Cut |
Ctrl + Z/Y
|
⌘ + Z/Y
|
Undo/Redo |
Ctrl + F
|
⌘ + F
|
Find |
Ctrl + H
|
⌘ + Shift + H
|
Find and replace |
Ctrl + Enter
|
⌘ + Enter
|
Fill range with formula |
Ctrl + D
|
⌘ + D
|
Fill down |
Ctrl + R
|
⌘ + R
|
Fill right |
Ctrl + ;
|
⌘ + ;
|
Insert current date |
Ctrl + Shift + ;
|
⌘ + Shift + ;
|
Insert current time |
Alt + Enter
|
⌥ + Enter
|
New line in cell |
Ctrl + K
|
⌘ + K
|
Insert link |
Ctrl + /
|
⌘ + /
|
Show keyboard shortcuts |
Ctrl + Shift + F
|
⌘ + Shift + F
|
Compact controls |
Alt + Shift + 5
|
⌥ + Shift + 5
|
Strikethrough |
Ctrl + Alt + M
|
⌘ + ⌥ + M
|
Insert comment |
Ctrl + Alt + Shift + H
|
⌘ + ⌥ + Shift + H
|
Show revision history |
Ctrl + Home
|
⌘ + Home
|
Go to A1 |
Ctrl + End
|
⌘ + End
|
Go to last cell |
Ctrl + Backspace
|
⌘ + Backspace
|
Scroll to active cell |
F4
|
F4
|
Toggle absolute reference |
Ctrl + Shift + V
|
⌘ + Shift + V
|
Paste values only |
Ctrl + Alt + V
|
⌘ + ⌥ + V
|
Paste special menu |
Alt + I, R
|
⌥ + I, R
|
Insert row |
Alt + I, C
|
⌥ + I, C
|
Insert column |
Alt + E, D
|
⌥ + E, D
|
Delete row/column |
Apps Script Common Patterns
| Task | Code Example |
|---|---|
| Get Active Sheet |
var sheet = SpreadsheetApp.getActiveSheet();
|
| Get Range Value |
var value = sheet.getRange("A1").getValue();
|
| Set Range Value |
sheet.getRange("A1").setValue("Hello");
|
| Get Range Values |
var data = sheet.getRange("A1:C10").getValues();
|
| Last Row |
var lastRow = sheet.getLastRow();
|
| Append Row |
sheet.appendRow(["A", "B", "C"]);
|
| Send Email |
MailApp.sendEmail(email, subject, body);
|
| Format Cell |
range.setBackground("#FF0000").setFontColor("white");
|
| Create Menu |
SpreadsheetApp.getUi().createMenu("Custom").addItem("Action", "functionName").addToUi();
|
| Show Alert |
SpreadsheetApp.getUi().alert("Message");
|
| Time Trigger |
ScriptApp.newTrigger("functionName").timeBased().everyHours(1).create();
|
| Loop Through Rows |
data.forEach(function(row) { Logger.log(row[0]); });
|
Essential Add-ons
| Add-on | Purpose |
|---|---|
| Power Tools | Advanced find/replace, remove duplicates, split/merge cells |
| Form Publisher | Convert form responses to PDFs, docs, slides |
| Supermetrics | Import marketing data from Google Ads, Facebook, etc. |
| Remove Duplicates | Advanced duplicate removal with multiple criteria |
| Advanced Find & Replace | Regex search, bulk replacements across sheets |
| Mail Merge | Send personalized emails from spreadsheet data |
| Google Analytics | Import GA data directly into sheets |
| Yet Another Mail Merge | Advanced mail merge with tracking |
| Coefficient | Live data imports from CRMs, databases |
| Doc Appender | Export sheets to Google Docs |
| Autocomplete | Smart autocomplete for data entry |
| Sheetgo | Connect and consolidate multiple sheets |
Collaboration Features
| Feature | How To Use |
|---|---|
| Share Settings | Share button → Set viewer/commenter/editor permissions |
| Comments | Right-click cell → Comment (Ctrl+Alt+M) for discussions |
| Suggestions | View → Suggestion mode → Edits require approval |
| Version History | File → Version history → See all changes, restore versions |
| Named Versions | Version history → Name current version for easy reference |
| Protect Ranges | Data → Protect sheets and ranges → Restrict editing |
| Data Validation | Data → Data validation → Control input with dropdowns/rules |
| Filters | Create filter → Each user can have own filter view |
| Filter Views | Data → Filter views → Save and share custom filters |
| Notifications | Tools → Notification rules → Get alerts on changes |
| Chat | Click chat icon to discuss while editing together |
| @ Mentions | Type @ in comment to notify specific people |
| Assign Tasks | In comment, assign action item to collaborator |
Data Visualization
| Chart Type | Best For |
|---|---|
| Line Chart | Trends over time, continuous data |
| Bar/Column Chart | Comparing categories, discrete data |
| Pie Chart | Parts of a whole, percentages (limit to 5-7 slices) |
| Scatter Plot | Correlation between two variables |
| Area Chart | Cumulative totals over time |
| Combo Chart | Multiple metrics with different scales |
| Histogram | Distribution of values in ranges |
| Sparkline | Mini charts in cells using =SPARKLINE() |
| Candlestick | Stock prices (open, high, low, close) |
| Treemap | Hierarchical data, nested categories |
| Gauge Chart | KPIs, progress towards goal |
| Geo Chart | Geographic/location-based data |
💡 Pro Tips:
- Use IMPORTRANGE with QUERY for powerful cross-sheet data analysis
- Combine ARRAYFORMULA with IF to avoid copying formulas down columns
- Named ranges (Data → Named ranges) make formulas more readable
- Use Filter views to save multiple filtering states without affecting others
- QUERY language is similar to SQL but columns use letters (A, B, C) or Col1, Col2
- Apps Script can automate repetitive tasks - use Extensions → Apps Script
- Conditional formatting with custom formulas: use $A1 to fix column, A$1 to fix row
- Data validation dropdowns can reference ranges from other sheets
- Use Explore (bottom right) for AI-powered insights and chart suggestions
- GOOGLEFINANCE updates every 20 minutes for real-time quotes
- Protected ranges allow warnings without blocking (useful for formulas)
- Version history shows who made changes - use named versions for milestones