📌 Basic Formulas
=SUM(A1:A10) — Add values in a range=AVERAGE(A1:A10) — Calculate the average=COUNT(A1:A10) — Count numeric entries=ROUND(A1, 2) — Round to 2 decimal places
🔍 Lookup & Reference
=VLOOKUP(lookup_value, table_array, col_index, FALSE) — Vertical lookup=HLOOKUP() — Horizontal lookup=INDEX() + MATCH() — More flexible lookup combo=XLOOKUP() — Modern Excel (replaces VLOOKUP/MATCH)
📅 Date & Time
=TODAY() — Current date=NOW() — Current date and time=DATEDIF(start, end, "D") — Days between two dates=TEXT(A1, "dd-mm-yyyy") — Format a date
🧠 Logical Functions
=IF(condition, value_if_true, value_if_false)=IF(AND(A1>10, B1<5), "Yes", "No")=IFERROR(formula, "Error message")=SWITCH() — Replace nested IFs (modern Excel)
🛠️ Text & Cleaning
=TRIM(A1) — Remove extra spaces=LEN(A1) — Character count=LEFT(), RIGHT(), MID() — Extract substrings=SUBSTITUTE(A1, "old", "new") — Replace text=TEXTJOIN(", ", TRUE, A1:A5) — Combine cells with delimiter
💡 Tips & Tricks
- Use Ctrl + Shift + ↓ to select large data ranges
- Alt + E + S + V — Paste special → values
- Apply filters: Ctrl + Shift + L
- Convert text to columns: Data → Text to Columns
- Use Conditional Formatting to highlight data dynamically