Complete Excel Formula Sheet, Tips & Tricks
Microsoft Excel is one of the most powerful tools used worldwide for data analysis, accounting, business management, reporting, calculations, dashboards, and automation. Whether you are a student, job seeker, accountant, business owner, or analyst, knowing Excel formulas can save you hours of manual work.
This post provides a complete Excel formula sheet with categories, examples, shortcuts, and powerful tips & tricks every user must know. Bookmark this guide for quick revision and daily use.
📌 1. Basic Excel Formulas
| Formula | Use | Example |
|---|---|---|
| SUM() | Adds numbers | =SUM(A1:A5) |
| AVERAGE() | Finds mean value | =AVERAGE(B1:B10) |
| COUNT() | Counts numeric values | =COUNT(C1:C20) |
| COUNTA() | Counts non-empty cells | =COUNTA(A1:A50) |
| MAX() | Returns highest value | =MAX(D1:D12) |
| MIN() | Returns lowest value | =MIN(D1:D12) |
📌 2. Text Formulas
| Formula | Use | Example |
|---|---|---|
| LEFT() | Extracts left-side characters | =LEFT(A1,5) |
| RIGHT() | Extracts right-side characters | =RIGHT(A1,3) |
| MID() | Extracts text from middle | =MID(A1,2,4) |
| LEN() | Counts characters | =LEN(B1) |
| CONCAT() | Combines text | =CONCAT(A1," ",B1) |
| TEXTJOIN() | Joins with delimiter | =TEXTJOIN("-",TRUE,A1:A5) |
| TRIM() | Removes extra spaces | =TRIM(A1) |
📌 3. Logical Formulas
| Formula | Use | Example |
|---|---|---|
| IF() | Checks condition | =IF(A1>50,"Pass","Fail") |
| AND() | Both conditions must be TRUE | =AND(A1>10,B1<5) |
| OR() | Either condition TRUE | =OR(A1=10,B1=20) |
| NOT() | Reverses condition | =NOT(A1="Yes") |
📌 4. Lookup Formulas
These are the most important formulas for data handling and business reporting.
| Formula | Use | Example |
|---|---|---|
| VLOOKUP() | Look up a value vertically | =VLOOKUP(A2,Table,3,FALSE) |
| HLOOKUP() | Horizontal lookup | =HLOOKUP("Sales",A1:F1,2,FALSE) |
| XLOOKUP() | Next-level lookup (replaces VLOOKUP) | =XLOOKUP(A2,A:A,B:B) |
| INDEX()+MATCH() | Advanced lookup | =INDEX(C:C,MATCH(A2,A:A,0)) |
📌 5. Date & Time Formulas
| Formula | Use | Example |
|---|---|---|
| TODAY() | Current date | =TODAY() |
| NOW() | Date + time | =NOW() |
| DATEDIF() | Gap between dates | =DATEDIF(A1,B1,"D") |
| EDATE() | Add months | =EDATE(A1,6) |
📌 6. Math Formulas
| Formula | Use | Example |
|---|---|---|
| ROUND() | Rounds value | =ROUND(A1,2) |
| ROUNDUP() | Round up | =ROUNDUP(A1,0) |
| ROUNDDOWN() | Round down | =ROUNDDOWN(A1,0) |
| ABS() | Absolute number | =ABS(-55) |
| MOD() | Find remainder | =MOD(10,3) |
📌 7. Advanced Formulas
| Formula | Use | Example |
|---|---|---|
| UNIQUE() | Extract unique values | =UNIQUE(A1:A50) |
| FILTER() | Filter data using formula | =FILTER(A1:C50,B1:B50="Paid") |
| SORT() | Sort data automatically | =SORT(A1:C50,1,TRUE) |
| SEQUENCE() | Create number sequences | =SEQUENCE(10) |
| LAMBDA() | Create your own formula | =LAMBDA(x,x*2)(10) |
📌 Excel Tips & Tricks (Used by Professionals)
1. Ctrl + Arrow Keys = Jump across data
Quickly navigate large sheets without scrolling.
2. Ctrl + Shift + Arrow Keys = Select entire block of data
3. Remove duplicates instantly
Data → Remove Duplicates
4. Convert data into a table
Press Ctrl + T — enables filtering, formatting, and auto-expansion.
5. Freeze Panes for easy viewing
View → Freeze Panes
6. Use Flash Fill (Mind-blowing feature)
Press Ctrl + E after typing the pattern.
7. Use Pivot Tables
Summarize thousands of rows instantly — a must for every job.
8. Use Power Query for cleaning & merging data
9. Use Conditional Formatting
Highlight trends, top performers, low values, duplicates.
10. Use Macros to automate tasks
Record any repeated action → run with one click.
📌 Final Thoughts
Excel is more than just a spreadsheet tool — it’s a complete business engine. With the right formulas and tips, you can calculate faster, analyze smarter, clean data automatically, and build professional reports.
This Excel Formula Sheet is designed to help beginners, job aspirants, accountants, analysts, and business owners work efficiently and confidently now and beyond.
Master these formulas — and Excel will handle the rest.
