EXCEL FORMULAS

Basic Formulas
1. =SUM (A1:A10) Adds numbers in a range.
Example: =SUM (10,20) → 30

2. =AVERAGE (A1:A10) Returns the mean.

Example: =AVERAGE (10,20,30) → 20

3. =MEDIAN (A1:A10) Returns the middle value.

Example: =MEDIAN (10,20,30,40) → 25

4. =MODE (A1:A10) Most frequent value.

Example: =MODE (1,2,2,3) → 2

5. =MIN (A1:A10) Smallest value in range.

Example: =MIN (5,7,9) → 5

6. =MAX (A1:A10) Largest value in range.

Example: =MAX (5,7,9) → 9

7. =ROUND(A1,2) Rounds to 2 decimal places.

Example: =ROUND (12.345,2) → 12.35

8. =ROUNDUP(A1,0) Always rounds up.

Example: =ROUNDUP (12.1,0) → 13

9. =ROUNDDOWN(A1,0) Always rounds down.

Example: =ROUNDDOWN (12.9,0) → 12

10. =TRUNC(A1,2) Cuts decimals without rounding.

Example: =TRUNC (12.345,2) → 12.34
 EXCEL FORMULAS

Logical Functions
11. =IF(A1>50,"Pass","Fail") Returns values based on condition.

12. =AND(A1>50, B1<100) Checks multiple conditions.

Example: Both must be TRUE → TRUE/FALSE

13. =OR(A1>50, B1<100) Any condition TRUE returns TRUE.

14. =NOT(A1=10) Returns TRUE if condition is FALSE.

15. =IFERROR(A1/B1,"Error") Handles division by zero.

Conditional Aggregation
16. =COUNT (A1:A10) Counts numbers.
Example: =COUNT(1,2,"x") → 2

17. =COUNTA (A1:A10) Counts non-empty.

Example: =COUNTA(1,2,"x","") → 3

18. =COUNTBLANK (A1:A10) Counts blanks.

Example: =COUNTBLANK({"A","","B"}) → 1

19. =COUNTIF (A1:A10,">10") Counts >10.

Example: =COUNTIF ({5,15,20},">10") → 2

20. =COUNTIFS (A1:A10,">10”, B1:B10,"Yes") Counts with multiple

criteria.

21. =SUMIF (A1:A10,">10") Sum if >10.

Example: =SUMIF ({5,15,20},">10") → 35

22. =SUMIFS (A1:A10, B1:B10,"Yes") Sum with multiple criteria.

 EXCEL FORMULAS

23. =AVERAGEIF (A1:A10,">10") Avg of values >10.

24. =AVERAGEIFS (A1:A10, B1:B10,"Yes") Avg with multiple criteria.

25. =IF(A1>10,"Pass","Fail") Conditional output

Lookup & Reference

26. = VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks for a value in the first column of a range and returns a value from
another column in the same row.
• Example:
=VLOOKUP(101, A2:D10, 3, FALSE) → Searches for 101 in column A and
returns the value from 3rd column in that row.

27. =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Works like VLOOKUP but searches horizontally in the first row.
• Example:
=HLOOKUP(50, A1:H5, 3, FALSE) → Searches for 50 in row 1 and returns
value from 3rd row in that column.

28. =INDEX(array, row_num, [col_num])

• Returns the value of a cell from a given row and column in a range.
• Example:
=INDEX(A2:C10, 2, 3) → Returns the value from row 2, column 3 of the
range.

29. =MATCH(lookup_value, lookup_array, [match_type])

• Returns the position of a value in a range.
• Example:
=MATCH(45, A1:A10, 0) → Finds position of 45 in the range
 EXCEL FORMULAS

30. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],

[match_mode], [search_mode])
• Modern replacement for VLOOKUP/HLOOKUP, more flexible.
• Example:
=XLOOKUP(45, A1:A10, B1:B10, "Not Found") → If 45 is found in A
column, returns corresponding value from column B.

31. =OFFSET(reference, rows, cols, [height], [width])

• Returns a cell reference that is offset from a starting cell by rows and
columns.
• Example:
=OFFSET(A1,2,3) → Returns the cell that is 2 rows down and 3 columns
right of A1.

32. =CHOOSE(index_num, value1, [value2], …)

• Chooses a value from a list by position.
• Example:
=CHOOSE(2, "Red","Blue","Green") → Returns "Blue".

Reference
33. Relative Reference → =A1+B1 Cell references change when copied.
• Example: Copying from row 1 to row 2 becomes =A2+B2.

34. Absolute Reference → =$A$1+$B$1

Cell reference does not change when copied.
• Example: Always adds values from A1 and B1.

35. Mixed Reference → =A$1+$B1 Locks either row or column.

Example: A$1 locks row 1, $B1 locks column B.
 EXCEL FORMULAS

Text Functions
36. =LEN(A1) Counts characters (including spaces).
• =LEN(text)
• Example: =LEN("Excel") → 5

37. =LEFT(A1,4) Returns first N characters.

• =LEFT(text, num_chars)
• Example: =LEFT("Excel",4) → "Exce"

38. =RIGHT(A1,3) Returns last N characters.

• =RIGHT(text, num_chars)
• Example: =RIGHT("Excel",3) → "cel"

39. =MID(A1,2,3) Extracts substring starting at a position.

• =MID(text, start_num, num_chars)
• Example: =MID("Excel",2,3) → "xce"

40. =CONCAT(A1,B1) Joins text values.

• =CONCAT(text1, [text2], …)
• Example: =CONCAT("Data","Science") → "DataScience"

41. =TEXTJOIN("-",TRUE,A1:A3) Joins text with delimiter.

• =TEXTJOIN(delimiter, ignore_empty, text1, …)
• Example: =TEXTJOIN("-",TRUE,{"Red","Blue","Green"}) → "Red-Blue-
Green"
 EXCEL FORMULAS

42. =TRIM(A1) Removes extra spaces.

• =TRIM(text)
• Example: =TRIM(" Excel Guide ") → "Excel Guide"

43. =PROPER(A1) Converts to Proper Case.

• =PROPER(text)
• Example: =PROPER("data analysis") → "Data Analysis"

44. =UPPER(A1) Converts to UPPERCASE.

• Example: =UPPER("excel") → "EXCEL"

45. =LOWER(A1) Converts to lowercase.

• Example: =LOWER("EXCEL") → "excel"

46. =REPLACE(A1,2,3,"XYZ") Replaces part of text.

• =REPLACE(old_text, start_num, num_chars, new_text)
• Example: =REPLACE("Excel",2,3,"XYZ") → "EXYZl"

47. =SUBSTITUTE(A1,"old","new") Replaces specific text.

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Example: =SUBSTITUTE("Good morning","morning","night") → "Good
night"

48. =FIND("x",A1) Returns position of text (case-sensitive).

• =FIND(find_text, within_text)
• Example: =FIND("x","Excel") → 2

49. =SEARCH("x",A1) Same as FIND but not case-sensitive.

• Example: =SEARCH("X","Excel") → 2
EXCEL FORMULAS

Date & Time

50.=TODAY() Returns current date.
Example: =TODAY() → 14-09-2025

51.=NOW() Returns current date & time.

Example: =NOW() → 14-09-2025 21:07

52.=DAY(A1) Extracts day from date.

Example: If A1 = 14-09-2025, =DAY(A1) → 14

53.=MONTH(A1) Extracts month from date.

Example: If A1 = 14-09-2025, =MONTH(A1) → 9

54.=YEAR(A1) Extracts year from date.

Example: If A1 = 14-09-2025, =YEAR(A1) → 2025

55.=WEEKDAY(A1) Returns weekday number (1=Sunday, 2=Monday

…).
Example: If A1 = 14-09-2025 (Sunday), =WEEKDAY(A1) → 1

56.=EDATE(A1,6) Returns date after 6 months.

Example: If A1 = 14-09-2025, =EDATE(A1,6) → 14-03-2026

57.=DATEDIF(A1,B1,"Y") Returns age in years.

Example: If A1 = 14-09-2000, B1 = 14-09-2025, → 25

58. =NETWORKDAYS(A1,B1) Working days between two dates.

Example: If A1 = 01-09-2025, B1 = 14-09-2025, → 10
EXCEL FORMULAS

Statistical & Math Functions

59.=STDEV(A1:A10) Standard deviation of values.
Example: If A1:A10 = {2,4,4,4,5,5,7,9}, → 2

60.=VAR(A1:A10) Variance of values.

Example: If A1:A10 = {2,4,4,4,5,5,7,9}, → 4

61.=CORREL(A1:A10,B1:B10) Correlation coefficient.

Example: If A1:A5 = {1,2,3,4,5}, B1:B5 = {2,4,6,8,10}, → 1

62.=COVAR(A1:A10,B1:B10) Covariance.
Example: If A1:A5 = {1,2,3}, B1:B5 = {2,4,6}, → 2.33

63.=FREQUENCY(A1:A10,B1:B5) Frequency distribution.

Example: A1:A10 = {5,7,3,8,5,10,12,7}, B1:B5 = {5,10,15} → {2,3,3}

64.=PERCENTILE(A1:A10,0.9) 90th percentile.

Example: If A1:A10 = {1,2,3,4,5,6,7,8,9,10}, → 9

65.=RANK(A1,A1:A10) Rank of a number.

Example: If A1=85 and A1:A10={90,85,70,60}, → 2

66.=QUARTILE(A1:A10,1) First quartile.

Example: If A1:A10={10,20,30,40,50,60,70,80}, → 27.5

67.=ABS(A1) Absolute value.

Example: If A1 = -15, =ABS(A1) → 15

68.=POWER(A1,2) Square/power.
Example: If A1=5, =POWER(A1,2) → 25

69.=SQRT(A1) Square root.

Example: If A1=49, =SQRT(A1) → 7

70.=PI() Returns 3.14159.

Example: =PI() → 3.14159
EXCEL FORMULAS

71.=RAND() Random number (0–1).

Example: =RAND() → 0.6732 (varies)

72. =RANDBETWEEN(1,100) Random integer.

Example: =RANDBETWEEN(1,100) → 57 (varies)

Data Analysis Helpers

73. =UNIQUE(A1:A10) Returns unique values from a range.
Example: A1:A10 = {A,B,B,C,A} → {A,B,C}

74. =SORT(A1:A10,1,TRUE) Sorts values ascending.

Example: A1:A5={30,10,50,20} → {10,20,30,50}

75. =FILTER(A1:C10,B1:B10="Yes") Filters rows where column B = Yes.

Example: If Column B has Yes/No, only rows with Yes return.

76. =TRANSPOSE(A1:B2) Converts rows to columns (and vice versa).

Example: If A1:B2 =1 2
3 4
Become:- 1 3
2 4