The Ultimate Guide to the
Top 100 Excel Functions
MAKE YOUR DATA WORK FOR YOU
POOJA PAWAR
1. Mathematical and Trigonometric Functions
1. SUM
o Syntax: SUM(number1, [number2], …)
o Adds all the numbers in a range of cells.
o Example: SUM(A1:A5) adds the values in cells A1 to A5.
2. AVERAGE
o Syntax: AVERAGE(number1, [number2], …)
o Returns the arithmetic mean of the numbers provided.
o Example: AVERAGE(B1:B5) calculates the average of the values in cells B1 to B5.
3. SUMIF
o Syntax: SUMIF(range, criteria, [sum_range])
o Adds cells specified by a given condition or criteria.
o Example: SUMIF(A1:A5, ">10") adds the values in cells A1 to A5 that are greater
than 10.
4. SUMIFS
o Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
o Adds cells that meet multiple criteria.
o Example: SUMIFS(B1:B5, A1:A5, ">10", C1:C5, "<20") adds values in B1 where A1 is
greater than 10 and C1 is less than 20.
5. COUNT
o Syntax: COUNT(value1, [value2], …)
o Counts the number of cells that contain numbers.
o Example: COUNT(A1:A10) counts the number of cells in the range A1 to A10 that
contain numbers.
POOJA PAWAR
6. COUNTA
o Syntax: COUNTA(value1, [value2], …)
o Counts the number of cells that are not empty.
o Example: COUNTA(A1:A10) counts the number of cells in the range A1 to A10 that
are not empty.
7. COUNTIF
o Syntax: COUNTIF(range, criteria)
o Counts the number of cells that meet a single criterion.
o Example: COUNTIF(A1:A10, ">5") counts the number of cells in the range A1 to
A10 that are greater than 5.
8. COUNTIFS
o Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
o Counts the number of cells that meet multiple criteria.
o Example: COUNTIFS(A1:A10, ">5", B1:B10, "<10") counts the number of cells where
A1 is greater than 5 and B1 is less than 10.
9. PRODUCT
o Syntax: PRODUCT(number1, [number2], …)
o Multiplies all the numbers given as arguments.
o Example: PRODUCT(A1:A5) multiplies the values in cells A1 to A5.
10. SQRT
o Syntax: SQRT(number)
o Returns the square root of a number.
o Example: SQRT(16) returns 4.
POOJA PAWAR
11. MOD
o Syntax: MOD(number, divisor)
o Returns the remainder after a number is divided by a divisor.
o Example: MOD(10, 3) returns 1.
12. ROUND
o Syntax: ROUND(number, num_digits)
o Rounds a number to a specified number of digits.
o Example: ROUND(3.14159, 2) returns 3.14.
13. ROUNDUP
o Syntax: ROUNDUP(number, num_digits)
o Rounds a number up, away from zero, to a specified number of digits.
o Example: ROUNDUP(3.14159, 2) returns 3.15.
14. ROUNDDOWN
o Syntax: ROUNDDOWN(number, num_digits)
o Rounds a number down, toward zero, to a specified number of digits.
o Example: ROUNDDOWN(3.14159, 2) returns 3.14.
15. INT
o Syntax: INT(number)
o Rounds a number down to the nearest integer.
o Example: INT(5.9) returns 5.
16. ABS
o Syntax: ABS(number)
o Returns the absolute value of a number.
o Example: ABS(-5) returns 5.
POOJA PAWAR
17. POWER
o Syntax: POWER(number, power)
o Returns the result of a number raised to a power.
o Example: POWER(2, 3) returns 8.
18. RAND
o Syntax: RAND()
o Returns a random number between 0 and 1.
o Example: RAND() might return 0.543.
19. RANDBETWEEN
o Syntax: RANDBETWEEN(bottom, top)
o Returns a random integer between the numbers you specify.
o Example: RANDBETWEEN(1, 10) returns a random number between 1 and 10.
20. PI
o Syntax: PI()
o Returns the value of π (Pi).
o Example: PI() returns 3.14159265358979.
POOJA PAWAR
2. Lookup and Reference Functions
21. VLOOKUP
o Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
o Searches for a value in the first column of a table and returns a value in the
same row from a specified column.
o Example: VLOOKUP("John", A2:C10, 2, FALSE) finds "John" in column A and returns
the value from column B.
22. HLOOKUP
o Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
o Searches for a value in the first row of a table and returns a value in the same
column from a specified row.
o Example: HLOOKUP("Q1", A1:E5, 3, FALSE) finds "Q1" in the first row and returns
the value from the third row.
23. INDEX
o Syntax: INDEX(array, row_num, [column_num])
o Returns the value of an element in a table or array, selected by the row and
column number.
o Example: INDEX(A1:C3, 2, 2) returns the value in the second row and second
column of the range A1.
24. MATCH
o Syntax: MATCH(lookup_value, lookup_array, [match_type])
o Searches for a specified value in a range and returns its relative position.
o Example: MATCH(25, A1:A10, 0) finds the position of the value 25 in the range A1.
POOJA PAWAR
25. XLOOKUP
o Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
o Returns a value from the corresponding position in another range o rararray.
o Example: XLOOKUP(“John”,A2:A10,B2:B10,”Not Found).
26. LOOKUP
o Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])
o Searches for a value in a vector and returns a value from the same position in
another vector.
o Example: LOOKUP(4, A1:A5, B1:B5) finds 4 in the range A1 and returns the
corresponding value from B1.
27. OFFSET
o Syntax: OFFSET(reference, rows, cols, [height], [width])
o Returns a reference to a range that is a specified number of rows and columns
from a cell or range.
o Example: OFFSET(A1, 2, 2) returns the reference to the cell that is two rows down
and two columns to the right of A1.
28. TRANSPOSE
o Syntax: TRANSPOSE(array)
o Changes the orientation of a range of cells (rows become columns, and vice
versa).
o Example: TRANSPOSE(A1:B2) converts rows to columns and vice versa.
29. HYPERLINK
o Syntax: HYPERLINK(link_location, [friendly_name])
o Creates a shortcut or jump to another location in the workbook, or an external
file.
o Example: HYPERLINK("http://example.com", "Visit Example") creates a clickable link to
"http://example.com".
POOJA PAWAR
30. ADDRESS
o Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
o Creates a cell address as text, given specified row and column numbers.
o Example: ADDRESS(1, 1) returns "A1".
POOJA PAWAR
3. Text Functions
31. CONCAT
o Syntax: CONCAT(text1, [text2], …)
o Joins several text strings into one string.
o Example: CONCAT("Hello", " ", "World") returns "Hello World".
32. TEXT
o Syntax: TEXT(value, format_text)
o Formats a number and converts it to text.
o Example: TEXT(1234.56, "$#,##0.00") returns "$1,234.56".
33. LEFT
o Syntax: LEFT(text, [num_chars])
o Extracts the leftmost characters from a text string.
o Example: LEFT("Excel", 2) returns "Ex".
34. RIGHT
o Syntax: RIGHT(text, [num_chars])
o Extracts the rightmost characters from a text string.
o Example: RIGHT("Excel", 2) returns "el".
35. MID
o Syntax: MID(text, start_num, num_chars)
o Extracts characters from the middle of a text string.
o Example: MID("Excel", 2, 3) returns "xce".
POOJA PAWAR
36. LEN
o Syntax: LEN(text)
o Returns the number of characters in a text string.
o Example: LEN("Excel") returns 5.
37. FIND
o Syntax: FIND(find_text, within_text, [start_num])
o Finds the position of a substring within another text string.
o Example: FIND("e", "Excel") returns 1.
38. SEARCH
o Syntax: SEARCH(find_text, within_text, [start_num])
o Similar to FIND, but not case-sensitive.
o Example: SEARCH("e", "Excel") returns 1.
39. TRIM
o Syntax: TRIM(text)
o Removes extra spaces from text, leaving only single spaces between words.
o Example: TRIM(" Excel ") returns "Excel".
40. UPPER
o Syntax: UPPER(text)
o Converts text to uppercase.
o Example: UPPER("Excel") returns "EXCEL".
41. LOWER
o Syntax: LOWER(text)
o Converts text to lowercase.
o Example: LOWER("Excel") returns "excel".
POOJA PAWAR
42. PROPER
o Syntax: PROPER(text)
o Capitalizes the first letter of each word in a text string.
o Example: PROPER("hello world") returns "Hello World".
43. REPLACE
o Syntax: REPLACE(old_text, start_num, num_chars, new_text)
o Replaces part of a text string with a different text string.
o Example: REPLACE("Excel", 1, 2, "Ax") returns "Axcel".
44. SUBSTITUTE
o Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])
o Substitutes new text for old text in a string.
o Example: SUBSTITUTE("Excel", "e", "a") returns "Excal".
POOJA PAWAR
4. Date and Time Functions
45. TODAY
o Syntax: TODAY()
o Returns the current date.
o Example: TODAY() returns today's date.
46. NOW
o Syntax: NOW()
o Returns the current date and time.
o Example: NOW() returns the current date and time.
47. DATE
o Syntax: DATE(year, month, day)
o Returns the serial number of a specific date.
o Example: DATE(2024, 9, 17) returns the serial number representing that date.
48. DATEVALUE
o Syntax: DATEVALUE(date_text)
o Converts a date in text format to a serial number.
o Example: DATEVALUE("1/1/2024") returns the serial number of the date.
49. DAY
o Syntax: DAY(serial_number)
o Returns the day of a date, as a number from 1 to 31.
o Example: DAY("1/1/2024") returns 1.
POOJA PAWAR
50. MONTH
o Syntax: MONTH(serial_number)
o Returns the month of a date, as a number from 1 (January) to 12 (December).
o Example: MONTH("1/1/2024") returns 1.
51. YEAR
o Syntax: YEAR(serial_number)
o Returns the year of a date.
o Example: YEAR("1/1/2024") returns 2024.
52. HOUR
o Syntax: HOUR(serial_number)
o Returns the hour of a time value, as a number from 0 (12:00 AM) to 23 (11:00
PM).
o Example: HOUR("2:30 PM") returns 14.
53. MINUTE
o Syntax: MINUTE(serial_number)
o Returns the minute of a time value, as a number from 0 to 59.
o Example: MINUTE("2:30 PM") returns 30.
54. SECOND
o Syntax: SECOND(serial_number)
o Returns the second of a time value, as a number from 0 to 59.
o Example: SECOND("2:30:15 PM") returns 15.
55. WEEKDAY
o Syntax: WEEKDAY(serial_number, [return_type])
o Returns the day of the week corresponding to a date.
o Example: WEEKDAY("1/1/2024") returns 2 (Monday).
POOJA PAWAR
56. WORKDAY
o Syntax: WORKDAY(start_date, days, [holidays])
o Returns the date before or after a specified number of workdays.
o Example: WORKDAY("1/1/2024", 10) returns the date 10 workdays after January 1,
2024.
57. NETWORKDAYS
o Syntax: NETWORKDAYS(start_date, end_date, [holidays])
o Returns the number of whole workdays between two dates.
o Example: NETWORKDAYS("1/1/2024", "1/10/2024") returns the number of workdays
between the two dates.
POOJA PAWAR
5. Logical Functions
58. IF
o Syntax: IF(logical_test, value_if_true, value_if_false)
o Returns one value if a condition is TRUE and another value if it is FALSE.
o Example: IF(A1 > 10, "Yes", "No") returns "Yes" if A1 is greater than 10, otherwise
"No".
59. IFERROR
o Syntax: IFERROR(value, value_if_error)
o Returns a value you specify if a formula evaluates to an error; otherwise, it
returns the result of the formula.
o Example: IFERROR(A1/B1, "Error") returns "Error" if B1 is 0.
60. AND
o Syntax: AND(logical1, [logical2], …)
o Returns TRUE if all arguments are TRUE.
o Example: AND(A1 > 10, B1 < 5) returns TRUE if both conditions are met.
61. OR
o Syntax: OR(logical1, [logical2], …)
o Returns TRUE if any argument is TRUE.
o Example: OR(A1 > 10, B1 < 5) returns TRUE if either condition is met.
62. NOT
o Syntax: NOT(logical)
o Reverses the logical value of its argument.
o Example: NOT(A1 > 10) returns TRUE if A1 is not greater than 10.
POOJA PAWAR
6. Financial Functions
63. PMT
o Syntax: PMT(rate, nper, pv, [fv], [type])
o Returns the payment for a loan based on constant payments and a constant
interest rate.
o Example: PMT(5%/12, 60, 10000) returns the monthly payment for a loan of
$10,000 over 60 months at 5% annual interest.
64. PV
o Syntax: PV(rate, nper, pmt, [fv], [type])
o Returns the present value of an investment.
o Example: PV(5%/12, 60, -200) returns the present value of receiving $200 monthly
for 60 months at 5% annual interest.
65. FV
o Syntax: FV(rate, nper, pmt, [pv], [type])
o Returns the future value of an investment.
o Example: FV(5%/12, 60, -200) returns the future value of receiving $200 monthly
for 60 months at 5% annual interest.
66. RATE
o Syntax: RATE(nper, pmt, pv, [fv], [type], [guess])
o Returns the interest rate per period of an annuity.
o Example: RATE(60, -200, 10000) calculates the monthly interest rate of a loan of
$10,000 with a $200 payment over 60 months.
POOJA PAWAR
67. NPV
o Syntax: NPV(rate, value1, [value2], …)
o Returns the net present value of an investment based on a series of periodic
cash flows and a discount rate.
o Example: NPV(5%, -10000, 2000, 3000, 4000) calculates the NPV of cash flows.
68. IRR
o Syntax: IRR(values, [guess])
o Returns the internal rate of return for a series of cash flows.
o Example: IRR(A1:A5) returns the IRR for the cash flows in cells A1 to A5.
69. XIRR
o Syntax: XIRR(values, dates, [guess])
o Returns the internal rate of return for a schedule of cash flows that is not
necessarily periodic.
o Example: XIRR(A1:A5, B1:B5) calculates the IRR for cash flows in A1 with
corresponding dates in B1.
POOJA PAWAR
7. Statistical Functions
70. AVERAGEIF
o Syntax: AVERAGEIF(range, criteria, [average_range])
o Returns the average of the cells that meet a single criterion.
o Example: AVERAGEIF(A1:A10, ">10") returns the average of cells in A1 that are
greater than 10.
71. MEDIAN
o Syntax: MEDIAN(number1, [number2], …)
o Returns the median of the given numbers.
o Example: MEDIAN(1, 3, 5, 7) returns 4.
72. MODE.SNGL
o Syntax: MODE.SNGL(number1, [number2], …)
o Returns the most frequently occurring number in a data set.
o Example: MODE.SNGL(1, 2, 2, 3) returns 2.
73. STDEV.P
o Syntax: STDEV.P(number1, [number2], …)
o Calculates standard deviation based on the entire population.
o Example: STDEV.P(1, 2, 3, 4) returns the standard deviation of the numbers 1, 2,
3, and 4.
74. STDEV.S
o Syntax: STDEV.S(number1, [number2], …)
o Calculates standard deviation based on a sample.
o Example: STDEV.S(1, 2, 3, 4) returns the standard deviation for the sample data.
75. VAR.P
o Syntax: VAR.P(number1, [number2], …)
POOJA PAWAR
o Calculates variance based on the entire population.
o Example: VAR.P(1, 2, 3, 4) returns the variance of the numbers 1, 2, 3, and 4.
76. VAR.S
o Syntax: VAR.S(number1, [number2], …)
o Calculates variance based on a sample.
o Example: VAR.S(1, 2, 3, 4) returns the variance for the sample data.
77. PERCENTILE.EXC
o Syntax: PERCENTILE.EXC(array, k)
o Returns the k-th percentile of values in a range, excluding the endpoints.
o Example: PERCENTILE.EXC(A1:A10, 0.9) returns the 90th percentile of values in A1.
78. PERCENTILE.INC
o Syntax: PERCENTILE.INC(array, k)
o Returns the k-th percentile of values in a range, including the endpoints.
o Example: PERCENTILE.INC(A1:A10, 0.9) returns the 90th percentile of values in A1.
79. QUARTILE.EXC
o Syntax: QUARTILE.EXC(array, quart)
o Returns the quartile of a data set, based on percentile values, excluding the
median.
o Example: QUARTILE.EXC(A1:A10, 2) returns the second quartile (median) of A1.
80. QUARTILE.INC
o Syntax: QUARTILE.INC(array, quart)
o Returns the quartile of a data set, based on percentile values, including the
median.
o Example: QUARTILE.INC(A1:A10, 2) returns the second quartile (median) of A1.
POOJA PAWAR
8. Information Functions
81. ISNUMBER
o Syntax: ISNUMBER(value)
o Checks whether a value is a number.
o Example: ISNUMBER(A1) returns TRUE if A1 contains a number.
82. ISBLANK
o Syntax: ISBLANK(value)
o Checks whether a cell is empty.
o Example: ISBLANK(A1) returns TRUE if A1 is empty.
83. ISERROR
o Syntax: ISERROR(value)
o Checks whether a value is an error.
o Example: ISERROR(A1/B1) returns TRUE if the formula results in an error.
84. ISEVEN
o Syntax: ISEVEN(number)
o Checks whether a number is even.
o Example: ISEVEN(4) returns TRUE.
85. ISODD
o Syntax: ISODD(number)
o Checks whether a number is odd.
o Example: ISODD(3) returns TRUE.
86. ISTEXT
o Syntax: ISTEXT(value)
o Checks whether a value is text.
POOJA PAWAR
o Example: ISTEXT(A1) returns TRUE if A1 contains text.
87. ISLOGICAL
o Syntax: ISLOGICAL(value)
o Checks whether a value is logical (TRUE or FALSE).
o Example: ISLOGICAL(A1) returns TRUE if A1 is logical.
POOJA PAWAR
9. Array Functions
88. ARRAYFORMULA
o Syntax: ARRAYFORMULA(array_formula)
o Allows functions to return multiple values.
o Example: ARRAYFORMULA(A1:A10 + B1:B10) adds corresponding elements from
two arrays.
89. FILTER
o Syntax: FILTER(array, include, [if_empty])
o Filters a range of data based on a condition.
o Example: FILTER(A1:A10, B1:B10 > 5) returns values in A1 where B1 is greater than
5.
90. UNIQUE
o Syntax: UNIQUE(array)
o Returns unique values from a range or array.
o Example: UNIQUE(A1:A10) returns the unique values from A1 to A10.
91. SORT
o Syntax: SORT(array, [sort_index], [sort_order])
o Sorts the contents of a range or array.
o Example: SORT(A1:A10, 1, TRUE) sorts A1
in ascending order.
92. SEQUENCE
o Syntax: SEQUENCE(rows, [columns], [start], [step])
o Generates a sequence of numbers in an array.
o Example: SEQUENCE(5) generates a vertical array of 5 numbers (1, 2, 3, 4, 5).
POOJA PAWAR
93. SPILL
o Syntax: SPILL(reference)
o Refers to the range of spilled values from a dynamic array formula.
o Example: SUM(SPILL(A1)) sums the dynamic array starting at A1.
POOJA PAWAR
10. Database Functions
94. DAVERAGE
o Syntax: DAVERAGE(database, field, criteria)
o Returns the average of selected database entries.
o Example: DAVERAGE(A1:C10, "Sales", E1:E2) returns the average sales value
meeting criteria in E1.
95. DCOUNT
o Syntax: DCOUNT(database, field, criteria)
o Counts the cells containing numbers in a database column that meets specified
criteria.
o Example: DCOUNT(A1:C10, "Sales", E1:E2) counts the number of sales entries
meeting the criteria in E1.
96. DSUM
o Syntax: DSUM(database, field, criteria)
o Adds the numbers in a database column that meets specified criteria.
o Example: DSUM(A1:C10, "Sales", E1:E2) adds the sales values meeting criteria in E1.
97. DGET
o Syntax: DGET(database, field, criteria)
o Extracts a single value from a database that matches specified criteria.
o Example: DGET(A1:C10, "Sales", E1:E2) returns the single sales value meeting
criteria in E1.
POOJA PAWAR
11. Cube Functions
98. CUBEVALUE
o Syntax: CUBEVALUE(connection, member_expression1, [member_expression2], …)
o Returns an aggregated value from a cube.
o Example: CUBEVALUE("Sales", "[Products].[All Products].[Bikes]") returns the value for
bikes from a cube data source.
99. CUBEMEMBER
o Syntax: CUBEMEMBER(connection, member_expression, [caption])
o Returns a member from a cube.
o Example: CUBEMEMBER("Sales", "[Products].[All Products].[Bikes]") returns the "Bikes"
member.
100. CUBERANKEDMEMBER
o Syntax: CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
o Returns the nth or ranked member in a set.
o Example: CUBERANKEDMEMBER("Sales", "[Top Products]", 1) returns the top-
ranked product.
POOJA PAWAR
