Essential Excel Formulas Cheat Sheet
🔹 Basic Formulas
1. =SUM(A1:A10) — Adds all values from A1 to A10
2. =AVERAGE(B1:B5) — Calculates the average
3. =MIN(C1:C5) — Returns the smallest number
4. =MAX(D1:D5) — Returns the largest number
5. =COUNT(E1:E10) — Counts how many cells have numbers
6. =COUNTA(E1:E10) — Counts all non-empty cells
🔹 Logical Formulas
1. =IF(A1>50, "Pass", "Fail") — Checks a condition
2. =AND(A1>10, B1<20) — TRUE if both conditions are true
3. =OR(A1>10, B1<5) — TRUE if either condition is true
4. =NOT(A1=100) — TRUE if A1 is not 100
🔹 Lookup & Reference
1. =VLOOKUP(101, A2:C10, 2, FALSE) — Searches for 101 in first column of range, returns
value from 2nd column
2. =HLOOKUP("Jan", A1:F3, 2, FALSE) — Same as VLOOKUP but horizontal
3. =INDEX(A1:C3, 2, 1) — Returns value at 2nd row, 1st column
4. =MATCH(50, A1:A10, 0) — Returns the position of 50 in range
🔹 Text Functions
1. =CONCATENATE(A1, " ", B1) — Joins text from two cells
2. =LEFT(A1, 4) — Returns first 4 characters
3. =RIGHT(A1, 3) — Returns last 3 characters
4. =LEN(A1) — Returns length of text
5. =TRIM(A1) — Removes extra spaces
6. =UPPER(A1) / LOWER(A1) — Changes case of text
🔹 Date & Time Functions
1. =TODAY() — Current date
2. =NOW() — Current date and time
3. =DAY(A1) — Day of date
4. =MONTH(A1) — Month of date
5. =YEAR(A1) — Year of date
6. =DATEDIF(A1, B1, "d") — Difference in days