Excel Formulas Cheat Sheet
🔹 1. Logical Formulas
Formula Description Example
=IF(condition, value_if_true, Returns value based on
=IF(A1>50,"Pass","Fail")
value_if_false) condition
=AND(condition1, condition2, Returns TRUE if all
=AND(A1>10,B1<100)
…) conditions are TRUE
=OR(condition1, condition2, Returns TRUE if any
=OR(A1="Yes",B1="Yes")
…) condition is TRUE
=NOT(condition) Reverses logical value =NOT(A1>10)
=IFERROR(value,
Handles errors =IFERROR(A1/B1,"Error")
value_if_error)
🔹 2. Mathematical / Arithmetic Formulas
Formula Description Example
=SUM(A1:A10) Adds numbers =SUM(B1:B5)
=AVERAGE(A1:A10) Finds mean =AVERAGE(C1:C5)
=ROUND(number, num_digits) Rounds number =ROUND(12.345,2) → 12.35
=ROUNDUP(number, num_digits) Rounds up =ROUNDUP(12.1,0) → 13
=ROUNDDOWN(number, num_digits) Rounds down =ROUNDDOWN(12.9,0) → 12
=INT(number) Integer part =INT(5.9) → 5
=MOD(number, divisor) Remainder =MOD(10,3) → 1
=POWER(number, power) Exponent =POWER(2,3) → 8
=SQRT(number) Square root =SQRT(25) → 5
=ABS(number) Absolute value =ABS(-10) → 10
=PRODUCT(A1:A5) Multiplication =PRODUCT(2,3,4) → 24
=PI() Returns π =PI() → 3.14159
🔹 3. Conditional & Lookup Formulas
Formula Description Example
Count with
=COUNTIF(range, criteria) =COUNTIF(A1:A10,">50")
condition
=SUMIF(range, criteria, Sum with
=SUMIF(A1:A10,">50",B1:B10)
sum_range) condition
Formula Description Example
=AVERAGEIF(range, criteria, Average with
=AVERAGEIF(A1:A10,"Pass",B1:B10)
average_range) condition
=VLOOKUP(lookup_value,
Vertical
table_array, col_index, =VLOOKUP(101,A2:C10,2,FALSE)
search
[range_lookup])
=HLOOKUP(lookup_value,
Horizontal
table_array, row_index, =HLOOKUP(101,A1:Z2,2,FALSE)
search
[range_lookup])
=XLOOKUP(lookup_value,
Advanced =XLOOKUP(101,A2:A10,B2:B10,"Not
lookup_array, return_array,
lookup Found")
[if_not_found])
=INDEX(array, row_num, Return value
=INDEX(A1:C10,2,3)
[col_num]) by position
=MATCH(lookup_value, Position of
=MATCH(50,A1:A10,0)
lookup_array, [match_type]) value
=CHOOSE(index_num, value1, Picks from =CHOOSE(2,"Red","Blue","Green")
value2, …) list → Blue
🔹 4. Text Formulas
Formula Description Example
=CONCAT("Hello ","World") →
=CONCAT(A1,B1) Joins text
Hello World
Joins with
=TEXTJOIN(" ",TRUE,A1:A5) =TEXTJOIN("-",TRUE,A1:A3)
delimiter
=LEFT(text, num_chars) Extract from left =LEFT("Excel",2) → Ex
Extract from
=RIGHT(text, num_chars) =RIGHT("Excel",2) → el
right
Extract from
=MID(text,start_num,num_chars) =MID("Excel",2,3) → xce
middle
Count
=LEN(text) =LEN("Excel") → 5
characters
=TRIM(text) Remove spaces =TRIM(" Hello ") → Hello
Convert to
=UPPER(text) =UPPER("excel") → EXCEL
uppercase
Convert to
=LOWER(text) =LOWER("EXCEL") → excel
lowercase
First letter =PROPER("excel formula") →
=PROPER(text)
capital Excel Formula
🔹 5. Date & Time Formulas
Formula Description Example
=TODAY() Current date =TODAY()
Current date &
=NOW() =NOW()
time
=DATE(year,month,day) Creates a date =DATE(2025,9,24)
=DAY(A1) Day from date =DAY("2025-09-24") → 24
=MONTH(A1) Month from date =MONTH("2025-09-24") → 9
=YEAR(A1) Year from date =YEAR("2025-09-24") → 2025
Day of week
=WEEKDAY(A1,2) =WEEKDAY("2025-09-24",2)
(1=Mon)
=EDATE(start_date, months) Add months =EDATE("2025-09-24",3)
=DATEDIF("2025-01-01","2025-09-
=DATEDIF(start,end,"d") Days between
24","d")
=NETWORKDAYS("2025-01-
=NETWORKDAYS(start,end) Working days
01","2025-09-24")