Here are the most useful Excel formulas explained in a simple and beginner-friendly way:
✅ Basic Excel Formulas
1. SUM
Adds numbers.
=SUM(A1:A10)
2. AVERAGE
Finds the average of values.
=AVERAGE(B1:B10)
3. COUNT
Counts numbers only.
=COUNT(A1:A20)
4. COUNTA
Counts text + numbers (non-empty cells).
=COUNTA(A1:A20)
5. MAX / MIN
Finds the highest or lowest value.
=MAX(A1:A20)
=MIN(A1:A20)
🔍 Logical Formulas
6. IF
Checks a condition.
=IF(A1>50,"Pass","Fail")
7. AND / OR
=AND(A1>10, B1<20)
=OR(A1="Yes", B1="Yes")
🔄 Lookup Formulas
8. VLOOKUP
Search a value in a table (vertical lookup).
=VLOOKUP(A2, D2:F10, 3, FALSE)
9. HLOOKUP
Search horizontally.
=HLOOKUP(A2, A1:F3, 2, FALSE)
10. XLOOKUP (New & Better)
=XLOOKUP(A2, D2:D10, E2:E10)
11. INDEX + MATCH
More powerful than VLOOKUP.
=INDEX(E2:E10, MATCH(A2, D2:D10, 0))
✂️ Text Formulas
12. CONCAT / TEXTJOIN
Join text.
=CONCAT(A1, " ", B1)
=TEXTJOIN(" ", TRUE, A1:C1)
13. LEFT / RIGHT / MID
Extract characters.
=LEFT(A1, 3)
=RIGHT(A1, 2)
=MID(A1, 2, 4)
14. TRIM
Removes extra spaces.
=TRIM(A1)
15. LEN
Counts characters.
=LEN(A1)
📅 Date Formulas
16. TODAY()
Gives today’s date.
=TODAY()
17. NOW()
Displays current date + time.
=NOW()
18. DATEDIF
Calculate age or difference between dates.
=DATEDIF(A1, TODAY(), "Y") → Age in years
📊 Data Cleaning / Analysis Formulas
19. UNIQUE
Removes duplicates (shows unique values).
=UNIQUE(A2:A20)
20. SORT
Sort data.
=SORT(A2:B20, 1, TRUE)
21. FILTER
Extract values matching a condition.
=FILTER(A2:B20, B2:B20>50)
Sure! Here are more useful Excel formulas, including advanced, text, date, financial, and data-analysis formulas — explained simply.
⭐ More Excel Formulas (Easy + Advanced)
🔵 22. SUMIF
Adds numbers based on one condition.
=SUMIF(B2:B20, ">50", C2:C20)
(Adds values in C2:C20 where column B is > 50)
🔵 23. SUMIFS
Multiple conditions.
=SUMIFS(C2:C20, A2:A20, "North", B2:B20, ">50")
🔵 24. COUNTIF
Count cells matching a condition.
=COUNTIF(A2:A20, "Yes")
🔵 25. COUNTIFS
Count cells matching multiple conditions.
=COUNTIFS(A2:A20,"North", B2:B20,">50")
🔵 26. PROPER
Convert text to Proper Case.
=PROPER(A1)
(“meenu singh” → “Meenu Singh”)
🔵 27. UPPER / LOWER
=UPPER(A1)
=LOWER(A1)
🔵 28. SUBSTITUTE
Replace text.
=SUBSTITUTE(A1, "old", "new")
🔵 29. FIND
Find position of text (case-sensitive).
=FIND("a", A1)
🔵 30. SEARCH
Find text (not case-sensitive).
=SEARCH("a", A1)
🔵 31. EXACT
Check if two texts are exactly the same.
=EXACT(A1, B1)
🔵 32. ROUND
Round numbers.
=ROUND(A1, 2)
🔵 33. ROUNDDOWN / ROUNDUP
=ROUNDDOWN(A1, 1)
=ROUNDUP(A1, 1)
🔵 34. INT
Returns integer value.
=INT(5.89) → 5
🔵 35. MOD
Returns remainder.
=MOD(10, 3) → 1
🔵 36. RAND / RANDBETWEEN
Generate random numbers.
=RAND()
=RANDBETWEEN(1, 100)
📅 More Date Formulas
🔵 37. EDATE
Add months to a date.
=EDATE(A1, 3) (Add 3 months)
🔵 38. EOMONTH
Last day of the month.
=EOMONTH(A1, 0)
🔵 39. WORKDAY
Add working days (skip weekends).
=WORKDAY(A1, 10)
🔵 40. NETWORKDAYS
Count working days between two dates.
=NETWORKDAYS(A1, B1)
📊 Data Analysis Formulas
🔵 41. PIVOT TABLE formulas
Used inside pivot tables:
- SUM
- COUNT
- AVERAGE
- % of Total
- Running Total
🔵 42. POWER QUERY transformations
(Not formulas, but very powerful)
- Remove duplicates
- Merge tables
- Split columns
- Append tables
🔵 43. GETPIVOTDATA
Extract value from pivot table.
=GETPIVOTDATA("Sales", A3, "Region", "North")
🔵 44. INDIRECT
Reference cell using text.
=INDIRECT("A" & 5) → refers to A5
🔵 45. OFFSET
Extract a shifted range.
=OFFSET(A1, 2, 1) → 2 rows down, 1 column right
📈 Statistical Formulas
🔵 46. MEDIAN
Middle value.
=MEDIAN(A1:A20)
🔵 47. MODE
Most frequent value.
=MODE(A1:A20)
🔵 48. STDEV
Standard deviation.
=STDEV(A1:A20)
🔵 49. VAR
Variance.
=VAR(A1:A20)
💰 Financial Formulas
🔵 50. PMT
Loan EMI calculation.
=PMT(rate, nper, pv)
Example:
=PMT(10%/12, 60, -500000)
EMI for 5 lakh loan, 5 years.
🔵 51. FV
Future value of investment.
=FV(rate, nper, pmt)
🔵 52. PV
Present value.
=PV(rate, nper, pmt)
🔵 53. NPV
Net present value.
=NPV(rate, values)
🔵 54. IRR
Internal rate of return.
=IRR(values)
.jpg)
0 Comments