Top 100 Excel Formulas
Complete Excel Formula Cheat Sheet with Practical Examples
Learn • Practice • Analyze • Automate
FREE PDF DOWNLOAD📥 Download Top 100 Excel Formulas PDF
📊 Master the Most Useful Excel Formulas
Excel formulas are the foundation of efficient data analysis, reporting, automation and business intelligence. This Top 100 Excel Formulas Cheat Sheet brings together the most useful Excel functions in one easy-to-reference guide.
Whether you are a student, Excel beginner, analyst, MIS executive, accountant, business professional or advanced Excel user, this reference guide can help you work faster and smarter.
🚀 What's Included in This Excel Formula Guide?
➕ Math Functions
SUM, ROUND, ABS, PRODUCT, MOD, POWER, SQRT and more.
🧠 Logical Functions
IF, IFS, AND, OR, NOT, XOR, SWITCH and error handling.
🔎 Lookup Functions
XLOOKUP, VLOOKUP, INDEX, MATCH, XMATCH and more.
🔤 Text Functions
LEFT, RIGHT, MID, TEXTJOIN, CONCAT, FIND, SEARCH and more.
📅 Date & Time
TODAY, NOW, DATE, YEAR, MONTH, DATEDIF, EOMONTH and more.
📊 Data Analysis
SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS and ranking.
⚡ Dynamic Arrays
FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE and DROP.
💼 Advanced Excel
CHOOSECOLS, CHOOSEROWS, TRANSPOSE and modern Excel functions.
🔥 Top 100 Excel Formulas – Quick Reference
| # | Function | Example Formula | Purpose |
|---|---|---|---|
| 1 | SUM | =SUM(A2:A10) | Adds values in a range |
| 2 | SUMPRODUCT | =SUMPRODUCT(B2:B10,C2:C10) | Multiplies arrays and sums products |
| 3 | PRODUCT | =PRODUCT(A2:A5) | Multiplies numbers |
| 4 | ABS | =ABS(A2) | Returns absolute value |
| 5 | ROUND | =ROUND(A2,2) | Rounds to specified decimals |
| 6 | ROUNDUP | =ROUNDUP(A2,0) | Rounds upward |
| 7 | ROUNDDOWN | =ROUNDDOWN(A2,0) | Rounds downward |
| 8 | MROUND | =MROUND(A2,5) | Rounds to a multiple |
| 9 | CEILING | =CEILING(A2,10) | Rounds up to a multiple |
| 10 | FLOOR | =FLOOR(A2,10) | Rounds down to a multiple |
| 11 | INT | =INT(A2) | Rounds down to an integer |
| 12 | TRUNC | =TRUNC(A2,2) | Truncates decimals |
| 13 | MOD | =MOD(A2,7) | Returns remainder |
| 14 | QUOTIENT | =QUOTIENT(A2,B2) | Returns integer portion of division |
| 15 | POWER | =POWER(A2,2) | Raises a number to a power |
| 16 | SQRT | =SQRT(A2) | Returns square root |
| 17 | SIGN | =SIGN(A2) | Returns number sign |
| 18 | RAND | =RAND() | Generates random decimal |
| 19 | RANDBETWEEN | =RANDBETWEEN(1,100) | Generates random integer |
| 20 | PI | =PI() | Returns pi |
| 21 | IF | =IF(A2>=50,"Pass","Fail") | Tests a condition |
| 22 | IFS | =IFS(A2>=80,"A",A2>=60,"B",TRUE,"C") | Tests multiple conditions |
| 23 | AND | =AND(A2>50,B2="Yes") | All conditions must be TRUE |
| 24 | OR | =OR(A2>50,B2="Yes") | Any condition can be TRUE |
| 25 | NOT | =NOT(A2="Closed") | Reverses TRUE/FALSE |
| 26 | XOR | =XOR(A2="Yes",B2="Yes") | Exactly one condition TRUE |
| 27 | SWITCH | =SWITCH(A2,1,"Low",2,"Medium",3,"High") | Matches values to results |
| 28 | IFERROR | =IFERROR(A2/B2,0) | Handles formula errors |
| 29 | IFNA | =IFNA(XLOOKUP(E2,A:A,B:B),"Not Found") | Handles #N/A errors |
| 30 | TRUE | =TRUE() | Returns TRUE |
| 31 | XLOOKUP | =XLOOKUP(E2,A:A,B:B,"Not Found") | Modern lookup |
| 32 | VLOOKUP | =VLOOKUP(E2,A:B,2,FALSE) | Vertical lookup |
| 33 | HLOOKUP | =HLOOKUP(B1,A1:F2,2,FALSE) | Horizontal lookup |
| 34 | LOOKUP | =LOOKUP(E2,A:A,B:B) | Looks up a value |
| 35 | INDEX | =INDEX(B2:B10,5) | Returns value by position |
| 36 | MATCH | =MATCH(E2,A2:A10,0) | Finds position |
| 37 | XMATCH | =XMATCH(E2,A2:A10,0) | Modern position lookup |
| 38 | INDEX + MATCH | =INDEX(B:B,MATCH(E2,A:A,0)) | Flexible lookup |
| 39 | CHOOSE | =CHOOSE(A2,"Red","Blue","Green") | Selects from a list |
| 40 | OFFSET | =OFFSET(A1,2,1) | Returns offset reference |
| 41 | LEFT | =LEFT(A2,5) | Extracts left characters |
| 42 | RIGHT | =RIGHT(A2,4) | Extracts right characters |
| 43 | MID | =MID(A2,3,5) | Extracts middle characters |
| 44 | LEN | =LEN(A2) | Counts characters |
| 45 | TRIM | =TRIM(A2) | Removes extra spaces |
| 46 | CLEAN | =CLEAN(A2) | Removes non-printing characters |
| 47 | UPPER | =UPPER(A2) | Converts to uppercase |
| 48 | LOWER | =LOWER(A2) | Converts to lowercase |
| 49 | PROPER | =PROPER(A2) | Capitalizes words |
| 50 | CONCAT | =CONCAT(A2," ",B2) | Joins text |
| 51 | CONCATENATE | =CONCATENATE(A2," ",B2) | Joins text |
| 52 | TEXTJOIN | =TEXTJOIN(", ",TRUE,A2:A10) | Joins text with delimiter |
| 53 | TEXT | =TEXT(A2,"dd-mmm-yyyy") | Formats value as text |
| 54 | FIND | =FIND("@",A2) | Finds text position |
| 55 | SEARCH | =SEARCH("excel",A2) | Finds text position |
| 56 | SUBSTITUTE | =SUBSTITUTE(A2,"Old","New") | Replaces matching text |
| 57 | REPLACE | =REPLACE(A2,1,3,"New") | Replaces by position |
| 58 | EXACT | =EXACT(A2,B2) | Compares text exactly |
| 59 | VALUE | =VALUE(A2) | Converts text to number |
| 60 | CHAR | =CHAR(65) | Returns character by code |
| 61 | TODAY | =TODAY() | Returns current date |
| 62 | NOW | =NOW() | Current date and time |
| 63 | DATE | =DATE(2026,8,9) | Creates a date |
| 64 | YEAR | =YEAR(A2) | Extracts year |
| 65 | MONTH | =MONTH(A2) | Extracts month |
| 66 | DAY | =DAY(A2) | Extracts day |
| 67 | HOUR | =HOUR(A2) | Extracts hour |
| 68 | MINUTE | =MINUTE(A2) | Extracts minute |
| 69 | SECOND | =SECOND(A2) | Extracts second |
| 70 | DATEDIF | =DATEDIF(A2,B2,"Y") | Calculates date difference |
| 71 | EDATE | =EDATE(A2,3) | Shifts date by months |
| 72 | EOMONTH | =EOMONTH(A2,0) | Returns month-end date |
| 73 | WEEKDAY | =WEEKDAY(A2,2) | Returns weekday number |
| 74 | WEEKNUM | =WEEKNUM(A2,2) | Returns week number |
| 75 | NETWORKDAYS | =NETWORKDAYS(A2,B2) | Counts working days |
| 76 | SUMIF | =SUMIF(A:A,"East",B:B) | Sum with one condition |
| 77 | SUMIFS | =SUMIFS(C:C,A:A,"East",B:B,"Laptop") | Sum with multiple conditions |
| 78 | COUNT | =COUNT(A2:A100) | Counts numbers |
| 79 | COUNTA | =COUNTA(A2:A100) | Counts non-empty cells |
| 80 | COUNTBLANK | =COUNTBLANK(A2:A100) | Counts blank cells |
| 81 | COUNTIF | =COUNTIF(A:A,"Completed") | Counts one condition |
| 82 | COUNTIFS | =COUNTIFS(A:A,"East",B:B,">=500") | Counts multiple conditions |
| 83 | AVERAGE | =AVERAGE(B2:B100) | Calculates average |
| 84 | AVERAGEIF | =AVERAGEIF(A:A,"East",B:B) | Average with one condition |
| 85 | AVERAGEIFS | =AVERAGEIFS(C:C,A:A,"East",B:B,"Laptop") | Average with multiple conditions |
| 86 | MAX | =MAX(B2:B100) | Largest value |
| 87 | MIN | =MIN(B2:B100) | Smallest value |
| 88 | MAXIFS | =MAXIFS(C:C,A:A,"East") | Maximum with conditions |
| 89 | MINIFS | =MINIFS(C:C,A:A,"East") | Minimum with conditions |
| 90 | RANK.EQ | =RANK.EQ(B2,$B$2:$B$20) | Ranks a value |
| 91 | FILTER | =FILTER(A2:C100,B2:B100="East") | Filters data dynamically |
| 92 | SORT | =SORT(A2:C100,2,1) | Sorts a range |
| 93 | SORTBY | =SORTBY(A2:C100,C2:C100,-1) | Sorts by another range |
| 94 | UNIQUE | =UNIQUE(A2:A100) | Returns unique values |
| 95 | SEQUENCE | =SEQUENCE(10) | Creates a sequence |
| 96 | TRANSPOSE | =TRANSPOSE(A2:A10) | Switches rows and columns |
| 97 | TAKE | =TAKE(A2:D100,10) | Returns selected rows/columns |
| 98 | DROP | =DROP(A2:D100,2) | Removes leading rows/columns |
| 99 | CHOOSECOLS | =CHOOSECOLS(A2:F100,1,3,5) | Selects columns |
| 100 | CHOOSEROWS | =CHOOSEROWS(A2:F100,1,5,10) | Selects rows |
⭐ 10 Excel Formulas Every Professional Should Know
- SUM() – Quickly calculate totals.
- IF() – Build logical decisions.
- SUMIFS() – Analyze data using multiple criteria.
- COUNTIFS() – Count records based on multiple conditions.
- XLOOKUP() – Perform powerful modern lookups.
- INDEX + MATCH – Create flexible lookup solutions.
- IFERROR() – Make reports cleaner by handling errors.
- FILTER() – Create dynamic filtered reports.
- UNIQUE() – Extract unique records automatically.
- SORT() – Build dynamic sorted lists.
💡 Excel Formula Tips
Tip 1:
Use absolute references such as
$A$1
when copying formulas.
Tip 2: Combine modern functions such as XLOOKUP + FILTER + SORT + UNIQUE to create dynamic reports.
Tip 3: Use IFERROR() to prevent unwanted error messages in dashboards and reports.
Tip 4: Practice each formula using a small sample dataset before applying it to real business data.
Tip 2: Combine modern functions such as XLOOKUP + FILTER + SORT + UNIQUE to create dynamic reports.
Tip 3: Use IFERROR() to prevent unwanted error messages in dashboards and reports.
Tip 4: Practice each formula using a small sample dataset before applying it to real business data.
🎯 Who Should Download This Excel Formula PDF?
🎓 Students
Prepare for Excel exams and practical assignments.
💼 Professionals
Improve daily reporting and productivity.
📊 Data Analysts
Build faster data analysis workflows.
📈 MIS Executives
Create efficient reports and dashboards.
🧑💻 Job Seekers
Prepare for Excel interview questions.
🏢 Business Users
Automate repetitive Excel tasks.
📥 Get the Complete Excel Formula Cheat Sheet
Keep all your essential Excel formulas in one convenient PDF reference guide.
Download Free PDF →📚 More Excel Resources
- Excel Functions Cheat Sheet
- Excel Keyboard Shortcuts Cheat Sheet
- Advanced Excel Formulas
- Excel Lookup Functions
- Excel Dynamic Array Functions
- Excel Dashboard Tutorials
- Excel Interview Questions
- Excel Tips & Tricks