Spreadsheets¶
Security features¶
- Permission access — sharing settings (limit who can access)
- Hide sheet, protect sheet, encrypt with password
- View vs. comment vs. edit permissions
- Audit history (Google Sheets) / Track changes (Excel)
Google Sheets¶
Connected Sheets¶
Google Sheets connects with BigQuery — analyze large BigQuery datasets directly in Sheets without specialized knowledge.
Useful function references¶
- Google Sheets Function List
- 23 Must-Know Formulas
- Tips and Techniques — Ben Collins
- Graphs in Google Sheets — DataCamp
Functions cheat¶
| Function | Description |
|---|---|
IMPORTRANGE("url", "Sheet1!A1:F13") |
Import a range from another sheet |
IMPORTHTML("url", "table", n) |
Web-scrape an HTML table or list |
IMPORTDATA("url") |
Import CSV/TSV from URL |
VLOOKUP(value, range, col, false) |
Look up value (false = exact match) |
XLOOKUP(value, lookup_range, return_range) |
Modern replacement; left-to-right not required |
INDEX + MATCH |
More flexible lookup |
LEN(s) |
String length |
FIND(needle, s) |
Position of substring |
LEFT(s, n) / RIGHT(s, n) |
First / last n chars |
MID(s, start, len) |
Substring |
VALUE(s) |
Convert text-number to number |
TRIM(s) |
Remove extra whitespace |
PRODUCT(a, b, ...) |
Multiply |
SUMPRODUCT(arr1, arr2) |
Sum of element-wise products |
QUERY(range, "SELECT A WHERE B > 10") |
SQL-like query (Google Sheets) |
ARRAYFORMULA(...) |
Apply formula across whole column (Google Sheets) |
Pivot tables¶
Insert > Pivot table — summarize, aggregate, drill down.
| Sort sheet | Sort range |
|---|---|
| Data across rows kept together | Only selected cells rearranged (isolation) |
Excel¶
Microsoft's spreadsheet. Common in finance and corporate.
References¶
Useful Excel-specific¶
- Power Query — ETL-in-Excel; load, clean, merge data
- Power Pivot — DAX measures, large data models
- PivotTables — same concept as Sheets
- Conditional formatting — highlight outliers
- Data validation — restrict input
Spreadsheet tips¶
- Keep raw data on its own tab; computed views separate
- Lock formula cells; allow input cells
- Document with named ranges
- Build models in tables (
Ctrl+T); structured references - Avoid merged cells (break sorting/filtering)