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).
