Most people use Google Sheets like a simple table editor. But it's capable of automating calculations, managing data, and building tools that would take hours to do manually — all for free. Here are 15 features that make a real difference.
1. ARRAYFORMULA — Apply One Formula to an Entire Column
Instead of copying a formula down 500 rows, ARRAYFORMULA applies it automatically to the whole column — and updates when you add new rows.
Example: =ARRAYFORMULA(A2:A * B2:B) — multiplies every row in column A by column B at once.
2. IMPORTRANGE — Link Data Between Sheets
Pull live data from a completely different spreadsheet into your current one. It updates automatically whenever the source changes.
Formula: =IMPORTRANGE("spreadsheet_url","Sheet1!A1:C100")
3. Dropdown Menus with Data Validation
Turn any cell into a dropdown menu — great for status fields, categories, or priority ratings.
Select cell(s) → Data → Data validation → Criteria: Dropdown
Type your options separated by commas. The cell now has a dropdown arrow and only accepts your specified values.
4. Conditional Formatting — Highlight Cells Automatically
Automatically colour-code cells based on their value. Use it to highlight overdue dates in red, completed items in green, or values above a threshold in orange.
Format → Conditional formatting → Set your rules
5. QUERY Function — Filter Data Like a Database
QUERY lets you filter, sort, and summarise data using SQL-like commands — without knowing SQL.
Example: =QUERY(A1:D100,"SELECT A,C WHERE D>'2026-01-01' ORDER BY C DESC")
6. Checkbox Column
Add real checkboxes to any column: Insert → Checkbox. Checked = TRUE, unchecked = FALSE. Use with COUNTIF to count how many boxes are checked.
7. Named Ranges
Instead of writing $A$2:$A$50 in every formula, name the range "Products" and write Products instead. Formulas become readable and maintainable.
Data → Named ranges → + Add a range
8. VLOOKUP vs XLOOKUP
VLOOKUP is the classic lookup formula. XLOOKUP (newer) is better — it works in any direction, returns ranges, and doesn't require columns in order.
XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array)
9. Freeze Rows and Columns
Keep your header row visible while scrolling through thousands of rows.
View → Freeze → 1 row
10. Keyboard Shortcuts That Save Minutes Daily
| Shortcut | Action |
|---|---|
| Ctrl + Shift + V | Paste values only (no formatting) |
| Ctrl + D | Copy cell above down |
| Ctrl + ; | Insert today's date |
| Ctrl + Shift + ; | Insert current time |
| Alt + Enter | New line within a cell |
11. SPLIT Function
Split a cell containing "John Smith" into two separate columns in one formula.
=SPLIT(A2," ") — splits at the space character.
12. COUNTIF and SUMIF
Count or sum cells that meet a condition — without filtering your data.
=COUNTIF(D2:D100,"Completed") — counts how many cells say "Completed".
=SUMIF(B2:B100,">100",C2:C100) — sums column C where column B is greater than 100.
13. Sparklines
Tiny inline charts inside a single cell — great for dashboards.
=SPARKLINE(A2:F2) — creates a small line chart from the range.
14. Google Finance Function
Pull live stock prices, currency rates, and market data directly into your spreadsheet.
=GOOGLEFINANCE("GOOGL","price") — current Google stock price, updates automatically.
15. Version History
Google Sheets saves every version automatically. You can go back to any previous state — useful if you accidentally delete data.
File → Version history → See version history