MS Excel Formulas, With Their Syntax, Examples, And

Detailed Explanations

1. SUM

 Syntax: =SUM(number1, [number2], ...)

 Example: =SUM(A1:A10)

 Explanation: Adds all the numbers in the specified range (A1 to A10).

2. AVERAGE

 Syntax: =AVERAGE(number1, [number2], ...)

 Example: =AVERAGE(A1:A10)

 Explanation: Calculates the arithmetic mean of the range.

3. IF

 Syntax: =IF(logical_test, value_if_true, value_if_false)

 Example: =IF(A1>50, "Pass", "Fail")

 Explanation: Checks a condition and returns "Pass" if true or "Fail" otherwise.

4. VLOOKUP

 Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

 Example: =VLOOKUP(101, A2:C10, 2, FALSE)

 Explanation: Searches for a value in the first column and returns data from the
specified column in the same row.

5. HLOOKUP

 Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

 Example: =HLOOKUP("Q2", A1:D3, 2, FALSE)

 Explanation: Looks up a value in the first row and returns a value from the specified row.

6. INDEX

 Syntax: =INDEX(array, row_num, [column_num])

 Example: =INDEX(A1:C10, 2, 3)

 Explanation: Retrieves the value from the 2nd row and 3rd column of the specified
range.
7. MATCH

 Syntax: =MATCH(lookup_value, lookup_array, [match_type])

 Example: =MATCH(50, A1:A10, 0)

 Explanation: Returns the position of a value within a range.

8. LEN

 Syntax: =LEN(text)

 Example: =LEN("Excel")

 Explanation: Returns the number of characters in a string (result: 5).

9. LEFT

 Syntax: =LEFT(text, num_chars)

 Example: =LEFT("Excel", 3)

 Explanation: Extracts the first 3 characters ("Exc").

10. RIGHT

 Syntax: =RIGHT(text, num_chars)

 Example: =RIGHT("Excel", 2)

 Explanation: Extracts the last 2 characters ("el").

11. CONCATENATE (or CONCAT)

 Syntax: =CONCATENATE(text1, [text2], ...)

 Example: =CONCATENATE(A1, " ", B1)

 Explanation: Joins multiple text strings into one.

12. TRIM

 Syntax: =TRIM(text)

 Example: =TRIM(" Excel ")

 Explanation: Removes extra spaces, leaving only single spaces between words.
13. UPPER

 Syntax: =UPPER(text)

 Example: =UPPER("excel")

 Explanation: Converts text to uppercase ("EXCEL").

14. LOWER

 Syntax: =LOWER(text)

 Example: =LOWER("EXCEL")

 Explanation: Converts text to lowercase ("excel").

15. PROPER

 Syntax: =PROPER(text)

 Example: =PROPER("microsoft excel")

 Explanation: Capitalizes the first letter of each word ("Microsoft Excel").

16. NOW

 Syntax: =NOW()

 Example: =NOW()

 Explanation: Returns the current date and time.

17. TODAY

 Syntax: =TODAY()

 Example: =TODAY()

 Explanation: Returns the current date without time.

18. ROUND

 Syntax: =ROUND(number, num_digits)

 Example: =ROUND(2.567, 2)

 Explanation: Rounds a number to the specified number of digits (result: 2.57).

19. ROUNDUP

 Syntax: =ROUNDUP(number, num_digits)

 Example: =ROUNDUP(2.123, 2)

 Explanation: Always rounds up (result: 2.13).

20. ROUNDDOWN

 Syntax: =ROUNDDOWN(number, num_digits)

 Example: =ROUNDDOWN(2.567, 2)

 Explanation: Always rounds down (result: 2.56).

21. COUNT

 Syntax: =COUNT(value1, [value2], ...)

 Example: =COUNT(A1:A10)

 Explanation: Counts numeric values in a range.

22. COUNTA

 Syntax: =COUNTA(value1, [value2], ...)

 Example: =COUNTA(A1:A10)

 Explanation: Counts all non-empty cells in a range.

23. COUNTIF

 Syntax: =COUNTIF(range, criteria)

 Example: =COUNTIF(A1:A10, ">50")

 Explanation: Counts cells in a range that meet the specified condition.

24. TEXT

 Syntax: =TEXT(value, format_text)

 Example: =TEXT(A1, "MM/DD/YYYY")

 Explanation: Converts a value to text in a specified format.

25. SEARCH

 Syntax: =SEARCH(find_text, within_text, [start_num])

 Example: =SEARCH("x", "Excel")

 Explanation: Finds the position of a character or substring in a text string.

26. REPLACE

 Syntax: =REPLACE(old_text, start_num, num_chars, new_text)

 Example: =REPLACE("Excel", 1, 2, "Sp")

 Explanation: Replaces a part of the text. This example replaces the first two characters
with "Sp" (result: "Spel").

27. SUBSTITUTE

 Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

 Example: =SUBSTITUTE("Excel is great", "great", "awesome")

 Explanation: Substitutes "great" with "awesome" in the text (result: "Excel is

awesome").

28. ISNUMBER

 Syntax: =ISNUMBER(value)

 Example: =ISNUMBER(A1)

 Explanation: Returns TRUE if the value in A1 is numeric, otherwise returns FALSE.

29. ISTEXT

 Syntax: =ISTEXT(value)

 Example: =ISTEXT(A1)

 Explanation: Returns TRUE if the value in A1 is text, otherwise FALSE.

30. ISBLANK

 Syntax: =ISBLANK(value)

 Example: =ISBLANK(A1)

 Explanation: Returns TRUE if the cell is empty, otherwise FALSE.

31. POWER

 Syntax: =POWER(number, power)

 Example: =POWER(3, 2)

 Explanation: Raises the number to the specified power (result: 32=93^2 = 932=9).

32. SQRT

 Syntax: =SQRT(number)

 Example: =SQRT(16)

 Explanation: Returns the square root of the number (result: 4).

33. MOD

 Syntax: =MOD(number, divisor)

 Example: =MOD(10, 3)

 Explanation: Returns the remainder of the division (result: 1).

34. ABS

 Syntax: =ABS(number)

 Example: =ABS(-7)

 Explanation: Returns the absolute value of the number (result: 7).

35. CEILING

 Syntax: =CEILING(number, significance)

 Example: =CEILING(4.2, 1)

 Explanation: Rounds the number up to the nearest multiple of the significance (result:
5).

36. FLOOR

 Syntax: =FLOOR(number, significance)

  Example: =FLOOR(4.8, 1)

 Explanation: Rounds the number down to the nearest multiple of the significance
(result: 4).

37. CONCATENATE (or CONCAT)

 Syntax: =CONCATENATE(text1, [text2], ...)

 Example: =CONCATENATE("Hello", " ", "World!")

 Explanation: Combines text strings into one (result: "Hello World!").

38. TEXTJOIN

 Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

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

 Explanation: Joins text in the range A1 to A3, separated by a comma, and ignores empty
cells.

39. SORT

 Syntax: =SORT(array, [sort_index], [sort_order])

 Example: =SORT(A1:A10, 1, 1)

 Explanation: Sorts the range in ascending order.

40. SEQUENCE

 Syntax: =SEQUENCE(rows, [columns], [start], [step])

 Example: =SEQUENCE(5, 1, 1, 1)

 Explanation: Generates a sequence of 5 numbers starting from 1.

41. RAND

 Syntax: =RAND()

 Example: =RAND()

 Explanation: Generates a random number between 0 and 1.

42. RANDBETWEEN

 Syntax: =RANDBETWEEN(bottom, top)

 Example: =RANDBETWEEN(1, 100)

 Explanation: Generates a random integer between the specified bottom and top values.

43. PMT

 Syntax: =PMT(rate, nper, pv, [fv], [type])

 Example: =PMT(5%/12, 60, -10000)

 Explanation: Calculates the payment for a loan based on constant payments and
interest rate.

44. XLOOKUP

 Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],

[match_mode], [search_mode])

 Example: =XLOOKUP(101, A1:A10, B1:B10, "Not Found")

 Explanation: Searches for a value in a range and returns the corresponding value from
another range.

45. SUBTOTAL

 Syntax: =SUBTOTAL(function_num, ref1, [ref2], ...)

 Example: =SUBTOTAL(9, A1:A10)

 Explanation: Returns a subtotal in a range based on the function specified by

function_num (e.g., 9 for SUM).