100+ MS Excel Functions Formulas for Exams, Office Work & Reporting

by TendToRead · Updated July 2, 2026

Rate this post

If you are doing preparation for exams for the excel and its reporting then this blog will help you to get memorize those formula and function of the excel that you know and do the needful for your exam and reporting. These are the important formula that is very basis, intermediate and few expert level formula that is very useful for the excel and reporting. These formula are also important for the exams like Lower division clerk LDC, Junior assistant and Senior assistant and assistant, Assistant registrar, Upper division clerk and many more in central university, IIMs and top university that are hiring for these level of candidate.

Apart from exams these functions and formula of the excel also essential for the reporting in your job. There are many types of reporting but below listed formulas are the important for any types of the reporting that you are doing at your job. By using these formula you can optimize and reduce your workload by applying those formula.

What is Microsoft Excel ?

Microsoft Excel or MS Excel is developed by the Microsoft that is very popular for the reporting and for other stuff such as organize data, calculate, analyze data, to present visual form with accurately. It is part of Microsoft 365 that widely used in schools, colleges, businesses, government offices, banks, hospitals, and many other places. With excel you can do the below:

• Make the data in rows and columns.
• Perform basic and complex calculations via functions.
• Create visual charts and graphs.
• Analyse business data.
• Prepare reports and dashboards.

Why Is Learning Excel Functions Important? ?

Learning MS Excel functions is the best ways to make your work easy and improve productivity and also optimize your data in form of visual.
1. Saves Time
2. Improves Accuracy
3. Increases Productivity
4. Helps in Data Analysis
5. Essential for Many Careers
6. Useful for Everyday Life
7. Improves Job Opportunities

Before applying the formula you must have to keep those below points at your finger tips:

Excel Formula Rules

• Every excel formula starts with =
• Use brackets correctly while applying the formula
• In the excel or spreadsheet Cell references are not case-sensitive
• Avoid typing numbers directly when possible
• Use the correct formula as per the Microsoft rule.

Microsoft_Excel_function_formula

1. SUM

What it does: Adds numbers

Syntax

=SUM(A1:A5)

Example: If A1:A5 = 10,20,30,40,50 → Result: 150

2. AVERAGE

What it does: Finds average

Syntax

=AVERAGE(A1:A5)

Example: 10,20,30,40,50 → 30

3. MAX

What it does: Largest value

Syntax

=MAX(A1:A5)

Example: Returns 50

4. MIN

What it does: Smallest value

Syntax

=MIN(A1:A5)

Example: Returns 10

5. COUNT

What it does: Counts numbers

Syntax

=COUNT(A1:A5)

Example: Counts numeric cells

6. COUNTA

What it does: Counts non-empty cells

Syntax

=COUNTA(A1:A5)

Example: Counts all filled cells

7. COUNTBLANK

What it does: Counts blank cells

Syntax

=COUNTBLANK(A1:A5)

Example: Returns number of blanks

8. IF

What it does: Checks a condition

Syntax

=IF(A1>=40,"Pass","Fail")

Example: 60 → Pass

9. AND

What it does: All conditions true

Syntax

=AND(A1>0,B1>0)

Example: TRUE if both are true

10. OR

What it does: Any condition true

Syntax

=OR(A1>0,B1>0)

Example: TRUE if one is true

11. NOT

What it does: Reverses logical value

Syntax

=NOT(A1>0)

Example: TRUE becomes FALSE

12. IFERROR

What it does: Handles errors

Syntax

=IFERROR(A1/B1,"Error")

Example: Shows Error if divide by zero

13. ROUND

What it does: Rounds number

Syntax

=ROUND(12.345,2)

Example: 12.35

14. ROUNDUP

What it does: Rounds up

Syntax

=ROUNDUP(12.31,1)

Example: 12.4

15. ROUNDDOWN

What it does: Rounds down

Syntax

=ROUNDDOWN(12.39,1)

Example: 12.3

16. ABS

What it does: Positive value

Syntax

=ABS(-25)

Example: 25

17. SQRT

What it does: Square root

Syntax

=SQRT(81)

Example: 9

18. POWER

What it does: Raises power

Syntax

=POWER(2,5)

Example: 32

19. MOD

What it does: Remainder

Syntax

=MOD(10,3)

Example: 1

20. TODAY

What it does: Current date

Syntax

=TODAY()

Example: Today's date

21. NOW

What it does: Current date & time

Syntax

=NOW()

Example: Current timestamp

22. YEAR

What it does: Gets year

Syntax

=YEAR(A1)

Example: 2026

23. MONTH

What it does: Gets month

Syntax

=MONTH(A1)

Example: 7

24. DAY

What it does: Gets day

Syntax

=DAY(A1)

Example: 2

25. LEN

What it does: Text length

Syntax

=LEN("Excel")

Example: 5

26. LEFT

What it does: Left characters

Syntax

=LEFT("Excel",2)

Example: Ex

27. RIGHT

What it does: Right characters

Syntax

=RIGHT("Excel",2)

Example: el

28. MID

What it does: Middle characters

Syntax

=MID("Excel",2,3)

Example: xce

29. UPPER

What it does: Uppercase

Syntax

=UPPER("excel")

Example: EXCEL

30. LOWER

What it does: Lowercase

Syntax

=LOWER("EXCEL")

Example: excel

31. PROPER

What it does: Capitalizes words

Syntax

=PROPER("hello world")

Example: Hello World

32. CONCAT

What it does: Joins text

Syntax

=CONCAT(A1," ",B1)

Example: John Smith

33. TRIM

What it does: Removes spaces

Syntax

=TRIM(A1)

Example: Extra spaces removed

34. VLOOKUP

What it does: Vertical lookup

Syntax

=VLOOKUP(101,A:C,2,FALSE)

Example: Returns matching value

35. HLOOKUP

What it does: Horizontal lookup

Syntax

=HLOOKUP(...)

Example: Returns matching value

36. XLOOKUP

What it does: Modern lookup

Syntax

=XLOOKUP(A2,D:D,E:E)

Example: Finds value

37. INDEX

What it does: Returns value by position

Syntax

=INDEX(A1:C5,2,3)

Example: Cell value

38. MATCH

What it does: Returns position

Syntax

=MATCH("Apple",A:A,0)

Example: Position

39. SUMIF

What it does: Conditional sum

Syntax

=SUMIF(A:A,"Apple",B:B)

Example: Sum Apples

40. COUNTIF

What it does: Conditional count

Syntax

=COUNTIF(A:A,"Yes")

Example: Count Yes

41. AVERAGEIF

What it does: Conditional average

Syntax

=AVERAGEIF(A:A,"A",B:B)

Example: Average

42. SUMIFS

What it does: Multiple condition sum

Syntax

=SUMIFS(...)

Example: Conditional sum

43. COUNTIFS

What it does: Multiple condition count

Syntax

=COUNTIFS(...)

Example: Conditional count

44. TEXT

What it does: Formats value

Syntax

=TEXT(A1,"dd-mm-yyyy")

Example: Formatted

45. TEXTJOIN

What it does: Join with separator

Syntax

=TEXTJOIN(", ",TRUE,A1:A3)

Example: A, B, C

46. UNIQUE

What it does: Unique values

Syntax

=UNIQUE(A:A)

Example: Unique list

47. SORT

What it does: Sorts data

Syntax

=SORT(A:A)

Example: Sorted

48. FILTER

What it does: Filters rows

Syntax

=FILTER(A:C,C:C="Yes")

Example: Filtered

49. RAND

What it does: Random decimal

Syntax

=RAND()

Example: 0-1

50. RANDBETWEEN

What it does: Random integer

Syntax

=RANDBETWEEN(1,100)

Example: 1-100

51. CEILING

What it does: Round up multiple

Syntax

=CEILING(22,5)

Example: 25

52. FLOOR

What it does: Round down multiple

Syntax

=FLOOR(22,5)

Example: 20

53. INT

What it does: Integer part

Syntax

=INT(12.9)

Example: 12

54. SIGN

What it does: Returns sign

Syntax

=SIGN(-5)

Example: -1

55. PRODUCT

What it does: Multiply

Syntax

=PRODUCT(A1:A3)

Example: Product

56. SUBTOTAL

What it does: Subtotal

Syntax

=SUBTOTAL(9,A1:A10)

Example: Sum visible

57. AGGREGATE

What it does: Advanced subtotal

Syntax

=AGGREGATE(...)

Example: Aggregate

58. OFFSET

What it does: Reference offset

Syntax

=OFFSET(A1,1,1)

Example: Shift ref

59. INDIRECT

What it does: Text to reference

Syntax

=INDIRECT("A1")

Example: Reference

60. ADDRESS

What it does: Cell address

Syntax

=ADDRESS(2,3)

Example: $C$2

61. ROW

What it does: Row number

Syntax

=ROW(A5)

Example: 5

62. COLUMN

What it does: Column number

Syntax

=COLUMN(C1)

Example: 3

63. ROWS

What it does: Row count

Syntax

=ROWS(A1:A10)

Example: 10

64. COLUMNS

What it does: Column count

Syntax

=COLUMNS(A:C)

Example: 3

65. ISNUMBER

What it does: Is number?

Syntax

=ISNUMBER(A1)

Example: TRUE/FALSE

66. ISTEXT

What it does: Is text?

Syntax

=ISTEXT(A1)

Example: TRUE/FALSE

67. ISBLANK

What it does: Is blank?

Syntax

=ISBLANK(A1)

Example: TRUE/FALSE

68. EXACT

What it does: Exact compare

Syntax

=EXACT("A","a")

Example: FALSE

69. SEARCH

What it does: Find text

Syntax

=SEARCH("cat",A1)

Example: Position

70. FIND

What it does: Case-sensitive find

Syntax

=FIND("A",A1)

Example: Position

71. REPLACE

What it does: Replace by position

Syntax

=REPLACE(A1,1,2,"Hi")

Example: Changed

72. SUBSTITUTE

What it does: Replace text

Syntax

=SUBSTITUTE(A1,"old","new")

Example: Changed

73. REPT

What it does: Repeat text

Syntax

=REPT("*",5)

Example: *****

74. CHAR

What it does: Character code

Syntax

=CHAR(65)

Example: A

75. CODE

What it does: Character code number

Syntax

=CODE("A")

Example: 65

76. DATE

What it does: Create date

Syntax

=DATE(2026,7,2)

Example: 2-Jul-2026

77. TIME

What it does: Create time

Syntax

=TIME(10,30,0)

Example: 10:30

78. WEEKDAY

What it does: Day number

Syntax

=WEEKDAY(A1)

Example: 1-7

79. WEEKNUM

What it does: Week number

Syntax

=WEEKNUM(A1)

Example: Week

80. EOMONTH

What it does: Month end

Syntax

=EOMONTH(A1,0)

Example: Last day

81. NETWORKDAYS

What it does: Working days

Syntax

=NETWORKDAYS(A1,B1)

Example: Days

82. WORKDAY

What it does: Next workday

Syntax

=WORKDAY(A1,5)

Example: Date

83. EDATE

What it does: Add months

Syntax

=EDATE(A1,2)

Example: Future

84. DATEDIF

What it does: Date difference

Syntax

=DATEDIF(A1,B1,"Y")

Example: Years

85. PMT

What it does: Loan payment

Syntax

=PMT(...)

Example: EMI

86. FV

What it does: Future value

Syntax

=FV(...)

Example: Investment

87. PV

What it does: Present value

Syntax

=PV(...)

Example: Current value

88. NPV

What it does: Net present value

Syntax

=NPV(...)

Example: NPV

89. IRR

What it does: Internal rate

Syntax

=IRR(A1:A5)

Example: %

90. CORREL

What it does: Correlation

Syntax

=CORREL(A:A,B:B)

Example: Correlation

91. MEDIAN

What it does: Median

Syntax

=MEDIAN(A:A)

Example: Middle

92. MODE.SNGL

What it does: Most common

Syntax

=MODE.SNGL(A:A)

Example: Mode

93. LARGE

What it does: Nth largest

Syntax

=LARGE(A:A,2)

Example: 2nd largest

94. SMALL

What it does: Nth smallest

Syntax

=SMALL(A:A,2)

Example: 2nd smallest

95. RANK

What it does: Rank

Syntax

=RANK(A1,A:A)

Example: Rank

96. TRANSPOSE

What it does: Swap rows/cols

Syntax

=TRANSPOSE(A1:C3)

Example: Transposed

97. CHOOSE

What it does: Choose value

Syntax

=CHOOSE(2,"A","B")

Example: B

98. SWITCH

What it does: Multiple conditions

Syntax

=SWITCH(A1,1,"One")

Example: One

99. IFS

What it does: Multiple IFs

Syntax

=IFS(A1>90,"A")

Example: Grade

100. XOR

What it does: Exclusive OR

Syntax

=XOR(TRUE,FALSE)

Example: TRUE

101. NA

What it does: Returns #N/A

Syntax

=NA()

Example: #N/A

102. HSTACK

What it does: Stack horizontally

Syntax

=HSTACK(A:A,B:B)

Example: Combined

103. VSTACK

What it does: Stack vertically

Syntax

=VSTACK(A:A,B:B)

Example: Combined

104. TAKE

What it does: Take rows

Syntax

=TAKE(A1:C10,5)

Example: Top 5

105. DROP

What it does: Drop rows

Syntax

=DROP(A1:C10,2)

Example: Without first 2

106. TOROW

What it does: Convert to row

Syntax

=TOROW(A1:C3)

Example: Single row

107. TOCOL

What it does: Convert to column

Syntax

=TOCOL(A1:C3)

Example: Single column

Above we have added excel formula and function that is very important for the reporting and job seeker or while doing the data analysis. There are also many formula in excel but above listed here are important one that is very essential for excel work and also important for the exams in University and government exams. These function are very popular in the excel that you must have to keep at your figure tip.

Frequently Asked Questions (FAQs)

1. What is an Excel function?

An Excel function is a in-built-in formula in microsoft excel that perform the calculations and other stuff by applying the formula. Formula functions like SUM, AVERAGE, and IF help to complete the tasks in the very efficiently.

Example:

=SUM(A1:A10)

2. What is the difference between a formula and a function?

A formula is an equation where you can create yourself, while a function is a predefined formula function that already present in Excel.

Formula Example:

=A1+B1

Function Example:

=SUM(A1:B1)

3. Which Excel functions should beginners learn first?

Beginners should learn with these essential functions:

SUM
AVERAGE
COUNT
COUNTA
MAX
MIN
IF
ROUND
TODAY
XLOOKUP or VLOOKUP

4. What is the SUM function?

The SUM function adds numbers from selected cells.

Example:

=SUM(B2:B10)

5. What is the IF function?

The IF function checks whether a condition is true or false and returns the value on the basis of the condition.

Example:

=IF(A2>=35,"Pass","Fail")

6. What is XLOOKUP?

XLOOKUP searches for a value in one column and returns the matching value from another column, it also work for both way horizontally and vertically. It is easier and more flexible than VLOOKUP.

Example: