Excel Formulas – Complete Reference
Guide
1. Math & Arithmetic Functions
Formula Description Example
SUM() Adds numbers =SUM(A1:A5)
AVERAGE() Calculates average =AVERAGE(B1:B5)
MIN() Finds smallest value =MIN(C1:C10)
MAX() Finds largest value =MAX(D1:D10)
ROUND() Rounds number =ROUND(E1, 2)
INT() Rounds down =INT(9.7)
MOD() Remainder after division =MOD(10, 3)
ABS() Absolute value =ABS(-5)
RAND() Random number between =RAND()
0-1
RANDBETWEEN() Random between two =RANDBETWEEN(1,100)
numbers
2. Logical Functions
Formula Description Example
IF() Logical test =IF(A1>10, "Yes", "No")
AND() Returns TRUE if all are =AND(A1>10, B1<5)
TRUE
OR() Returns TRUE if any is =OR(A1>10, B1<5)
TRUE
NOT() Reverses logic =NOT(A1>10)
3. Text Functions
Formula Description Example
CONCAT() Joins text =CONCAT(A1, B1)
TEXTJOIN() Joins text with delimiter =TEXTJOIN("-", TRUE,
A1:C1)
LEFT() Extracts characters from =LEFT(A1, 3)
left
RIGHT() Extracts characters from =RIGHT(A1, 4)
right
MID() Extracts text from middle =MID(A1, 2, 3)
LEN() Length of text =LEN(A1)
UPPER() Converts to uppercase =UPPER(A1)
LOWER() Converts to lowercase =LOWER(A1)
PROPER() Capitalizes words =PROPER("hello world")
TRIM() Removes extra spaces =TRIM(A1)
FIND() Finds position of text =FIND("e", A1)
SUBSTITUTE() Replaces text =SUBSTITUTE(A1, "old",
"new")
4. Lookup & Reference Functions
Formula Description Example
VLOOKUP() Vertical lookup =VLOOKUP(101, A2:C10, 2,
FALSE)
HLOOKUP() Horizontal lookup =HLOOKUP("Math", A1:E5,
2, FALSE)
XLOOKUP() Flexible lookup (Excel =XLOOKUP(101, A2:A10,
365+) B2:B10)
INDEX() Returns value from table =INDEX(A2:C10, 3, 2)
MATCH() Returns position of value =MATCH(90, B2:B10, 0)
OFFSET() Returns reference offset =OFFSET(A1, 2, 1)
CHOOSE() Picks value from list =CHOOSE(2, "Apple",
"Banana", "Mango")
5. Date & Time Functions
Formula Description Example
TODAY() Current date =TODAY()
NOW() Current date and time =NOW()
DAY() Extracts day =DAY(A1)
MONTH() Extracts month =MONTH(A1)
YEAR() Extracts year =YEAR(A1)
HOUR() Extracts hour =HOUR(A1)
MINUTE() Extracts minute =MINUTE(A1)
SECOND() Extracts second =SECOND(A1)
DATEDIF() Difference between dates =DATEDIF(A1, B1, "D")
EDATE() Adds months to date =EDATE(A1, 2)
6. Statistical Functions
Formula Description Example
COUNT() Count numbers =COUNT(A1:A10)
COUNTA() Count non-empty cells =COUNTA(A1:A10)
COUNTIF() Count with condition =COUNTIF(A1:A10, ">5")
COUNTIFS() Multiple conditions =COUNTIFS(A1:A10, ">5",
B1:B10, "<10")
AVERAGEIF() Average with condition =AVERAGEIF(A1:A10,
">10")
MEDIAN() Middle value =MEDIAN(A1:A10)
MODE() Most frequent value =MODE(A1:A10)
7. Financial Functions
Formula Description Example
PMT() Loan payment =PMT(0.08/12, 60, -10000)
FV() Future value =FV(0.05, 10, -2000)
PV() Present value =PV(0.05, 10, -2000)
NPER() Number of periods =NPER(0.05, -100, 1000)
RATE() Interest rate =RATE(10, -200, 1000)
8. Information Functions
Formula Description Example
ISBLANK() Checks if empty =ISBLANK(A1)
ISNUMBER() Checks for number =ISNUMBER(A1)
ISTEXT() Checks for text =ISTEXT(A1)
ISERROR() Checks for any error =ISERROR(A1)
IFERROR() Custom output if error =IFERROR(A1/B1, "Error")
