Advance Excel Formulas with Practical Examples

Advance Excel formulas with practical examples: Microsoft Excel is a powerful tool used in almost every industry for data analysis, accounting, reporting, and more. Knowing the right formulas can save hours of manual work.

Below is a list of popular Excel formulas with simple explanations and examples to help you understand how and when to use them.

Advance Excel formulas with practical examples

1. SUM()

Description	Adds numeric values from a range of cells
Inputs	A1 = 10, A2 = 15, A3 = 20
Formula	=sum(A1:A3)
Output	45

2. Average()

Description	Calculates the average of the selected numbers
Inputs	A1 = 12, A2 = 18, A3 = 30
Formula	=AVERAGE(A1:A3)
Output	20

3. MIN()

Description	Returns the smallest value in the range
Inputs	A1 = 7, A2 = 3, A3 = 12
Formula	=MIN(A1:A3)
Output	3

4. MAX()

Description	Returns the largest value in the range
Inputs	A1 = 7, A2 = 3, A3 = 12
Formula	=MAX(A1:A3)
Output	12

5. COUNT()

Description	Counts how many numeric values are in a range
Inputs	A1 = 10, A2 = “Text”, A3 = 30
Formula	=COUNT(A1:A3)
Output	2

6. IF()

Description	Returns one value if condition is TRUE, another if FALSE
Inputs	A1 = 75
Formula	=IF(A1>50,”Pass”, “Fail”)
Output	Pass

7. AND()

Description	Returns TRUE only if all conditions are TRUE
Inputs	A1 = 20, B1 = 50
Formula	=AND(A>10, B<60)
Output	TRUE

8. OR()

Description	Returns TRUE if at least one condition is TRUE
Inputs	A1 = 5, B1 = 150
Formula	=OR(A1>1, B1<100)
Output	TRUE

9. NOT()

Description	Reverses the logical value (TRUE becomes FALSE, and vice versa)
Inputs	A1 = 100
Formula	=NOT(A1=100)
Output	FALSE

10. TODAY()

Description	Returns the current system date
Inputs	—
Formula	=TODAY()
Output	09-Jul-2025 (example)

11. NOW()

Description	Returns current date and time
Inputs	—
Formula	=NOW()
Output	09-Jul-2025 09:40 PM (example)

12. DAY()

Description	Extracts the day from a date
Inputs	A1 = 09/07/2025
Formula	=DAY(A1)
Output	9

13. MONTH()

Description	Extracts the month number from a date
Inputs	A1 = 09/07/2025
Formula	=MONTH(A1)
Output	7

14. YEAR()

Description	Extracts the year from a date
Inputs	A1 = 09/07/2025
Formula	=YEAR(A1)
Output	2025

15. CONCAT()

Description	Combines two or more text strings
Inputs	A1 = “Good”, B1 = “Morning”
Formula	=CONCAT(A1,” “, B1)
Output	Good Morning

16. TEXTJOIN()

Description	Joins text with a specified delimiter
Inputs	A1 = “Red”, B1 = “Blue”, C1 = “Green”
Formula	=TEXTJOIN(“,”,TRUE,A1:C1)
Output	Red, Blue, Green

17. LEFT()

Description	Returns first N characters from a string
Inputs	A1 = “ExcelTips”
Formula	=LEFT(A1, 5)
Output	Excel

18. RIGHT()

Description	Returns last N characters from a string
Inputs	A1 = “ExcelTips”
Formula	=RIGHT(A1, 4)
Output	Tips

19. LEN()

Description	Counts number of characters in a cell
Inputs	A1 = “Excel”
Formula	=LEN(A1)
Output	5

20. VLOOKUP

Description	Searches for a value vertically and returns corresponding data
Inputs	Table: A2:C4 → 101, John, A+A1=101
Formula	=VLOOKUP(A1, A2:C4, 2, FALSE)
Output	John

Complete Example of Vlookup

21. HLOOKUP()

Description	Searches a value in top row and returns value from a specific row
Inputs	Row 1: Math, EngRow 2: 95, 88
Formula	=HLOOKUP(“MATH”, A1:B2, 2, FALSE)
Output	95

22. INDEX()

Description	Returns value from specific row and column in a range
Inputs	Range: A1:C32nd row, 3rd column = “Z”
Formula	=INDEX(A1:C3, 2,3)
Output	Z

23. MATCH()

Description	Returns the position of a value in a list
Inputs	A1:A5 = 10, 20, 30, 40, 50
Formula	=MATCH(30, A1:A5, 0)
Output	3

24. IFERROR()

Description	Returns custom value if formula has an error
Inputs	A1 = 10, B1 = 0
Formula	=IFERROR(A1/B1,”ERROR”)
Output	Error

25. ISERROR()

Description	Checks whether a cell contains an error
Inputs	A1 = #DIV/0!
Formula	=ISERROR(A1)
Output	TRUE

Must Read: Excel Shortcuts