📘 Excel Functions Cheat Sheet
1️⃣ Basic Arithmetic Operators
| Operator | Purpose |
|---|---|
| + | Adds two numbers |
| - | Subtracts one number from another |
| * | Multiplies two numbers |
| / | Divides one number by another |
| ^ | Raises a number to the power of another |
2️⃣ Mathematical Functions
| Function | Syntax | Example | Description |
|---|---|---|---|
| SUM | =SUM(number1, [number2], ...) | =SUM(A1:A5) | Adds numbers |
| AVERAGE | =AVERAGE(number1, ...) | =AVERAGE(A1:A5) | Calculates the mean |
| MIN | =MIN(number1, ...) | =MIN(A1:A5) | Returns smallest number |
| MAX | =MAX(number1, ...) | =MAX(A1:A5) | Returns largest number |
| ROUND | =ROUND(number, num_digits) | =ROUND(A1, 2) | Rounds number |
| COUNT | =COUNT(value1, ...) | =COUNT(A1:A5) | Counts numeric values |
| COUNTA | =COUNTA(value1, ...) | =COUNTA(A1:A5) | Counts non-empty cells |
| ABS | =ABS(number) | =ABS(-10) | Absolute value |
3️⃣ Logical Functions
| Function | Syntax | Example | Description |
|---|---|---|---|
| IF | =IF(condition, true_value, false_value) | =IF(A1>10,"Yes","No") | Returns "Yes" if TRUE |
| AND | =AND(condition1, ...) | =AND(A1>5,B1<10) | TRUE if all TRUE |
| OR | =OR(condition1, ...) | =OR(A1>5,B1<10) | TRUE if any TRUE |
| NOT | =NOT(logical) | =NOT(A1>5) | Opposite of condition |
4️⃣ Lookup & Reference
| Function | Syntax | Example | Description |
|---|---|---|---|
| VLOOKUP | =VLOOKUP(value, table, col, [range]) | =VLOOKUP(101,A2:C10,2,FALSE) | Vertical lookup |
| HLOOKUP | =HLOOKUP(value, table, row, [range]) | =HLOOKUP("Jan",A1:Z3,2,FALSE) | Horizontal lookup |
| INDEX | =INDEX(array, row, [col]) | =INDEX(A1:C5,2,3) | Value from row & col |
| MATCH | =MATCH(value, array, [type]) | =MATCH(50,A1:A10,0) | Position of value |
| XLOOKUP | =XLOOKUP(value, array, return_array, [not_found]) | =XLOOKUP(101,A2:A10,B2:B10,"Not Found") | Modern lookup |
5️⃣ Text Functions
| Function | Syntax | Example | Description |
|---|---|---|---|
| CONCAT | =CONCAT(text1, text2, ...) | =CONCAT(A1,B1) | Joins text strings |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, ...) | =TEXTJOIN(", ",TRUE,A1:A5) | Joins with delimiter |
| LEFT | =LEFT(text, num_chars) | =LEFT(A1,5) | First N characters |
| RIGHT | =RIGHT(text, num_chars) | =RIGHT(A1,3) | Last N characters |
| MID | =MID(text,start,num_chars) | =MID(A1,3,4) | Substring from middle |
| LEN | =LEN(text) | =LEN(A1) | Character count |
| TRIM | =TRIM(text) | =TRIM(A1) | Removes extra spaces |
| UPPER | =UPPER(text) | =UPPER(A1) | Uppercase text |
| LOWER | =LOWER(text) | =LOWER(A1) | Lowercase text |
| PROPER | =PROPER(text) | =PROPER(A1) | Capitalizes first letter of each word |
6️⃣ Date & Time Functions
| Function | Syntax | Example | Description |
|---|---|---|---|
| TODAY | =TODAY() | =TODAY() | Current date |
| NOW | =NOW() | =NOW() | Current date & time |
| DAY | =DAY(serial_number) | =DAY(A1) | Day of date |
| MONTH | =MONTH(serial_number) | =MONTH(A1) | Month of date |
| YEAR | =YEAR(serial_number) | =YEAR(A1) | Year of date |
| HOUR | =HOUR(serial_number) | =HOUR(A1) | Hour from time |
| MINUTE | =MINUTE(serial_number) | =MINUTE(A1) | Minute from time |
| SECOND | =SECOND(serial_number) | =SECOND(A1) | Second from time |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | =DATEDIF(A1,B1,"Y") | Difference between dates |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | =NETWORKDAYS(A1,B1) | Workdays between dates |
7️⃣ Financial Functions
| Function | Syntax | Example | Description |
|---|---|---|---|
| PMT | =PMT(rate, nper, pv, [fv], [type]) | =PMT(5%/12,60,-10000) | Loan payment |
8️⃣ Error Handling
| Function | Syntax | Example | Description |
|---|---|---|---|
| IFERROR | =IFERROR(value, value_if_error) | =IFERROR(A1/B1,"Error!") | Handles errors gracefully |
9️⃣ Miscellaneous
| Function | Syntax | Example | Description |
|---|---|---|---|
| UNIQUE | =UNIQUE(array) | =UNIQUE(A1:A10) | Unique values |
| FILTER | =FILTER(array, include, [if_empty]) | =FILTER(A2:A10,B2:B10="Yes") | Filter array |
| SORT | =SORT(array, [sort_index], [sort_order], [by_col]) | =SORT(A1:A10) | Sort array |