Complete Excel Formulas Course with Examples

Basic Excel Formulas List

Here is a practical basic Excel formulas list for beginners, with examples:

Essential formulas to learn first

If you’re a beginner, start with these 10 formulas:

1. SUM
2. AVERAGE
3. COUNT
4. COUNTA
5. MAX
6. MIN
7. IF
8. COUNTIF
9. SUMIF
10. VLOOKUP / XLOOKUP

These cover a large portion of everyday Excel tasks such as calculations, grading, sales reports, data analysis, and lookups.

Excel Sheet Formulas

1. Mathematical Excel Basic Formulas

Example: Sales Calculation

Suppose:

In D2:

=B2*C2

Copy down to calculate total sales for each product.

2. SUM

Adds numbers together.

Syntax

=SUM(number1,number2,…)

Example

=SUM(A1:A10)

Adds all values from A1 to A10.

Another example:

=SUM(B2:B20)

Multiple ranges

Multiple Ranges Formulas

=SUM(B2:B20,D2:D20)

3. AVERAGE

Calculates the arithmetic mean.

=AVERAGE(A1:A10)

Example:

10

20

30

40

50

=AVERAGE(A1:A5)

Result:

30

4. MIN

Returns the smallest value.

=MIN(A1:A10)

Example:

45

20

80

15

60

Result:

15

5. MAX

Returns the largest value.

=MAX(A1:A10)

Result:

80

6. COUNT

Counts cells containing numbers.

=COUNT(A1:A20)

If A1:A5 contains:

100

200

Apple

300

500

Result:

4

7. COUNTA

Counts non-empty cells.

=COUNTA(A1:A20)

It counts numbers, text, dates, etc.

8. COUNTBLANK

Counts empty cells.

=COUNTBLANK(A1:A20)

Useful for finding missing information.

9. ROUND

Rounds a number to a specified number of decimal places.

=ROUND(A1,2)

Example:

=ROUND(125.6789,2)

Result:

125.68

10. ROUNDUP

Always rounds upward.

=ROUNDUP(125.121,2)

Result:

125.13

11. ROUNDDOWN

Always rounds downward.

=ROUNDDOWN(125.129,2)

Result:

125.12

12. INT

Returns the integer portion of a number.

=INT(25.99)

Result:

25

13. ABS

Returns the absolute value.

=ABS(-250)

Result:

250

14. MOD

Returns the remainder after division.

=MOD(10,3)

Result:

1

Useful for checking odd/even numbers:

=MOD(A1,2)

If result is 0, the number is even.

15. POWER

Raises a number to a power.

=POWER(5,3)

Result:

125

Equivalent:

=5^3

16. SQRT

Calculates square root.

=SQRT(144)

Result:

12

17. IF

One of the most important Excel functions.

Syntax

=IF(condition,value_if_true,value_if_false)

Example

=IF(A2>=50,”Pass”,”Fail”)

If A2 is 75:

Pass

If A2 is 40:

Fail

18. Nested IF

Multiple conditions can be evaluated.

=IF(A2>=80,”A”,IF(A2>=70,”B”,IF(A2>=60,”C”,IF(A2>=50,”D”,”F”))))

Example:

19. IFS

A cleaner alternative to multiple nested IFs.

=IFS(

A2>=80,”A”,

A2>=70,”B”,

A2>=60,”C”,

A2>=50,”D”,

A2<50,”F”

)

20. AND

Returns TRUE when all conditions are true.

=AND(A2>=50,B2>=50)

Example:

=IF(AND(B2>=50,C2>=75),”Eligible”,”Not Eligible”)

21. OR

Returns TRUE when at least one condition is true.

=OR(A2>=50,B2>=50)

Example:

=IF(OR(B2=”Yes”,C2=”Yes”),”Approved”,”Rejected”)

22. NOT

Reverses TRUE/FALSE.

=NOT(A2=”Paid”)

If A2 is Paid, result is FALSE.

23. IFERROR

Handles errors gracefully.

=IFERROR(A2/B2,0)

If B2 is zero, instead of #DIV/0!, Excel returns:

0

Another example:

=IFERROR(VLOOKUP(E2,A2:C100,3,FALSE),”Not Found”)

24. COUNTIF

Counts cells matching a condition.

=COUNTIF(A2:A100,”Pass”)

Numeric condition

=COUNTIF(B2:B100,”>=50″)

Text condition

=COUNTIF(A2:A100,”Laptop”)

25. COUNTIFS

Counts using multiple conditions.

=COUNTIFS(A2:A100,”Male”,B2:B100,”>=50″)

Example:

Count students who:

• are Male
• scored 50 or more

26. SUMIF

Adds values based on one condition.

=SUMIF(A2:A100,”Laptop”,C2:C100)

If column A contains products and C contains sales, this calculates total Laptop sales.

27. SUMIFS

Adds values using multiple conditions.

=SUMIFS(D2:D100,A2:A100,”Laptop”,B2:B100,”January”)

This adds sales where:

• Product = Laptop
• Month = January

28. AVERAGEIF

Calculates average based on a condition.

=AVERAGEIF(A2:A100,”Male”,B2:B100)

Calculates the average marks of male students.

29. AVERAGEIFS

Calculates average using multiple conditions.

=AVERAGEIFS(D2:D100,A2:A100,”IT”,B2:B100,”>=50″)

30. CONCAT

Combines text.

=CONCAT(A2,” “,B2)

If:

A2 = Ali

B2 = Khan

Result:

Ali Khan

31. CONCATENATE

Older Excel function for combining text.

=CONCATENATE(A2,” “,B2)

CONCAT or TEXTJOIN is generally preferable in modern Excel.

32. TEXTJOIN

Combines multiple pieces of text with a delimiter.

=TEXTJOIN(” “,TRUE,A2:C2)

Example:

Result:

Muhammad Ali Khan

33. LEFT

Extracts characters from the left.

=LEFT(A2,5)

If A2 is:

Pakistan

Result:

Pakis

34. RIGHT

Extracts characters from the right.

=RIGHT(A2,3)

Pakistan → tan

35. MID

Extracts text from the middle.

=MID(A2,3,4)

Meaning:

• Start at character 3
• Extract 4 characters

36. LEN

Counts characters.

=LEN(A2)

Example:

IT Code Hub

returns the number of characters, including spaces.

37. TRIM

Removes unnecessary spaces.

=TRIM(A2)

Useful when imported data contains extra spaces.

38. UPPER

Converts text to uppercase.

=UPPER(A2)

hello world → HELLO WORLD

39. LOWER

Converts text to lowercase.

=LOWER(A2)

HELLO WORLD → hello world

40. PROPER

Capitalizes the first letter of each word.

=PROPER(A2)

ali khan → Ali Khan

41. FIND

Finds the position of text.

=FIND(“@”,A2)

For:

ali@gmail.com

the result identifies the position of @.

FIND is case-sensitive.

42. SEARCH

Similar to FIND but not case-sensitive.

=SEARCH(“gmail”,A2)

43. SUBSTITUTE

Replaces specific text.

=SUBSTITUTE(A2,”Pakistan”,”PK”)

Example:

I live in Pakistan

becomes:

I live in PK

44. REPLACE

Replaces characters based on their position.

=REPLACE(A2,1,5,”Hello”)

45. TEXT

Formats numbers or dates as text.

=TEXT(A2,”#,##0″)

Example:

1250000

becomes:

1,250,000

Excel Date Formulas

Excel date formulas list with exampls

Date example

=TEXT(A2,”dd-mm-yyyy”)

46. TODAY

Returns today’s date.

=TODAY()

47. NOW

Returns the current date and time.

=NOW()

48. DATE

Creates a date.

=DATE(2026,8,13)

Result:

13-Aug-2026

49. YEAR

Extracts the year.

=YEAR(A2)

50. MONTH

Extracts the month number.

=MONTH(A2)

51. DAY

Extracts the day.

=DAY(A2)

52. DAYS

Calculates the number of days between dates.

=DAYS(B2,A2)

If:

A2 = 01-Aug-2026

B2 = 13-Aug-2026

Result:

12

53. DATEDIF

Calculates the difference between two dates.

Years

=DATEDIF(A2,B2,”Y”)

Months

=DATEDIF(A2,B2,”M”)

Days

=DATEDIF(A2,B2,”D”)

Age calculation

If date of birth is in A2:

=DATEDIF(A2,TODAY(),”Y”)

This returns the person’s age in completed years.

54. NETWORKDAYS

Calculates working days between dates.

=NETWORKDAYS(A2,B2)

By default, Saturday and Sunday are excluded.

55. WORKDAY

Returns a date after a specified number of working days.

=WORKDAY(A2,10)

Returns the date 10 working days after A2.

Lookup formulas List

56. VLOOKUP

Searches vertically in a table.

=VLOOKUP(E2,A2:C100,3,FALSE)

Example:

If E2 contains 102:

=VLOOKUP(E2,A2:C4,3,FALSE)

Result:

60000

57. HLOOKUP

Searches horizontally.

=HLOOKUP(B1,A1:F3,3,FALSE)

Useful when lookup values are arranged across columns.

58. XLOOKUP

Modern replacement for many VLOOKUP/HLOOKUP situations.

=XLOOKUP(E2,A2:A100,C2:C100,”Not Found”)

Example:

E2 = 102

Excel searches A2:A100 and returns the corresponding value from C2:C100.

Why XLOOKUP is powerful

It can:

• Look left or right
• Return a custom “not found” message
• Use exact matching easily
• Avoid hard-coded column numbers

FILTER Formulas List

59. INDEX

Returns a value from a specified position.

=INDEX(C2:C100,5)

Returns the fifth value from C2:C100.

60. MATCH

Finds the position of a value.

=MATCH(E2,A2:A100,0)

0 means exact match.

61. INDEX + MATCH

A powerful traditional lookup combination.

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

This searches for E2 in column A and returns the corresponding value from column C.

62. FILTER

Returns rows matching criteria.

=FILTER(A2:D100,C2:C100=”Passed”)

Returns all rows where column C equals Passed.

Multiple conditions

=FILTER(A2:D100,(C2:C100=”Passed”)*(D2:D100>=80))

This means:

Status = Passed

AND

Marks >= 80

63. SORT

Sorts a range.

=SORT(A2:C100,2,1)

Meaning:

• Sort A2:C100
• By column 2
• Ascending

Descending:

=SORT(A2:C100,2,-1)

64. SORTBY

Sorts one range based on another.

=SORTBY(A2:C100,C2:C100,-1)

Sorts the dataset by column C descending.

65. UNIQUE

Returns unique values.

=UNIQUE(A2:A100)

If the original list contains:

PHP

Python

PHP

Java

Python

Result:

PHP

Python

Java

66. SEQUENCE

Generates sequential numbers.

=SEQUENCE(10)

Returns:

1

2

3

…

10

10 rows × 3 columns

=SEQUENCE(10,3)

67. TRANSPOSE

Changes rows into columns or columns into rows.

=TRANSPOSE(A1:C3)

68. LARGE

Returns the nth largest value.

=LARGE(A2:A100,1)

Largest value.

=LARGE(A2:A100,2)

Second-largest value.

69. SMALL

Returns the nth smallest value.

=SMALL(A2:A100,1)

Smallest value.

70. RANK

Finds the ranking of a number.

=RANK(B2,$B$2:$B$100,0)

0 ranks largest number as #1.

Percentage formulas List

Percentage formulas list with easy examples.

71. PERCENTAGE

Percentage of total

If sales are in B2 and total sales are in B10:

=B2/$B$10

Format the result as %.

The $ makes B10 an absolute reference.

72. Percentage Increase

If old price is A2 and new price is B2:

=(B2-A2)/A2

Example:

Old = 1000

New = 1200

Result:

20%

73. Percentage Decrease

=(A2-B2)/A2

74. Discount Calculation

If original price is A2 and discount percentage is B2:

=A2*B2

Discount amount.

Final price:

=A2-(A2*B2)

Example:

Price = 50,000

Discount = 10%

Discount:

5,000

Final price:

45,000

Tax Calculation formulas list

Tax Calculation formulas list with easy Examples.

75. Tax Calculation

If price is A2 and tax rate is B2:

=A2*B2

Total including tax:

=A2+(A2*B2)

76. SUMPRODUCT

One of the most useful advanced formulas.

Suppose:

Use:

=SUMPRODUCT(B2:B4,C2:C4)

Result:

170500

It effectively calculates:

(2×80000)+(5×1500)+(3×3000)

77. SUMPRODUCT with Conditions

=SUMPRODUCT((A2:A100=”Laptop”)*C2:C100)

This sums values in C where Product = Laptop.

78. COUNTUNIQUE Equivalent

Excel’s modern approach:

=COUNTA(UNIQUE(A2:A100))

Counts distinct values.

79. ISBLANK

Checks whether a cell is empty.

=ISBLANK(A2)

80. ISNUMBER

Checks whether a cell contains a number.

=ISNUMBER(A2)

81. ISTEXT

Checks whether a cell contains text.

=ISTEXT(A2)

82. ISERROR

Checks whether a formula produces an error.

=ISERROR(A2/B2)

83. CELL

Returns information about a cell.

=CELL(“address”,A1)

84. ROW

Returns the row number.

=ROW(A10)

Result:

10

85. COLUMN

Returns the column number.

=COLUMN(C1)

Result:

3

86. ROWS

Counts rows.

=ROWS(A1:A20)

Result:

20

87. COLUMNS

Counts columns.

=COLUMNS(A1:D1)

Result:

4

88. ADDRESS

Creates a cell reference as text.

=ADDRESS(5,3)

Result:

$C$5

89. INDIRECT

Converts text into a cell reference.

=INDIRECT(“A10”)

Returns the value in A10.

90. OFFSET

Returns a reference offset from another cell.

=SUM(OFFSET(A1,0,0,10,1))

This can sum a dynamically sized range.

91. SUBTOTAL

Useful for filtered data.

=SUBTOTAL(9,B2:B100)

9 means SUM.

For average:

=SUBTOTAL(1,B2:B100)

Unlike a normal SUM, SUBTOTAL can ignore filtered-out rows.

92. AGGREGATE

Performs calculations while allowing you to ignore errors, hidden rows, etc.

Example:

=AGGREGATE(9,6,A2:A100)

Here:

• 9 = SUM
• 6 = ignore errors

Text Formulas List

All Text formulas list with solve easy examples.

93. TEXTBEFORE

Modern Excel function.

=TEXTBEFORE(A2,”@”)

For:

ali@gmail.com

Result:

ali

94. TEXTAFTER

=TEXTAFTER(A2,”@”)

Result:

gmail.com

95. TEXTSPLIT

Splits text into separate cells.

=TEXTSPLIT(A2,”,”)

If A2 contains:

PHP,Python,Java,JavaScript

Excel separates the values into cells.

96. LET

Creates variables inside a formula, making complex formulas easier to read and sometimes more efficient.

=LET(

price,A2,

qty,B2,

discount,C2,

price*qty*(1-discount)

)

97. LAMBDA

Allows you to create custom reusable Excel functions.

Example:

=LAMBDA(price,qty,price*qty)(500,4)

Result:

2000

98. SWITCH

Useful when one value has multiple possible results.

=SWITCH(A2,

“PHP”,”Web Development”,

“Python”,”Programming”,

“SEO”,”Digital Marketing”,

“Other”)

99. CHOOSE

Returns a value based on an index number.

=CHOOSE(A2,”Beginner”,”Intermediate”,”Advanced”)

If A2 = 2:

Intermediate

100. Mathematical Trigonometry

Excel also supports functions such as:

=SIN(A2)

=COS(A2)

=TAN(A2)

=ASIN(A2)

=ACOS(A2)

=ATAN(A2)

For degrees:

=SIN(RADIANS(30))

Result:

0.5

101. Financial Formulas

PMT — Loan Payment

=PMT(rate,nper,pv)

Example:

=PMT(10%/12,60,-500000)

Calculates the monthly payment for a 500,000 loan at 10% annual interest over 60 months.

FV — Future Value

=FV(rate,nper,pmt,pv)

Example:

=FV(10%/12,60,-5000,0)

Calculates future value of monthly savings.

PV — Present Value

=PV(rate,nper,pmt)

NPV — Net Present Value

=NPV(10%,B2:B6)

Used in investment analysis.

IRR — Internal Rate of Return

=IRR(B2:B10)

Used to estimate the return rate of an investment.

102. Database Style Formulas

Modern Excel can use:

=DSUM(…)

=DCOUNT(…)

=DAVERAGE(…)

=DMAX(…)

=DMIN(…)

These operate on structured database-like ranges using criteria.

103. Example: Student Result Sheet

Suppose:

Total

In E2:

=SUM(B2:D2)

Average

In F2:

=AVERAGE(B2:D2)

Grade

In G2:

=IFS(

F2>=80,”A”,

F2>=70,”B”,

F2>=60,”C”,

F2>=50,”D”,

TRUE,”F”

)

Status

In H2:

=IF(F2>=50,”Pass”,”Fail”)

Copy all formulas downward.

Salary Formulas list

104. Example: Employee Salary Sheet

Gross Salary

=B2+C2

Tax

=D2*(B2+C2)

Net Salary

=(B2+C2)-((B2+C2)*D2)

105. Example: Sales Commission

Suppose:

Commission rules:

• Below 100,000 → 2%
• 100,000–249,999 → 5%
• 250,000–499,999 → 7%
• 500,000+ → 10%

Formula:

=IFS(

B2<100000,B2*2%,

B2<250000,B2*5%,

B2<500000,B2*7%,

B2>=500000,B2*10%

)

106. Example: Attendance System

Suppose:

Attendance Percentage

=B2/C2

Format as percentage.

Eligibility

If minimum attendance is 75%:

=IF(D2>=75%,”Eligible”,”Not Eligible”)

107. Example: Inventory Management

Formula:

=IF(B2<=C2,”Reorder”,”Available”)

108. Example: Invoice

Suppose:

Line Total

=B2*C2

Subtotal

=SUM(D2:D4)

Discount

=D5*10%

Tax

=(D5-D6)*18%

Grand Total

=D5-D6+D7

109. Absolute, Relative & Mixed References

This is extremely important when copying formulas.

Relative

=A1*B1

When copied down:

=A2*B2

Absolute

=A1*$B$1

$B$1 remains fixed.

Mixed

=A1*B$1

Row 1 stays fixed.

=A1*$B1

Column B stays fixed.

110. Most Important Excel Functions to Master

For practical Excel work, prioritize these:

Beginner

SUM

AVERAGE

MIN

MAX

COUNT

COUNTA

ROUND

IF

Intermediate

COUNTIF

COUNTIFS

SUMIF

SUMIFS

AVERAGEIF

AVERAGEIFS

IFERROR

AND

OR

Lookup

XLOOKUP

VLOOKUP

HLOOKUP

INDEX

MATCH

Text

LEFT

RIGHT

MID

LEN

TRIM

UPPER

LOWER

PROPER

CONCAT

TEXTJOIN

TEXTBEFORE

TEXTAFTER

TEXTSPLIT

Date & Time

TODAY

NOW

DATE

YEAR

MONTH

DAY

DAYS

DATEDIF

NETWORKDAYS

WORKDAY

Dynamic Arrays

FILTER

SORT

SORTBY

UNIQUE

SEQUENCE

TRANSPOSE

Advanced

SUMPRODUCT

SUBTOTAL

AGGREGATE

LET

LAMBDA

INDIRECT

OFFSET

Financial

PMT

FV

PV

NPV

IRR

Important: Functions such as XLOOKUP, FILTER, SORT, UNIQUE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, LET, and LAMBDA require relatively recent versions of Excel/Microsoft 365. Older Excel versions may not support them.